Scala/Slick plain SQL: retrieve result as a map

Question

I have a simple method to retrieve a user from a db with Sclick plain SQL method:

object Data {

    implicit val getListStringResult = GetResult[List[String]] (
        prs => (1 to prs.numColumns).map(_ => prs.nextString).toList
    )

    def getUser(id: Int): Option[List[String]] = DB.withSession {
        sql"""SELECT * FROM "user" WHERE "id" = $id""".as[List[String]].firstOption
    }

}

The result is List[String] but I would like it to be something like Map[String, String] - column name and value pair map. Is this possible? If so, how?

My stack is Play Framework 2.2.1, Slick 1.0.1, Scala 2.10.3, Java 8 64bit

also see http://stackoverflow.com/questions/19891881/scala-slick-plain-sql-retrieve-result-as-a-map/19892330?noredirect=1#comment29592959_19892330 — cvogt, Nov 11 '13 at 14:32

score 8 · Accepted Answer · answered Nov 10 '13 at 16:50

8

import scala.slick.jdbc.meta._
val columns = MTable.getTables(None, None, None, None)
                    .list.filter(_.name.name == "USER") // <- upper case table name
                    .head.getColumns.list.map(_.column)
val user = sql"""SELECT * FROM "user" WHERE "id" = $id""".as[List[String]].firstOption
                                                         .map( columns zip _ toMap )

answered Nov 10 '13 at 16:50

cvogt

11,260
30
46

Thanks, it seems to be working, although I can see why I didn't figure it out myself - didn't think it can possibly be that complicated... – Caballero Nov 10 '13 at 17:07
There is no documentation about this feature, but you can find usages in: https://github.com/slick/slick/blob/master/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/MapperTest.scala and https://github.com/cvogt/migrations/blob/master/src/main/scala/scala/slick/jdbc/reflect/reflect.scala – cvogt Nov 11 '13 at 00:59

score 8 · Answer 2 · answered Dec 03 '13 at 19:19

It's possible to do this without querying the table metadata as follows:

   implicit val resultAsStringMap = GetResult[Map[String,String]] ( prs => 
      (1 to prs.numColumns).map(_ => 
         prs.rs.getMetaData.getColumnName(prs.currentPos+1) -> prs.nextString
      ).toMap
   )

It's also possible to build a Map[String,Any] in the same way but that's obviously more complicated.

Scala/Slick plain SQL: retrieve result as a map

2 Answers2

Linked