JDBC预编译语句案例

本文目录导读：

1. 基础CRUD操作案例
2. 批量操作案例
3. 事务处理案例
4. 动态查询案例
5. 防SQL注入案例
6. 实体类
7. 测试案例

我来为您展示一个完整的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 ? "成功" : "失败"));
    }
}

1. 性能优势：

   • 预编译SQL语句被数据库缓存
   • 重复使用相同的PreparedStatement对象
   • 批量操作时性能提升明显

2. 安全性：

   • 参数自动转义,防止SQL注入
   • 数据类型自动转换
   • 参数化查询确保安全性

3. 代码维护性：

   • SQL语句与参数分离
   • 结构清晰,易于调试
   • 复用性强,便于封装

4. 功能丰富：

   • 支持各种数据类型（字符串、数字、日期、二进制等）
   • 支持批量操作
   • 支持事务处理
   • 支持获取自动生成的主键

这个案例涵盖了JDBC预编译语句的大部分常见用法,您可以根据实际需求进行修改和扩展。