Using Scala with JDBC to connect to MySQL

A quick howto on connecting Scala to a MySQL database using JDBC. There are a number of database libraries for Scala, but I ran into a problem getting most of them to work. I have the highest hopes for Querulous, a library created by Twitter. However, it currently only supports Scala 2.7.7 and I’m running a 2.8 candidate which is the only compatible version with the template engine I’m using. Hopefully things stabilize once 2.8 is final.

I attempted to use scala.dbc, scala.dbc2 and Scala Query but either they aren’t supported, have a very limited featured set or abstracts SQL to a weird pseudo language. So I ended up with basic Java JDBC and it turned out to be the easiest solution.

Here is the code for the a database query example using JDBC. You need to change the connection string parameters and modify the query for your database.

import java.sql.{Connection, DriverManager, ResultSet};

// Change to Your Database Config
val conn_str = "jdbc:mysql:/localhost:3306/DBNAME?user=DBUSER&password=DBPWD"

// Load the driver
classOf[com.mysql.jdbc.Driver]

// Setup the connection
val conn = DriverManager.getConnection(conn_str)
try {
    // Configure to be Read Only
    val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

    // Execute Query
    val rs = statement.executeQuery("SELECT quote FROM quotes LIMIT 5")

    // Iterate Over ResultSet
    while (rs.next) {
        println(rs.getString("quote"))
    }
}
finally {
    conn.close
}

You will need to download the mysql-connector jar. Download the mysql connector jar from here.

Or if you are using maven, the pom snippets to load the mysql connector

 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.12</version>
 </dependency>

To run the example, save the following to a file (query_test.scala) a run using, the following specifying the classpath to the connector jar:

scala -classpath mysql-connector-java-5.1.12.jar:. query_test.scala

Hope it helps.