--- title: Light Access - A simple JDBC DSL | Codurance description: Our team of dedicated software craftspeople provides consultancy, software development, and training services to clients seeking high quality development processes and software solutions. --- [Publications | Codurance ](https://www.codurance.com/publications) # [Light Access - A simple JDBC DSL | Codurance](https://www.codurance.com/publications/2017/08/02/light-access-dsl) Written by [Sandro Mancuso, Co-founder and Group CEO](https://www.codurance.com/publications/author/sandro-mancuso) | 01 Aug 2017 I prefer not to couple my entities (or data structures as I prefer to call them) to my database, neither via [annotations](https://docs.oracle.com/javase/tutorial/java/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](https://github.com/codurance/light-access) is a very simple [DSL](https://en.wikipedia.org/wiki/Domain-specific_language) on top of [JDBC](http://www.oracle.com/technetwork/java/overview-141217.html) that I use in the implementation of my [repositories](https://martinfowler.com/eaaCatalog/repository.html). ## A quick overview of some of the features *For a full view of all the features, please check [Light Access GitHub repository](https://github.com/codurance/light-access)* The main class to look at is [LightAccess](https://github.com/codurance/light-access/blob/master/src/main/java/com/codurance/lightaccess/LightAccess.java). I recommend to have this class injected into your repositories. LightAccess receives a [DataSource](https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html) in its constructor and you can pass a connection pool to it. Let's do it using [h2](http://www.h2database.com/html/main.html). ``` 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> 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 products = lightAccess.executeQuery(retrieveAllProducts()); ``` And in case you prefer the inlined version: ``` List 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](https://github.com/codurance/light-access). [View full post](https://www.codurance.com/publications/2017/08/02/light-access-dsl) ```json { "@context" : "http://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Sandro Mancuso, Co-founder and Group CEO" }, "dateModified" : "2021-12-02T22:51:24.818Z", "datePublished" : "2017-08-01T23:00:00Z", "headline" : "Light Access - A simple JDBC DSL", "image" : { "@type" : "ImageObject", "height" : 800, "url" : "https://f.hubspotusercontent10.net/hubfs/3042464/Imported_Blog_Media/2017-08-02-light-access-1.png", "width" : 722 }, "mainEntityOfPage" : "https://www.codurance.com/publications/2017/08/02/light-access-dsl", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "name" : "Publications" } } ```