跳到主要内容

Java JDBC 数据库连接

JDBC(Java Database Connectivity)是 Java 访问数据库的标准 API。理解 JDBC 的使用是进行数据库应用开发的基础。本章将详细介绍 Java 中的 JDBC 使用。

JDBC 驱动与连接

JDBC 概述

JDBC是 Java 访问数据库的标准接口,提供了统一的数据库访问方式。

JDBC 架构

Java 应用程序

JDBC API

JDBC 驱动

数据库

JDBC 驱动类型

四种 JDBC 驱动类型

  1. Type 1:JDBC-ODBC Bridge(已过时)
  2. Type 2:Native API Driver
  3. Type 3:Network Protocol Driver
  4. Type 4:Pure Java Driver(推荐)

加载驱动

import java.sql.*;

// 方式 1:显式加载(Java 5+ 可以省略)
Class.forName("com.mysql.cj.jdbc.Driver");

// 方式 2:使用 DriverManager(Java 6+ 自动加载)
// 不需要显式加载

建立连接

import java.sql.*;

// 数据库连接 URL
String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
String username = "root";
String password = "password";

// 建立连接
Connection connection = DriverManager.getConnection(url, username, password);

// 或使用 Properties
Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
Connection connection2 = DriverManager.getConnection(url, props);

常用数据库连接 URL

// MySQL
String mysqlUrl = "jdbc:mysql://localhost:3306/mydb";

// PostgreSQL
String pgUrl = "jdbc:postgresql://localhost:5432/mydb";

// SQLite
String sqliteUrl = "jdbc:sqlite:mydb.db";

// Oracle
String oracleUrl = "jdbc:oracle:thin:@localhost:1521:orcl";

// SQL Server
String sqlServerUrl = "jdbc:sqlserver://localhost:1433;databaseName=mydb";

Statement / PreparedStatement / ResultSet

Statement

**Statement**用于执行静态 SQL 语句。

Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();

// 执行查询
ResultSet rs = statement.executeQuery("SELECT * FROM users");

// 执行更新
int rows = statement.executeUpdate("UPDATE users SET name='张三' WHERE id=1");

// 执行任意 SQL
boolean hasResult = statement.execute("SELECT * FROM users");

statement.close();
connection.close();

PreparedStatement(推荐)

**PreparedStatement**用于执行预编译的 SQL 语句,防止 SQL 注入。

// 使用占位符
String sql = "SELECT * FROM users WHERE id = ? AND name = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// 设置参数
pstmt.setInt(1, 1);
pstmt.setString(2, "张三");

// 执行查询
ResultSet rs = pstmt.executeQuery();

// 执行更新
String updateSql = "UPDATE users SET name = ? WHERE id = ?";
PreparedStatement updateStmt = connection.prepareStatement(updateSql);
updateStmt.setString(1, "李四");
updateStmt.setInt(2, 1);
int rows = updateStmt.executeUpdate();

pstmt.close();

优势

  • 防止 SQL 注入
  • 性能更好(预编译)
  • 代码更清晰

ResultSet

**ResultSet**表示查询结果集。

String sql = "SELECT id, name, age FROM users";
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();

// 遍历结果集
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");

System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}

// 或使用索引
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
}

rs.close();

ResultSet 的常用方法

// 移动光标
rs.next(); // 下一行
rs.previous(); // 上一行
rs.first(); // 第一行
rs.last(); // 最后一行
rs.absolute(5); // 移动到第 5 行

// 获取数据
rs.getInt("id");
rs.getString("name");
rs.getDouble("price");
rs.getDate("birthday");
rs.getTimestamp("created_at");

// 检查
boolean hasNext = rs.next();
boolean isFirst = rs.isFirst();
boolean isLast = rs.isLast();

数据库增删改查示例

示例 1:查询数据

import java.sql.*;

public class QueryExample {
public static void queryUsers() throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(
"SELECT id, name, email FROM users WHERE age > ?")) {

pstmt.setInt(1, 18);

try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
}
}
}
}
}

示例 2:插入数据

public class InsertExample {
public static void insertUser(String name, String email, int age) throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql)) {

pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, age);

int rows = pstmt.executeUpdate();
System.out.println("插入 " + rows + " 行");
}
}
}

示例 3:更新数据

public class UpdateExample {
public static void updateUser(int id, String newName) throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

String sql = "UPDATE users SET name = ? WHERE id = ?";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql)) {

pstmt.setString(1, newName);
pstmt.setInt(2, id);

int rows = pstmt.executeUpdate();
System.out.println("更新 " + rows + " 行");
}
}
}

示例 4:删除数据

public class DeleteExample {
public static void deleteUser(int id) throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

String sql = "DELETE FROM users WHERE id = ?";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql)) {

pstmt.setInt(1, id);

int rows = pstmt.executeUpdate();
System.out.println("删除 " + rows + " 行");
}
}
}

示例 5:获取自增主键

