Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

LightAccess - A Simple JDBC DSL

DZone's Guide to

LightAccess - A Simple JDBC DSL

In this post we take a look at a DSL solution to work with your next JDBC project. Come take a look as the author walks us through its usage, complete with code!

· Java Zone
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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 a 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 boilerplate code. For that reason, I decided to externalize a library I created in one of our internal projects at Codurance. LightAccess is a very simple DSL on top of JDBC that I use to in my repositories.

A Quick Overview of Some of the Features

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

The main class to look at is LightAccess. I recommend having 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, normalize one-to-many relationships, execute INSERT, DELETE, UPDATE statements, return value from sequences, please check LightAccess GitHub repository.

This article was first published on the Codurance blog.

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:
code ,data ,jdbc ,dsl ,java

Published at DZone with permission of Sandro Mancuso, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}