萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

Java 程序操作 MySQL 數據庫完整實戰教程

準備工作

  1. MySQL 數據庫已安裝並運行
  2. 下載 MySQL Connector/J JDBC 驅動(mysql-connector-java-x.x.x.jar)
  3. IDE 中添加 JAR 到 Classpath(Eclipse: 右鍵項目 → Build Path → Add External JARs)

數據庫表結構


CREATE DATABASE people_manage CHARACTER SET utf8mb4;
USE people_manage;

CREATE TABLE people (
    uid VARCHAR(20) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    banji VARCHAR(50),
    score INT DEFAULT 0
);

INSERT INTO people VALUES 
('001', '張三', '一班', 100),
('002', '李四', '二班', 95),
('003', '王五', '一班', 100);

數據庫工具類(連接池優化版)


import java.sql.*;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariDataSource; // 推薦 HikariCP 連接池

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/people_manage?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    
    private static DataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

實體類


public class People {
    private String uid;
    private String name;
    private String banji;
    private int score;
    
    // 構造器、Getter/Setter、toString 省略
    // 建議使用 Lombok @Data 自動生成
}

DAO 層完整實現


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PeopleDAO {
    
    // 查詢分數為 100 的所有人員
    public List findByScore(int score) {
        String sql = "SELECT * FROM people WHERE score = ?";
        List list = new ArrayList<>();
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setInt(1, score);
            
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    People p = new People();
                    p.setUid(rs.getString("uid"));
                    p.setName(rs.getString("name"));
                    p.setBanji(rs.getString("banji"));
                    p.setScore(rs.getInt("score"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查詢失敗", e);
        }
        return list;
    }
    
    // 插入
    public int insert(People p) {
        String sql = "INSERT INTO people (uid, name, banji, score) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, p.getUid());
            ps.setString(2, p.getName());
            ps.setString(3, p.getBanji());
            ps.setInt(4, p.getScore());
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("插入失敗", e);
        }
    }
    
    // 更新
    public int update(People p) {
        String sql = "UPDATE people SET name=?, banji=?, score=? WHERE uid=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, p.getName());
            ps.setString(2, p.getBanji());
            ps.setInt(3, p.getScore());
            ps.setString(4, p.getUid());
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新失敗", e);
        }
    }
    
    // 刪除
    public int delete(String uid) {
        String sql = "DELETE FROM people WHERE uid = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, uid);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("刪除失敗", e);
        }
    }
}

測試主程序


public class Main {
    public static void main(String[] args) {
        PeopleDAO dao = new PeopleDAO();
        
        // 查詢分數 100
        List topStudents = dao.findByScore(100);
        System.out.println("分數 100 的學生:");
        topStudents.forEach(p -> System.out.println(p.getUid() + " " + p.getName() + " " + p.getBanji() + " " + p.getScore()));
        
        // 新增
        People newStudent = new People();
        newStudent.setUid("004");
        newStudent.setName("趙六");
        newStudent.setBanji("三班");
        newStudent.setScore(88);
        dao.insert(newStudent);
        
        // 更新
        newStudent.setScore(90);
        dao.update(newStudent);
        
        // 刪除
        // dao.delete("004");
    }
}

關鍵最佳實踐

  1. 使用 PreparedStatement:防止 SQL 注入、預編譯提升性能
  2. try-with-resources:Java 7+ 自動關閉資源,防止洩漏
  3. 連接池:生產環境必須使用(HikariCP/Druid/C3P0)
  4. 異常處理:捕獲 SQLException 包裝為業務異常拋出
  5. 事務控制:多表操作需 conn.setAutoCommit(false) 手動提交/回滾
copyright © 萬盛學電腦網 all rights reserved