jdbc-middleware

Maven dependency

<dependency>
    <groupId>no.obje</groupId>
    <artifactId>jdbc-middleware</artifactId>
    <version>1.0.0</version>
</dependency>

What is jdbc-middleware

jdbc-middleware attempts to simplify interaction with the java JDBC API, by acting as a thin abstraction layer.

What is jdbc-middleware not

jdbc-middleware is not an ORM. It does not attempt to shield a developer from SQL.

Features

• Simpler interactions with JDBC
• Transactions
• Ability to interect directly with the datasource when needed
• No dependencies aside from the slf4j-api

Requirements

• Java 8

Code examples

How to interact with JDBC using this library

First, instantiate an instance of the JdbcService, which will be the access point for JDBC interactions using this library.

import org.hsqldb.jdbc.JDBCDataSource;
import JdbcService;

JDBCDataSource jdbcDataSource = new JDBCDataSource(); // lets use a HSQL datasource for this example
jdbcDataSource.setDatabase("jdbc:hsqldb:mem:db");
JdbcService jdbcService = new JdbcService(dataSource);

Congratulations, you've now got a ready to go JdbcService!

How to select multiple rows

Say, you've got a table of employees, which have a column for ID and a column for NAME, how would one go about fetching the names of all the employees?

List<String> names = jdbcService.queryForList(
    "SELECT * FROM employee",
    queryResult -> queryResult.requireString("name")
);

How to select a single row

Now, we'd like to just fetch the name of the employee with the ID of 1.

Optional<String> name = jdbcService.queryForSingle(
    "SELECT * FROM employee WHERE id = ?",
    queryResult -> queryResult.requireString("name"),
    1
);

How about insertions?

Lets say we want to insert a row with a new name (let's assume the ID is generated by the DB in this scenario).

jdbcService.insert("INSERT INTO employee (name) VALUES (?)", "Gregor Clegane");

Or, how about inserting a row where we want to get an auto generated key back as a result.

String idColumnName = "id";
Long id = jdbcService.insertAndReturnKey("INSERT INTO employee (name) VALUES (?)", idColumnName, "Jon Snow");

Transactions

Annother use case might be that we want to do one or multiple statements inside a transaction. In order to do so, we need to initialize JdbcService with a ConnectionManager, instead of giving it a DataSource directly. We also need to instantiate a TransactionManager which will be used to manage transactions.

DataSource dataSource = // create some data source
final ConnectionManager connectionManager = new ConnectionManager(dataSource);
final TransactionManager transactionManager = new TransactionManager(connectionManager);
final JdbcService jdbcService = new JdbcService(connectionManager);

We now have a JdbcService we can use to execute queries and a TransactionManager we can use to manage transactions.

So how do we actually execute statements inside a transaction? Like so:

transactionManager.doInTransaction(() -> {
    jdbcService.deleteSingle("DELETE FROM customer WHERE id = ?", 1);
    throw new RuntimeException("Something went wrong!");
});

In this scenario the delete statement would be rolled back, due to an unhandled exception being thrown inside the Runnable the TransactionManager is executing.

And more

This library also supports deleting one or multiple rows, updating one or multiple rows and general update statements.

For the time being the best place to look for documentation of behaviour is in the tests of this project.