萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 連接 MySQL 資料庫完整實作:Scriptlet 與 JavaBean 兩種模式

MySQL 是 JSP 入門最常搭配的資料庫。本文提供兩種完整實作模式:直接在 JSP 中撰寫 JDBC 程式碼 (適合學習驗證),以及封裝於 JavaBean (適合專案雛形),並說明驅動選擇、連線參數與中文編碼設定。

前置準備:
MySQL 建立測試表:CREATE TABLE username (uid VARCHAR(20), pwd VARCHAR(20))
插入測試資料:INSERT INTO username VALUES ('admin', '123456'), ('user1', 'abcdef')

模式一:JSP 直寫 JDBC (學習驗證用)

<%@ 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();
%>

模式二:JavaBean 封裝連線邏輯 (專案雛形)

1. DBConnMySql.java (放入 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(); }
  }
}

2. connmysql.jsp (使用 Bean)

<%@ 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.Drivercom.mysql.cj.jdbc.Driver
URL 參數useUnicode=true&characterEncoding=GB2312characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Taipei
JAR 檔mysql-connector-java-3.x.jarmysql-connector-java-8.0.x.jar

最佳實務建議

  1. 使用連線池:HikariCP / DBCP2 / Tomcat JDBC Pool,DataSource 注入替代 DriverManager
  2. 參數化查詢:PreparedStatement 防 SQL 注入
  3. 資源管理:Java 7+ try-with-resources 自動關閉
  4. 編碼統一:資料庫/表/欄位/連線/頁面全部 UTF-8,避免亂碼
  5. 外部化設定:連線參數放 context.xml、db.properties 或環境變數
copyright © 萬盛學電腦網 all rights reserved