Any better, more idiomatic way to convert SQL ResultSet to a Scala List or other collection type?

Question

I'm using the following naive code to convert a ResultSet to a Scala List:

val rs = pstmt.executeQuery()
var nids = List[String]()
while (rs.next()) {
  nids = nids :+ rs.getString(1)
}
rs.close()

Is there a better approach, something more idiomatic to Scala, that doesn't require using a mutable object?

Why don't you use a mapper library like Slick or Quill? It would make your life easier! — joesan, Jan 19 '18 at 05:41
I use it in Spark executors, so I prefer synchronous mode; and as I need to use the PostGIS extension to Postgres, a Plain SQL is much easier to start with. So I come back to old JDBC connections. — user6502167, Jan 19 '18 at 06:00
Dont know it helps but use this query.execute query.getResultList.asScala.toList — Som Bhattacharyya, Jan 19 '18 at 06:59

score 14 · Accepted Answer · answered Jan 19 '18 at 07:32

14

Why don't you try this:

new Iterator[String] {
  def hasNext = resultSet.next()
  def next() = resultSet.getString(1)
}.toStream

Taken from this answer here

answered Jan 19 '18 at 07:32

SCouto

7,808
5
32
49

1

When I use this function, and then "map" over the Iterator (to create an object from the columns returned - (or use a for-comprehension /yield) and then tack on a .toVector - my vector always ends up missing the first record. – Gavin Baumanis Mar 09 '20 at 11:12
How do I convert more than one columns from the resultset into Arrays? next() would remove elements from the Iterator – davidzxc574 Aug 05 '20 at 07:22
You can return a Tuple in next => def next() = (resultSet.getString(1), resultSet.getString(2)) – SCouto Aug 11 '20 at 09:48
@GavinBaumanis is right :I am doing new Iterator[Employee] { def hasNext = rs.next() def next() = Employee(rs.getString("name"), rs.getInt("age")) }.toStream. and it is skipping one record – user9920500 Feb 03 '21 at 09:06

score 8 · Answer 2 · answered Oct 02 '18 at 20:39

8

I have a similar problem and my solution is:

Iterator.from(0).takeWhile(_ => rs.next()).map(_ => rs.getString(1)).toList

Hope that will help.

answered Oct 02 '18 at 20:39

Michal Przysucha

1,021
11
13

I think this way of doing, skips one record – user9920500 Feb 03 '21 at 09:09

Akash Sethi · Answer 3 · 2018-01-19T07:50:26.753

0

Using ORM tool like slick or Quill as mention in comment section is considered to be better approach.

If you want to use the Scala Code for processing the ResultSet.You can use the tailRecursion.

@scala.annotation.tailrec
def getResult(resultSet: ResultSet, list: List[String] = Nil): List[String] = {
  if (resultSet.next()) {
    val value = resultSet.getString(0)
    getResult(resultSet, value :: list)
  }
  else {
    list
  }
}

This method returns the list of string that contains column value at 0th position. This process is pure immutable so you don't need to worry.On the plus side this method is tail recursion so Scala will internally optimize this method accordingly.

Thanks

edited Jan 19 '18 at 07:50

answered Jan 19 '18 at 05:54

Akash Sethi

2,284
1
20
40

3

Using an ORM is hardly the "best approach". It depends on the type and size of the project, and usually introduces a complexity that isn't always worth the trouble. – Kraylog Jan 19 '18 at 07:40
1

Agree, @Kraylog. Actually I'm using PostGIS extensions to PostgreSQL, for spatial and geographic query; there're some DSL functions, which I would probably not feel comfortable with ORM. – user6502167 Jan 19 '18 at 09:20
I have select query with 40 fields , can I apply this logic to fetch all the fields ? – user9920500 Feb 03 '21 at 08:24

Any better, more idiomatic way to convert SQL ResultSet to a Scala List or other collection type?

3 Answers3

Linked