Using Scala Implicits with JDBC Prepared Statements

This article discusses the use of Scala’s implicit conversions to add a more functional feel to database access using Prepared Statements. This article assumes you have a working knowledge of Scala and Scala implicits, and some knowledge of Java’s PreparedStatement and Spring Framework’s JdbcTemplate.

I begin by making a SELECT statement against a SQL database. When making any SELECT call with a JdbcTemplate I invoke the query method. While there are many query methods, I will use the one that accepts a String containing the SQL statement, a  PreparedStatementSetter to inject the proper values, and a ResultSetExtractor to parse the answer. The form follows:

jdbcTemplate.query(String sql, PreparedStatementSetter pss, ResultSetExtractor rse)

The PreparedStatementSetter will replace all instances of the ? character in the SQL string with the values specified in the body of the PreparedStatementSetter. Here is a more fleshed out example dealing with an imaginary movie database:

val profitThreshold = 1000000 jdbcTemplate.query( “““SELECT movieTitle FROM movies WHERE profit > ?”””, (ps: PreparedStatement) => {ps.setLong(1, profitThreshold)}, (rs: ResultSet) => {rs.getString(1)} )

If you read carefully, you’ll notice I didn’t actually use a PreparedStatementSetter or a ResultSetExtractor. Instead I use a value of type PreparedStatement => Unit and ResultSet => Unit. Compiled alone, this code will cause an error because Scala doesn’t know how to convert a value of type PreparedStatement => Unit to a PreparedStatementSetter or a ResultSet => Unit to a ResultSetExtractor.

Here is where the magic of Scala implicits comes into play.

Elsewhere in my project I created an object to store my implicit converter and all I need to do is import the members of this object (the “._” pattern matcher is useful here) to the class that contains my JDBC code. In a simpler project, instead of making a separate object and importing its members you can just place your implicits at the top of your file. Take a look:

object JdbcConverter {     implicit def preparedStatementConverter(ps: PreparedStatement => Unit): PreparedStatementSetter = {     new PreparedStatementSetter {         override def setValues(dps: PreparedStatement): Unit = ps(dps)     }     }

    implicit def resultSetConverter[T](rs: ResultSet => T): ResultSetExtractor = {         new ResultSetExtractor[T] {     override def extractValues(drs: ResultSet): T = rs(drs) } } }

The Scala compiler will find the implicit definitions for the converters and use that to convert my PreparedStatement => Unit call to a PreparedStatementSetter and my ResultSet => Unit call to a ResultSetExtractor.

(ps: PreparedStatement) => {ps.setLong(1, profitThreshold)} (rs: ResultSet) => {rs.getString(1)}

All you need to do is make sure you include the implicits in your file.

Happy coding!

• Scala
• JDBC Prepared Statements
• JDBC
• Scala Implicits