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

    Подробнее
    Реклама
    Комментарии 2
    • 0
      Хех, плюс вам. Сам недавно такой велосипед написал, только потом пришел к выводу, что в SpringJDBC это уже есть, и получается тоже самое и нифига не быстрее :) Думал сначала, что побыстрому напишу без шелухи, но тут написал фабрику, там интефейс для wrapper'а логики и опа — spring jdbc.
      • 0
        ну да, похоже, полегче и без Spring. у меня куча небольших модулей (SOA). С каждым Spring с собой не хочется тащить.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.