提供一個可直接執行的 Java 專案範例,展示如何封裝 DBHelper 工具類、使用 PreparedStatement 參數化查詢、處理 ResultSet、單值查詢與資源釋放。適合初學者快速上手 JDBC 開發。
JavaSqlServerDemo/
├── lib/
│ └── sqljdbc4.jar (或 sqljdbc42.jar)
├── src/
│ ├── com.example.util/
│ │ └── DBHelper.java (核心封裝類)
│ └── test1.java (測試主程式)
└── bin/

CREATE TABLE w_brand (
id INT PRIMARY KEY,
brand_name NVARCHAR(100) NOT NULL
);
-- 測試資料
INSERT INTO w_brand VALUES (1, '柔軟'), (2, '耐克'), (3, '阿迪達斯'), (5, '優衣庫柔系列');

package com.example.util;
import java.sql.*;
import javax.sql.DataSource;
public class DBHelper {
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=TestDB;user=sa;password=123";
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } }
// 取得連線 (生產環境請改用 DataSource/連接池)
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL);
}
// 執行查詢回傳 ResultSet (呼叫端負責關閉 rs, stmt, conn)
public ResultSet getResultSet(String sql, Object... params) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
setParams(ps, params);
return ps.executeQuery(); // 注意: conn/ps 需在外部 finally 關閉
}
// 執行單值查詢 (COUNT, MAX, SUM 等)
public Object getSingle(String sql, Object... params) {
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParams(ps, params);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) return rs.getObject(1);
}
} catch (SQLException e) { e.printStackTrace(); }
return null;
}
// 執行更新 (INSERT/UPDATE/DELETE)
public int executeUpdate(String sql, Object... params) {
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParams(ps, params);
return ps.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); return -1; }
}
private void setParams(PreparedStatement ps, Object... params) throws SQLException {
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
}
// 測試連線
public boolean testConnection() {
try (Connection conn = getConnection()) { return conn.isValid(2); } catch (SQLException e) { return false; }
}
}
import com.example.util.DBHelper;
import java.sql.ResultSet;
public class test1 {
public static void main(String[] args) {
DBHelper help = new DBHelper();
// 1. 單值查詢: 查 id=5 且品牌名含「柔」的記錄數
String sql = "SELECT COUNT(*) FROM w_brand WHERE id = ? AND brand_name LIKE ?";
Object[] params = new Object[] { 5, "%柔%" };
Object result = help.getSingle(sql, params);
System.out.println("單值查詢結果: " + (result != null ? result.toString() : "無符合資料"));
// 2. 完整結果集查詢 (示範手動關閉資源)
String sql2 = "SELECT id, brand_name FROM w_brand WHERE brand_name LIKE ?";
try (Connection conn = help.getConnection();
PreparedStatement ps = conn.prepareStatement(sql2)) {
ps.setString(1, "%柔%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.printf("ID: %d, Brand: %s%n", rs.getInt("id"), rs.getString("brand_name"));
}
}
} catch (Exception e) { e.printStackTrace(); }
// 3. 執行更新
int upd = help.executeUpdate("UPDATE w_brand SET brand_name = ? WHERE id = ?", "新品牌", 99);
System.out.println("更新列數: " + upd);
}
}

單值查詢結果: 1
ID: 1, Brand: 柔軟
ID: 5, Brand: 優衣庫柔系列
更新列數: 0 (因 id=99 不存在)
JavaSqlServerDemo 完整專案 (含 sqljdbc4.jar、DBHelper、測試類)
ps.setObject(index, value) 自動處理型別與逸出,防 SQL 注入。Connection/Statement/ResultSet,杜絕洩漏。getSingle 適用 COUNT(*)、MAX(id)、SUM(amt) 等標量查詢。getResultSet 回傳 ResultSet 時,呼叫端必須關閉 rs、stmt、conn (範例中用 try-with-resources 示範)。DBHelper 改為使用 **HikariCP DataSource** 注入。encrypt=true;trustServerCertificate=true)。這個範例專案展示了 JDBC 核心 API 的正確用法,是進階框架學習前的最佳練習。