Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

scala - Slick: query multiple tables/databases with getting column names

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...