DeepSeek PreparedStatement 与 Statement 全面对比

PreparedStatement 与 Statement 全面对比

1. 基础区别

import java.sql.*;

public class StatementVsPreparedStatement {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";

try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);

demoStatement(conn);
demoPreparedStatement(conn);
demoBatchOperations(conn);

conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* Statement 基本用法
*/
public static void demoStatement(Connection conn) throws SQLException {
System.out.println("=== Statement 演示 ===");

Statement stmt = null;
try {
stmt = conn.createStatement();

// 1. 执行查询
String query = "SELECT id, name, email FROM users WHERE status = 'ACTIVE'";
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
System.out.printf("ID: %d, Name: %s, Email: %s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"));
}
rs.close();

// 2. 执行更新
String update = "UPDATE users SET last_login = NOW() WHERE status = 'ACTIVE'";
int rowsUpdated = stmt.executeUpdate(update);
System.out.println("更新行数: " + rowsUpdated);

// 3. 执行任意SQL
String sql = "CREATE TABLE IF NOT EXISTS temp_table (id INT, name VARCHAR(50))";
stmt.execute(sql);

} finally {
if (stmt != null) stmt.close();
}
}

/**
* PreparedStatement 基本用法
*/
public static void demoPreparedStatement(Connection conn) throws SQLException {
System.out.println("\\n=== PreparedStatement 演示 ===");

// 1. 查询示例
String query = "SELECT * FROM users WHERE id = ? AND status = ?";
PreparedStatement pstmt = null;

try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, 1001); // 设置第一个参数
pstmt.setString(2, "ACTIVE"); // 设置第二个参数

ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("User: %s, Email: %s%n",
rs.getString("name"),
rs.getString("email"));
}
rs.close();

} finally {
if (pstmt != null) pstmt.close();
}

// 2. 插入示例
String insert = "INSERT INTO users (name, email, password, created_at) VALUES (?, ?, ?, ?)";
try (PreparedStatement insertStmt = conn.prepareStatement(insert,
Statement.RETURN_GENERATED_KEYS)) {

insertStmt.setString(1, "张三");
insertStmt.setString(2, "zhangsan@example.com");
insertStmt.setString(3, "encrypted_password");
insertStmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));

int affectedRows = insertStmt.executeUpdate();
System.out.println("插入行数: " + affectedRows);

// 获取自增ID
ResultSet keys = insertStmt.getGeneratedKeys();
if (keys.next()) {
int generatedId = keys.getInt(1);
System.out.println("生成的ID: " + generatedId);
}
keys.close();
}

// 3. 更新示例
String update = "UPDATE users SET last_login = ?, login_count = login_count + 1 WHERE id = ?";
try (PreparedStatement updateStmt = conn.prepareStatement(update)) {
updateStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
updateStmt.setInt(2, 1001);

int rows = updateStmt.executeUpdate();
System.out.println("更新用户登录信息，影响行数: " + rows);
}
}
}

2. SQL注入演示与防护

import java.sql.*;

public class SQLInjectionDemo {

/**
* Statement 的 SQL 注入漏洞演示
*/
public static void vulnerableLogin(Connection conn, String username, String password)
throws SQLException {

// ❌ 危险写法：容易受到SQL注入攻击
String sql = "SELECT * FROM users WHERE username = '" + username +
"' AND password = '" + password + "'";

System.out.println("执行的SQL: " + sql);

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

if (rs.next()) {
System.out.println("登录成功！");
} else {
System.out.println("登录失败！");
}

rs.close();
stmt.close();
}

/**
* PreparedStatement 防止 SQL 注入
*/
public static void safeLogin(Connection conn, String username, String password)
throws SQLException {

// ✅ 安全写法：使用 PreparedStatement
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);

System.out.println("执行的SQL: " + pstmt);

ResultSet rs = pstmt.executeQuery();

if (rs.next()) {
System.out.println("安全登录成功！");
} else {
System.out.println("安全登录失败！");
}

rs.close();
pstmt.close();
}

/**
* SQL注入攻击示例
*/
public static void demonstrateInjection() throws SQLException {
String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";

Connection conn = DriverManager.getConnection(url, user, password);

System.out.println("=== SQL注入攻击演示 ===");

// 正常输入
System.out.println("\\n1. 正常登录:");
vulnerableLogin(conn, "admin", "123456");

// SQL注入攻击
System.out.println("\\n2. SQL注入攻击:");
// 输入: admin' —
// SQL会变成: SELECT * FROM users WHERE username = 'admin' –' AND password = 'xxx'
vulnerableLogin(conn, "admin' –", "任意密码");

// 更危险的注入
System.out.println("\\n3. 更危险的注入:");
// 输入: ' OR '1'='1
vulnerableLogin(conn, "' OR '1'='1", "' OR '1'='1");

// 使用 PreparedStatement 防止注入
System.out.println("\\n4. 使用 PreparedStatement 防止注入:");
safeLogin(conn, "admin' –", "任意密码");

conn.close();
}

