A ScalikeJdbc SQL SELECT “service” example

Here’s another ScalikeJdbc SQL SELECT query example. In this example I use the concept of a “service,” which I probably originally got from the ScalikeJdbc website:

import scalikejdbc._
import scalikejdbc.config._

/**
 * this example depends on the database settings in the file
 * src/main/resources/application.conf
 * @see http://scalikejdbc.org/documentation/configuration.html
 * (see the “scalikejdbc-config” section)
 */
object Scalike3 extends App {

    // DBs.setup/DBs.setupAll loads specified JDBC driver classes.
    DBs.setupAll()
    
    UserService.allUsers.foreach(println)

    // wipes out ConnectionPool
    DBs.closeAll()

}

object UserService {

    // uses “SQLInterpolation”
    // `sql` strings are PreparedStatements
    def allUsers: Seq[User] = DB.readOnly { implicit session =>
        sql"select * from users".map(User.fromDb).list().apply()
    }

    def findUser(firstName: String): Option[User] = DB.readOnly { implicit session =>
        sql"select * from users where first = $firstName limit 1"
            .map(User.fromDb)
            .headOption()
            .apply()
    }

}

This example is from very early in my ScalikeJdbc learning process, so this is probably a direct copy of something from the ScalikeJdbc website, though I hope I added something useful in my example.

As with my other examples, this example relies on having an application.conf configuration in your src/main/resources folder (assuming that you’re using SBT), and it should look like this:

# JDBC settings
db.default.driver="org.h2.Driver"
db.default.url="jdbc:h2:./test"
db.default.user="sa"
db.default.password="sa"

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=10
db.default.poolConnectionTimeoutMillis=1000

I hope to write more about ScalikeJdbc in the future, but until then, I hope this example is helpful.