sqlps

Scala JDBC prepared statement library

The problem of integration between a programming language and SQL is a very common task. In JVM world it is typically approached using some kind of wrapper above JDBC. There are approaches such as anorm, slick and others that integrate scala and SQL rather deeply.

However, in practice direct mapping of scala structures to SQL using some kind of DSL is not always a good idea as generated SQL often has it's own structural components and a better approach is to use scala structures generating a piece of SQL; these structures are are then manually combined to an SQL request.

For reading the data a wrapper of java.sql.ResultSet is typically easy to write as all resultset columns have their own names. For creating SQL it is just a string concatenation operation if programming language values are directly incorporated into the request, or some meta-language (e.g. the symbol "?" in JDBC) that is later interpreted as the place to include the value of a prepared statement. Currently a "direct inclusion" of values to SQL request is considered insecure and java.sql.PreparedStatement is the proper way to go. Proposed library addresses the problem of programming language to SQL prepared statement arguments in a type--safe way. It is small (below 200 lines) and fast. In contrast with other wrappers TINKOFF it can combine both non--sql (e.g. strings) and sql, the distinction is made by the object type.

The concept is to have two types:

• SQLArg for a single prepared statement argument
• SQLst as a container for SQL query or it's portion.

The later has two important methods: getSQL to obtain SQL request as String and setAllValues to initialize a prepared statement with the values.

To simplify the syntax a string interpolation with sql"...." is implemented to create an object of SQLst type An example:

import com.polytechnik.sqlps.SQLargs._ // implicit sql"...", aLong, aString, etc...

val q=sql"""SELECT * FROM tableX WHERE y=${aLong(33)}"""
// created q:SQLst ; q.getSQL()="SELECT * FROM tableX WHERE y=?"

in the object arg an implicit string interpolation method sql"...." is set along with the definition of methods aLong(Long,String):SQLArg aString(String,String):SQLArg aInt(Int,String):SQLArg, and others for other SQL types. One can implement his own methods as necessary.

These methods return an instance of SQLArg class that is used for prepared statement initialization. Regular string interpolation can be used as well:

val tableName="tableX"
val q=sql"""SELECT * FROM ${tableName} WHERE y=${aLong(33)}"""

The interpolator distinguishes prepared statement and the values to be directly interpolated by the type. Two types are treated specially by the sql interpolator: SQLArg and SQLst.

For SQLArg types the value to be inserted to SQL is the one returned by getSQL() method, by default it is ?, it can be changed to anything, e.g.:

val q=sql"""SELECT * FROM tableX WHERE ${aLong(33,"y=?")}""" // q:SQLst

This q.getSQL() also produces SELECT * FROM tableX WHERE y=?, same as in the example above.

The second method of SQLst is the setAllValues that performs SQL initialization of the prepared statements, e.g.:

import com.polytechnik.sqlps.SQLargs._ // implicit sql"...", aLong, aString, etc...

val q=sql"""SELECT * FROM tableX WHERE y=${aLong(33)} and z=${aString("abc")}"""
// created q:SQLst, getSQL() is: SELECT * FROM tableX WHERE y=? and z=?

val st=some_jdbc_connection.prepareStatement(q.getSQL())
q.setAllValues(st) // will issue st.setLong(1,33), st.setString(2,"abc")

This way a SQLst object carries an information about both: SQL statement and prepared statement arguments initialization. There are two convenience wrappers:

• ReadObjs Read multiple objects
• ReadObjOpt Read a single object

For example

import com.polytechnik.sqlps._
import com.polytechnik.sqlps.SQLargs._ // implicit sql"...", aLong, aString, etc...

// extract a Tuple2[Long,String] from a ResultSet
val extractT:java.sql.ResultSet=>Tuple2[Long,String]=rs=>(rs.getLong("y"),rs.getString("z"))



val q=sql"""SELECT * FROM tableX WHERE y=${aLong(33)} and z=${aString("abc")}"""
// created q:SQLst, getSQL() is: SELECT * FROM tableX WHERE y=? and z=?
val res=ReadObjs(q,extractT)(some_jdbc_connection)

the result is a Seq[T], where the type T is determined by the second argument type (a function extracting the data from java.sql.ResultSet and returning an object of T type). Extractor functions (e.g. extractTypeT:java.sql.ResultSet=>T and extractTypeR:java.sql.ResultSet=>R) are typically defined in-place for reading simple SQL types (e.g. Long from BIGINT as rs=>rs.getLong("fieldName")) and are defined externally (e.g. in datatype companion object) for reading a multi-field datatype from a single java.sql.ResultSet raw. The SQL request then looks like:

val dataTypeT=ReadObjs(
		sql"""SELECT * FROM tableX WHERE y=${aLong(33)} and z=${aString("abc")}"""
    		extractTypeT)(some_jdbc_connection)
// scala.collection.Seq[T] is returned

val dataTypeR=ReadObjOpt(
		sql"""SELECT * FROM tableX WHERE y=${aLong(33)} and z=${aString("abc")}"""
    		extractTypeR)(some_jdbc_connection)
// Option[R] is returned

One can implement other wrappers as needed.

This is a typical SQL interpolation functionality, used in most java/scala frameworks. The difference with this library is that SQL-pieces (of SQLst type) can itself be interpolated by the sql" ... " interpolator, e.g:

val q1=sql"""SELECT z FROM tableX WHERE z=${aString("abc")}"""
val q=sql""" SELECT * FROM tableX WHERE x=${aLong(33)} AND z IN (${q1})"""
// created q:SQLst q.getSQL()=" SELECT * FROM tableX WHERE x=? AND z IN (SELECT y FROM tableX WHERE z=?)"

when issued q.setAllValues(st) the SQL prepared statement will be properly initialized regardless the order/depth of used "sql pieces" of SQLst type. Inside the interpolator there is a recursive tree walk, this makes it possible.

The proposed library allows a seamless integration of scala language variables and SQL prepared statement variables. The goal was achieved by introduction of two types SQLArg and SQLst and treating them specially during sql"..." interpolation.

In some cases it is convenient to create SQLst directly, without sql"..." interpolation, e.g. let we have an array of data:(Long,Int) tuples, then

val data=List((101L,1),(102L,2),(103L,3))

val q=sql"INSERT INTO tableX VALUES "+
      SQLst.mergeWithSeparator(
    		 s=for((x,i)<-data) yield sql"""(x=${aLong(x)},i=${aInt(i)})""",
		 separator=",")+
      " RETURNING * "
// will create q.getSQL()=
//   "INSERT INTO tableX VALUES (x=?,i=?),(x=?,i=?),(x=?,i=?) RETURNING * "
// and q.setAllValues(st) will issue:
//   st.setLong(1,101L);st.setInt(2,1);st.setLong(3,102L);st.setInt(4,2);st.setLong(5,103L);st.setInt(6,3);

and the values will be properly bound by q.selAllValues(st) or using ReadObjs wrapper

// extract a Tuple2[Long,Int] from a ResultSet
val extractT:java.sql.ResultSet=>Tuple2[Long,Int]=rs=>(rs.getLong("x"),rs.getInt("i"))
.....
val res=ReadObjs(q,extractT)(some_jdbc_connection)
// scala.collection.Seq[(Long,Int)] is terurned

where the method mergeWithSeparator is used to combine individual SQLst together.

Caveats

There is a hard limit of 32767 JDBC prepared statement arguments see link. An SQL with too many prepared statement arguments may fail for this reason. A proper way is to fix JDBC driver.

License

This software is available under the GPLV3 license. If you need this software under any other license -- it can be made available for a fee of $200.