JSP 連接 MySQL 實戰：註冊登錄完整範例與最佳實踐

JSP + MySQL Web 應用基礎範例：從資料表設計到連接池優化

本文提供完整的 JSP 連接 MySQL 註冊/登錄功能實現，涵蓋建表腳本、JDBC 連接配置、PreparedStatement 防注入、會話管理、轉發跳轉，並指出生產環境必須的連接池、異常處理、資源關閉等優化方向。

一、資料表結構設計

CREATE TABLE `tb_user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `linkman` VARCHAR(255) DEFAULT NULL,
  `company` VARCHAR(255) DEFAULT NULL,
  `companytel` VARCHAR(255) DEFAULT NULL,
  `qq` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 初始測試數據
INSERT INTO `tb_user` VALUES (1, 'admin', 'admin', NULL, NULL, NULL, NULL);
INSERT INTO `tb_user` VALUES (2, 'user', 'user', NULL, NULL, NULL, NULL);

建議改進：密碼欄位存儲 BCrypt/Argon2 雜湊值，非明文；新增 created_at、updated_at、status 欄位。

二、JDBC 連接配置常量

<%!
public static final String DB_DRIVER = "com.mysql.cj.jdbc.Driver";  // MySQL 8.0+ 驅動類
public static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
public static final String DB_USER = "root";
public static final String DB_PASS = "taizhen";
%>

關鍵參數：useSSL=false 禁用 SSL（本地開發）、serverTimezone=UTC 避免時區警告、characterEncoding=utf8 字符集顯式指定。

三、登錄頁面邏輯實現

1. 表單提交

<form action="login.jsp" method="post">
  <input type="hidden" name="action" value="login" />
  用戶名：<input type="text" name="username" /><br/>
  密碼：<input type="password" name="password" /><br/>
  <input type="submit" value="登錄" />
</form>

2. 後端驗證邏輯

<%
String action = request.getParameter("action");
if ("login".equals(action)) {
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    boolean success = false;
    
    try {
        Class.forName(DB_DRIVER);
        conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        String sql = "SELECT id, username FROM tb_user WHERE username=? AND password=?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);  // 實際應比對雜湊值
        rs = pstmt.executeQuery();
        if (rs.next()) {
            success = true;
            session.setAttribute("loginUser", username);
        }
    } catch (Exception e) {
        e.printStackTrace();  // 生產環境應用日誌框架
    } finally {
        // 逆序關閉資源
        try { if (rs != null) rs.close(); } catch (Exception e) {}
        try { if (pstmt != null) pstmt.close(); } catch (Exception e) {}
        try { if (conn != null) conn.close(); } catch (Exception e) {}
    }
    
    if (success) {
        response.sendRedirect("success.jsp");
    } else {
        response.sendRedirect("error.jsp?msg=登錄失敗");
    }
}
%>

四、註冊頁面邏輯實現

<%
String action = request.getParameter("action");
if ("register".equals(action)) {
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String linkman = request.getParameter("linkman");
    String company = request.getParameter("company");
    String companytel = request.getParameter("companytel");
    String qq = request.getParameter("qq");
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    boolean success = false;
    
    try {
        Class.forName(DB_DRIVER);
        conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        String sql = "INSERT INTO tb_user (username, password, linkman, company, companytel, qq) VALUES (?,?,?,?,?,?)";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);  // 應存雜湊
        pstmt.setString(3, linkman);
        pstmt.setString(4, company);
        pstmt.setString(5, companytel);
        pstmt.setString(6, qq);
        int rows = pstmt.executeUpdate();
        success = (rows > 0);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try { if (pstmt != null) pstmt.close(); } catch (Exception e) {}
        try { if (conn != null) conn.close(); } catch (Exception e) {}
    }
    
    if (success) {
        response.sendRedirect("success.jsp?msg=註冊成功");
    } else {
        response.sendRedirect("error.jsp?msg=註冊失敗");
    }
}
%>

五、結果頁面模板

<!-- success.jsp -->
${param.msg }成功！ <a href="login.jsp">去登錄</a>

<!-- error.jsp -->
${param.msg } <a href="javascript:history.back()">返回重試</a>

六、生產環境必須的六大優化

七、MVC 分層重構建議

// DAO 層
public interface UserDao {
    User findByUsernameAndPassword(String username, String passwordHash);
    int insert(User user);
}

// Service 層
@Service
public class UserService {
    @Autowired UserDao userDao;
    public User login(String username, String rawPassword) { ... }
    public void register(UserForm form) { ... }
}

// Controller (Servlet)
@WebServlet("/user/*")
public class UserController extends HttpServlet { ... }

八、總結

JSP Model 1（頁面包含邏輯）適合快速原型與教學，生產系統必須遷移至 Model 2（MVC）架構。核心原則：連接池複用、參數化查詢、密碼雜湊、資源確定性釋放、分層解耦。建議新專務直接採用 Spring Boot + MyBatis/JPA + Druid/HikariCP 技術棧，站在巨人肩膀上構建安全、高效、可維護的應用。