如何在JDBC中高效组织大量SQL查询语句

我当前的应用需要执行大量查询语句，维护难度极高。请问如何高效地组织/精简这些查询语句？以下是部分代码片段：

static MysqlConnectionPoolDataSource ds = BankDataConnection.ds;

static Connection cn;
static CachedRowSet crs;

static {
    try (CachedRowSet newCrs = RowSetProvider.newFactory().createCachedRowSet()) {
        cn = ds.getConnection();
        crs = newCrs;
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

//Used for logins
static String checkMatchPasswordQuery = "CALL checkMatchPassword(?, ?, ?)";

static boolean checkMatchPassword(int client_id, String name, String inputPassword) throws SQLException {
    try (CallableStatement cs = cn.prepareCall(checkMatchPasswordQuery)) {
        cs.setInt(1, client_id);
        cs.setString(2, name);
        cs.setString(3, inputPassword);

        ResultSet rs = cs.executeQuery();
        rs.next();

        return rs.getBoolean("matched");
    } catch (Exception e) {
        e.printStackTrace();
    }
    return false;
}

static String checkNameExist = "CALL checkNameExist(?, ?)";

protected static boolean checkAccountNameExists(int client_id, String name) throws SQLException {
    try (CallableStatement cs = cn.prepareCall(checkNameExist)) {
        cs.setInt(1, client_id);
        cs.setString(2, name);

        ResultSet rs = cs.executeQuery();
        rs.next();

        return rs.getBoolean("exist");
    } catch (Exception e) {
        e.printStackTrace();
    }
    return false;
}

static String getAccountId = "SELECT id FROM accounts WHERE account_name = ? AND client_id = ?";

protected static int getAccountId(String name, int client_id) throws SQLException {
    crs.setCommand(getAccountId);
    crs.setString(1, name);
    crs.setInt(2, client_id);

    crs.execute(cn);
    crs.first();

    return crs.getInt("id");
}

static String getAccountIdLastInserted = "SELECT MAX(id) AS 'lastInsertedID' FROM accounts";

protected static int getLastInsertedId(String name, String password) throws SQLException {
    crs.setCommand(getAccountIdLastInserted);
    crs.setString(1, name);
    crs.setString(2, password);

    crs.execute(cn);
    crs.first();

    return crs.getInt("lastInsertedID");
}

注：这只是一小段代码，实际应用中还有更多此类方法。为方便参考，附上数据库表结构：

我最初的设想是使用HashMap存储带有对应ID的BankAccount对象来处理所有查询，但这仅降低了应用对数据库的依赖，对查询语句的组织帮助不大，像getAccountId或checkAccountNameExists这类方法仍涉及大量循环操作。

具体优化方案 #

1. 用DAO模式拆分数据访问逻辑 #

把所有数据库操作按业务实体拆分到独立的DAO类中，比如创建AccountDAO、ClientDAO，每个DAO只负责对应实体的数据库操作，避免所有查询逻辑堆在一个类里，降低维护成本。

2. 封装通用数据库工具类 #

提取重复的JDBC操作代码到工具类，比如DBUtils，提供通用的查询、存储过程调用方法，避免每个方法都重复写连接获取、语句创建、结果集处理的代码：

public class DBUtils {
    private static MysqlConnectionPoolDataSource ds = BankDataConnection.ds;

    // 通用存储过程调用方法
    public static <T> T callStoredProc(String sql, Object[] params, ResultSetExtractor<T> extractor) throws SQLException {
        try (Connection conn = ds.getConnection();
             CallableStatement stmt = conn.prepareCall(sql)) {
            // 设置参数
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i+1, params[i]);
            }
            // 处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                return extractor.extract(rs);
            }
        }
    }

    // 结果集提取器接口
    public interface ResultSetExtractor<T> {
        T extract(ResultSet rs) throws SQLException;
    }
}

使用时只需关注SQL、参数和结果提取：

static boolean checkMatchPassword(int clientId, String name, String inputPassword) throws SQLException {
    return DBUtils.callStoredProc(
        checkMatchPasswordQuery,
        new Object[]{clientId, name, inputPassword},
        rs -> {
            rs.next();
            return rs.getBoolean("matched");
        }
    );
}

3. 集中管理SQL语句 #

把所有SQL（包括存储过程调用）从Java代码中抽离到sql.properties配置文件中，修改SQL时无需重新编译代码，也方便统一查看维护：

account.checkMatchPassword=CALL checkMatchPassword(?, ?, ?)
account.checkNameExist=CALL checkNameExist(?, ?)
account.getAccountId=SELECT id FROM accounts WHERE account_name = ? AND client_id = ?

通过工具类加载配置文件中的SQL即可。

4. 引入轻量ORM框架（如MyBatis） #

如果项目允许，直接用MyBatis这类框架，它会自动处理JDBC的连接、参数绑定、结果映射，大幅减少手动代码。比如创建Mapper接口：

public interface AccountMapper {
    @Select("CALL checkMatchPassword(#{clientId}, #{name}, #{inputPassword})")
    boolean checkMatchPassword(@Param("clientId") int clientId, @Param("name") String name, @Param("inputPassword") String inputPassword);

    @Select("CALL checkNameExist(#{clientId}, #{name})")
    boolean checkAccountNameExists(@Param("clientId") int clientId, @Param("name") String name);
}

直接注入Mapper调用即可，无需手动处理JDBC细节。

5. 修复现有代码的线程安全问题 #

当前代码中static Connection cn和static CachedRowSet crs是线程不安全的，多线程调用会出现并发错误。必须在每个方法内部从连接池获取连接，而非用静态变量持有：

static boolean checkMatchPassword(int client_id, String name, String inputPassword) throws SQLException {
    try (Connection conn = ds.getConnection();
         CallableStatement cs = conn.prepareCall(checkMatchPasswordQuery)) {
        // 参数设置和结果处理
    }
}

另外getLastInsertedId方法中，SQL不需要参数，但代码里设置了两个参数，属于明显错误，需要及时修复。

内容的提问来源于stack exchange，提问作者Shadow