A Guide to the sql2o JDBC Wrapper

1. Introduction

In this tutorial, we’re going to take a look at Sql2o, a small and fast library for relational database access in idiomatic Java.

It is worth to mention that even though Sql2o works by mapping query results to POJOs (plain old Java objects), it’s not a complete ORM solution such as Hibernate.

2. Sql2o Setup

Sql2o is a single jar file that we can easily add to our project’s dependencies:

<dependency>
    <groupId>org.sql2o</groupId>
    <artifactId>sql2o</artifactId>
    <version>1.6.0</version>
</dependency>

We’ll also use HSQL, the embedded database, in our examples; in order to follow along, we can include it as well:

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.4.0</version>
    <scope>test</scope>
</dependency>

Maven Central hosts the latest version of sql2o and HSQLDB.

3. Connecting to the Database

To establish a connection, we start from an instance of the Sql2o class:

Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");

Here, we’re specifying the connection URL, username, and password as constructor parameters.

The Sql2o object is thread-safe and we can share it across the application.

3.1. Using a DataSource

In most applications, we’ll want to use a DataSource instead of a raw DriverManager connection, perhaps to leverage a connection pool, or to specify additional connection parameters. Worry not, Sql2o has got us covered:

Sql2o sql2o = new Sql2o(datasource);

3.2. Working With Connections

Merely instantiating a Sql2o object does not establish any connection to the database.

Instead, we use the open method to get a Connection object (note that it’s not a JDBC Connection). Since Connection is AutoCloseable, we can wrap it in a try-with-resources block:

try (Connection connection = sql2o.open()) {
    // use the connection
}

4. Insert and Update Statements

Now let’s create a database and put some data in it. Throughout the tutorial, we’ll use a simple table called project:

