準備工作
- MySQL 數據庫已安裝並運行
- 下載 MySQL Connector/J JDBC 驅動(mysql-connector-java-x.x.x.jar)
- 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");
}
}
關鍵最佳實踐
- 使用 PreparedStatement:防止 SQL 注入、預編譯提升性能
- try-with-resources:Java 7+ 自動關閉資源,防止洩漏
- 連接池:生產環境必須使用(HikariCP/Druid/C3P0)
- 異常處理:捕獲 SQLException 包裝為業務異常拋出
- 事務控制:多表操作需
conn.setAutoCommit(false) 手動提交/回滾