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

Build vs Buy a Data Quality Solution: Which is Best for You? Gain insights on a hybrid approach. Download white paper now!

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.

Build vs Buy a Data Quality Solution: Which is Best for You? Maintaining high quality data is essential for operational efficiency, meaningful analytics and good long-term customer relationships. But, when dealing with multiple sources of data, data quality becomes complex, so you need to know when you should build a custom data quality tools effort over canned solutions. Download our whitepaper for more insights into a hybrid approach.

Topics:
code ,data ,jdbc ,dsl ,java

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}