Java SQL数据库编程实战：从基础连接到高级事务处理
本文深入讲解Java.sql包的核心功能，详细介绍了Connection、Statement、PreparedStatement、ResultSet等关键接口的使用方法。通过完整的代码示例展示了数据库连接建立、SQL查询执行、事务管理、批量操作等实际应用场景，特别强调了使用PreparedStatement防止SQL注入的最佳实践，以及try-with-resources进行资源自动管理的重要性。
Java.sql包是Java标准库中用于数据库连接和操作的核心API，它提供了一系列接口和类来实现与关系型数据库的连接、SQL语句执行、事务管理和结果集处理等功能。
主要类和接口
1. 核心接口
1、Connection - 数据库连接
2、Statement - 执行静态SQL语句
3、PreparedStatement - 执行预编译SQL语句
4、CallableStatement - 执行存储过程
5、ResultSet - 查询结果集
6、DatabaseMetaData - 数据库元数据
2. 驱动管理
- DriverManager - 管理数据库驱动程序
3. 数据类型
- Types - 定义SQL数据类型常量
示例代码
1. 基础数据库连接和查询
import java.sql.*;
public class BasicJDBCExample {
// 数据库连接信息
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "username";
private static final String PASSWORD = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1. 注册驱动 (新版本JDBC可以自动加载)
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 建立连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println("数据库连接成功!");
// 3. 创建Statement对象
stmt = conn.createStatement();
// 4. 执行查询
String sql = "SELECT id, name, email FROM users";
rs = stmt.executeQuery(sql);
// 5. 处理结果集
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
System.out.printf("ID: %d, Name: %s, Email: %s%n",
id, name, email);
}
} catch (ClassNotFoundException e) {
System.err.println("找不到数据库驱动: " + e.getMessage());
} catch (SQLException e) {
System.err.println("数据库操作错误: " + e.getMessage());
} finally {
// 6. 关闭资源
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
System.err.println("关闭资源错误: " + e.getMessage());
}
}
}
}
2. 使用PreparedStatement防止SQL注入
import java.sql.*;
public class PreparedStatementExample {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "username";
private static final String PASSWORD = "password";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
// 插入数据
insertUser(conn, "张三", "zhangsan@example.com");
// 查询数据
findUserByName(conn, "张三");
// 更新数据
updateUserEmail(conn, "张三", "new_email@example.com");
// 删除数据
deleteUser(conn, "张三");
} catch (SQLException e) {
e.printStackTrace();
}
}
// 插入用户
public static void insertUser(Connection conn, String name, String email)
throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
int rows = pstmt.executeUpdate();
System.out.println("插入了 " + rows + " 行数据");
}
}
// 根据姓名查询用户
public static void findUserByName(Connection conn, String name)
throws SQLException {
String sql = "SELECT id, name, email FROM users WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf("找到用户: ID=%d, Name=%s, Email=%s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"));
}
}
}
}
// 更新用户邮箱
public static void updateUserEmail(Connection conn, String name, String newEmail)
throws SQLException {
String sql = "UPDATE users SET email = ? WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, newEmail);
pstmt.setString(2, name);
int rows = pstmt.executeUpdate();
System.out.println("更新了 " + rows + " 行数据");
}
}
// 删除用户
public static void deleteUser(Connection conn, String name)
throws SQLException {
String sql = "DELETE FROM users WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
int rows = pstmt.executeUpdate();
System.out.println("删除了 " + rows + " 行数据");
}
}
}
3. 事务管理示例
import java.sql.*;
public class TransactionExample {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "username";
private static final String PASSWORD = "password";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
try {
// 执行多个数据库操作
transferMoney(conn, 1, 2, 100.0);
// 提交事务
conn.commit();
System.out.println("事务提交成功!");
} catch (SQLException e) {
// 回滚事务
conn.rollback();
System.err.println("事务回滚: " + e.getMessage());
} finally {
// 恢复自动提交
conn.setAutoCommit(true);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 转账操作
public static void transferMoney(Connection conn, int fromAccount,
int toAccount, double amount)
throws SQLException {
// 扣款
String deductSQL = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(deductSQL)) {
pstmt.setDouble(1, amount);
pstmt.setInt(2, fromAccount);
pstmt.executeUpdate();
}
// 检查余额是否足够
String checkBalanceSQL = "SELECT balance FROM accounts WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(checkBalanceSQL)) {
pstmt.setInt(1, fromAccount);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next() && rs.getDouble("balance") < 0) {
throw new SQLException("余额不足");
}
}
}
// 存款
String addSQL = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(addSQL)) {
pstmt.setDouble(1, amount);
pstmt.setInt(2, toAccount);
pstmt.executeUpdate();
}
}
}
4. 批量操作示例
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BatchOperationExample {
private static final String URL = "jdbc:mysql://localhost:3306/testdb";
private static final String USER = "username";
private static final String PASSWORD = "password";
public static void main(String[] args) {
List<User> users = new ArrayList<>();
users.add(new User("李四", "lisi@example.com"));
users.add(new User("王五", "wangwu@example.com"));
users.add(new User("赵六", "zhaoliu@example.com"));
batchInsertUsers(users);
}
public static void batchInsertUsers(List<User> users) {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 关闭自动提交以提高性能
conn.setAutoCommit(false);
for (User user : users) {
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.addBatch(); // 添加到批处理
}
// 执行批处理
int[] results = pstmt.executeBatch();
conn.commit();
System.out.println("批量插入完成，影响了 " + results.length + " 行");
} catch (SQLException e) {
e.printStackTrace();
}
}
static class User {
private String name;
private String email;
public User(String name, String email) {
this.name = name;
this.email = email;
}
public String getName() { return name; }
public String getEmail() { return email; }
}
}
注意事项
1、资源管理: 使用try-with-resources语句自动关闭连接，避免资源泄漏
2、SQL注入: 始终使用PreparedStatement而不是字符串拼接来构建SQL
3、事务管理: 合理使用事务确保数据一致性
4、连接池: 在生产环境中使用连接池管理数据库连接
5、异常处理: 妥善处理SQLException，记录适当的错误信息
这些示例展示了java.sql包的基本用法，实际开发中通常会结合连接池和更复杂的错误处理机制。
最后更新于6月前
本文由人工编写，AI优化，转载请注明原文地址： java.sql使用方法及代码示例
