4

I have methods in my Play app that query database tables with over hundred columns. I can't define case class for each such query, because it would be just ridiculously big and would have to be changed with each alter of the table on the database.

I'm using this approach, where result of the query looks like this:

Map(columnName1 -> columnVal1, columnName2 -> columnVal2, ...)

Example of the code:

implicit val getListStringResult = GetResult[List[Any]] (
    r => (1 to r.numColumns).map(_ => r.nextObject).toList
)

def getSomething(): Map[String, Any] = DB.withSession {
    val columns = MTable.getTables(None, None, None, None).list.filter(_.name.name == "myTable").head.getColumns.list.map(_.column) 
    val result = sql"""SELECT * FROM myTable LIMIT 1""".as[List[Any]].firstOption.map(columns zip _ toMap).get
}

This is not a problem when query only runs on a single database and single table. I need to be able to use multiple tables and databases in my query like this:

def getSomething(): Map[String, Any] = DB.withSession {

    //The line below is no longer valid because of multiple tables/databases
    val columns = MTable.getTables(None, None, None, None).list.filter(_.name.name == "table1").head.getColumns.list.map(_.column) 
    val result = sql"""
        SELECT      * 
        FROM        db1.table1
        LEFT JOIN   db2.table2 ON db2.table2.col1 = db1.table1.col1
        LIMIT       1
    """.as[List[Any]].firstOption.map(columns zip _ toMap).get

}

The same approach can no longer be used to retrieve column names. This problem doesn't exist when using something like PHP PDO or Java JDBCTemplate - these retrieve column names without any extra effort needed.

My question is: how do I achieve this with Slick?

Caballero
  • 11,546
  • 22
  • 103
  • 163

2 Answers2

3
import scala.slick.jdbc.{GetResult,PositionedResult}
object ResultMap extends GetResult[Map[String,Any]] {
  def apply(pr: PositionedResult) = {
    val rs = pr.rs // <- jdbc result set
    val md = rs.getMetaData();
    val res = (1 to pr.numColumns).map{ i=> md.getColumnName(i) -> rs.getObject(i) }.toMap
    pr.nextRow // <- use Slick's advance method to avoid endless loop
    res
  }
}
val result = sql"select * from ...".as(ResultMap).firstOption
cvogt
  • 11,260
  • 30
  • 46
  • Thanks. I think it should be `scala.slick.session.PositionedResult` instead of `scala.slick.jdbc.PositionedResult`? Another issue - if `as(ResultMap).fistOption` is used - it gets stuck in an endless loop. If `as(ResultMap).list` is used everything is fine. Is there a way to rectify this? – Caballero Nov 29 '13 at 15:17
  • Please contact us if it is still the case in Slick 2.0 – cvogt Dec 06 '13 at 15:25
  • I've noticed that in query `SELECT c AS column1 FROM t` the name of the column in the result still remains `c` which is kind of a big problem especially for dynamically calculated columns. Is there a solution for this? – Caballero Feb 12 '14 at 11:35
  • I don't understand. Is this related? If yes, please explain. Otherwise please open a separate question. – cvogt Feb 12 '14 at 14:43
  • @cvogt how does this work when the query does not return any results. I have a similar requirement to get only the columns from a query. Works for queries that return results but not for the queries that has no results. – mjlowky Mar 15 '21 at 07:20
0

Another variant that produces map with not null columns (keys in lowercase):

private implicit val getMap = GetResult[Map[String, Any]](r => {
    val metadata = r.rs.getMetaData
    (1 to r.numColumns).flatMap(i => {
        val columnName = metadata.getColumnName(i).toLowerCase
        val columnValue = r.nextObjectOption
        columnValue.map(columnName -> _)
    }).toMap
})
caiiiycuk
  • 1,466
  • 14
  • 20