JDBC实现工具类及相关工具类使用，满足属性映射

这篇博客分享了一位开发者在新公司中为提高SQL查询效率，如何封装和优化JDBC工具类的经验。最初是为MySQL设计，后因数据量大而采用Apache KyLin，并将数据库参数抽象为接口。文中提供了主要逻辑的抽象类和MySQL实现的示例，同时提到了依赖的RedisClient（jedis-2.6.0）和JsonHelper（基于jackson）。该代码适用于JDK8，需要注意Java版本可能带来的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

到了新公司之后，学习使用了play framework，其中orm是由JPA完成，所以难以避免一些复杂SQL查询上的繁琐，所以一个趁手的JDBC工具类就显得很重要了，现在第一个项目基本完成，自己封装的JDBC工具类也基本成型，所以拿到这里分享一下（ps:因为篇幅有限，所以我尽量缩减了代码行）okay，上代码~

import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

public interface DataFetcher {

    /**
     * 执行SQL
     */
    void execute(SQLCallback callback);

    /**
     * 执行DML语句
     */
    void executeUpdate(CharSequence sql);

    /**
     * 返回一个标准结构的查询结果
     * @param isCache 是否需要缓存
     * @param sqlArr 不限量的查询SQL
     * @return 一个map类型的集合
     */
    List<Map<String, Object>> executeQuerySql(boolean isCache, CharSequence... sqlArr);

    /**
     * 返回一个泛型集合
     * @param isCache 是否需要缓存
     * @param clazz 指定返回集合类型
     * @param sqlArr 不限量的查询SQL
     */
    <T> List<T> executeQuerySql(boolean isCache, Class<T> clazz, CharSequence... sqlArr);

    /**
     * 获得组函数返回值
     * @param isCache 值为true,由Redis缓存一天
     * @throws RuntimeException 如果返回结果中没有amount字段，抛出异常
     */
    Long executeQuerySqlGetCount(boolean isCache, CharSequence sql);

    /**
     * 只保留查询结果的第一条数据
     * @param isCache 值为true,由Redis缓存一天
     * @throws RuntimeException 如果查询结果为空，抛出异常
     */
    Map<String, Object> executeQuerySqlGetFirst(boolean isCache, CharSequence sql);

    /**
     * 返回标准结构的分页查询
     */
    List<Map<String, Object>> executeQuerySqlWithLimit(boolean isCache, CharSequence sql, int start, int end);

    /**
     * 返回泛型集合的分页查询
     */
    <T> List<T> executeQuerySqlWithLimit(boolean isCache, Class<T> clazz, CharSequence sql, int start, int end);

    /**
     * 给SQL添加参数
     */
    static void appendParam(StringBuilder sql, String... params) {
        if (params == null || params.length == 0) return;

        int car = 0;
        for (String param : params) {
            if (StringUtils.isBlank(param)) continue;
            sql.append(car++ == 0 ? " WHERE " : " AND ")
                    .append(param);
        }
    }

    /**
     * 处理查询结果的实现接口
     */
    interface SQLCallback {
        void callback(Statement stmt) throws SQLException;
    }
}

之前本来是直接封装为MySQL操作类的，但是后面因为数据量过大，导致查询太慢，经理提议使用Apache KyLin解决，所以就把具体的数据库参数抽象出来，尽量减少耦合，使用抽象类解决后，发现还是用接口更舒服些，以下为实现了主要逻辑的抽象类。

import org.apache.commons.beanutils.ConvertUtils;
import play.Logger;
import play.libs.Codec;
import utils.Constants;
import utils.JsonHelper;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 数据库操作工具类
 */
public abstract class AbstractDataFetcher implements DataFetcher{

    // 数据库参数
    private String driver;
    private String url;
    private String user;
    private String pass;

    AbstractDataFetcher(String driver, String url, String user, String pass) {
        this.driver = driver;
        loadDriver();

        this.url = url;
        this.user = user;
        this.pass = pass;
    }

    /**
     * 获取数据库连接connection
     */
    private Connection getConn() {
        try {
            return DriverManager.getConnection(url, user, pass);
        }catch (Exception e) {
            throw new RuntimeException("Please check your application.conf!", e);
        }
    }

