JSP 連接主流資料庫完整範例大全

初學 JSP 時，資料庫連線往往是第一道門檻。驅動程式放哪裡？連線字串怎麼寫？為什麼總報錯？本文彙整 Oracle、SQL Server、DB2、Informix、Sybase、MySQL、PostgreSQL 七大主流資料庫的 JSP 直連範例，並建立統一測試表 test(test1 varchar(20), test2 varchar(20))，插入一筆測試資料，讓你一次跑通所有環境。

前置準備

1. 將對應 JDBC 驅動 JAR 放入應用伺服器 Classpath（如 Tomcat lib 目錄）。
2. 資料庫建立測試表並插入資料：

   CREATE TABLE test (test1 VARCHAR(20), test2 VARCHAR(20));
   INSERT INTO test VALUES ('Hello', 'World');

3. JSP 頁面引入 java.sql.* 套件。

一、Oracle 8/8i/9i/10g/11g/12c/19c（Thin 模式）

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; // SID 或 Service Name
    String user = "scott", password = "tiger";
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

提示：新版驅動建議用 oracle.jdbc.OracleDriver，支援 Service Name 格式 jdbc:oracle:thin:@//host:port/service。

二、SQL Server 7.0 / 2000 / 2005+（Microsoft 官方驅動）

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 新版驅動類別
    String url = "jdbc:sqlserver://localhost:1433;databaseName=pubs;encrypt=false";
    String user = "sa", password = "your_password";
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

注意：舊版 com.microsoft.jdbc.sqlserver.SQLServerDriver 已淘汰，請改用 sqljdbc4.jar 或更新版本。

三、DB2（Universal JDBC Driver）

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("com.ibm.db2.jcc.DB2Driver"); // Type 4 驅動
    String url = "jdbc:db2://localhost:50000/SAMPLE"; // 預設埠 50000
    String user = "db2admin", password = "your_password";
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

四、Informix

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("com.informix.jdbc.IfxDriver");
    String url = "jdbc:informix-sqli://123.45.67.89:1533/testDB:INFORMIXSERVER=myserver;user=testuser;password=testpassword";
    Connection conn = DriverManager.getConnection(url);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

五、Sybase ASE / SQL Anywhere

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("com.sybase.jdbc4.jdbc.SybDriver"); // jConnect 7.0+
    String url = "jdbc:sybase:Tds:localhost:5000/mydb";
    Properties props = new Properties();
    props.setProperty("user", "userid");
    props.setProperty("password", "user_password");
    Connection conn = DriverManager.getConnection(url, props);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

六、MySQL（Connector/J 5.1+ / 8.0+）

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("com.mysql.cj.jdbc.Driver"); // 8.0+ 類別名
    String url = "jdbc:mysql://localhost:3306/softforum?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Taipei";
    String user = "soft", password = "soft1234";
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

關鍵參數：MySQL 8.0 必須指定 serverTimezone 並使用 cj 套件路徑。

七、PostgreSQL

<%@ page contentType="text/html;charset=UTF-8" import="java.sql.*" %>
<%
    Class.forName("org.postgresql.Driver");
    String url = "jdbc:postgresql://localhost:5432/soft";
    String user = "myuser", password = "mypassword";
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        out.println("欄位1：" + rs.getString(1) + "、欄位2：" + rs.getString(2) + "<br>");
    }
    rs.close(); stmt.close(); conn.close();
%>

最佳實務提醒

• 不要在生產環境 JSP 直寫 JDBC 代碼：請採用連線池 + DAO + Service + Controller 分層架構。
• 使用 try-with-resources（Java 7+）自動關閉資源，避免洩漏。
• 敏感資訊（帳密、URL）應外部化至設定檔或環境變數。
• 參數化查詢（PreparedStatement）防止 SQL 注入。