Scala integration with H2 Database

Scala project structure (IntelliJ Ultimate)
// 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"
)
// 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 database driver
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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store