jdbi3

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

官网：https://jdbi.org/

        <!-- https://mvnrepository.com/artifact/org.jdbi/jdbi3-postgres -->
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi3-postgres</artifactId>
            <version>3.49.6</version>
        </dependency>

简单使用（基于quarkus）

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

import jakarta.enterprise.context.ApplicationScoped;
import jakarta.transaction.Transactional;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.jdbi.v3.core.Handle;
import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.result.ResultIterable;
import org.jdbi.v3.core.statement.PreparedBatch;
import org.jdbi.v3.core.statement.Query;

/**
 * .
 *
 * @since 2023-11-01
 */
@ApplicationScoped
@Slf4j
public class SqlServiceImpl {

    private final DataSource ds;
    private final Jdbi jdbi;

    public SqlServiceImpl(DataSource dataSource) {
        this.ds = dataSource;
        this.jdbi = Jdbi.create(ds);
    }

    @Transactional
    public AffectedRowsResp saveBySqls(List<String> sqls) {
        if (CollectionUtils.isEmpty(sqls)) {
            return AffectedRowsResp.empty();
        }
        int sum = sqls.stream().mapToInt(this::saveBySql).sum();
        return AffectedRowsResp.of(sum);
    }

    private int saveBySql(String sql) {
        try (Connection conn = ds.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            return stmt.executeUpdate();
        } catch (Exception e) {
            log.error("error when saveBySql", e);
        }
        return 0;
    }


    public <T> T queryForObject(String sql, Class<T> clazz, final Object... params) {
        return jdbi.withHandle(handle -> {
            ResultIterable<T> ri = buildQueryResultIterable(handle, sql, params, clazz);
            return ri.findOne().orElse(null);
        });
    }

    private static <T> ResultIterable<T> buildQueryResultIterable(Handle handle, String sql, Object[] params, Class<T> clazz) {
        Query query = handle.createQuery(sql);
        if (ArrayUtils.isNotEmpty(params)) {
            for (int i = 0; i < params.length; i++) {
                query.bind(i, params[i]); // 从0开始
            }
        }
        return query.mapToBean(clazz);
    }

    /**
     * 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) {
        return jdbi.withHandle(handle -> {
            ResultIterable<T> ri = buildQueryResultIterable(handle, sql, params, clazz);
            return ri.list();
        });
    }

    public int[] batchUpdate(String sql, List<Object[]> batchArgs) {
        return jdbi.withHandle(handle -> {
            PreparedBatch batch = handle.prepareBatch(sql);

            for (Object[] args : batchArgs) {
                for (int i = 0; i < args.length; i++) {
                    batch.bind(i, args[i]);
                }
                batch.add();
            }

            return batch.execute();
        });
    }

    public void execute(String sql) {
        jdbi.withHandle(handle -> handle.execute(sql));
    }
}

测试代码

import java.util.List;

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

import javax.sql.DataSource;

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

    private DataSource getDataSourceH2(String jdbcUrl) {
        JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setUrl(jdbcUrl);
        dataSource.setUser("sa");
        return dataSource;
    }

    @Test
    public void queryForList() {
        String jdbcUrl = "jdbc:h2:mem:db-test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL";
        DataSource dataSourceH2 = getDataSourceH2(jdbcUrl);
        SqlServiceImpl sqlService = new SqlServiceImpl(dataSourceH2);
        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.nullValue());
    }
}

问题

• 可以自动处理 varchar 到 java Enum的情况。
• 可以自动处理 timestamp 到 java.time.LocalDateTime的情况。