/**
* 批量注入风险
*/
public static void batchInjectionRisk(Connection conn) throws SQLException {
// ❌ 危险：直接拼接SQL
String userInput = "test'; DROP TABLE users; –";
String sql = "INSERT INTO logs (message) VALUES ('" + userInput + "')";

Statement stmt = conn.createStatement();
stmt.execute(sql); // 可能执行恶意SQL

// ✅ 安全：使用 PreparedStatement
String safeSql = "INSERT INTO logs (message) VALUES (?)";
PreparedStatement pstmt = conn.prepareStatement(safeSql);
pstmt.setString(1, userInput); // 只会作为字符串值处理
pstmt.executeUpdate();
}

public static void main(String[] args) {
try {
demonstrateInjection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

3. 性能对比与批量操作

import java.sql.*;
import java.util.*;

public class PerformanceComparison {

/**
* Statement 批量插入 – 性能较差
*/
public static void batchInsertWithStatement(Connection conn, List<User> users)
throws SQLException {

long startTime = System.currentTimeMillis();
Statement stmt = conn.createStatement();

for (User user : users) {
// 每次都需要拼接SQL
String sql = String.format(
"INSERT INTO users (name, email, age) VALUES ('%s', '%s', %d)",
user.name.replace("'", "''"), // 需要手动转义
user.email.replace("'", "''"),
user.age
);
stmt.executeUpdate(sql);
}

stmt.close();
long endTime = System.currentTimeMillis();
System.out.printf("Statement 批量插入 %d 条数据耗时: %d ms%n",
users.size(), endTime – startTime);
}

/**
* PreparedStatement 批量插入 – 性能较好
*/
public static void batchInsertWithPreparedStatement(Connection conn, List<User> users)
throws SQLException {

long startTime = System.currentTimeMillis();
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

for (User user : users) {
pstmt.setString(1, user.name); // 自动处理转义
pstmt.setString(2, user.email); // 自动处理转义
pstmt.setInt(3, user.age);
pstmt.addBatch(); // 添加到批处理
}

int[] results = pstmt.executeBatch();
pstmt.close();

long endTime = System.currentTimeMillis();
System.out.printf("PreparedStatement 批量插入 %d 条数据耗时: %d ms%n",
users.size(), endTime – startTime);

// 检查执行结果
int successCount = 0;
for (int result : results) {
if (result >= 0 || result == Statement.SUCCESS_NO_INFO) {
successCount++;
}
}
System.out.println("成功插入: " + successCount + " 条记录");
}

/**
* 使用事务的批量插入
*/
public static void batchInsertWithTransaction(Connection conn, List<User> users)
throws SQLException {

boolean originalAutoCommit = conn.getAutoCommit();
long startTime = System.currentTimeMillis();

try {
conn.setAutoCommit(false); // 开始事务

String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

for (int i = 0; i < users.size(); i++) {
User user = users.get(i);
pstmt.setString(1, user.name);
pstmt.setString(2, user.email);
pstmt.setInt(3, user.age);
pstmt.addBatch();

// 每1000条执行一次批处理
if (i % 1000 == 0 && i > 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}

pstmt.executeBatch(); // 执行剩余的批处理
conn.commit(); // 提交事务

pstmt.close();

} catch (SQLException e) {
conn.rollback(); // 发生异常时回滚
throw e;
} finally {
conn.setAutoCommit(originalAutoCommit);
}

long endTime = System.currentTimeMillis();
System.out.printf("带事务的批量插入 %d 条数据耗时: %d ms%n",
users.size(), endTime – startTime);
}

/**
* Statement 与 PreparedStatement 性能对比
*/
public static void performanceTest() throws SQLException {
String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";

Connection conn = DriverManager.getConnection(url, user, password);

// 准备测试数据
List<User> testUsers = generateTestUsers(1000);

// 清理测试表
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), age INT)");
stmt.execute("TRUNCATE TABLE users");
stmt.close();

System.out.println("=== 性能对比测试 ===");

// 测试1: Statement
batchInsertWithStatement(conn, testUsers);

// 清理数据
stmt = conn.createStatement();
stmt.execute("TRUNCATE TABLE users");
stmt.close();

// 测试2: PreparedStatement
batchInsertWithPreparedStatement(conn, testUsers);

// 清理数据
stmt = conn.createStatement();
stmt.execute("TRUNCATE TABLE users");
stmt.close();

// 测试3: PreparedStatement + 事务
batchInsertWithTransaction(conn, testUsers);

conn.close();
}

private static List<User> generateTestUsers(int count) {
List<User> users = new ArrayList<>();
Random random = new Random();

for (int i = 0; i < count; i++) {
User user = new User();
user.name = "User" + i;
user.email = "user" + i + "@example.com";
user.age = 20 + random.nextInt(50);
users.add(user);
}
return users;
}

static class User {
String name;
String email;
int age;
}

public static void main(String[] args) {
try {
performanceTest();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

4. 高级功能与最佳实践

import java.sql.*;
import java.math.BigDecimal;

public class AdvancedFeatures {

/**
* PreparedStatement 高级功能
*/
public static void demoAdvancedFeatures(Connection conn) throws SQLException {

// 1. 设置查询超时
String sql = "SELECT * FROM large_table WHERE status = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setQueryTimeout(30); // 30秒超时
pstmt.setString(1, "ACTIVE");

// 2. 设置获取行数限制
pstmt.setMaxRows(1000); // 最多获取1000行

// 3. 设置获取大小
pstmt.setFetchSize(100); // 每次从数据库获取100行

// 4. 处理各种数据类型
String insertSQL = """
INSERT INTO products (
name, price, quantity, available,
created_at, description, metadata
) VALUES (?, ?, ?, ?, ?, ?, ?)
""";

try (PreparedStatement insertStmt = conn.prepareStatement(insertSQL)) {
// 字符串
insertStmt.setString(1, "笔记本电脑");

// 浮点数
insertStmt.setBigDecimal(2, new BigDecimal("5999.99"));

// 整数
insertStmt.setInt(3, 100);

// 布尔值
insertStmt.setBoolean(4, true);

// 日期时间
insertStmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));

// 长文本
insertStmt.setClob(6, new javax.sql.rowset.serial.SerialClob(
"这是一款高性能笔记本电脑…".toCharArray()));

// 二进制数据
byte[] metadata = "{'color': 'silver', 'weight': '2.5kg'}".getBytes();
insertStmt.setBytes(7, metadata);

// NULL值处理
insertStmt.setNull(3, Types.INTEGER); // 设置quantity为NULL

insertStmt.executeUpdate();
}

// 5. 可滚动的结果集
sql = "SELECT * FROM users ORDER BY id";
pstmt = conn.prepareStatement(
sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, // 可滚动
ResultSet.CONCUR_READ_ONLY // 只读
);

ResultSet rs = pstmt.executeQuery();

// 移动到结果集末尾
if (rs.last()) {
System.out.println("总记录数: " + rs.getRow());

// 移动到第一行
rs.first();

// 相对移动
rs.relative(5); // 向前移动5行

// 绝对移动
rs.absolute(10); // 移动到第10行

// 移动到插入行（用于插入新记录）
// rs.moveToInsertRow();
// rs.updateString("name", "New User");
// rs.insertRow();
// rs.moveToCurrentRow();
}
rs.close();
pstmt.close();
}

/**
* 动态SQL构建（安全的参数化查询）
*/
public static List<Map<String, Object>> dynamicQuery(
Connection conn,
Map<String, Object> filters) throws SQLException {

StringBuilder sql = new StringBuilder("SELECT * FROM products WHERE 1=1");
List<Object> parameters = new ArrayList<>();

// 动态构建WHERE条件
if (filters.containsKey("category")) {
sql.append(" AND category = ?");
parameters.add(filters.get("category"));
}

if (filters.containsKey("minPrice")) {
sql.append(" AND price >= ?");
parameters.add(filters.get("minPrice"));
}

if (filters.containsKey("maxPrice")) {
sql.append(" AND price <= ?");
parameters.add(filters.get("maxPrice"));
}

if (filters.containsKey("name")) {
sql.append(" AND name LIKE ?");
parameters.add("%" + filters.get("name") + "%");
}

// 排序
if (filters.containsKey("sortBy")) {
String sortField = (String) filters.get("sortField");
// 防止SQL注入：验证排序字段
if (isValidSortField(sortField)) {
sql.append(" ORDER BY ").append(sortField);
if ("DESC".equalsIgnoreCase((String) filters.get("sortOrder"))) {
sql.append(" DESC");
}
}
}

// 分页
if (filters.containsKey("pageSize")) {
sql.append(" LIMIT ? OFFSET ?");
int pageSize = (int) filters.get("pageSize");
int page = (int) filters.getOrDefault("page", 1);
parameters.add(pageSize);
parameters.add((page – 1) * pageSize);
}

// 执行查询
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
for (int i = 0; i < parameters.size(); i++) {
pstmt.setObject(i + 1, parameters.get(i));
}

ResultSet rs = pstmt.executeQuery();
List<Map<String, Object>> results = new ArrayList<>();

ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();

while (rs.next()) {
Map<String, Object> row = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
row.put(columnName, rs.getObject(i));
}
results.add(row);
}

rs.close();
pstmt.close();
return results;
}

private static boolean isValidSortField(String field) {
// 白名单验证
Set<String> validFields = Set.of("id", "name", "price", "created_at", "category");
return validFields.contains(field.toLowerCase());
}

/**
* 存储过程调用
*/
public static void callStoredProcedure(Connection conn) throws SQLException {
// 调用无返回值的存储过程
String callSQL = "{call update_user_status(?, ?)}";

try (CallableStatement cstmt = conn.prepareCall(callSQL)) {
cstmt.setInt(1, 1001);
cstmt.setString(2, "INACTIVE");
cstmt.execute();
}

// 调用有返回值的存储过程
callSQL = "{? = call calculate_order_total(?)}";

try (CallableStatement cstmt = conn.prepareCall(callSQL)) {
cstmt.registerOutParameter(1, Types.DECIMAL);
cstmt.setInt(2, 5001);
cstmt.execute();

BigDecimal total = cstmt.getBigDecimal(1);
System.out.println("订单总额: " + total);
}

// 调用有结果集的存储过程
callSQL = "{call get_user_orders(?)}";

try (CallableStatement cstmt = conn.prepareCall(callSQL)) {
cstmt.setInt(1, 1001);
boolean hasResults = cstmt.execute();

if (hasResults) {
ResultSet rs = cstmt.getResultSet();
while (rs.next()) {
System.out.println("订单: " + rs.getString("order_no"));
}
rs.close();
}
}
}

/**
* 批量更新的最佳实践
*/
public static void batchUpdateBestPractice(Connection conn,
List<Map<String, Object>> updates)
throws SQLException {

boolean originalAutoCommit = conn.getAutoCommit();

try {
conn.setAutoCommit(false);

String sql = "UPDATE products SET price = ?, stock = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

int batchSize = 0;
for (Map<String, Object> update : updates) {
pstmt.setBigDecimal(1, (BigDecimal) update.get("price"));
pstmt.setInt(2, (Integer) update.get("stock"));
pstmt.setInt(3, (Integer) update.get("id"));
pstmt.addBatch();
batchSize++;

// 定期执行批处理，避免内存溢出
if (batchSize % 1000 == 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}

// 执行剩余的批处理
int[] updateCounts = pstmt.executeBatch();

// 验证更新结果
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] == Statement.EXECUTE_FAILED) {
System.err.println("第 " + (i+1) + " 条更新失败");
// 可以考虑记录失败的数据以便重试
}
}

conn.commit();
pstmt.close();

} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(originalAutoCommit);
}
}

