QueryRunner

简单的sql数据查询与执行，比JdbcTemplate轻量级。

官网：https://commons.apache.org/proper/commons-dbutils/

        <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.8.1</version>
        </dependency>

简单使用（基于quarkus）

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;
import javax.sql.DataSource;

import cn.vt.exception.CommonException;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;
import jakarta.transaction.Transactional;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

@ApplicationScoped
@Slf4j
public class SqlServiceImpl {

    @Inject
    DataSource dataSource;

    private final QueryRunner runner = new QueryRunner();

    public <T> T queryForObject(String sql, Class<T> clazz, final Object... params) {
        try (Connection conn = dataSource.getConnection()) {
            return runner.query(conn, sql, new BeanHandler<T>(clazz), params);
        } catch (Exception e) {
            log.error("error when queryForObject", e);
        }
        return null;
    }

    /**
     * queryForList.
     *
     * @param sql    sql字段需要as成和对象(T)属性一样。
     * @param clazz  clazz
     * @param params params
     * @param <T>    T
     * @return List(T)
     */
    public <T> List<T> queryForList(String sql, Class<T> clazz, final Object... params) {
        try (Connection conn = dataSource.getConnection()) {
            return runner.query(conn, sql, new BeanListHandler<T>(clazz), params);
        } catch (Exception e) {
            log.error("error when queryForList", e);
            throw new CommonException(e);
        }
    }

    public int[] batchUpdate(String sql, List<Object[]> batchArgs) {
        try (Connection conn = dataSource.getConnection()) {
            return runner.batch(conn, sql, batchArgs.toArray(new Object[0][]));
        } catch (Exception e) {
            log.error("error when batchUpdate", e);
        }
        return new int[0];
    }

    public void execute(String sql) {
        try (Connection conn = dataSource.getConnection()) {
            runner.execute(conn, sql);
        } catch (Exception e) {
            log.error("error when execute", e);
        }
    }
}

测试代码

import java.util.List;

import lombok.extern.slf4j.Slf4j;
import org.h2.jdbcx.JdbcDataSource;
import org.hamcrest.CoreMatchers;
import org.hamcrest.MatcherAssert;
import org.junit.jupiter.api.Test;

/**
 * Tests for {@link SqlServiceImpl}.
 *
 * @since 2025-10-22
 */
@Slf4j
public class SqlServiceImplTest {

    @Test
    public void queryForList() {
        String jdbcUrl = "jdbc:h2:mem:db-test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL";
        SqlServiceImpl sqlService = new SqlServiceImpl();
        JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setUrl(jdbcUrl);
        dataSource.setUser("sa");
        sqlService.dataSource = dataSource;
        String sqlForCreateTable = """
            CREATE TABLE tbl_abc (
                id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                code varchar(16)  NOT NULL,
                status varchar(32) NOT NULL,
                create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
            );
            """;
        sqlService.execute(sqlForCreateTable);
        sqlService.execute("insert into tbl_abc (code, status) values('123456', 'NORMAL')");
        AbcPO abcPO = sqlService.queryForObject("select * from tbl_abc limit 1", AbcPO.class);
        log.info("abcPO={}", abcPO);
        AbcEnumPO abcEnumPO = sqlService.queryForObject("select * from tbl_abc limit 1", AbcEnumPO.class);
        log.info("abcEnumPO={}", abcEnumPO);
        String exMsg = null;
        try {
            List<AbcEnumPO> list = sqlService.queryForList("select t.id, t.code, t.status, t.create_time as createTime from tbl_abc t limit 1", AbcEnumPO.class);
            log.info("list={}", list);
        } catch (Exception e) {
            exMsg = e.getMessage();
        }
        MatcherAssert.assertThat(exMsg, CoreMatchers.containsString("Cannot set createTime: incompatible types, cannot convert java.sql.Timestamp to java.time.LocalDateTime"));
    }
}

问题

• 可以自动处理 varchar 到 java Enum的情况。
• 不能自动处理 timestamp 到 java.time.LocalDateTime的情况。此时可以手工转换，也可以使用jdbi3（可以在本站搜索“jdbi3”）。