JDBC 工具類 JDBCTools 完整實現：連線管理、CRUD 封裝與泛型實體映射

設計目標

• 統一連線獲取/釋放（支援 Connection、Statement、PreparedStatement、ResultSet）
• 封裝通用 update(sql, args...) 與 query(sql, args...)
• 查詢結果自動映射為泛型實體（反射 + 中繼資料）
• 配置外部化（jdbc.properties）

核心程式碼

1. 配置檔 src/jdbc.properties

jdbcDriver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
user=root
password=root

2. JDBCTools.java

package util;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class JDBCTools {
    private static String driver, url, user, password;

    static {
        try (InputStream is = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties")) {
            Properties p = new Properties();
            p.load(is);
            driver = p.getProperty("jdbcDriver");
            url = p.getProperty("url");
            user = p.getProperty("user");
            password = p.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        closeQuietly(rs);
        closeQuietly(stmt);
        closeQuietly(conn);
    }
    private static void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignored) {}
    }

    /** 通用更新：insert / update / delete */
    public static void update(String sql, Object... args) {
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bind(ps, args);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /** 通用單行查詢 → 泛型實體 */
    public static  T queryForObject(Class clazz, String sql, Object... args) {
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bind(ps, args);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return mapRow(clazz, rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    /** 通用多行查詢 → List */
    public static  List queryForList(Class clazz, String sql, Object... args) {
        List list = new ArrayList<>();
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bind(ps, args);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) list.add(mapRow(clazz, rs));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    private static void bind(PreparedStatement ps, Object[] args) throws SQLException {
        for (int i = 0; i < args.length; i++) ps.setObject(i + 1, args[i]);
    }

    private static  T mapRow(Class clazz, ResultSet rs) throws Exception {
        T obj = clazz.getDeclaredConstructor().newInstance();
        ResultSetMetaData md = rs.getMetaData();
        int cols = md.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            String colName = md.getColumnLabel(i);  // 支援別名
            Object val = rs.getObject(colName);
            try {
                Field f = clazz.getDeclaredField(colName);
                f.setAccessible(true);
                f.set(obj, val);
            } catch (NoSuchFieldException ignored) {
                // 欄位不存在於實體 → 忽略
            }
        }
        return obj;
    }
}

使用範例

// 實體類（欄位名對應資料庫欄位/別名）
class User { long id; String name; int age; /* getter/setter 省略 */ }

// 插入
JDBCTools.update("INSERT INTO user(name,age) VALUES(?,?)", "Tom", 25);

// 查單筆
User u = JDBCTools.queryForObject(User.class, "SELECT * FROM user WHERE id=?", 1L);

// 查列表
List list = JDBCTools.queryForList(User.class, "SELECT * FROM user WHERE age>?", 18);

關鍵設計細節