PreparedStatement 是 JDBC 中預編譯 SQL 的核心介面,能大幅提升批次處理效能、防止 SQL 注入、簡化參數綁定。本文系統整理其優勢、核心用法與三層擴充範例(查詢、擴充查詢、增刪改)。
addBatch()/executeBatch() 大量寫入效能遠優於 Statement。String sql = "SELECT * FROM student WHERE year(birthday) BETWEEN ? AND ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1987");
ps.setString(2, "1990");
ResultSet rs = ps.executeQuery();
public ResultSet queryRange(String sql, String s1, String s2) {
getConnection();
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, s1);
pstmt.setString(2, s2);
return pstmt.executeQuery();
} catch (SQLException e) { e.printStackTrace(); }
return null;
}
public ResultSet queryFlexible(String sql, Object[] params) {
getConnection();
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
return pstmt.executeQuery();
} catch (SQLException e) { e.printStackTrace(); }
return null;
}
// 應用
String[] birthdayRange = {"1992", "1992"};
ResultSet rs = dao.queryFlexible("SELECT * FROM t_user WHERE year(birthdate) BETWEEN ? AND ?", birthdayRange);
String[] idRange = {"100", "200"};
ResultSet rs2 = dao.queryFlexible("SELECT * FROM t_user WHERE id BETWEEN ? AND ?", idRange);
public int executeUpdate(String sql, Object[] params) {
int count = 0;
getConnection();
try {
pstmt = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
count = pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); }
finally { close(); }
return count;
}
// 批次新增
for (int i = 0; i < 10; i++) {
dao.executeUpdate("INSERT INTO jdbc_test(username, password) VALUES(?, ?)", new Object[]{"user"+i, "pwd"+i});
}
// 批次刪除
dao.executeUpdate("DELETE FROM t_user WHERE id BETWEEN ? AND ?", new Object[]{"1010", "1030"});
try-with-resources (Java 7+) 自動關閉 Connection/PreparedStatement/ResultSet。rewriteBatchedStatements=true (MySQL Connector/J) 獲得最大吞吐。setObject 可自動推斷型別,但明確使用 setString/setInt/setDate 更安全。prepareStatement,應在迴圈外準備,迴圈內只 setParameter + addBatch。PreparedStatement 是 JDBC 開發的基石,善用參數化與批次處理,能寫出安全、高效、可維護的資料存取層。