• en | es

Light Access - A simple JDBC DSL

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.

About the author

Software craftsman, author, and founder of the London Software Craftsmanship Community (LSCC). Sandro has been coding since a very young age but only started his professional career in 1996. He has worked for startups, software houses, product companies, international consultancy companies, and investment banks.

During his career Sandro had the opportunity to work in a good variety of projects, with different languages, technologies, and across many different industries. Sandro has a lot of experience in bringing the Software Craftsmanship ideology and Extreme Programming practices to organisations of all sizes. Sandro is internationally renowned by his work on evolving and spreading Software Craftsmanship and is frequently invited to speak in many conferences around the world. His professional aspiration is to raise the bar of the software industry by helping developers become better at and care more about their craft.

Subscribe to newsletter