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

Поддержка транзакций, тестового окружения и другие удобные надстройки над 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-ы результирующих классов.

Автор: gkislin

Источник