How to connect to a MySQL database with Scala and JDBC

This is an excerpt from the 1st Edition of the Scala Cookbook (partially modified for the internet). This is Recipe 16.1, “How to connect to a MySQL database with Scala and JDBC.”

Problem

You want to connect to a MySQL database (or any other database with a JDBC driver) from a Scala application using “plain old JDBC.”

Solution

Use JDBC just like you would in a Java application. Download the MySQL JDBC driver, and then access your database with code like this:

package tests

import java.sql.{Connection,DriverManager}

object ScalaJdbcConnectSelect extends App {
    // connect to the database named "mysql" on port 8889 of localhost
    val url = "jdbc:mysql://localhost:8889/mysql"
    val driver = "com.mysql.jdbc.Driver"
    val username = "root"
    val password = "root"
    var connection:Connection = _
    try {
        Class.forName(driver)
        connection = DriverManager.getConnection(url, username, password)
        val statement = connection.createStatement
        val rs = statement.executeQuery("SELECT host, user FROM user")
        while (rs.next) {
            val host = rs.getString("host")
            val user = rs.getString("user")
            println("host = %s, user = %s".format(host,user))
        }
    } catch {
        case e: Exception => e.printStackTrace
    }
    connection.close
}

That code shows how to query a database table named user in a database named mysql. That database name and table name are standard in any MySQL installation.

As shown in the example, the format of the MySQL JDBC URL string is:

jdbc:mysql://HOST:PORT/DATABASE

In this code I have MySQL running on port 8889 on my computer because it’s the default port when using MAMP, a tool that makes it easy to run MySQL, Apache, and PHP on Mac OS X systems. If you have MySQL running on its standard port (3306), just drop the port off the URL string.

Discussion

The easiest way to run this example is to use the Simple Build Tool (SBT). To do this, create an SBT directory structure as described in Recipe 18.1, “Creating a Project Directory Structure for SBT”, then add the MySQL JDBC dependency to the build.sbt file:

libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.24"

Copy and paste the code shown in this recipe into a file named Test1.scala in the root directory of your project, and then run the program:

$ sbt run

You should see some output like this:

host = localhost, user =
host = localhost, user = fred

That output will vary depending on the users actually defined in your MySQL database.

This recipe works well for small applications where you want one connection to a database, and you don’t mind running simple JDBC SQL queries using the Statement, PreparedStatement, and ResultSet classes. For larger applications, you’ll want to use a tool that gives you connection pooling capabilities, and possibly DSL or ORM capabilities to simplify your SQL queries.

If you’re using a different relational database, the approach is the same as long as the database provides a JDBC driver. For instance, to use PostgreSQL, just use the PostgreSQL JDBC driver and this information to create a connection:

Class.forName("org.postgresql.Driver")
val url = "jdbc:postgresql://HOST/DATABASE"
val conn = DriverManager.getConnection(url,"username", "password")

Of course your database tables will be different, but the process of connecting to the database is the same.

See Also

• The MySQL JDBC driver
• MAMP
• The Scala Simple Build Tool (SBT)
• Recipe 18.1, “Creating a Project Directory Structure for SBT” shows how to create an SBT directory structure
• If you’re new to MySQL and JDBC, I wrote a series of MySQL JDBC tutorials that can help you get started