A ScalikeJdbc SQL SELECT example with WrappedResultSet

By Alvin Alexander. Last updated: September 27, 2022

Here’s another Scala ScalikeJdbc SQL SELECT example. A few unique things about this example is that it shows the JDBC-style parameters; it shows how to use a case class and the “service” style approach; and it shows how to use the WrappedResultSet as shown. Here’s the code:

import scalikejdbc._
import scalikejdbc.config._

object LookupIpAddressTest1 extends App {

    // initialize JDBC driver & connection pool
    Class.forName("com.mysql.jdbc.Driver")
    ConnectionPool.singleton("jdbc:mysql://localhost:8889/kbhr2", "root", "root")

    // ad-hoc session provider on the REPL
    implicit val session = AutoSession

    val ips = IpAddressService.allIps
    println("\n\n*** LOADED ALL IP ADDRESSES ***")
    //ips.foreach(println)
    
    // wipes out ConnectionPool
    DBs.closeAll()
    
}

object IpAddressService {

    // `sql` strings are PreparedStatements
    def allIps: Seq[IpAddress] = DB.readOnly { implicit session =>
        sql"""
            select * from dbip_lookup
            where addr_type='ipv4'
        """.map(IpAddress.fromDb).list().apply()
    }

}

case class IpAddress(
    ipStart: Array[Byte],
    ipEnd: Array[Byte],
    country: String,
    state: String,
    city: String
)

// for `rs.bytes`, see:
// https://github.com/scalikejdbc/scalikejdbc/blob/master/scalikejdbc-core/src/main/scala/scalikejdbc/WrappedResultSet.scala
object IpAddress {
    def fromDb(rs: WrappedResultSet): IpAddress = {
        val ipStart  = rs.bytes("ip_start")
        val ipEnd    = rs.bytes("ip_end")
        val country  = rs.string("country")
        val state    = rs.string("stateprov")
        val city     = rs.string("city")
        IpAddress(ipStart, ipEnd, country, state, city)
    }
}

As I’ve written before, at some point I hope to take some time to write a ScalikeJdbc tutorial, but until then, I hope this is another helpful example.