消除 JDBC 開發瓶頸：打造可重用的資料存取框架

問題背景

傳統 JDBC 程式碼重複度高：建立連線、準備陳述式、綁定參數、執行、處理結果集、關閉資源、捕捉例外。每次查詢/更新都需重寫樣板碼，錯誤處理分散，維護困難。

核心設計：SQLProcessor

public class SQLProcessor {
    public Object[] executeQuery(String sql, Object[] params, ResultProcessor processor) {
        Connection conn = ConnectionManager.getConnection();
        try {
            return handleQuery(sql, params, processor, conn);
        } finally {
            closeConn(conn);
        }
    }

    protected Object[] handleQuery(String sql, Object[] params, ResultProcessor processor, Connection conn) {
        PreparedStatement stmt = conn.prepareStatement(sql);
        PreparedStatementFactory.buildStatement(stmt, params);
        ResultSet rs = stmt.executeQuery();
        Object[] results = processor.process(rs);
        closeStmt(stmt);
        return results;
    }
    // executeUpdate / handleUpdate 類似，處理更新操作
}

關鍵介面

public interface ResultProcessor {
    Object[] process(ResultSet rs) throws SQLException;
}
public interface UpdateProcessor {
    void process(int rows);
}

PreparedStatementFactory

統一處理參數綁定，支援 NullSQLType 封裝 NULL 值及其 JDBC 型別：

for (int i = 0; i < values.length; i++) {
    if (values[i] instanceof NullSQLType) {
        stmt.setNull(i+1, ((NullSQLType)values[i]).getFieldType());
    } else {
        stmt.setObject(i+1, values[i]);
    }
}

實戰範例：查詢使用者

private static final String SQL_GET_USER = "SELECT * FROM USERS WHERE ID = ?";

public class UserResultProcessor implements ResultProcessor {
    public Object[] process(ResultSet rs) {
        List list = new ArrayList<>();
        while (rs.next()) {
            list.add(new User(rs.getInt("ID"), rs.getString("USERNAME"), ...));
        }
        return list.toArray(new User[0]);
    }
}

public User getUser(int id) {
    SQLProcessor p = new SQLProcessor();
    Object[] users = p.executeQuery(SQL_GET_USER, new Object[]{id}, new UserResultProcessor());
    return (User) users[0];
}

更新操作與 MandatoryUpdateProcessor

public class MandatoryUpdateProcessor implements UpdateProcessor {
    public void process(int rows) {
        if (rows < 1) throw new IllegalStateException("No rows updated");
    }
}

交易管理：SQLTransaction

public class SQLTransaction {
    private SQLProcessor processor;
    private Connection conn; // autoCommit = false

    public void executeUpdate(String sql, Object[] params, UpdateProcessor up) {
        try { processor.handleUpdate(sql, params, up, conn); }
        catch (DatabaseUpdateException e) { rollback(); throw e; }
    }
    public void commit() { conn.commit(); closeConn(conn); }
    private void rollback() { conn.rollback(); conn.setAutoCommit(true); closeConn(conn); }
}

使用交易更新多筆使用者

public void updateUsers(User[] users) {
    SQLTransaction tx = sqlProcessor.startTransaction();
    for (User u : users) {
        tx.executeUpdate(SQL_UPDATE_USER, new Object[]{u.getName(), ..., u.getId()}, new MandatoryUpdateProcessor());
    }
    tx.commit();
}

常見問題與擴充

• 自訂連線參數：在 executeQuery/Update 多加參數傳給 ConnectionManager
• ResultProcessor 回傳型別：陣列適合單一物件，List 適合集合
• 例外處理策略：包裝為 Runtime Exception 簡化呼叫端，或保留 Checked Exception 強制處理

結語

此框架將不變的 JDBC 流程封裝，讓開發者專注於 SQL 與領域物件映射，大幅減少樣板碼，提升開發效率與可維護性。