Поддержка транзакций, тестового окружения и другие удобные надстройки над commons-dbutils.

"/>
  Если обращение к RDBMS вашего проекта ведется не через ORM или Spring JdbcTemplate, то работу через голый JDBC может сильно скрасить commons-dbutils.
Особенно если его надстроить транзакциями, работой с разными окружениями, авто-открытием/закрытием ресурсов, запрсами read-only, обработкой SqlException, возвратом pk при вставке и т.д.
Т.е, например, чтобы можно было бы сделать так:

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

И этот код работал бы внутри транзакции, через Application Server Connction pool и в JUnit без изменений и mock-ов.
За чуть меньше года моей работы с commons-dbutils он оброс различными классами и утильными методами, о которых и хочу здесь рассказать.

Работа в разных окружениях

Используя Инициализацию по тербованию через интерфейс DataSourceFactory получаем DataSource, объявленный в 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(); }

Для тестов подменяем его на 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;         }     }; }

Если креденшелы к базе находятся в профилях maven settings.xml, то можно инициализировать тесты, основываясь на maven profile.
Создаем test/resources/project.properties c креденшелами базы:

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

Фильтруем его переменными из settings:

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

и загружаем properties через getResourceAsStream:

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

Остается в начале тестов инициализировать TestSql.initProfileDb() и все запросы к базе выбранного профиля (mvn -P env test) будут идти через commons-dbcp.

Работа с ресурсами и исключениями

Добавляем в ConnectionFactory получение коннекта и его закрытие:

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

Объявляем интерфейс где собственно будет логика запроса

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

и его обертку:

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

Теперь все запросы к базе будут идти через нашу обертку:

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

Скаляры достаем через утильные методы:

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

Получение PK при вставке

Пока Generated key handling for updates в статусе Unresolved, приходится делать все самим:

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

Если ключи типа long — не забудте заменить int->long, Integer->Long.

Транзакционность

Наконец, какая работа с базой без транзакций?
Допустим, у нас есть какой-то метод, обращающийся к базе:

checkAssess(final int docId, final Access accessMode)

Connection он не принимает, а нам захотелось использовать его внутри транзакции.
И хотелось бы не дублировать его в

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

Делаем транзакционную обертку:

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

и кладем коннекшен транзакции в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); }

И учитываем возможность транзакции в нашем 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);     } }

Теперь любой наш запрос к базе через Execute внутри транзакции будет работать с транзакционным коннектом.
Но также останется возможность внутри транзакции обращаться к базе не через 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, ...);             }         });

Подход простой по сравнению, например, с Spring TransactionManager, нет вложенных транзакций, но для 99% случаев этого достаточно. Особенно если не хочется тянуть за собой весь Spring или ORM.

Наконец, чтобы не писать тип Void и return null, сделаем возможность запросов без возвращения результата (аналог 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)); }

Если Вам понравился подход, то собрав из кусков классы вы будете иметь удобный (и уже знакомый) инструментарий для несложной работы с вашей базой.
Спасибо за внимание. Ссылки на проекты, которые уже реализуют нечто похожее и конструктивная критика приветствуется.

PS: при использовании BeanHandler и BeanListHandler маппинга ResultSet в бины java не забывайте про конструктор по умолчанию и setter-ы результирующих классов.