Ad Hoc Querying with ScalikeJDBC

scalikejdbc_adhoc.scala

/*
in app/models/Query.scala
*/

package models

import scalikejdbc._
import play.api.libs.json._
import play.api.libs.json.Json
import play.api.libs.json.util._
import play.api.libs.json.Format


object Query extends SQLSyntaxSupport[Any] {

  def run(table: String, limit: Int): List[Map[String,Any]] = {
    val tableName = SQLSyntax.createUnsafely(table)
    DB readOnly { implicit session =>
      sql"select * from ${tableName} limit ${List(limit, 500).min}".toMap.list.apply()
    }
  }

  implicit object MapJsonFormat extends Format[List[Map[String, Any]]] {

    def writes(list: List[Map[String, Any]]): JsArray = {
      JsArray((list.map { l =>
        JsObject(l.mapValues {
            case v: String  => JsString(v)
            case v: Int     => JsNumber(v)
            case v: Boolean => JsBoolean(v)
            case v: Any     => JsString(v.toString)
          })
        }))
      }

    def reads(json: play.api.libs.json.JsValue): play.api.libs.json.JsResult[List[Map[String,Any]]] = ???
  }
}


/*
in config/routes.txt
*/

GET   /query/:table/:limit    controllers.Query.query(table: String, limit: Int)


/*
in app/controllers/QueryController.scala
*/

package controllers

import models.Query
import javax.inject._
import play.api._
import play.api.mvc._
import play.api.data._
import play.api.libs.json._
import play.api.libs.json.Json

class Query extends Controller {

  def query(table: String, limit: Int) = Action { request =>
    Ok(Query.MapJsonFormat.writes(Query.run(table, limit)))
  }

}