A ScalikeJdbc SQL SELECT example that maps results to a list of case class instances

By Alvin Alexander. Last updated: March 14, 2020

Here’s another ScalikeJdbc example. The main benefits of this example are:

It shows how to perform a SQL SELECT with ScalikeJdbc
How to map the ResultSet values to create a series of objects from the ResultSet
How to connect to a database with ScalikeJdbc using a JDBC style of connecting (as opposed to putting the JDBC parameters in a configuration file)

Here’s the ScalikeJdbc example code:

import scalikejdbc._

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

/**
 * Purpose: munge/massage the Drupal6 photo data, especially for the OMA
 * website, so i don’t have to change my Scrupal6 Play Framework app.
 *
 * Algorithm:
 *
 * - get a list of all url_alias where dst like 'gallery%' (including nid)
 * - go into `content_type_photo` with nid and get field_photo_fid and field_photo_data
 * - go into `files` with field_photo_fid and get filepath
 * - update `body` in node_revisions (with filepath and field_photo_data)
 */
object ScrupalMungePhotoData extends App {

    // initialize JDBC driver & connection pool
    Class.forName("com.mysql.jdbc.Driver")
    ConnectionPool.singleton("jdbc:mysql://localhost:8889/scrupal_oma", "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
        """
        .stripMargin
        .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()

    // update `body` in node_revisions (with filepath and field_photo_data)
    for {
        n <- photoNodes
    } {
        val body = createNodeHtml(n)
        println(n)
        sql"update node_revisions set body=${body} where nid=${n.nid}".update.apply()
    }
    
    private def createNodeHtml(node: PhotoNode): String = {
        val data = node.fieldPhotoData
        val desc = if (data.isEmpty) {
            ""
        } else {
            if (data.endsWith(".")) {
                node.fieldPhotoData
            } else {
                node.fieldPhotoData + "."
            }
        }
        
        if (desc.isEmpty) {
            s"""
            |<div id="photo-field">
            |<p><img src="/${node.filepath}" alt="$desc" /></p>
            |</div>
            """.stripMargin
        } else {
            s"""
            |<div id="photo-field">
            |<p>$desc</p>
            |<p><img src="/${node.filepath}" alt="$desc" /></p>
            |</div>
            """.stripMargin
        }
    }

}

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.