A Scala JDBC select and insert example (showing Statement, ResultSet, and PreparedStatement)

Here’s an example of how to connect to a JDBC database with Scala. I have no idea why I got into the try/catch/finally details in a trivial little example like this, but if you want to see how to connect to a database with JDBC and query that database with a JDBC Statement (which yields a ResultSet), I hope this example is a little helpful:

import java.sql._

object H23Select extends App {

    // jdbc driver name and database URL
    val JDBC_DRIVER = "org.h2.Driver"
    val DB_URL      = "jdbc:h2:./test"
   
    // database credentials
    val USER = "sa"
    val PASS = "sa"
 
    val sql =  "SELECT * FROM USERS"

    var conn: Connection = null
    var stmt: Statement = null

    try {
        Class.forName(JDBC_DRIVER)
        conn = DriverManager.getConnection(DB_URL, USER, PASS)

        stmt = conn.createStatement
        val rs: ResultSet = stmt.executeQuery(sql)
        while (rs.next) {
            val id = rs.getInt("id")
            val first = rs.getString("first")
            val last = rs.getString("last")
            val age = rs.getInt("age")
            println(s"$id, $first, $last, $age")
        }

        // cleanup
        stmt.close
        conn.close
    } catch {
        case se: SQLException => se.printStackTrace
        case e:  Exception => e.printStackTrace
    } finally {
        try {
            if (stmt!=null) stmt.close
        } catch {
            case se2: SQLException => // nothing we can do
        }
        try {
            if (conn!=null) conn.close
        } catch {
            case se: SQLException => se.printStackTrace
        } //end finally-try
    } //end try

    println("the end")
    
}

If you further want to see how to perform a SQL INSERT with a PreparedStatement, I hope this additional code is also helpful:

val insertSql = """
    |insert into users (first,last,age)
    |values (?,?,?)
""".stripMargin

val preparedStmt: PreparedStatement = conn.prepareStatement(insertSql)

preparedStmt.setString (1, "Alvin")
preparedStmt.setString (2, "Alexander")
preparedStmt.setInt    (3, 18)
preparedStmt.execute

preparedStmt.close()

I wrote this code as a way to refresh my brain after getting away from JDBC for a while, and if you needed a little Scala/JDBC example, I hope it’s helpful.