ScalikeJdbc: An example of SQL SELECT query to create a Scala List

By Alvin Alexander. Last updated: September 27, 2022

As a brief note today, this Scala example code shows how I make a SQL query using ScalikeJdbc to return results as a List, in this case making a SQL SELECT query to create a Scala list of PhotoNode instances:

import scalikejdbc._

case class PhotoNode (
    nid: Long,
    fieldPhotoFid: Long,
    fieldPhotoData: String,
    filepath: String
)

// later ...

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

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

// get a list of all url_alias where dst like 'gallery%' (including nid).
// get all the url_alias rows as a List of Map values.
val photoNodes: List[PhotoNode] = sql"""
    select u.nid, ctp.field_photo_fid, ctp.field_photo_data, f.filepath
    from url_alias u, content_type_photo ctp, files f
    where u.dst like 'gallery/%'
    and u.nid is not null
    and u.nid = ctp.nid
    and ctp.field_photo_fid = f.fid
""".map(rs =>
    PhotoNode(
        rs.long("nid"),
        rs.long("field_photo_fid"),
        rs.string("field_photo_data"),
        rs.string("filepath")
    ).copy(fieldPhotoData = rs.string("field_photo_data").split(":")(6).drop(1).dropRight(3))
)
.list
.apply()


// later ...
for {
    node <- photoNodes
    ...

I’m not going to take the time to explain that code, but if you wanted to see how to write a ScalikeJdbc SQL SELECT query to create a list/sequence in Scala, I hope that’s hopeful.

A simpler SQL SELECT query

Update: I thought I saved a simpler query where I create a list of integers (or long values) from a ScalikeJdbc SQL SELECT query, but I can’t find it. That being said, I’m about 98% sure that the ScalikeJdbc query looked like this:

val nodeIds: List[Long] = sql"select nid from node".list.apply()

Also, FWIW, my SBT build.sbt file for this code looks like this:

name := "ScalikeJdbcExamples"

version := "1.0"

scalaVersion := "2.12.4"

libraryDependencies ++= Seq(
    "com.h2database"  %  "h2" % "1.4.196",
    "org.scalikejdbc" %% "scalikejdbc"          % "3.1.0",
    "org.scalikejdbc" %% "scalikejdbc-config"   % "3.1.0",
    "ch.qos.logback"  %  "logback-classic"      % "1.2.3",
    "mysql"           %  "mysql-connector-java" % "5.1.46"
)

scalacOptions += "-deprecation"

Once again, I hope that ScalikeJdbc example is helpful.

scala

list