    /**
     * 加载驱动类
     */
    private void loadDriver() {
        try {
            Class.forName(driver);
            Logger.info(String.format("JDBC Driver Class load completion:%s", driver));
        } catch (ClassNotFoundException e) {
            Logger.error(String.format("JDBC Driver Class [%s] is not Found!", driver), e);
        }
    }

    /**
     * 执行SQL
     */
    @Override
    public void execute(DataFetcher.SQLCallback callback) {
        try (Connection conn = getConn();
             Statement stmt = conn.createStatement()){
            if (callback != null)
                callback.callback(stmt);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行DML语句
     */
    @Override
    public void executeUpdate(CharSequence sql) {
        execute(stmt -> {
            stmt.executeUpdate(sql.toString());
            Logger.info("execute update:" + sql);
        });
    }

    @Override
    public List<Map<String, Object>> executeQuerySql(boolean isCache, CharSequence... sqlArr) {
        return isCache ? executeQuerySqlWithCache(sqlArr) : executeQuerySql(sqlArr);
    }

    @Override
    public <T> List<T> executeQuerySql(boolean isCache, Class<T> clazz, CharSequence... sqlArr) {
        return isCache ? executeQuerySqlWithCache(clazz, sqlArr) : executeQuerySql(clazz, sqlArr);
    }

    @Override
    public Long executeQuerySqlGetCount(boolean isCache, CharSequence sql) {
        Object count = executeQuerySqlGetFirst(isCache, sql).get("AMOUNT");
        if (count == null) {
            throw new RuntimeException("There is no AMOUNT field in the query result!");
        }
        try {
            return new Long(count.toString());
        } catch (NumberFormatException e) {
            throw new RuntimeException("The AMOUNT field is not a number!");
        }
    }

    @Override
    public Map<String, Object> executeQuerySqlGetFirst(boolean isCache, CharSequence sql) {
        List<Map<String, Object>> data = executeQuerySql(isCache, sql);
        if (data.isEmpty()) {
            throw new RuntimeException("The result of the query is empty！");
        }
        return data.get(0);
    }

    @Override
    public List<Map<String, Object>> executeQuerySqlWithLimit(boolean isCache, CharSequence sql, int start, int end) {
        String sqlStr = sql.toString() + String.format(" LIMIT %d, %d", start, end);
        return executeQuerySql(isCache, sqlStr);
    }

    @Override
    public <T> List<T> executeQuerySqlWithLimit(boolean isCache, Class<T> clazz, CharSequence sql, int start, int end) {
        String sqlStr = sql.toString() + String.format(" LIMIT %d, %d", start, end);
        return executeQuerySql(isCache, clazz, sqlStr);
    }

    private List<Map<String, Object>> executeQuerySql(CharSequence... sqlArr) {
        List<Map<String, Object>> list = new ArrayList<>();
        for (CharSequence sql : sqlArr) {
            execute(stmt -> {
                Logger.info("execute sql : " + sql);
                try (ResultSet rs = stmt.executeQuery(sql.toString())){
                    int columnCount = rs.getMetaData().getColumnCount();
                    while (rs.next()) {
                        Map<String, Object> data = new HashMap<>();
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = rs.getMetaData().getColumnLabel(i);
                            Object value = rs.getObject(columnName);
                            data.put(columnName, value);
                        }
                        list.add(data);
                    }
                }
            });
        }
        return list;
    }

    private List<Map<String, Object>> executeQuerySqlWithCache(CharSequence... sqlArr) {
        List<Map<String, Object>> list = new ArrayList<>();
        for (CharSequence sql : sqlArr) {
            String redisKey = buildKey(sql);
            String redisValue = RedisClient.getAndExpireIfPresent(redisKey, RedisClient.SECOND);
            if (redisValue != null) {
                Logger.info("redis get sql : " + sql);
                list.addAll(JsonHelper.fromString(redisValue, list.getClass()));
            } else {
                List<Map<String, Object>> querySql = executeQuerySql(sql);
                // 查询异常时返回结果为空，此类数据不缓存
                if (! querySql.isEmpty()) {
                    list.addAll(querySql);
                    RedisClient.setAndExpire(redisKey, JsonHelper.toString(querySql), RedisClient.SECOND);
                }
            }
        }
        return list;
    }

    private <T> List<T> executeQuerySql(Class<T> clazz, CharSequence... sqlArr) {
        List<T> data = new ArrayList<>();
        for (CharSequence sql : sqlArr) {
            execute(stmt -> {
                Logger.info("execute sql : " + sql);
                try (ResultSet rs = stmt.executeQuery(sql.toString())){
                    int columnCount = rs.getMetaData().getColumnCount();
                    while (rs.next()) {
                        T entity = clazz.newInstance();
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = rs.getMetaData().getColumnLabel(i);
                            Field field = clazz.getField(columnName);
                            Class<?> fieldType = field.getType();
                            Object value = rs.getObject(columnName);

                            if (value != null) {
                                // 如果指定的Object与此Class所表示的对象赋值不兼容
                                if (!fieldType.isInstance(value)){
                                    // 调用common.lang包转化值为对应类型
                                    value = ConvertUtils.convert(value, fieldType);
                                }
                                field.set(entity, value);
                            }
                        }
                        data.add(entity);
                    }
                } catch (IllegalAccessException | InstantiationException | NoSuchFieldException e) {
                    Logger.error("No such field of your class,Please check!");
                    e.printStackTrace();
                }
            });
        }
        return data;
    }

