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
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.
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 create 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());
DDLCommand
The LightAccess.executeDDLCommand(DDLCommand command) receives a DDLCommand as parameter.
public interface DDLCommand {
void execute(LAConnection connection) throws SQLException;
}
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:
private static final String CREATE_USERS_TABLE = "CREATE TABLE users (userId integer PRIMARY KEY, name VARCHAR(255))";
private static final String CREATE_WISHLISTS_TABLE = "CREATE TABLE wishlists (wishListId integer PRIMARY KEY, userId integer, name VARCHAR(255), creationDate TIMESTAMP)";
private static final String CREATE_PRODUCTS_TABLE = "CREATE TABLE products (productId integer PRIMARY KEY, name VARCHAR(255), date TIMESTAMP)";
private static final String CREATE_WISHLIST_PRODUCT_TABLE = "CREATE TABLE wishlist_product (id integer PRIMARY KEY, wishListId integer, productId integer)";
public void create_all_tables() {
lightAccess.executeDDLCommand(createTables());
}
private DDLCommand createTables() {
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.
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 - single result
Let’s take the following select statement.
private static final String SELECT_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>.
private SQLQuery<Optional<Product>> retrieveProductWithId(int id) {
return conn -> 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.
private Product toProduct(LAResultSet laResultSet) {
return new Product(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:
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);
}
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:
CREATE TABLE users (userId integer PRIMARY KEY, name VARCHAR(255));
CREATE TABLE wishlists (wishListId integer PRIMARY 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.
select u.userId, u.name, w.wishListId, w.userId, w.name, w.creationDate
from users u
left join wishlists w on u.userId = w.userId
We want the result to be stored in a list containing the following DTO:
public class UserWithWishList {
private final User user;
private final List<WishList> wishLists;
public UserWithWishList(User user, 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:
public class User {
private final Integer id;
private final String name;
public User(Integer id, String name) {
this.id = id;
this.name = name;
}
// getters, equals, hashcode.
}
public class WishList {
private final Integer id;
private final Integer userId;
private final String name;
private final LocalDate creationDate;
public WishList(Integer id, Integer userId, String name, LocalDate creationDate) {
this.id = id;
this.userId = userId;
this.name = name;
this.creationDate = creationDate;
}
}
So now we are ready to get a list of UserWithWishList objects:
public List<UserWithWishList> usersWithWishLists() {
OneToMany<User, WishList> wishListsPerUser = lightAccess.executeQuery((conn ->
conn.prepareStatement(SELECT_WISHLISTS_PER_USER_SQL)
.executeQuery()
.normaliseOneToMany(this::mapToUserWishList)))
return wishListsPerUser.collect((user, wishLists) -> new UserWithWishList(user, wishLists));
}
private KeyValue<User, Optional<WishList>> mapToUserWishList(LAResultSet laResultSet) {
User user = new User(laResultSet.getInt(1), laResultSet.getString(2));
Optional<WishList> wishList = Optional.ofNullable((laResultSet.getInt(3) > 0)
? new WishList(laResultSet.getInt(3),
laResultSet.getInt(4),
laResultSet.getString(5),
laResultSet.getLocalDate(6))
: null);
return new KeyValue<>(user, wishList);
}
For more details, please check the integration tests for joins
Insert
Let’s assume we have the following product table:
CREATE TABLE products (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), date TIMESTAMP)
And we have the following Product DTO.
public class Product {
private int id;
private String name;
private LocalDate date;
public Product(int id, String name, LocalDate date) {
this.id = id;
this.name = name;
this.date = date;
}
public int id() {
return id;
}
public String name() {
return name;
}
public LocalDate date() {
return date;
}
// equals, hashcode
}
For inserting a product, we just need to do the following:
INSERT_PRODUCT_SQL = "insert into products (id, name, date) values (?, ?, ?)";
Product product = new Product(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:
private SQLCommand insert(Product product) {
return conn -> 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.
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();
}
Delete
Delete is exactly the same as inserts and updates.
Calling sequences (PostgreSQL / H2)
Let’s first create a sequence:
private static final String ID_SEQUENCE = "id_sequence";
private static final String CREATE_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.
int id = lightAccess.nextId(ID_SEQUENCE);
In case we don’t want an int ID, we can also map the ID to something else:
ProductID secondId = lightAccess.nextId(ID_SEQUENCE, ProductID::new);
Where ProductID is:
public class ProductID {
private int id;
public ProductID(int id) {
this.id = id;
}
// getter, equals, hashcode
}
We can also map that to String or any other object:
String stringID = 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.