Transaction support, test environment, and other convenient add-ins over commons-dbutils .

"/>
If your project is not accessing the RDBMS of your project through an ORM or Spring JdbcTemplate, then working through bare JDBC can greatly brighten commons-dbutils .
Especially if you add it to transactions, work with different environments, auto-open / close resources, read-only requests, handle SqlException, return pk when inserting, etc.
Ie, for example, to be able to do this:

int userId = Sql.queryForValue("SELECT ID FROM OU.USERS WHERE LAST_NAME=?", "IVANOV"); Sql.update("DELETE FROM OU.USERS WHERE FIRST_NAME=? AND LAST_NAME=?", fname, lname); List<User> list = Sql.query("SELECT FROM OU.USERS", USER_LIST_HANDLER); int newId = Sql.insert("INSERT INTO OU.USERS VALUES (?,?)", fname, lname); 

And this code would work inside a transaction, through the Application Server Connction pool and in JUnit unchanged and mock.
For a little less than a year of my work with commons-dbutils, he was surrounded by various classes and utilitarian methods, which I want to tell about here.

Work in different environments

Using initialization via the DataSourceFactory interface, we obtain the DataSource declared in the JNDI AS:

 public class ConnectionFactory { private static final LoggerWrapper LOGGER = LoggerWrapper.get(ConnectionFactory.class); private static class JndiDataSource { private static final DataSource dataSource; static { try { InitialContext ctx = new InitialContext(); dataSource = (DataSource) ctx.lookup("java:/comp/env/jdbc/db_name"); } catch (Exception ex) { throw LOGGER.getIllegalStateException("PostgreSQL initialization failed", ex); } } } interface DataSourceFactory { DataSource getDataSource(); } static DataSourceFactory dataSourceFactory = new DataSourceFactory() { @Override public DataSource getDataSource() { return JndiDataSource.dataSource; } }; public static DataSource getDataSource() { return dataSourceFactory.getDataSource(); } 

For tests, substitute it with commons-dbcp:

  <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> 

 public class TestSq { private static String DB_URL; private static String DB_USER; private static String DB_PSW; private static class DbcpDataSource { private static final BasicDataSource dataSource; static { dataSource = new BasicDataSource(); dataSource.setUrl(DB_URL); dataSource.setUsername(DB_USER); dataSource.setPassword(DB_PSW); } } public static void initDb(String dbUrl, String dbUser, String dbPsw) { DB_URL = dbUrl; DB_USER = dbUser; DB_PSW = dbPsw; ConnectionFactory.dataSourceFactory = new ConnectionFactory.DataSourceFactory() { @Override public DataSource getDataSource() { return DbcpDataSource.dataSource; } }; } 

If the database credentials are in the maven settings.xml profiles, then you can initialize the tests based on the maven profile.
Create test / resources / project.properties with base credits:

  db.url=${db.url} db.user=${db.user} db.password=${db.password} 

Filter it with variables from settings:

  <testResources> <testResource> <filtering>true</filtering> </testResource> 

and load properties via getResourceAsStream:

 public static void initProfileDb() { Properties prop = UtilProperties.loadAsResource("project.properties"); initDb(prop.getProperty("db.url"), prop.getProperty("db.user"), prop.getProperty("db.password")); } 

It remains to initialize the TestSql.initProfileDb () at the beginning of the tests and all queries to the database of the selected profile (mvn -P env test) will go through commons-dbcp.

Work with resources and exceptions

Add a connection to ConnectionFactory and close it:

 static Connection getConnection(boolean isReadOnly) throws SQLException { Connection conn = getDataSource().getConnection(); if (isReadOnly) { conn.setReadOnly(true); } return conn; } static void close(Connection conn) { if (conn != null) { try { if (conn.isReadOnly()) { conn.setReadOnly(false); // restore NOT readOnly before return to pool } conn.close(); } catch (SQLException e) { Sql.warn(e); } } } 

We declare the interface where the request logic will be

 public interface SqlExecutor<T> { T run(Connection conn) throws SQLException; } 

and its wrapper:

 public class Sql { public static <T> T execute(boolean isReadOnly, SqlExecutor<T> executor) { try { return executor.run(ConnectionFactory.getConnection(isReadOnly) } catch (SQLException e) { throw LOGGER.getStateException(ExceptionType.DATA_BASE, e); } finally { ConnectionFactory.close(conn); } } 

Now all requests to the database will go through our wrapper:

 private static final QueryRunner QUERY_RUNNER = new QueryRunner(); public static int update(Connection conn, final String updateSql, final Object... params) throws SQLException { return QUERY_RUNNER.update(conn, updateSql, params); } public static <T> T query(Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException { return QUERY_RUNNER.query(conn, sql, rsh, params); } public static int update(final String updateSql, final Object... params) { return Sql.execute(false, new SqlExecutor<Integer>() { @Override public Integer run(Connection conn) throws SQLException { return update(conn, updateSql, params); } }); } public static <T> T query(final String sql, final ResultSetHandler<T> rsh, final Object... params) { return execute(true, new SqlExecutor<T>() { @Override public T run(Connection conn) throws SQLException { return query(conn, sql, rsh, params); } }); } 

Scalars are obtained through utilitarian methods:

 public static <T> T queryForValue(Connection conn, final String sql, final Object... params) throws SQLException { return query(conn, sql, new ScalarHandler<T>(), params); } public static <T> T queryForValue(final String sql, final Object... params) throws SQLException { return query(sql, new ScalarHandler<T>(), params); } 

Getting PK on insertion

While the generated key handling for updates is in the Unresolved status, you have to do everything yourself:

 public class KeyQueryRunner extends QueryRunner { private static final LoggerWrapper LOGGER = LoggerWrapper.get(KeyQueryRunner.class); private static final ResultSetHandler<Integer> KEY_HANDLER = new ScalarHandler<Integer>(); public int insert(Connection conn, String sql, Object... params) throws SQLException { PreparedStatement stmt = null; int rows = 0; try { stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); this.fillStatement(stmt, params); rows = stmt.executeUpdate(); if (rows != 1) { throw LOGGER.getStateException("   " + sql, ExceptionType.DATA_BASE); } return KEY_HANDLER.handle(stmt.getGeneratedKeys()); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { close(stmt); } return rows; } } private static final KeyQueryRunner KEY_QUERY_RUNNER = new KeyQueryRunner(); public static int insert(Connection conn, final String insertSql, final Object... params) throws SQLException { return KEY_QUERY_RUNNER.insert(conn, insertSql, params); } public static int insert(final String insertSql, final Object... params) { return Sql.execute(false, new SqlExecutor<Integer>() { @Override public Integer run(Connection conn) throws SQLException { return insert(conn, insertSql, params); } }); } 

If the keys are of the long type, do not forget to replace int-> long, Integer-> Long.

Transactional

Finally, how can you work with a database without transactions?
Suppose we have some method that refers to the database:

 checkAssess(final int docId, final Access accessMode) 

It does not accept Connection, but we wanted to use it inside the transaction.
And I would like not to duplicate it in

 checkAssess(Connection conn, final int docId, final Access accessMode) 

Making a transactional wrapper:

 public static <T> T executeInTransaction(SqlExecutor<T> executor) { Connection conn = null; try { conn = ConnectionFactory.getTxConnection(); T res = executor.run(conn); conn.commit(); return res; } catch (Error e) { throw rollback(conn, e); } catch (Exception e) { throw rollback(conn, e); } finally { ConnectionFactory.closeTx(conn); } } private static StateException rollback(Connection conn, Throwable e) { try { if (conn != null) { conn.rollback(); } return LOGGER.getStateException(ExceptionType.DATA_BASE, e); } catch (SQLException se) { return LOGGER.getStateException("Unable to rollback transaction", ExceptionType.DATA_BASE, e); } } 

and put the transaction in the ThreadLocal:

 public class ConnectionFactory { ... private static final ThreadLocal<Connection> TRANSACT_CONN = new ThreadLocal<Connection>(); static Connection getCurrentConn() { return TRANSACT_CONN.get(); } static Connection getTxConnection() throws SQLException { Connection conn = TRANSACT_CONN.get(); if (conn != null) { throw LOGGER.getIllegalStateException("Start second transaction in one thread"); } conn = getDataSource().getConnection(); conn.setAutoCommit(false); TRANSACT_CONN.set(conn); return conn; } static void closeTx(Connection conn) { close(conn); TRANSACT_CONN.set(null); } 

And we consider the possibility of a transaction in our Execute:

 public static <T> T execute(boolean isReadOnly, SqlExecutor<T> executor) { Connection conn = null; Connection txConn = ConnectionFactory.getCurrentConn(); try { return executor.run( (txConn == null) ? ConnectionFactory.getConnection(isReadOnly) : txConn); } catch (SQLException e) { throw LOGGER.getStateException(ExceptionType.DATA_BASE, e); } finally { if (txConn == null) ConnectionFactory.close(conn); } } 

Now, any of our requests to the database via Execute inside the transaction will work with the transactional connection.
But it will also remain possible within the transaction to access the database through an Executor:

  List<Person> list = Sql.executeInTransaction(new SqlExecutor<List<Person>>() { @Override public List<Person> run(Connection conn) throws SQLException { Sql.select(conn, ...); Sql.update(conn, ...); return Sql.select(conn, ...); } }); 

The approach is simple compared to, for example, Spring TransactionManager, there are no nested transactions, but for 99% of cases this is enough. Especially if you do not want to pull the whole Spring or ORM.
')
Finally, in order not to write the Void type and return null, we will make it possible to query without returning the result (analogous to Spring TransactionCallbackWithoutResult)

 public interface SqlExecutorVoid { void run(Connection conn) throws SQLException; } public class Sql { ... private static SqlExecutor<Void> getWrapperExecutor(final SqlExecutorVoid voidExecutor) { return new SqlExecutor<Void>() { @Override public Void run(Connection conn) throws SQLException { voidExecutor.run(conn); return null; } }; } public static void execute(boolean isReadOnly, SqlExecutorVoid executor) { execute(isReadOnly, getWrapperExecutor(executor)); } public static void executeInTransaction(SqlExecutorVoid executor) { executeInTransaction(getWrapperExecutor(executor)); } 

If you like the approach, then collecting classes from the pieces you will have a convenient (and already familiar) toolkit for simple work with your base.
Thanks for attention. Links to projects that are already implementing something similar and constructive criticism is welcome.

PS: when using BeanHandler and BeanListHandler mapping ResultSet into java bins, do not forget about the default constructor and the setter s of the resulting classes.