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!