public class AutoIncrementExample {
public static int insertUserWithId(String name, String email) throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(
sql, Statement.RETURN_GENERATED_KEYS)) {

pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.executeUpdate();

// 获取生成的主键
try (ResultSet rs = pstmt.getGeneratedKeys()) {
if (rs.next()) {
int id = rs.getInt(1);
System.out.println("插入的用户 ID:" + id);
return id;
}
}
}
return -1;
}
}

事务与批处理

事务处理

事务用于保证数据库操作的原子性。

Connection connection = DriverManager.getConnection(url, username, password);

try {
// 关闭自动提交
connection.setAutoCommit(false);

// 执行多个操作
PreparedStatement pstmt1 = connection.prepareStatement("UPDATE account SET balance = balance - ? WHERE id = ?");
pstmt1.setDouble(1, 100);
pstmt1.setInt(2, 1);
pstmt1.executeUpdate();

PreparedStatement pstmt2 = connection.prepareStatement("UPDATE account SET balance = balance + ? WHERE id = ?");
pstmt2.setDouble(1, 100);
pstmt2.setInt(2, 2);
pstmt2.executeUpdate();

// 提交事务
connection.commit();
System.out.println("事务提交成功");

} catch (SQLException e) {
// 回滚事务
connection.rollback();
System.out.println("事务回滚");
e.printStackTrace();
} finally {
connection.setAutoCommit(true);
connection.close();
}

批处理

批处理用于提高批量操作的性能。

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = connection.prepareStatement(sql)) {

// 关闭自动提交
connection.setAutoCommit(false);

// 添加多个批处理
for (int i = 0; i < 1000; i++) {
pstmt.setString(1, "User" + i);
pstmt.setString(2, "user" + i + "@example.com");
pstmt.addBatch(); // 添加到批处理
}

// 执行批处理
int[] results = pstmt.executeBatch();
connection.commit();

System.out.println("批处理完成,插入 " + results.length + " 行");
}

实际示例

示例 1:完整的 CRUD 操作

import java.sql.*;

public class UserDAO {
private String url;
private String username;
private String password;

public UserDAO(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}

// 查询
public void findAll() throws SQLException {
String sql = "SELECT * FROM users";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {

while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") +
", Name: " + rs.getString("name"));
}
}
}

// 插入
public int insert(String name, String email) throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {

pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.executeUpdate();

try (ResultSet rs = pstmt.getGeneratedKeys()) {
if (rs.next()) {
return rs.getInt(1);
}
}
}
return -1;
}

// 更新
public int update(int id, String name) throws SQLException {
String sql = "UPDATE users SET name = ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setString(1, name);
pstmt.setInt(2, id);
return pstmt.executeUpdate();
}
}

// 删除
public int delete(int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setInt(1, id);
return pstmt.executeUpdate();
}
}
}

示例 2:事务示例

public class TransactionExample {
public static void transfer(int fromId, int toId, double amount) throws SQLException {
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "password";

try (Connection conn = DriverManager.getConnection(url, username, password)) {
conn.setAutoCommit(false);

try {
// 扣款
PreparedStatement pstmt1 = conn.prepareStatement(
"UPDATE account SET balance = balance - ? WHERE id = ?");
pstmt1.setDouble(1, amount);
pstmt1.setInt(2, fromId);
pstmt1.executeUpdate();

// 存款
PreparedStatement pstmt2 = conn.prepareStatement(
"UPDATE account SET balance = balance + ? WHERE id = ?");
pstmt2.setDouble(1, amount);
pstmt2.setInt(2, toId);
pstmt2.executeUpdate();

conn.commit();
System.out.println("转账成功");
} catch (SQLException e) {
conn.rollback();
System.out.println("转账失败,已回滚");
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
}

JDBC 的最佳实践

1. 使用 try-with-resources

// ✅ 推荐:自动关闭资源
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
// 使用资源
}

2. 使用 PreparedStatement

// ✅ 推荐:防止 SQL 注入,性能更好
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");

// ❌ 不推荐:SQL 注入风险
// Statement stmt = conn.createStatement();
// stmt.executeQuery("SELECT * FROM users WHERE id = " + id);

3. 使用连接池

// 使用连接池(如 HikariCP)
HikariConfig config = new HikariConfig();
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
HikariDataSource dataSource = new HikariDataSource(config);

Connection conn = dataSource.getConnection();

4. 正确处理异常

try {
// JDBC 操作
} catch (SQLException e) {
System.err.println("SQL 错误:" + e.getMessage());
System.err.println("错误码:" + e.getErrorCode());
System.err.println("SQL 状态:" + e.getSQLState());
e.printStackTrace();
}

小结

Java JDBC 数据库连接要点:

  • JDBC 驱动:连接 Java 和数据库
  • Connection:数据库连接
  • Statement/PreparedStatement:执行 SQL 语句
  • ResultSet:查询结果集
  • 事务:保证操作原子性
  • 批处理:提高批量操作性能

关键要点

  • 使用 PreparedStatement 防止 SQL 注入
  • 使用 try-with-resources 自动关闭资源
  • 使用事务保证数据一致性
  • 使用批处理提高性能
  • 使用连接池管理连接

理解了 JDBC,你就能进行数据库应用开发。恭喜你完成了第八部分的学习!