打造高效易用的 Java MySQL 操作封裝庫

在 Java 開發中，為了簡化資料庫操作並保持對不同資料庫的兼容性，許多開發者選擇自行封裝資料存取層，而非直接使用傳統的 SqlHelper 模式。本文介紹一個輕量級的資料庫操作封裝模型，專注於解決類型轉換錯誤與結果集封裝不當兩大核心痛點，且不包含連接池等進階特性，適合作為教學或小型專案的基礎框架。

核心介面設計

首先定義 Database 介面，規範資料庫應具備的基本操作：

package dao;

import java.sql.SQLException;

public interface Database {
    int executeNoneQuery(String cmdText, Parameters parms) throws SQLException;
    <T> T executeObject(Data2Object<T> convertor, String cmdText, Parameters parms) throws SQLException;
    Object executeScalar(String cmdText, Parameters parms) throws SQLException;
    Parameters createFixedParms(int size);
}

MySQL 具體實作

MySqlDatabase 實作了上述介面，使用 JDBC 原生 API 操作資料庫，關鍵在於 prepareCommand 方法統一處理參數綁定與型別轉換：

package dao;

import java.sql.*;

public class MySqlDatabase implements Database {
    private Connection conn;

    public MySqlDatabase(String connString) throws SQLException {
        conn = DriverManager.getConnection(connString);
    }

    @Override
    public int executeNoneQuery(String cmdText, Parameters parms) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(cmdText);
            prepareCommand(pstmt, parms);
            return pstmt.executeUpdate();
        } finally {
            closeQuietly(pstmt);
            closeQuietly(conn);
        }
    }

    @Override
    public <T> T executeObject(Data2Object<T> convertor, String cmdText, Parameters parms) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(cmdText);
            prepareCommand(pstmt, parms);
            rs = pstmt.executeQuery();
            return convertor.dataMap(rs);
        } finally {
            closeQuietly(rs);
            closeQuietly(pstmt);
            closeQuietly(conn);
        }
    }

    @Override
    public Object executeScalar(String cmdText, Parameters parms) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(cmdText);
            prepareCommand(pstmt, parms);
            rs = pstmt.executeQuery();
            return rs.next() ? rs.getObject(1) : null;
        } finally {
            closeQuietly(rs);
            closeQuietly(pstmt);
            closeQuietly(conn);
        }
    }

    private void prepareCommand(PreparedStatement pstmt, Parameters parms) throws SQLException {
        if (parms != null && parms.getLength() > 0) {
            for (int i = 0; i < parms.getLength(); i++) {
                MySqlParameter parm = parms.getParameter(i);
                String value = parm.getValue().toString();
                switch (parm.getType()) {
                    case STRING:    pstmt.setString(i + 1, value); break;
                    case INT16:     pstmt.setShort(i + 1, Short.parseShort(value)); break;
                    case INT32:     pstmt.setInt(i + 1, Integer.parseInt(value)); break;
                    case INT64:     pstmt.setLong(i + 1, Long.parseLong(value)); break;
                    case DATETIME:  pstmt.setDate(i + 1, Date.valueOf(value)); break;
                    default:        pstmt.setObject(i + 1, value);
                }
            }
        }
    }

    static {
        try { Class.forName("com.mysql.jdbc.Driver").newInstance(); }
        catch (Exception ignored) {}
    }

    @Override
    public Parameters createFixedParms(int size) {
        return new FixedParameters(size);
    }

    private void closeQuietly(AutoCloseable closeable) {
        if (closeable != null) try { closeable.close(); } catch (Exception ignored) {}
    }
}

結果集轉換策略

引入 Data2Object<T> 介面，將 ResultSet 到領域物件的映射邏輯外部化，避免硬編碼：

package dao;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface Data2Object<T> {
    T dataMap(ResultSet rs) throws SQLException;
}

資料庫管理器與輔助類別

DatabaseManager 提供單一進入點；DBType 列舉定義支援的資料型別；Parameters 介面與 FixedParameters 實作構建定長參數陣列；MySqlParameter 封裝單一參數的型別、長度與值。

實際應用範例

以下展示如何查詢列表資料與新增記錄：

// 查詢範例
public List<TestBean> find() throws SQLException {
    String sql = "SELECT * FROM TestTable";
    Database db = DatabaseManager.getMySqlDatabase();
    return db.executeObject(new ListConvertor(), sql, null);
}

class ListConvertor implements Data2Object<List<TestBean>> {
    public List<TestBean> dataMap(ResultSet rs) throws SQLException {
        List<TestBean> list = new ArrayList<>();
        while (rs.next()) {
            TestBean bean = new TestBean();
            bean.setId(rs.getInt(1));
            bean.setName(rs.getString(2));
            list.add(bean);
        }
        return list;
    }
}

// 新增範例
public int add(TestBean test) throws SQLException {
    String sql = "INSERT INTO TestTable (name) VALUES (?)";
    Database db = DatabaseManager.getMySqlDatabase();
    Parameters parms = db.createFixedParms(1);
    parms.add(new MySqlParameter(DBType.STRING, 0, test.getName()));
    return db.executeNoneQuery(sql, parms);
}

此封裝雖簡單，卻清晰展示了介面導向設計、參數化查詢與結果集映射分離的核心思想，為後續擴展連接池、事務管理等功能奠定基礎。