您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

JDBC 操作 MySQL 實戰指南：Statement 與 PreparedStatement 最佳實踐

編輯：MySQL 綜合教學

Java 操作 MySQL 核心工具類封裝

規範的資料庫操作應封裝通用工具類，管理連接獲取、預編譯語句建立、結果集處理與資源釋放。以下為生產環境可用的 DBUtil 完整實作：

package com.example.util;

import java.sql.*;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC";
    private static final String USER = "username";
    private static final String PASS = "password";

    static {
        try { Class.forName("com.mysql.cj.jdbc.Driver"); } 
        catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }

    public static PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    public static void close(AutoCloseable... resources) {
        for (AutoCloseable res : resources) {
            if (res != null) {
                try { res.close(); } catch (Exception ignored) {}
            }
        }
    }
}

PreparedStatement 參數化插入實戰

預編譯語句有效防止 SQL 注入，提升重複執行效能。以下為評論表插入範例：

public boolean saveComment(Comment comment) {
    String sql = "INSERT INTO comment (comment_text, comment_time, user_id, article_id) VALUES (?, ?, ?, ?)";
    try (Connection conn = DBUtil.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        
        ps.setString(1, comment.getCommentText());
        ps.setString(2, comment.getCommentTime());
        ps.setInt(3, comment.getUserId());
        ps.setInt(4, comment.getArticleId());
        
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        log.error("插入評論失敗", e);
        return false;
    }
}

PreparedStatement 條件查詢與結果映射

使用 executeQuery() 獲取結果集，逐行映射為實體對象：

public List<Comment> getComments(int userId, int articleId) {
    String sql = "SELECT * FROM comment WHERE user_id = ? AND article_id = ?";
    List<Comment> list = new ArrayList<>();
    
    try (Connection conn = DBUtil.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        
        ps.setInt(1, userId);
        ps.setInt(2, articleId);
        
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Comment c = new Comment();
                c.setCommentId(rs.getInt("comment_id"));
                c.setCommentText(rs.getString("comment_text"));
                c.setCommentTime(rs.getString("comment_time"));
                c.setUserId(rs.getInt("user_id"));
                c.setArticleId(rs.getInt("article_id"));
                list.add(c);
            }
        }
    } catch (SQLException e) {
        log.error("查詢評論失敗", e);
    }
    return list;
}

Statement 簡單全表查詢場景

無參數綁定的靜態 SQL 可使用 Statement，但嚴禁拼接用戶輸入：

public List<Article> getAllArticles() {
    String sql = "SELECT * FROM article";
    List<Article> list = new ArrayList<>();
    
    try (Connection conn = DBUtil.getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        
        while (rs.next()) {
            Article a = new Article();
            a.setTitle(rs.getString("title"));
            a.setContent(rs.getString("content"));
            a.setArticleTime(rs.getString("article_time"));
            a.setUserId(rs.getInt("user_id"));
            a.setArticleId(rs.getInt("article_id"));
            list.add(a);
        }
    } catch (SQLException e) {
        log.error("查詢文章失敗", e);
    }
    return list;
}