main

Elegant SQL Queries and O/R mapping

I’ve used Apache iBATIS for years and always liked how it works, but there are some boiler plate that could be avoided, and I don’t really feel like wrapping my SQL in XML. I’d rather keep the expressiveness of the Java/Groovy language at my fingertips, but still write SQL (Don’t even get me started on Hibernate/JPA and other O/R mappers that do magical things behind your back and try to hide that you are working with a relational database).

These thoughts gave birth to Tornado Query, a simple and elegant way of writing SQL and mapping results to your domain objects.
Let’s write an INSERT statement to save the content of a fictive User object:

1

2

3

4

Query.create("INSERT INTO users (id, username, password)")

.add("VALUES (:id, :username, :password)")

.param(user)

.insert();

We did a couple of neat things here:

Named parameters avoids the need to keep track of long arrays of ?’s. The param() argument makes all the properties in the User object directly accessible as named parameters, even nested properties are allowed.

The fluent interface makes it easy to break the SQL into multiple lines, and also makes it easier to implement custom logic while building the SQL

Let’s go a bit further and say that the id field should be filled with the result of incrementing a sequence:

1

2

3

4

5

Query.create("INSERT INTO users (id, username, password)")

.add("VALUES (:id, :username, :password)")

.param(user)

.key("id","SELECT nextval('user_id_seq')")

.insert();

The id propertiy retrieved from the sequence is also put back into the domain object. There is even a shortcut for sequences that does the same as the key() statement above:

1

sequence("id","user_id_seq")

Mapping results to domain objects

If we want to select all users by some criteria and turn the result into a List of User objects, we would write:

1

List grownups=Query.create(User.class,"SELECT * FROM users WHERE age &gt; 18").rows();

If there is a one to one mapping between column names and domain object properties, they will be automatically wired together. However, this will not be enough if we have nested object properties in our domain objects, or if the name of the columns does not match the properties in the domain objects. We can fix this by supplying a ResultMap that will map the columns that does not directly match a domain object property. Our User object looks like this:

1

2

3

4

5

6

classUser{

Integerid;

Stringusername;

Stringpassword;

BooleanuserAdmin;

}

And our users table look like this:

1

2

3

4

5

6

CREATE TABLE users(

id INTEGERNOTNULLPRIMARY KEY,

username TEXT,

password TEXT,

user_admin BOOLEAN

)

As you can see, the user_admin column will not match the userAdmin property of our domain object. Tornado Query will still find this mapping, by switching out underscores for camel case. If we want to add a spesific mapping, we add a ResultMap:

1

2

3

4

5

6

7

ResultMap userMapping=newResultMap(User.class)

.automap()

.add("userAdmin","user_admin");

List grownups=Query.create(User.class,"SELECT * FROM users WHERE age &gt; 18")

.resultMap(userMapping)

.rows();

There is no need to mention the remaining, directly matching, columns in the resultMap, because we told the ResultMap to automap.

This works exactly the same for nested properties. Let’s add a father property to our domain object:

1

2

3

4

classUser{

...

User father;

}

And in our DDL:

1

2

3

4

CREATETABLEusers{

...

father INTEGERNOTNULLREFERENCES users(id)

}

We alter our resultMap to include a mapping for the father’s id and username:

Here we switched to just getting the first result object, using the first() command instead of rows(). We also created the Query object without any initial SQL, to make it line up nicer.

What if…? Conditional statements

Sometimes you build SQL queries where some of the SQL is included only if certain conditions are met. Let’s say we have a DAO method method where the two age parameters are conditional:

1

2

3

4

5

6

7

8

9

10

publicList findUsers(IntegerminAge,IntegermaxAge){

returnQuery.create(User.class,"SELECT * FROM users")

.dynamic("WHERE")

.addIf(minAge!=null,"AND age &gt;= :minAge")

.addIf(maxAge!=null,"AND age &lt;= :maxAge")

.param("minAge",minAge)

.param("maxAge",maxAge)

.resultMap(userMapping)

.rows();

}

Right about now, you might be scratching your head, wondering if this will really render a valid SQL statement!? I’ll try to explain 🙂 Firstly, the dynamic() command makes sure that the WHERE clause is only included if at least one discriminator command is included. The addIf() command will only add the provided discriminator if the statement in the first argument is true (there are also addUnless() and other convenience methods available. Tornado Query will automatically fix the situation where "WHERE AND" would be concatinated. Lastly, we added two named param’s instead of one root param, like we did earlier with the User object.

Status

The code in the examples are working right now, but I’ve only been doing about 4 hours of work on this framework (and one sleepless night thinking about it), so this is still alpha quality, «proof of concept» code. I will continue to play with the syntax and the ideas to make it more elgant and fluent, but I already feel that I’ve killed off lots of boilerplate compared to other frameworks, without resorting to magic. I strongly believe that an SQL framework should not do magical, hard to explain stuff, and it should not alter your SQL significantly. To write a performant database application you must care about your schema and your data!