Scala: How to connect to a SQL database with the Spring Framework

By Alvin Alexander. Last updated: October 9, 2016

This is an excerpt from the Scala Cookbook. This is Recipe 16.2, “How to connect to a database with the Spring Framework.”

Problem

You want to connect to a database using the Spring Framework. This gives you a nice way to add connection pooling and other capabilities to your Scala SQL code.

Solution

Use the same Spring Framework configuration you’ve used in Java applications, but convert your Java source code to Scala. The biggest changes involve the differences in class casting between Java and Scala, and conversions between Java and Scala collections.

Discussion

To demonstrate this, create a basic Spring JDBC example. Start by creating a simple SBT project directory structure as demonstrated in Recipe 18.1, “Creating a Project Directory Structure for SBT”.

Once the SBT directory structure is created, place this Spring applicationContext.xml file in the src/main/resources directory:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="testDao" class="springtests.TestDao">
        <property name="dataSource" ref="basicDataSource"/>
    </bean>
    <bean id="basicDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost/mysql" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <property name="initialSize" value="1" />
        <property name="maxActive" value="5" />
    </bean>
</beans>

This file declares that you’ll have a class named TestDao in a package named springtests. This bean declaration will be used in the Main object, which you’ll create shortly.

This file also lets you connect to a MySQL database named mysql, on the default port (3306) of the localhost server, with the username and password both set to root. The initialSize and maxActive settings let you control the database connection pool settings. Change those properties as needed for your system.

You’ll need to add a number of dependencies to your build.sbt file to get Spring to work:

name := "MySQLTest1"

version := "1.0"

scalaVersion := "2.10.1"

libraryDependencies ++= Seq(
    "mysql" % "mysql-connector-java" % "5.1.+",
    "commons-dbcp" % "commons-dbcp" % "1.4",
    "org.springframework" % "spring-core" % "3.1+",
    "org.springframework" % "spring-beans" % "3.1+",
    "org.springframework" % "spring-jdbc" % "3.1+",
    "org.springframework" % "spring-tx" % "3.1+"
)

Alternatively, you can manually download the JAR files that are needed and put them in your lib directory.

Next, create a file named Main.scala in your root SBT directory with the following contents:

package springtests

import org.springframework.context.support.ClassPathXmlApplicationContext

object Main extends App {

    // read the application context file
    val ctx = new ClassPathXmlApplicationContext("applicationContext.xml")

    // get a testDao instance
    val testDao = ctx.getBean("testDao").asInstanceOf[TestDao]
    val numUsers = testDao.getNumUsers
    println("You have this many users: " + numUsers)

}

Note how an instance of the TestDao is instantiated in this object. This code is similar to Java, except for the way class casting is handled. As shown, Scala uses the asInstanceOf method to declare that the testDao bean is of the type TestDao.

Next, create another file in the root directory of the project named TestDao.scala with these contents:

package springtests

import org.springframework.jdbc.core.simple._

class TestDao extends SimpleJdbcDaoSupport {
    def getNumUsers: Int  = {
        val query = "select count(1) from user"
        return getJdbcTemplate.queryForInt(query)
    }
}

Now run the project with the sbt run command. You should see some simple output, including the number of records in your MySQL user database table.

Although this example was created to demonstrate how to use the Spring JDBC support with Scala, you can use the steps in this recipe to use other Spring libraries in your Scala applications.