JSP 連接主流數據庫完整代碼集：Oracle、SQL Server、DB2、Informix、Access

前言

初學 JSP 最常問「數據庫怎麼連」。本文匯總 5 大主流數據庫的 **JDBC 連接完整 JSP 範例**，涵蓋驅動類、URL 格式、認證方式、常見坑點，適合學習參考與快速複製驗證。

準備工作

1. 下載對應 JDBC 驅動 JAR 放入 WEB-INF/lib。
2. 數據庫建表：CREATE TABLE test (test1 VARCHAR(20), test2 VARCHAR(20));
3. 插入測試數據：INSERT INTO test VALUES ('Hello', 'World');

1. Oracle (Thin 模式，推薦)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html><body>
<%
    Class.forName("oracle.jdbc.OracleDriver"); // JDBC 4.0+ 可省略
    String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; // SID
    // String url = "jdbc:oracle:thin:@//localhost:1521/PDB1"; // Service Name
    Connection conn = DriverManager.getConnection(url, "scott", "tiger");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
%>
    欄位1: <%= rs.getString(1) %>, 欄位2: <%= rs.getString(2) %><br/>
<%
    }
    rs.close(); stmt.close(); conn.close();
%>
操作成功！
</body></html>

驅動：ojdbc8.jar (JDK 8/11) / ojdbc11.jar (JDK 11+)。

2. SQL Server (Microsoft 官方驅動)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html><body>
<%
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    // URL 格式：jdbc:sqlserver://host:port;databaseName=xxx;encrypt=true;trustServerCertificate=true
    String url = "jdbc:sqlserver://localhost:1433;databaseName=pubs;encrypt=false";
    Connection conn = DriverManager.getConnection(url, "sa", "your_password");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
%>
    欄位1: <%= rs.getString(1) %>, 欄位2: <%= rs.getString(2) %><br/>
<%
    }
    rs.close(); stmt.close(); conn.close();
%>
操作成功！
</body></html>

驅動：mssql-jdbc-12.x.x.jre11.jar (Maven: com.microsoft.sqlserver:mssql-jdbc)。

注意：SQL Server 2016+ 默認加密，開發環境可 encrypt=false 或配置信任憑證。

3. DB2 (IBM Data Server Driver)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html><body>
<%
    Class.forName("com.ibm.db2.jcc.DB2Driver"); // Type 4 純 Java
    String url = "jdbc:db2://localhost:50000/SAMPLE"; // 數據庫 SAMPLE
    Connection conn = DriverManager.getConnection(url, "db2admin", "password");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
%>
    欄位1: <%= rs.getString(1) %>, 欄位2: <%= rs.getString(2) %><br/>
<%
    }
    rs.close(); stmt.close(); conn.close();
%>
操作成功！
</body></html>

驅動：db2jcc4.jar + db2jcc_license_cu.jar。

4. Informix (IBM Informix JDBC Driver)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html><body>
<%
    Class.forName("com.informix.jdbc.IfxDriver");
    // URL 包含伺服器名、數據庫、用戶、密碼
    String url = "jdbc:informix-sqli://192.168.1.50:1526/testDB:INFORMIXSERVER=ol_test;user=informix;password=pass";
    Connection conn = DriverManager.getConnection(url);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
%>
    欄位1: <%= rs.getString(1) %>, 欄位2: <%= rs.getString(2) %><br/>
<%
    }
    rs.close(); stmt.close(); conn.close();
%>
操作成功！
</body></html>

驅動：ifxjdbc.jar / ifxjdbcx.jar (XA)。

5. Access (JDBC-ODBC Bridge / UCanAccess)

⚠️ JDBC-ODBC Bridge (sun.jdbc.odbc.JdbcOdbcDriver) 自 JDK 8 起已移除。

現代方案：UCanAccess (純 Java，支援 .accdb/.mdb)

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" %>
<html><body>
<%
    // Maven: net.sf.ucanaccess:ucanaccess:5.0.1
    Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
    String url = "jdbc:ucanaccess://C:/data/mydb.accdb"; // 絕對路徑
    Connection conn = DriverManager.getConnection(url);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
%>
    欄位1: <%= rs.getString(1) %>, 欄位2: <%= rs.getString(2) %><br/>
<%
    }
    rs.close(); stmt.close(); conn.close();
%>
操作成功！
</body></html>

依賴：ucanaccess.jar + jackcess.jar + commons-lang3.jar + commons-logging.jar + hsqldb.jar。

通用最佳實踐模板 (生產環境)

<%@ page import="java.sql.*, javax.naming.*, javax.sql.*" %>
<%
    // 1. 從 JNDI 獲取 DataSource (配置在容器 context.xml)
    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MyDS");
    
    // 2. Try-with-resources 自動關閉資源 (JDK 7+)
    try (Connection conn = ds.getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM test");
         ResultSet rs = ps.executeQuery()) {
        
        while (rs.next()) {
            out.print("欄位1: " + rs.getString(1) + ", 欄位2: " + rs.getString(2) + "<br/>");
        }
    } catch (SQLException e) {
        e.printStackTrace(); // 生產環境請用 Logger
    }
%>

常見錯誤速查