Light Access

Light Access is a small library on top of JDBC, under the Apache 2 license.

Who should use this library?

This library is for Java developers who:

• Want full control of their code.
• Want a nice and fluid API on top of JDBC, using lambdas.
• Prefer to use non-intrusive small libraries instead of intrusive ORM frameworks.
• Want to reduce boiler plate code from their repositories.
• Don't want to deal with JDBC's complexities and annoying exception handling.
• Don't like to use any sort of automatic binding between their data structures and database.

Table of Contents

1. Installing LightAccess
2. Getting started
3. DDL statements
   1. DDLCommand
   2. Executing multiple DDL commands
4. DML statements
   1. Select - single result
   2. Select - multiple results
   3. Normalising one to many joins
   4. Insert
   5. Update
   6. Delete
   7. Statement, PreparedStatement and CallableStatement
5. Further documentation
   1. Databases tested
6. History

Installing Light Access

Maven

<dependency>
<groupId>com.codurance</groupId>
<artifactId>light-access</artifactId>
<version>0.1.0</version>
</dependency>

Gradle

compile group: 'com.codurance', name: 'light-access', version: '0.1.0' 

Getting started

The main class to look at is LightAccess. We 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.

importcom.codurance.lightaccess.LightAccess;
importorg.h2.jdbcx.JdbcConnectionPool;

JdbcConnectionPooljdbcConnectionPool = JdbcConnectionPool.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "user", "password");
LightAccesslightAccess = newLightAccess(jdbcConnectionPool);

Executing DDL statements

First let's define a DDL statement which create a table called 'products' with 3 fields.

privatestaticfinalStringCREATE_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.

privateDDLCommandcreateProductsTable() {
return (conn) -> conn.statement(CREATE_PRODUCTS_TABLE).execute();
}

And use it like this.

lightAccess.executeDDLCommand(createProductsTable());

DDLCommand

The LightAccess.executeDDLCommand(DDLCommand command) receives a DDLCommand as parameter.

publicinterfaceDDLCommand {
voidexecute(LAConnectionconnection) throwsSQLException;
}

With that, you can pass in any lambda that satisfy the execute(LAConnection connection) method signature.

Executing multiple DDL statements

It is possible to execute multiple commands in one go:

privatestaticfinalStringCREATE_USERS_TABLE = "CREATE TABLE users (userId integer PRIMARY KEY, name VARCHAR(255))";
privatestaticfinalStringCREATE_WISHLISTS_TABLE = "CREATE TABLE wishlists (wishListId integer PRIMARY KEY, userId integer, name VARCHAR(255), creationDate TIMESTAMP)";
privatestaticfinalStringCREATE_PRODUCTS_TABLE = "CREATE TABLE products (productId integer PRIMARY KEY, name VARCHAR(255), date TIMESTAMP)";
privatestaticfinalStringCREATE_WISHLIST_PRODUCT_TABLE = "CREATE TABLE wishlist_product (id integer PRIMARY KEY, wishListId integer, productId integer)";

publicvoidcreate_all_tables() {
lightAccess.executeDDLCommand(createTables());
}
privateDDLCommandcreateTables() {
return (conn) -> {
conn.statement(CREATE_USERS_TABLE).execute();
conn.statement(CREATE_WISHLISTS_TABLE).execute();
conn.statement(CREATE_PRODUCTS_TABLE).execute();
conn.statement(CREATE_WISHLIST_PRODUCT_TABLE).execute();
};
}

Executing DML statements

Let's assume we have an object Product that we want to map to the products table.

publicclassProduct {
privateintid;
privateStringname;
privateLocalDatedate; Product(intid, Stringname, LocalDatedate) {
this.id = id;
this.name = name;
this.date = date;
}
// getters// equals and hashcode 
}

Select - single result

Let's take the following select statement.

privatestaticfinalStringSELECT_PRODUCT_BY_ID_SQL = "select * from products where id = ?";

Now let's create a method that returns a lambda for this select statement. As we are looking for a single entity and we may not find it, it would be good if our query (SQLQuery) returned an Optional<Product>.

privateSQLQuery<Optional<Product>> retrieveProductWithId(intid) {
returnconn -> conn.prepareStatement(SELECT_PRODUCT_BY_ID_SQL)
.withParam(id)
.executeQuery()
.onlyResult(this::toProduct);
}

In case we find a product with this ID, we need to map the result set to the Product object. This is done in the toProduct method passed to .onlyResult() above.

privateProducttoProduct(LAResultSetlaResultSet) {
returnnewProduct(laResultSet.getInt(1),
laResultSet.getString(2),
laResultSet.getLocalDate(3));
}

Now we only need to execute the select statement.

Optional<Product> product = lightAccess.executeQuery(retrieveProductWithId(10));

In case you prefer an inline version, you can use:

Optional<Product> product = lightAccess.executeQuery(conn -> conn.prepareStatement(SELECT_PRODUCT_BY_ID_SQL)
.withParam(PRODUCT_TWO.id)
.executeQuery()
.onlyResult(this::toProduct));

Select - multiple results

Let's take the following select statement:

privatestaticfinalStringSELECT_ALL_PRODUCTS_SQL = "select * from products";

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

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

Note that now we are calling mapResults(this::toProduct) instead of onlyResult(this::toProduct), and the SQLQuery is parameterised to return List<Product>.

Now we just need to invoke the query like before.

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));

Normalising one to many joins

Let's say we have a table with users and a table with wish lists:

