Sep 19, 2016

Using Scala Implicits with JDBC Prepared Statements

Alex Krogel

Alex Krogel

Default image background

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!

Conversation Icon

Contact Us

Ready to achieve your vision? We're here to help.

We'd love to start a conversation. Fill out the form and we'll connect you with the right person.

Searching for a new career?

View job openings