Scala: A ScalikeJdbc example (including SQL SELECT and UPDATE statements)

By Alvin Alexander. Last updated: October 8, 2022

If you want to see a relatively simple Scala ScalikeJdbc example that performs SQL SELECT and UPDATE commands, I hope this example is helpful.

The background for this example goes like this:

I have a couple of old websites running Drupal 6
Drupal 6 has been discontinued and is now a security risk
It’s not worth trying to upgrade them to Drupal 8 (upgrading isn’t easy)
As a result, I decided to (a) keep the old Drupal 6 database and (b) write a Play Framework application to replace Drupal 6 (which I currently call “Scrupal6”)
As I started working on this I discovered that a database table named url_alias has a wonky setup, where a “node id” value I want to do a join on is embedded inside a varchar field
To make my life easier I decided to write the Scala code shown below to populate a nid (node id) field I added to that table
After I issued an ALTER TABLE statement to add the nid field to the url_alias table, its schema looks like this:

mysql> desc url_alias;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| pid      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| src      | varchar(128)     | NO   | MUL |         |                |
| dst      | varchar(128)     | NO   | MUL |         |                |
| language | varchar(12)      | NO   |     |         |                |
| nid      | int(10) unsigned | YES  | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Before I run the Scala application below, some sample data from that table looks like this:

| 3717 | node/901      | 2017/06/17/riding-the-wave                        |  | NULL |
| 3718 | node/901/feed | 2017/06/17/riding-the-wave/feed                   |  | NULL |
| 3736 | node/902      | 2017/07/28/wondering-if-its-best-say-nothing      |  | NULL |
| 3737 | node/902/feed | 2017/07/28/wondering-if-its-best-say-nothing/feed |  | NULL |

The purpose of the Scala code below is to put 901 in the last column of the first row (the one with node/901 in the second column), and 902 in the last column of the third row (the one with node/902 in the second column).

The Scala + ScalikeJdbc code

Given that background, here’s the Scala code I wrote to populate the last column in all of the node/XXX rows, while also skipping all of the node/XXX/feed rows:

import scalikejdbc._

/**
 * Purpose: Populate the new `nid` column in the Drupal 6 `url_alias` table.
 */
object Scrupal6NidPopulator extends App {

    // initialize JDBC driver & connection pool. the database is MySQL running under MAMP.
    Class.forName("com.mysql.jdbc.Driver")
    ConnectionPool.singleton("jdbc:mysql://localhost:8889/scrupal", "root", "root")

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

    // get all the `url_alias` rows as a List of Map values.
    // (note: i could have been smarter about how i wrote this query.)
    val entities: List[Map[String, Any]] = sql"select * from url_alias"
        .map(_.toMap)
        .list
        .apply()

    // populate the `nid` column in the `url_alias` table when you find
    // (a) a "node" entry where (b) that entry isn’t a "node/xxx/feed" entry.
    for {
        e <- entities
        srcField = e("src").toString
        if srcField.startsWith("node/")   // a guard condition
        if !srcField.contains("/feed")    // a second guard
    } {
        val pid = e("pid").toString.toInt
        val nidOption = getNodeNumberFromSrcUri(srcField)
        nidOption match {
            case None => //nada
            case Some(nid) => {
                // this is a valid `node/xxx` row, so update the `nid` column
                sql"update url_alias set nid=${nid} where pid=${pid}".update.apply()
            }
        }
    }

    private def getNodeNumberFromSrcUri(srcUriFromDb: String): Option[Int] = {
        if (srcUriFromDb.startsWith("node/")) {
            // this is the "happy path" use case
            toIntOption(srcUriFromDb.drop(5))  //"249" -> 249
        } else {
            None
        }
    }
   
    private def toIntOption(s: String): Option[Int] = {
        try {
            Some(s.toInt)
        } catch {
            case _: Throwable => None
        }
    }
   
}

Discussion

In retrospect some of that code could have been smarter, but a few good things about it are:

It shows how to perform SQL SELECT and UPDATE queries with ScalikeJdbc
More generally it shows how to connect to a MySQL database with ScalikeJdbc
It shows how to write a couple of guard conditions in the for-expression
If you’ve never used it before, there’s some Option/Some/None stuff in there

After I run this code the url_alias table now looks like this:

| 3717 | node/901      | 2017/06/17/riding-the-wave                        |  |  901 |
| 3718 | node/901/feed | 2017/06/17/riding-the-wave/feed                   |  | NULL |
| 3736 | node/902      | 2017/07/28/wondering-if-its-best-say-nothing      |  |  902 |
| 3737 | node/902/feed | 2017/07/28/wondering-if-its-best-say-nothing/feed |  | NULL |

Notice that the 901 and 902 values have been added to the last column of the table for the appropriate rows.

There’s probably some way to do the same thing using only SQL queries, but SQL isn’t one of my strengths, and I wanted to experiment with ScalikeJdbc anyway.

The build.sbt file

As a final note, here’s the SBT build.sbt file I used on this project:

name := "ScalikeJdbcExamples"

version := "1.0"

scalaVersion := "2.12.4"

libraryDependencies ++= Seq(
    "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"