本文目录导读：
我来为您展示一个完整的JDBC预编译语句（PreparedStatement）案例，包含各种常见场景。
基础CRUD操作案例
数据库表结构
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
完整工具类
package com.example.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
// 数据库连接配置
private static final String URL = "jdbc:mysql://localhost:3306/testdb?useSSL=false&characterEncoding=utf8";
private static final String USER = "root";
private static final String PASSWORD = "password";
/**
* 获取数据库连接
*/
private Connection getConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return DriverManager.getConnection(URL, USER, PASSWORD);
}
/**
* 新增用户
*/
public boolean addUser(User user) {
String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 设置参数
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
// 执行插入
int affectedRows = pstmt.executeUpdate();
return affectedRows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
/**
* 根据ID查询用户
*/
public User getUserById(int id) {
String sql = "SELECT * FROM users WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return extractUserFromResultSet(rs);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 查询所有用户
*/
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users ORDER BY id";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
users.add(extractUserFromResultSet(rs));
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
/**
* 根据用户名模糊查询
*/
public List<User> searchUsers(String keyword) {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users WHERE username LIKE ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// LIKE语句的参数设置
pstmt.setString(1, "%" + keyword + "%");
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
users.add(extractUserFromResultSet(rs));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
/**
* 更新用户信息
*/
public boolean updateUser(User user) {
String sql = "UPDATE users SET username = ?, email = ?, age = ? WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
pstmt.setInt(4, user.getId());
int affectedRows = pstmt.executeUpdate();
return affectedRows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
/**
* 删除用户
*/
public boolean deleteUser(int id) {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
int affectedRows = pstmt.executeUpdate();
return affectedRows > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
/**
* 从ResultSet中提取User对象
*/
private User extractUserFromResultSet(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
user.setAge(rs.getInt("age"));
user.setCreatedAt(rs.getTimestamp("created_at"));
return user;
}
}
批量操作案例
package com.example.jdbc;
import java.sql.*;
import java.util.List;
public class BatchOperationDemo {
/**
* 批量插入用户
*/
public void batchInsertUsers(List<User> userList) {
String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password")) {
// 关闭自动提交
conn.setAutoCommit(false);
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (User user : userList) {
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
// 添加到批量处理
pstmt.addBatch();
}
// 执行批量操作
int[] results = pstmt.executeBatch();
// 提交事务
conn.commit();
System.out.println("批量插入成功，共插入 " + results.length + " 条记录");
} catch (SQLException e) {
// 发生异常时回滚
conn.rollback();
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 批量更新用户年龄
*/
public void batchUpdateAges(List<User> users) {
String sql = "UPDATE users SET age = ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password")) {
conn.setAutoCommit(false);
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (User user : users) {
pstmt.setInt(1, user.getAge());
pstmt.setInt(2, user.getId());
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
conn.commit();
System.out.println("批量更新成功，更新了 " + results.length + " 条记录");
} catch (SQLException e) {
conn.rollback();
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
事务处理案例
package com.example.jdbc;
import java.sql.*;
public class TransactionDemo {
/**
* 转账操作 - 使用事务
*/
public void transferMoney(int fromUserId, int toUserId, double amount) {
String deductSQL = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
String addSQL = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password")) {
// 关闭自动提交
conn.setAutoCommit(false);
try {
// 扣款操作
try (PreparedStatement deductStmt = conn.prepareStatement(deductSQL)) {
deductStmt.setDouble(1, amount);
deductStmt.setInt(2, fromUserId);
deductStmt.setDouble(3, amount);
int rowsAffected = deductStmt.executeUpdate();
if (rowsAffected == 0) {
throw new SQLException("余额不足或账户不存在");
}
}
// 加款操作
try (PreparedStatement addStmt = conn.prepareStatement(addSQL)) {
addStmt.setDouble(1, amount);
addStmt.setInt(2, toUserId);
int rowsAffected = addStmt.executeUpdate();
if (rowsAffected == 0) {
throw new SQLException("收款账户不存在");
}
}
// 提交事务
conn.commit();
System.out.println("转账成功");
} catch (SQLException e) {
// 回滚事务
conn.rollback();
System.out.println("转账失败，事务已回滚");
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 多表操作事务
*/
public void createOrderWithItems(Order order, List<OrderItem> items) {
String orderSQL = "INSERT INTO orders (order_no, user_id, total_amount) VALUES (?, ?, ?)";
String itemSQL = "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)";
String stockSQL = "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password")) {
conn.setAutoCommit(false);
try {
// 1. 创建订单
int orderId;
try (PreparedStatement orderStmt = conn.prepareStatement(orderSQL, Statement.RETURN_GENERATED_KEYS)) {
orderStmt.setString(1, order.getOrderNo());
orderStmt.setInt(2, order.getUserId());
orderStmt.setDouble(3, order.getTotalAmount());
orderStmt.executeUpdate();
// 获取自动生成的订单ID
try (ResultSet rs = orderStmt.getGeneratedKeys()) {
if (rs.next()) {
orderId = rs.getInt(1);
} else {
throw new SQLException("创建订单失败");
}
}
}
// 2. 创建订单明细并更新库存
for (OrderItem item : items) {
// 插入订单明细
try (PreparedStatement itemStmt = conn.prepareStatement(itemSQL)) {
itemStmt.setInt(1, orderId);
itemStmt.setInt(2, item.getProductId());
itemStmt.setInt(3, item.getQuantity());
itemStmt.setDouble(4, item.getPrice());
itemStmt.executeUpdate();
}
// 更新库存
try (PreparedStatement stockStmt = conn.prepareStatement(stockSQL)) {
stockStmt.setInt(1, item.getQuantity());
stockStmt.setInt(2, item.getProductId());
stockStmt.setInt(3, item.getQuantity());
int rowsAffected = stockStmt.executeUpdate();
if (rowsAffected == 0) {
throw new SQLException("库存不足，产品ID: " + item.getProductId());
}
}
}
// 提交事务
conn.commit();
System.out.println("订单创建成功，订单ID: " + orderId);
} catch (SQLException e) {
conn.rollback();
System.out.println("订单创建失败，事务已回滚");
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
动态查询案例
package com.example.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DynamicQueryDemo {
/**
* 动态条件查询用户
* @param username 用户名（可空）
* @param email 邮箱（可空）
* @param minAge 最小年龄（可空）
* @param maxAge 最大年龄（可空）
*/
public List<User> dynamicQueryUsers(String username, String email, Integer minAge, Integer maxAge) {
List<User> users = new ArrayList<>();
StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
List<Object> params = new ArrayList<>();
if (username != null && !username.isEmpty()) {
sql.append(" AND username LIKE ?");
params.add("%" + username + "%");
}
if (email != null && !email.isEmpty()) {
sql.append(" AND email LIKE ?");
params.add("%" + email + "%");
}
if (minAge != null) {
sql.append(" AND age >= ?");
params.add(minAge);
}
if (maxAge != null) {
sql.append(" AND age <= ?");
params.add(maxAge);
}
sql.append(" ORDER BY id");
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password");
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
// 动态设置参数
for (int i = 0; i < params.size(); i++) {
Object param = params.get(i);
if (param instanceof String) {
pstmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
pstmt.setInt(i + 1, (Integer) param);
}
}
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
user.setAge(rs.getInt("age"));
users.add(user);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
}
防SQL注入案例
package com.example.jdbc;
import java.sql.*;
public class SqlInjectionDemo {
/**
* 不安全的查询（使用Statement）- 容易受到SQL注入攻击
*/
public User unsafeLogin(String username, String password) {
// 危险：使用字符串拼接SQL
String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
// 攻击示例：username = "admin' OR '1'='1"
System.out.println("登录成功（不安全的查询）");
return new User();
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 安全的查询（使用PreparedStatement）- 防止SQL注入
*/
public User safeLogin(String username, String password) {
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password");
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, username);
pstmt.setString(2, password);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
System.out.println("登录成功（安全的查询）");
return new User();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args) {
SqlInjectionDemo demo = new SqlInjectionDemo();
// 演示SQL注入
String maliciousUsername = "admin' OR '1'='1";
String maliciousPassword = "anything";
// 不安全的登录（可能被攻击）
demo.unsafeLogin(maliciousUsername, maliciousPassword);
// 安全的登录（防止攻击）
demo.safeLogin(maliciousUsername, maliciousPassword);
}
}
实体类
package com.example.jdbc;
import java.sql.Timestamp;
public class User {
private int id;
private String username;
private String email;
private int age;
private Timestamp createdAt;
// 构造函数
public User() {}
public User(String username, String email, int age) {
this.username = username;
this.email = email;
this.age = age;
}
// Getter和Setter方法
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public int getAge() { return age; }
public void setAge(int age) { this.age = age; }
public Timestamp getCreatedAt() { return createdAt; }
public void setCreatedAt(Timestamp createdAt) { this.createdAt = createdAt; }
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", email='" + email + '\'' +
", age=" + age +
", createdAt=" + createdAt +
'}';
}
}
测试案例
package com.example.jdbc;
import java.util.ArrayList;
import java.util.List;
public class TestJdbc {
public static void main(String[] args) {
UserDao userDao = new UserDao();
// 1. 测试添加用户
System.out.println("=== 添加用户 ===");
User newUser = new User("张三", "zhangsan@example.com", 25);
boolean added = userDao.addUser(newUser);
System.out.println("添加用户" + (added ? "成功" : "失败"));
// 2. 测试查询用户
System.out.println("\n=== 查询用户 ===");
User user = userDao.getUserById(1);
if (user != null) {
System.out.println("查询到用户: " + user);
}
// 3. 测试更新用户
System.out.println("\n=== 更新用户 ===");
if (user != null) {
user.setAge(26);
boolean updated = userDao.updateUser(user);
System.out.println("更新用户" + (updated ? "成功" : "失败"));
}
// 4. 测试搜索用户
System.out.println("\n=== 搜索用户 ===");
List<User> searchResults = userDao.searchUsers("张");
for (User u : searchResults) {
System.out.println(u);
}
// 5. 测试批量操作
System.out.println("\n=== 批量插入 ===");
List<User> batchUsers = new ArrayList<>();
for (int i = 0; i < 10; i++) {
batchUsers.add(new User("用户" + i, "user" + i + "@example.com", 20 + i));
}
BatchOperationDemo batchDemo = new BatchOperationDemo();
batchDemo.batchInsertUsers(batchUsers);
// 6. 测试动态查询
System.out.println("\n=== 动态查询 ===");
DynamicQueryDemo queryDemo = new DynamicQueryDemo();
List<User> dynamicResults = queryDemo.dynamicQueryUsers("用户", null, 20, 25);
for (User u : dynamicResults) {
System.out.println(u);
}
// 7. 测试删除用户
System.out.println("\n=== 删除用户 ===");
boolean deleted = userDao.deleteUser(1);
System.out.println("删除用户" + (deleted ? "成功" : "失败"));
}
}
-
性能优势：
- 预编译SQL语句被数据库缓存
- 重复使用相同的PreparedStatement对象
- 批量操作时性能提升明显
-
安全性：
- 参数自动转义,防止SQL注入
- 数据类型自动转换
- 参数化查询确保安全性
-
代码维护性：
- SQL语句与参数分离
- 结构清晰,易于调试
- 复用性强,便于封装
-
功能丰富：
- 支持各种数据类型（字符串、数字、日期、二进制等）
- 支持批量操作
- 支持事务处理
- 支持获取自动生成的主键
这个案例涵盖了JDBC预编译语句的大部分常见用法,您可以根据实际需求进行修改和扩展。
