Scala integration with H2 Database

This is always the same case. When I decide to use something new for my project and search for a quick example of how to use Scala, I always get frustrated why people publish examples with so much complexity instead of trivial code and few pictures to get quickly what we developers need. H2 with Scala is the best example. However, this article should help you skip the searching, copy-paste, edit, and solve problems.

If you are reading this article, you probably know what you need, so I am not going to spend too much time with the theory about the H2 database.

What you need is:

• link to official H2 database page: https://www.h2database.com/html/main.html
• link to Maven to search for the latest update:
  https://mvnrepository.com/artifact/com.h2database/h2

The current version and the version used in the code is 1.4.196. There are not too many new features. However, it is important not to combine DB file created with, for example, .196 and try to write/read with .200. It won’t work and from the error message is not evident that the problem is in the used version.

Version .200 has some issues! Use .196 if you want to avoid any problems.

Scala sbt project structure in IntelliJ:

Scala project structure (IntelliJ Ultimate)

Please focus just on the two most important files — build.sbt and App.scala (main)

// build.sbt (minimalistic)
name := "scalabox"

version := "0.1"

scalaVersion := "2.12.15"

libraryDependencies ++= Seq(
  "org.slf4j" % "slf4j-nop" % "1.7.32",
  "com.h2database" % "h2" % "1.4.196"
)

Don’t forget to refresh sbt to pull all dependencies! ;)

Minimalistic code to demonstrate how to create a database, insert record, select record, delete record — whatever you want to put into the sql statement.

// App.scala
package com.janrock

import java.sql.{Connection, DriverManager, ResultSet, Timestamp}
import java.util.Calendar

object App {

  private def createDbStructure(conn: Connection): Unit = {
    val sql = """
      create schema if not exists state;
      set schema state;
      create table if not exists dump (
        id int auto_increment primary key,
        db_url varchar(255) not null,
        table_name varchar(255) not null,
        dtm timestamp not null);"""
    val stmt = conn.createStatement()
    try {
      stmt.execute(sql)
    } finally {
      stmt.close()
    }
  }

  private def insertDbData(conn: Connection): Unit = {
    val sqlIns =
      """insert into state.dump(db_url, table_name, dtm)
                values (?, ?, ?)"""
    val stmt = conn.prepareStatement(sqlIns)
    stmt.setString(1, conn.getMetaData.getURL)
    stmt.setString(2, "state.dump")
    stmt.setTimestamp(
      3,
      new Timestamp(Calendar.getInstance().getTime.getTime)
    )
    stmt.executeUpdate()
    stmt.close()
  }

  private def selectDbData(conn: Connection): Unit = {
    val sqlIns =
      """select db_url, table_name, dtm from state.dump"""
    val stmt = conn.prepareStatement(sqlIns)
    val res: ResultSet = stmt.executeQuery()
    while ({
      res.next
    }) {
      val db_url = res.getString("db_url")
      val table_name = res.getString("table_name")
      val dtm = res.getTimestamp("dtm")
      println(db_url + " " + table_name + " " + dtm.toString)
    }
    stmt.close()
  }
  private def deleteDbData(conn: Connection, id: Int): Unit = {
    val sqlDel = "delete from state.dump where id = " + id.toString
    val stmt = conn.prepareStatement(sqlDel)
    stmt.execute()
    stmt.close()
  }
  def main(args: Array[String]): Unit = {
    Class.forName("org.h2.Driver")
    val conn: Connection =
      DriverManager.getConnection("jdbc:h2:./db/h2", "sa", "")
    try {
      createDbStructure(conn)
      println("createDbStructure: done!")
      insertDbData(conn)
      println("insertDbData: done!")
      selectDbData(conn)
      //deleteDbData(conn, 2)
    } finally {
      conn.close()
    }
  }
}

Step #1: select H2 data source (IntelliJ Ultimate)

Step #2: select Connection type as Embedded/Local (IntelliJ Ultimate)

Step #3 click on Path — tree dots to select the path to the h2 database file

Step #4 add user “sa” and password leave empty, click on Test Connection

Step #5 now it is important to select the correct schema — STATE

Step #6 the minimalistic selection to get STATE schema ready to browse

Step #7 open the STATE schema and table DUMP is populated

Step #8 double click to get table preview with inserted record

The use case for H2 and Scala can be a simple state database for map-based key-value cache. The speed is not bad and, in some case 2–3x faster than standard RDBMS — https://www.h2database.com/html/performance.html.
There are other use cases with Slick and Play where H2 can be again used as a persistent database. You can definitely try different alternatives, but the option to connect to the database with IntelliJ and have a full database admin toolset integrated within IDE can be the reason to pick H2.

Please let me know if you have any questions, click on “Clap Hands”
or ”Follow” if you like the article.