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

mysql
mysql-connector-java
5.1.12


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.