CREATETABLEusers (userId integerPRIMARY KEY, name VARCHAR(255));
CREATETABLEwishlists (wishListId integerPRIMARY KEY, userId integer, name VARCHAR(255), creationDate TIMESTAMP);

Now let's assume we want to have all users and their respective wish lists, including the users without wish list.

selectu.userId, u.name, w.wishListId, w.userId, w.name, w.creationDatefrom users u left join wishlists w onu.userId=w.userId

We want the result to be stored in a list containing the following DTO:

publicclassUserWithWishList {
privatefinalUseruser;
privatefinalList<WishList> wishLists;
publicUserWithWishList(Useruser, List<WishList> wishLists) {
this.user = user;
this.wishLists = unmodifiableList(wishLists);
}
// getters, equals, hashcode.
}

For this to work we need to have a DTO for user and a DTO for the wish list:

publicclassUser {
privatefinalIntegerid;
privatefinalStringname;
publicUser(Integerid, Stringname) {
this.id = id;
this.name = name;
}
// getters, equals, hashcode.
}

publicclassWishList {
privatefinalIntegerid;
privatefinalIntegeruserId;
privatefinalStringname;
privatefinalLocalDatecreationDate;
publicWishList(Integerid, IntegeruserId, Stringname, LocalDatecreationDate) {
this.id = id;
this.userId = userId;
this.name = name;
this.creationDate = creationDate;
}
}

So now we are ready to get a list of UserWithWishList objects:

publicList<UserWithWishList> usersWithWishLists() {
OneToMany<User, WishList> wishListsPerUser = lightAccess.executeQuery((conn -> conn.prepareStatement(SELECT_WISHLISTS_PER_USER_SQL)
.executeQuery()
.normaliseOneToMany(this::mapToUserWishList)))
returnwishListsPerUser.collect((user, wishLists) -> newUserWithWishList(user, wishLists));
}
privateKeyValue<User, Optional<WishList>> mapToUserWishList(LAResultSetlaResultSet) {
Useruser = newUser(laResultSet.getInt(1), laResultSet.getString(2));
Optional<WishList> wishList = Optional.ofNullable((laResultSet.getInt(3) > 0)
? newWishList(laResultSet.getInt(3),
laResultSet.getInt(4),
laResultSet.getString(5),
laResultSet.getLocalDate(6))
: null);
returnnewKeyValue<>(user, wishList);
} 

For more details, please check the integration tests for joins

Insert

Let's assume we have the following product table:

CREATETABLEproducts (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), dateTIMESTAMP)

And we have the following Product DTO.

publicclassProduct {
privateintid;
privateStringname;
privateLocalDatedate;
publicProduct(intid, Stringname, LocalDatedate) {
this.id = id;
this.name = name;
this.date = date;
}
publicintid() {
returnid;
}
publicStringname() {
returnname;
}
publicLocalDatedate() {
returndate;
}
// equals, hashcode
}

For inserting a product, we just need to do the following:

INSERT_PRODUCT_SQL = "insert into products (id, name, date) values (?, ?, ?)";
Productproduct = newProduct(1, "Product 1", LocalDate.of(2017, 07, 26));
lightAccess.executeCommand(conn -> conn.prepareStatement(INSERT_PRODUCT_SQL)
.withParam(product.id())
.withParam(product.name())
.withParam(product.date())
.executeUpdate());

And as always, can extract the lambda to a method:

privateSQLCommandinsert(Productproduct) {
returnconn -> conn.prepareStatement(INSERT_PRODUCT_SQL)
.withParam(product.id())
.withParam(product.name())
.withParam(product.date())
.executeUpdate();
}

And call it like that:

lightAccess.executeCommand(insert(produt));

Update

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

privatestaticfinalStringUPDATE_PRODUCT_NAME_SQL = "update products set name = ? where id = ?";

Now we can execute the update:

lightAccess.executeCommand(updateProductName(1, "Another name"));

privateSQLCommandupdateProductName(intid, Stringname) {
returnconn -> conn.prepareStatement(UPDATE_PRODUCT_NAME_SQL)
.withParam(name)
.withParam(id)
.executeUpdate();
}

Delete

Delete is exactly the same as inserts and updates.

Calling sequences (PostgreSQL / H2)

Let's first create a sequence:

privatestaticfinalStringID_SEQUENCE = "id_sequence";
privatestaticfinalStringCREATE_SEQUENCE_DDL = "CREATE SEQUENCE " + ID_SEQUENCE + " START WITH 1";

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

Now we can read the next ID from it.

intid = lightAccess.nextId(ID_SEQUENCE);

In case we don't want an int ID, we can also map the ID to something else:

ProductIDsecondId = lightAccess.nextId(ID_SEQUENCE, ProductID::new);

Where ProductID is:

publicclassProductID {
privateintid;
publicProductID(intid) {
this.id = id;
}
// getter, equals, hashcode
}

We can also map that to String or any other object:

StringstringID = lightAccess.nextId(ID_SEQUENCE, Object::toString);

Creating Statement, PreparedStatement and CallableStatement

An instance of LAConnection will be received in all queries and commands represented by DDLCommand, SQLCommand and SQLQuery. With this instance you can create a Statement, PreparedStatement and CallableStatement, according to your need.

As a guideline, we normally use a Statement for DDL, a PreparedStatement for DML and CallableStatement for calling stored procedures or sequences.

Further documentation

Please check the tests for more details in how to use this library.

Databases tested

We have only tested this library with Amazon RDS for PostgreSQL.

History

This library was first created by Sandro Mancuso while refactoring and removing duplication from multiple repositories in one of the Codurance's internal projects.