    private  <T> List<T> executeQuerySqlWithCache(Class<T> clazz, CharSequence... sqlArr) {
        List<T> list = new ArrayList<>();
        for (CharSequence sql : sqlArr) {
            String redisKey = buildKey(sql);
            String redisValue = RedisClient.getAndExpireIfPresent(redisKey, RedisClient.SECOND);
            if (redisValue != null) {
                Logger.info("redis get sql : " + sql);
                list.addAll(JsonHelper.toList(redisValue, clazz));
            } else {
                List<T> querySql = executeQuerySql(clazz, sql);
                if (! querySql.isEmpty()) {
                    list.addAll(querySql);
                    RedisClient.setAndExpire(redisKey, JsonHelper.toString(querySql), RedisClient.SECOND);
                }
            }
        }
        return list;
    }
    /**
     * 生成Redis key
     */
    private static String buildKey(CharSequence sql) {
        return Constants.NO_FORMAT.format(new Date()) + Codec.hexMD5(sql.toString());
    }
}

在这里我只贴一个大家可能经常用到的MySQL实现：

import play.Play;
/**
 * MySQL连接jdbc驱动
 */
public class MySqlDataFetcher extends DataFetcher{
    // 数据库参数
    // 这里是用到了play framework的一个特性，其实就是加载conf文件
    // 这里大家可以直接改为写死的字符串，或者在static块中赋值
    private static final String DB_URL = Play.configuration.getProperty("db.url");
    private static final String DB_DRIVER = Play.configuration.getProperty("db.driver");
    private static final String DB_USER = Play.configuration.getProperty("db.user");
    private static final String DB_PASS = Play.configuration.getProperty("db.pass");
    static {
        loadDriver(DB_DRIVER);
    }
    // 封装私有构造方法
    private MySqlDataFetcher() {
        super(DB_URL, DB_USER, DB_PASS);
    }
    // 设为单例
    private static class MySqlDataFetcherHolder {
        private static final DataFetcher INSTANCE = new MySqlDataFetcher();
    }
    public static DataFetcher getInstance() {
        return MySqlDataFetcherHolder.INSTANCE;
    }
}

以上工具类用到的依赖工具类，篇幅有限，只粘贴了方法，如下：

RedisClient 依赖包版本：jedis-2.6.0

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import play.Play;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.JedisPoolConfig;
import redis.clients.jedis.exceptions.JedisConnectionException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
/**
 * redis常用操作工具类
 */