public static void main(String[] args) {
try {
String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";

Connection conn = DriverManager.getConnection(url, user, password);

demoAdvancedFeatures(conn);

// 测试动态查询
Map<String, Object> filters = new HashMap<>();
filters.put("category", "electronics");
filters.put("minPrice", 1000);
filters.put("sortField", "price");
filters.put("sortOrder", "DESC");
filters.put("pageSize", 10);
filters.put("page", 1);

List<Map<String, Object>> results = dynamicQuery(conn, filters);
System.out.println("查询结果数量: " + results.size());

conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

5. 综合对比表格

6. 最佳实践总结

// 最佳实践示例
public class BestPracticeExample {
public void queryUsers(DataSource dataSource, int departmentId) throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE department_id = ? AND active = ?";

// 使用try-with-resources自动关闭资源
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {

// 设置参数
pstmt.setInt(1, departmentId);
pstmt.setBoolean(2, true);

// 设置查询超时
pstmt.setQueryTimeout(10);

// 执行查询
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
// 处理结果
System.out.println(rs.getString("name"));
}
}
} catch (SQLException e) {
// 记录日志并重新抛出
logger.error("查询用户失败", e);
throw e;
}
}
}

选择 PreparedStatement 可以获得更好的安全性、性能和可维护性。只有在执行动态DDL语句或确实需要字符串拼接的特殊情况下才考虑使用 Statement。