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;
}
}
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.