connection.createQuery(
    "create table project "
    + "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();

executeUpdate returns the Connection object so that we can chain multiple calls. Then, if we want to know the number of affected rows, we use getResult:

assertEquals(0, connection.getResult());

We’ll apply the pattern that we’ve just seen – createQuery and executeUpdate – for all DDL, INSERT and UPDATE statements.

4.1. Getting Generated Key Values

In some cases, though, we might want to get generated key values back. Those are the values of key columns that are automatically computed (like when using auto-increment on certain databases).

We do that in two steps. First, with an additional parameter to createQuery:

Query query = connection.createQuery(
    "insert into project (name, url) "
    + "values ('tutorials', 'github.com/eugenp/tutorials')", true);

Then, invoking getKey on the connection:

assertEquals(0, query.executeUpdate().getKey());

If the keys are more than one, we use getKeys instead, which returns an array:

assertEquals(1, query.executeUpdate().getKeys()[0]);

5. Extracting Data From the Database

Let’s now get to the core of the matter: SELECT queries and the mapping of result sets to Java objects.

First, we have to define a POJO class with getters and setters to represent our projects table:

public class Project {
    long id;
    private String name;
    private String url;
    //Standard getters and setters
}

Then, as before, we’ll write our query:

Query query = connection.createQuery("select * from project order by id");

However, this time we’ll use a new method, executeAndFetch:

List<Project> list = query.executeAndFetch(Project.class);

As we can see, the method takes the class of the results as a parameter, to which Sql2o will map the rows of the raw result set coming from the database.

5.1. Column Mapping

Sql2o maps columns to JavaBean properties by name, case-insensitive.

However, naming conventions differ between Java and relational databases. Suppose that we add a creation date property to our projects:

public class Project {
    long id;
    private String name;
    private String url;
    private Date creationDate;
    //Standard getters and setters
}

In the database schema, most probably we’ll call the same property creation_date.

Of course, we can alias it in our queries:

Query query = connection.createQuery(
    "select name, url, creation_date as creationDate from project");

However, it’s tedious and we lose the possibility to use select *.

Another option is to instruct Sql2o to map creation_date to creationDate. That is, we can tell the query about the mapping:

connection.createQuery("select * from project")
    .addColumnMapping("creation_date", "creationDate");

This is nice if we use creationDate sparingly, in a handful of queries; however, when used extensively in a larger project, it becomes tedious and error-prone to tell the same fact over and over.

Fortunately, we can also specify mappings globally:

Map<String, String> mappings = new HashMap<>();
mappings.put("CREATION_DATE", "creationDate");
sql2o.setDefaultColumnMappings(mappings);

Of course, this will cause every instance of creation_date to be mapped to creationDate, so that’s another reason for striving to keep names consistent across the definitions of our data.

5.2. Scalar Results

Sometimes, we want to extract a single scalar result from a query. For example, when we need to count the number of records.

In those cases, defining a class and iterating over a list that we know to contain a single element is overkill. Thus, Sql2o includes the executeScalar method:

Query query = connection.createQuery(
    "select count(*) from project");
assertEquals(2, query.executeScalar(Integer.class));

Here, we’re specifying the return type to be Integer. However, that’s optional and we can let the underlying JDBC driver decide.

5.3. Complex Results

Sometimes instead, complex queries (such as for reporting) may not easily map onto a Java object. We might also decide that we don’t want to code a Java class to use only in a single query.

Thus, Sql2o also allows a lower-level, dynamic mapping to tabular data structures. We get access to that using the executeAndFetchTable method:

Query query = connection.createQuery(
    "select * from project order by id");
Table table = query.executeAndFetchTable();

Then, we can extract a list of maps:

List<Map<String, Object>> list = table.asList();
assertEquals("tutorials", list.get(0).get("name"));

Alternatively, we can map the data onto a list of Row objects, that are mappings from column names to values, akin to ResultSets:

List<Row> rows = table.rows();
assertEquals("tutorials", rows.get(0).getString("name"));

6. Binding Query Parameters

Many SQL queries have a fixed structure with a few parameterized portions. We might naively write those partially dynamic queries with string concatenation.

However, Sql2o allows parameterized queries, so that:

• We avoid SQL injection attacks
• We allow the database to cache often-used queries and gain in performance
• Finally, we are spared from the need to encode complex types such as dates and times

So, we can use named parameters with Sql2o to achieve all of the above. We introduce parameters with a colon and we bind them with the addParameter method:

Query query = connection.createQuery(
    "insert into project (name, url) values (:name, :url)")
    .addParameter("name", "REST with Spring")
    .addParameter("url", "github.com/eugenp/REST-With-Spring");
assertEquals(1, query.executeUpdate().getResult());

6.1. Binding From a POJO

Sql2o offers an alternative way of binding parameters: that is, by using POJOs as the source. This technique is particularly suitable when a query has many parameters and they all refer to the same entity. So, let’s introduce the bind method:

Project project = new Project();
project.setName("REST with Spring");
project.setUrl("github.com/eugenp/REST-With-Spring");
connection.createQuery(
    "insert into project (name, url) values (:name, :url)")
    .bind(project)
    .executeUpdate();
assertEquals(1, connection.getResult());

7. Transactions and Batch Queries

With a transaction, we can issue multiple SQL statements as a single operation that is atomic. That is, either it succeeds or it fails in bulk, with no intermediate results. In fact, transactions are one of the key features of relational databases.

In order to open a transaction, we use the beginTransaction method instead of the open method that we’ve used so far:

try (Connection connection = sql2o.beginTransaction()) {
    // here, the transaction is active
}

When execution leaves the block, Sql2o automatically rolls back the transaction if it’s still active.

7.1. Manual Commit and Rollback

However, we can explicitly commit or rollback the transaction with the appropriate methods:

try (Connection connection = sql2o.beginTransaction()) {
    boolean transactionSuccessful = false;
    // perform some operations
    if(transactionSuccessful) {
        connection.commit();
    } else {
        connection.rollback();
    }
}

Note that both commit and rollback end the transaction. Subsequent statements will run without a transaction, thus they won’t be automatically rolled back at the end of the block.

However, we can commit or rollback the transaction without ending it:

try (Connection connection = sql2o.beginTransaction()) {
    List list = connection.createQuery("select * from project")
        .executeAndFetchTable()
        .asList();
    assertEquals(0, list.size());
    // insert or update some data
    connection.rollback(false);
    // perform some other insert or update queries
}
// implicit rollback
try (Connection connection = sql2o.beginTransaction()) {
    List list = connection.createQuery("select * from project")
        .executeAndFetchTable()
        .asList();
    assertEquals(0, list.size());
}

7.2. Batch Operations

When we need to issue the same statement many times with different parameters, running them in a batch provides a great performance benefit.

Fortunately, by combining two of the techniques that we’ve described so far – parameterized queries and transactions – it’s easy enough to run them in batch:

• First, we create the query only once
• Then, we bind the parameters and call addToBatch for each instance of the query
• Finally, we call executeBatch:

try (Connection connection = sql2o.beginTransaction()) {
    Query query = connection.createQuery(
        "insert into project (name, url) " +
        "values (:name, :url)");
    for (int i = 0; i < 1000; i++) {
        query.addParameter("name", "tutorials" + i);
        query.addParameter("url", "https://github.com/eugenp/tutorials" + i);
        query.addToBatch();
    }
    query.executeBatch();
    connection.commit();
}
try (Connection connection = sql2o.beginTransaction()) {
    assertEquals(
        1000L,
        connection.createQuery("select count(*) from project").executeScalar());
}

7.3. Lazy Fetch

Conversely, when a single query returns a great number of results, converting them all and storing them in a list is heavy on memory.

So, Sql2o supports a lazy mode, where rows are returned and mapped one at a time:

Query query = connection.createQuery("select * from project");
try (ResultSetIterable<Project> projects = query.executeAndFetchLazy(Project.class)) {
    for(Project p : projects) {
        // do something with the project
    }
}

Note that ResultSetIterable is AutoCloseable and is meant to be used with try-with-resources to close the underlying ResultSet when finished.

8. Conclusions

In this tutorial, we’ve presented an overview of the Sql2o library and its most common usage patterns. Further information can be found in the Sql20 wiki on GitHub.