public class RedisClient {
    public static final int SECOND = 24 * 60 * 60;
    private static AtomicInteger nextIdx = new AtomicInteger(-1);
    private static String[] servers = StringUtils.split(Play.configuration.getProperty("redis.servers"), ",");
    private static String password = Play.configuration.getProperty("redis.pass");
    private static JedisPoolConfig poolConfig = new JedisPoolConfig();
    static {
        poolConfig.setTestOnBorrow(false);
        poolConfig.setTestOnReturn(false);
        poolConfig.setMaxTotal(500);
        poolConfig.setMaxIdle(500);
        poolConfig.setMaxWaitMillis(1000 * 10);
    }
    private static List<JedisPool> pools = new ArrayList<>();
    static {
        for (String server : servers) {
            String[] hostPort = StringUtils.split(server, ":");
            if (hostPort.length < 1) continue;
            String host = hostPort[0];
            int port = 6379;
            if (hostPort.length > 1) {
                port = NumberUtils.toInt(hostPort[1]);
            }
            JedisPool pool = new JedisPool(poolConfig, host, port, 10000 * 3, password);
            pools.add(pool);
        }
    }
    public static String getAndExpireIfPresent(final String key, final int seconds) {
        return execute(jedis -> {
            String ret = jedis.get(key);
            if (ret != null) {
                jedis.expire(key, seconds);
            }
            return ret;
        });
    }
    /**
     * 保存到redis，若key存在，则不更新
     */
    public static void setAndExpire(final String key, final String value, final int seconds) {
        execute(jedis -> jedis.set(key, value, "NX", "EX", seconds));
    }

    private static <T> T execute(RedisCallback<T> callback) {
        if (pools.size() == 0)
            throw new RuntimeException("redis no pool");
        // RoundRobin Pool模式
        JedisPool pool;
        for (;;) {
            int current = nextIdx.get();
            int next = current >= pools.size() - 1 ? 0 : current + 1;
            if (nextIdx.compareAndSet(current, next)) {
                pool = pools.get(next);
                break;
            }
        }
        if (pool == null)
            throw new RuntimeException("redis pool error");
        Jedis jedis = null;
        try {
            jedis = pool.getResource();
            if (callback != null)
                return callback.execute(jedis);
        } catch (JedisConnectionException e) {
            if (null != jedis) {
                pool.returnBrokenResource(jedis);
                jedis = null;
            }
        } finally {
            if (null != jedis)
                pool.returnResource(jedis);
        }
        return null;
    }
    public interface RedisCallback<T> {
        T execute(Jedis jedis);
    }
}

JsonHelper jackson实现（因为play依赖包中有jackson，所以就没重复导入功能类似的fastjson）

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.databind.*;
import com.fasterxml.jackson.databind.type.CollectionType;
import com.fasterxml.jackson.databind.type.MapType;
import com.fasterxml.jackson.databind.type.TypeFactory;
import org.apache.commons.lang.StringUtils;
import play.Logger;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 调用Jackson实现的json转换工具类
 */
public class JsonHelper {
    private static ObjectMapper mapper = new ObjectMapper();
    static {
        mapper.configure(DeserializationFeature.USE_BIG_INTEGER_FOR_INTS, true);
        // 空值转换异常
        mapper.configure(DeserializationFeature.ACCEPT_EMPTY_STRING_AS_NULL_OBJECT, true);
        mapper.getSerializerProvider().setNullValueSerializer(new JsonSerializer<Object>() {
            @Override
            public void serialize(Object value, JsonGenerator jg, SerializerProvider sp) throws IOException {
                jg.writeString("");
            }
        });
    }
    public static <T> T fromString(String json, Class<T> valueType) {
        try {
            return mapper.readValue(toUTFByte(json), valueType);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public static <T> List<T> toList(String json, Class<T> elementClass) {
        CollectionType collectionType =
                TypeFactory.defaultInstance().constructCollectionType(List.class, elementClass);
        try {
            return mapper.readValue(toUTFByte(json), collectionType);
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }
    public static <K, V> Map<K,V> toMap(String json, Class<K> keyClass, Class<V> valueClass) {
        MapType mapType =
                TypeFactory.defaultInstance().constructMapType(Map.class, keyClass, valueClass);
        try {
            return mapper.readValue(toUTFByte(json), mapType);
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap<>();
        }
    }
    public static String toString(Object obj) {
        try {
            return mapper.writeValueAsString(obj);
        } catch (Exception e) {
            Logger.error(e, e.getMessage());
        }
        return StringUtils.EMPTY;
    }
    private static byte[] toUTFByte(String str) {
        if (StringUtils.isNotBlank(str)){
            try {
                return str.getBytes("UTF-8");
            } catch (UnsupportedEncodingException e) {
                Logger.error(e, e.getMessage());
            }
        }
        return new byte[0];
    }