MySQL 是 JSP 入門最常搭配的資料庫。本文提供兩種完整實作模式:直接在 JSP 中撰寫 JDBC 程式碼 (適合學習驗證),以及封裝於 JavaBean (適合專案雛形),並說明驅動選擇、連線參數與中文編碼設定。
前置準備:
MySQL 建立測試表:CREATE TABLE username (uid VARCHAR(20), pwd VARCHAR(20))
插入測試資料:INSERT INTO username VALUES ('admin', '123456'), ('user1', 'abcdef')
<%@ page contentType="text/html;charset=gb2312" language="java" import="java.sql.*" %>
<%
// 連線參數 (實務請外部化)
String server = "localhost", db = "test", user = "root", pass = "yourPass", port = "3306";
String url = "jdbc:mysql://" + server + ":" + port + "/" + db
+ "?user=" + user + "&password=" + pass
+ "&useUnicode=true&characterEncoding=GB2312";
Class.forName("org.gjt.mm.mysql.Driver").newInstance(); // 舊版驅動類別名
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM username");
while (rs.next()) {
out.print("用戶名:" + rs.getString("uid") + " 密碼:" + rs.getString("pwd") + "<br>");
}
rs.close(); stmt.close(); conn.close();
%>
WEB-INF/classes/conn/)package conn;
import java.sql.*;
public class DBConnMySql {
private Connection conn;
private Statement stmt;
private ResultSet rs;
private static final String DRIVER = "org.gjt.mm.mysql.Driver";
private static final String URL_PREFIX = "jdbc:mysql://";
// 取得連線
public Connection getConn(String server, String db, String user, String pass, String port) {
try {
String url = URL_PREFIX + server + ":" + port + "/" + db
+ "?user=" + user + "&password=" + pass
+ "&useUnicode=true&characterEncoding=GB2312";
Class.forName(DRIVER);
conn = DriverManager.getConnection(url);
} catch (Exception e) {
System.err.println("資料庫連線失敗: " + e.getMessage());
}
return conn;
}
// 關閉資源
public void close() {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) { e.printStackTrace(); }
}
}
<%@ page contentType="text/html;charset=gb2312" import="java.sql.*" %>
<jsp:useBean id="DBConn" scope="page" class="conn.DBConnMySql" />
<%
String server = "localhost", db = "test", user = "root", pass = "yourPass", port = "3306";
Connection conn = DBConn.getConn(server, db, user, pass, port);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
// 測試寫入
stmt.executeUpdate("INSERT INTO username (uid, pwd) VALUES ('夢想年華', '夢想年華')");
// 查詢顯示
ResultSet rs = stmt.executeQuery("SELECT * FROM username");
while (rs.next()) {
out.print("用戶名:" + rs.getString("uid") + " 密碼:" + rs.getString("pwd") + "<br>");
}
DBConn.close(); // Bean 封裝的關閉方法
%>
| 項目 | 舊版驅動 (MySQL Connector/J 3.x) | 新版驅動 (5.1+/8.0+) |
|---|---|---|
| 驅動類別 | org.gjt.mm.mysql.Driver | com.mysql.cj.jdbc.Driver |
| URL 參數 | useUnicode=true&characterEncoding=GB2312 | characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Taipei |
| JAR 檔 | mysql-connector-java-3.x.jar | mysql-connector-java-8.0.x.jar |
DataSource 注入替代 DriverManagerPreparedStatement 防 SQL 注入try-with-resources 自動關閉context.xml、db.properties 或環境變數