初學 JSP 最常遇到的問題就是資料庫連線配置。本文提供五大主流資料庫的完整 JSP 連線範例,涵蓋驅動類別、連線 URL、帳號密碼設定與基本查詢操作,適合初學者快速驗證環境。
前置作業:
1. 將對應 JDBC 驅動 JAR 放入伺服器 Classpath (如 Tomcatcommon/lib)
2. 資料庫建立測試表:CREATE TABLE test (test1 VARCHAR(20), test2 VARCHAR(20))
3. 插入一筆測試資料
<%@ page contentType="text/html;charset=gb2312" import="java.sql.*" %>
<html><body>
<%
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url = "jdbc:oracle:thin:@localhost:1521:orcl"; // orcl 為 SID
Connection conn = DriverManager.getConnection(url, "scott", "tiger");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
while (rs.next()) {
%>
您的第一個欄位:<%= rs.getString(1) %><br>
您的第二個欄位:<%= rs.getString(2) %><br>
<%
}
out.print("資料庫操作成功,恭喜你");
rs.close(); stmt.close(); conn.close();
%>
</body></html>
<%@ page contentType="text/html;charset=gb2312" import="java.sql.*" %>
<html><body>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs";
Connection conn = DriverManager.getConnection(url, "sa", "");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
while (rs.next()) {
%>
欄位一:<%= rs.getString(1) %><br>
欄位二:<%= rs.getString(2) %><br>
<%
}
out.print("資料庫操作成功,恭喜你");
rs.close(); stmt.close(); conn.close();
%>
</body></html>
<%@ page contentType="text/html;charset=gb2312" import="java.sql.*" %>
<html><body>
<%
Class.forName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
String url = "jdbc:db2://localhost:5000/sample";
Connection conn = DriverManager.getConnection(url, "admin", "");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
while (rs.next()) {
%>
欄位一:<%= rs.getString(1) %><br>
欄位二:<%= rs.getString(2) %><br>
<%
}
out.print("資料庫操作成功,恭喜你");
rs.close(); stmt.close(); conn.close();
%>
</body></html>
<%@ page contentType="text/html;charset=gb2312" import="java.sql.*" %>
<html><body>
<%
Class.forName("com.informix.jdbc.IfxDriver").newInstance();
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.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
while (rs.next()) {
%>
欄位一:<%= rs.getString(1) %><br>
欄位二:<%= rs.getString(2) %><br>
<%
}
out.print("資料庫操作成功,恭喜你");
rs.close(); stmt.close(); conn.close();
%>
</body></html>
<%@ page import="java.sql.*,java.util.*,java.io.*,java.text.*"
contentType="text/html; charset=gb2312" buffer="20kb" %>
<%
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); }
catch (ClassNotFoundException e) { out.print("驅動程式不存在"); }
try {
Connection conn = DriverManager.getConnection("jdbc:odbc:db1"); // db1 為 ODBC 系統 DSN 名稱
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM book WHERE datetime > '2001-4-26' ORDER BY datetime DESC");
while (rs.next()) {
out.println(rs.getString(1) + " " + rs.getString(2) + "<br>");
}
rs.close(); stmt.close(); conn.close();
} catch (SQLException e) { e.printStackTrace(); }
%>