JDBC 連接 MySQL 完整實戰：從驅動載入到連線池 Proxool 整合

涵蓋 JDBC 連接 MySQL 全流程：驅動註冊、URL 格式、連線建立、Statement/PreparedStatement/CallableStatement 操作、ResultSet 游標模式、批次處理、JNDI DataSource 與 Proxool 連線池整合範例。

1. 載入驅動

Class.forName("com.mysql.jdbc.Driver");
// 或
Class.forName("com.mysql.jdbc.Driver").newInstance();

2. JDBC URL 格式

jdbc:mysql://[host][:port]/[dbname][?param1=value1¶m2=value2...]
範例：jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password&useUnicode=true&characterEncoding=UTF-8

3. 建立連線與執行 SQL

String url = "jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();

// 查詢
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
while (rs.next()) {
    System.out.println(rs.getString(1) + " " + rs.getInt(2));
}

// 更新
int cnt = stmt.executeUpdate("INSERT INTO test (id,name) VALUES (1001,'xuzhaori')");

4. PreparedStatement（預編譯、防注入、重複執行）

PreparedStatement pstmt = con.prepareStatement("INSERT INTO test(id,name) VALUES (?,?)");
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.executeUpdate();
// 參數設定後可多次 execute，直到 clearParameters()

5. ResultSet 游標模式（JDBC 2.0）

Statement stmt = con.createStatement(
    ResultSet.TYPE_SCROLL_SENSITIVE,   // 可上下捲動、看見變更
    ResultSet.CONCUR_UPDATABLE);       // 可直接更新 ResultSet
ResultSet rs = stmt.executeQuery("SELECT * FROM test");

// 直接新增
rs.moveToInsertRow();
rs.updateInt(1, 1001);
rs.updateString(2, "許召日");
rs.insertRow();

// 直接更新
rs.last();
rs.updateString("name", "xuzhaori");
rs.updateRow();

// 直接刪除
rs.absolute(4);
rs.deleteRow();

6. 批次處理

con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO test VALUES (1001,'xuzhaori')");
stmt.addBatch("INSERT INTO test VALUES (1002,'xuyalin')");
int[] rows = stmt.executeBatch();
con.commit();

7. Proxool 連線池整合

web.xml

<servlet>
  <servlet-name>ServletConfigurator</servlet-name>
  <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>
  <init-param>
    <param-name>propertyFile</param-name>
    <param-value>WEB-INF/classes/Proxool.properties</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>
<servlet>
  <servlet-name>Admin</servlet-name>
  <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
</servlet>
<servlet-mapping>
  <servlet-name>Admin</servlet-name>
  <url-pattern>/Admin</url-pattern>
</servlet-mapping>

Proxool.properties

jdbc-0.proxool.alias=JSPBook
jdbc-0.proxool.driver-class=com.mysql.jdbc.Driver
jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/sample_db?user=root&password=browser&useUnicode=true&characterEncoding=UTF-8
jdbc-0.proxool.maximum-connection-count=10
jdbc-0.proxool.prototype-count=4
jdbc-0.proxool.house-keeping-test-sql=select CURRENT_DATE
jdbc-0.proxool.verbose=true
jdbc-0.proxool.statistics=10s,1m,1d
jdbc-0.proxool.statistics-log-level=DEBUG

使用

Connection con = DriverManager.getConnection("proxool.JSPBook");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM employee");

提示：生產環境建議使用 HikariCP 或 Druid 等現代高效能連線池，Proxool 已停止維護。