Light Access - A simple JDBC DSL

02 Aug 2017

I prefer not to couple my entities (or data structures as I prefer to call them) to my database, neither via annotations nor via frameworks that use naming convention. I like to have total freedom to map whatever data in whatever format I have stored to whatever data structure I want to use in each business flow. As I like to test-drive everything I do, I also like to have full control of my code. However, I don’t want to write a lot of boiler plate code. For that reason I decided to externalise a library I created in one of our internal projects at Codurance. Light Access is a very simple DSL on top of JDBC that I use in the implementation of my repositories.

A quick overview of some of the features

For a full view of all the features, please check Light Access GitHub repository

The main class to look at is LightAccess. I recommend to have this class injected into your repositories.

LightAccess receives a DataSource in its constructor and you can pass a connection pool to it. Let's do it using h2.

import com.codurance.lightaccess.LightAccess;
import org.h2.jdbcx.JdbcConnectionPool;
JdbcConnectionPool jdbcConnectionPool = JdbcConnectionPool.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "user", "password");
LightAccess lightAccess = new LightAccess(jdbcConnectionPool);

Executing DDL statements

First let's define a DDL statement which creates a table called products with 3 fields:

    private static final String CREATE_PRODUCTS_TABLE = 
        "CREATE TABLE products (id integer PRIMARY KEY, name VARCHAR(255), date TIMESTAMP)";

So now, the only thing we need to do is to use the LightAccess to execute this DDL command.

    lightAccess.executeDDLCommand((conn) -> conn.statement(CREATE_PRODUCTS_TABLE).execute());

And that's it. No exception handling or dealings with database connections. It is all handled for you.

Alternatively, you can extract the lambda to a method.

    private DDLCommand createProductsTable() {
        return (conn) -> conn.statement(CREATE_PRODUCTS_TABLE).execute();
    }

And use it like this.

    lightAccess.executeDDLCommand(createProductsTable());

Executing DML statements

Let's assume we have an object Product that we want to populate with data stored in the products table.

    public class Product {
        private int id;
        private String name;
        private LocalDate date;    

        Product(int id, String name, LocalDate date) {
            this.id = id;
            this.name = name;
            this.date = date;
        }

        // getters
        // equals and hashcode    
    }

Select - multiple results

Let's take the following select statement:

    private static final String SELECT_ALL_PRODUCTS_SQL = "select * from products";

Now let's create a method that returns a lambda:

    private SQLQuery<List<Product>> retrieveAllProducts() {
        return conn -> conn.prepareStatement(SELECT_ALL_PRODUCTS_SQL)
                            .executeQuery()
                            .mapResults(this::toProduct);
    }

For mapping the database results to Product objects we need to pass a lambda toProduct:

    private Product toProduct(LAResultSet laResultSet) {
        return new Product(laResultSet.getInt(1),
                           laResultSet.getString(2),
                           laResultSet.getLocalDate(3));
    }

Now we just need to invoke the query.

    List<Product> products = lightAccess.executeQuery(retrieveAllProducts());

And in case you prefer the inlined version:

    List<Product> products = lightAccess.executeQuery(conn -> 
        conn.prepareStatement(SELECT_ALL_PRODUCTS_SQL)
            .executeQuery()
            .mapResults(this::toProduct));

Update

Let's say that we want to update the name of the given product.

private static final String UPDATE_PRODUCT_NAME_SQL = "update products set name = ? where id = ?";

Now we can execute the update:

    lightAccess.executeCommand(updateProductName(1, "Another name"));
    private SQLCommand updateProductName(int id, String name) {
        return conn -> conn.prepareStatement(UPDATE_PRODUCT_NAME_SQL)
                            .withParam(name)
                            .withParam(id)
                            .executeUpdate();
    }

Further documentation

For the full documentation on how to execute multiple DDL statements, return a single record, map joins, normalise one-to-many relationships, execute INSERT, DELETE, UPDATE statements, return value from sequences, please check Light Access GitHub repository.

Related Blogs

Get content like this straight to your inbox!

Codurance Logo

Software is our passion.

We are software craftspeople. We build well-crafted software for our clients, we help developers to get better at their craft through training, coaching and mentoring, and we help companies get better at delivering software.

Latest Blogs




Contact Us

3 Sutton Lane
London EC1M 5PU

Phone: +44 207 4902967

2 Mount Street
Manchester M2 5WQ

Phone: +44 161 302 6795

Carrer Aragó, 208
08011, Barcelona

Phone: +34 937 82 28 82

Email: hello@codurance.com