15.1 JDBC基础
15.1.1 JDBC概述
JDBC（Java Database Connectivity）是Java语言访问数据库的标准API，它提供了一套完整的接口，允许Java程序与各种关系型数据库进行交互。
JDBC的核心组件： - DriverManager：管理数据库驱动程序 - Connection：表示与数据库的连接 - Statement：执行SQL语句 - ResultSet：处理查询结果 - PreparedStatement：预编译的SQL语句 - CallableStatement：调用存储过程
15.1.2 数据库连接
import java.sql.*;
import java.util.Properties;
// JDBC基础连接演示
public class JDBCBasicsDemo {
// 数据库连接信息
private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/testdb";
private static final String MYSQL_USER = "root";
private static final String MYSQL_PASSWORD = "password";
private static final String H2_URL = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
private static final String H2_USER = "sa";
private static final String H2_PASSWORD = "";
public static void main(String[] args) {
// 基础连接演示
demonstrateBasicConnection();
// 连接属性配置
demonstrateConnectionProperties();
// 连接池基础
demonstrateConnectionPooling();
// 数据库元数据
demonstrateDatabaseMetadata();
}
// 基础连接演示
public static void demonstrateBasicConnection() {
System.out.println("=== JDBC基础连接演示 ===");
// 1. 使用H2内存数据库（无需安装）
System.out.println("1. H2内存数据库连接:");
try {
// 加载驱动（可选，JDBC 4.0后自动加载）
Class.forName("org.h2.Driver");
// 建立连接
try (Connection conn = DriverManager.getConnection(H2_URL, H2_USER, H2_PASSWORD)) {
System.out.println(" 连接成功: " + conn.getMetaData().getDatabaseProductName());
System.out.println(" 数据库版本: " + conn.getMetaData().getDatabaseProductVersion());
System.out.println(" 驱动版本: " + conn.getMetaData().getDriverVersion());
// 测试连接
if (!conn.isClosed()) {
System.out.println(" 连接状态: 活跃");
}
}
} catch (ClassNotFoundException e) {
System.out.println(" 驱动未找到: " + e.getMessage());
} catch (SQLException e) {
System.out.println(" 连接失败: " + e.getMessage());
}
// 2. MySQL连接示例（需要MySQL服务器）
System.out.println("\n2. MySQL数据库连接示例:");
try (Connection conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
System.out.println(" MySQL连接成功");
} catch (SQLException e) {
System.out.println(" MySQL连接失败（可能未安装MySQL）: " + e.getMessage());
}
}
// 连接属性配置
public static void demonstrateConnectionProperties() {
System.out.println("\n=== 连接属性配置 ===");
// 使用Properties配置连接
Properties props = new Properties();
props.setProperty("user", H2_USER);
props.setProperty("password", H2_PASSWORD);
props.setProperty("useSSL", "false");
props.setProperty("serverTimezone", "UTC");
props.setProperty("characterEncoding", "utf8");
try (Connection conn = DriverManager.getConnection(H2_URL, props)) {
System.out.println("使用Properties连接成功");
// 设置连接属性
conn.setAutoCommit(false); // 关闭自动提交
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
System.out.println(" 自动提交: " + conn.getAutoCommit());
System.out.println(" 事务隔离级别: " + getIsolationLevelName(conn.getTransactionIsolation()));
System.out.println(" 只读模式: " + conn.isReadOnly());
// 恢复自动提交
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("连接配置失败: " + e.getMessage());
}
}
private static String getIsolationLevelName(int level) {
switch (level) {
case Connection.TRANSACTION_NONE: return "NONE";
case Connection.TRANSACTION_READ_UNCOMMITTED: return "READ_UNCOMMITTED";
case Connection.TRANSACTION_READ_COMMITTED: return "READ_COMMITTED";
case Connection.TRANSACTION_REPEATABLE_READ: return "REPEATABLE_READ";
case Connection.TRANSACTION_SERIALIZABLE: return "SERIALIZABLE";
default: return "UNKNOWN";
}
}
// 连接池基础
public static void demonstrateConnectionPooling() {
System.out.println("\n=== 连接池基础 ===");
// 简单的连接池实现
SimpleConnectionPool pool = new SimpleConnectionPool(H2_URL, H2_USER, H2_PASSWORD, 5);
try {
// 获取连接
Connection conn1 = pool.getConnection();
Connection conn2 = pool.getConnection();
System.out.println("获取连接1: " + (conn1 != null ? "成功" : "失败"));
System.out.println("获取连接2: " + (conn2 != null ? "成功" : "失败"));
System.out.println("池中可用连接数: " + pool.getAvailableConnections());
// 归还连接
pool.releaseConnection(conn1);
pool.releaseConnection(conn2);
System.out.println("归还连接后可用数: " + pool.getAvailableConnections());
} catch (SQLException e) {
System.out.println("连接池操作失败: " + e.getMessage());
} finally {
pool.close();
}
}
// 连接池性能测试
public static void demonstrateConnectionPoolPerformance() {
System.out.println("\n=== 连接池性能测试 ===");
// 配置连接池
ConnectionPoolConfig config = new ConnectionPoolConfig()
.setUrl("jdbc:h2:mem:perftest;DB_CLOSE_DELAY=-1")
.setUsername("sa")
.setPassword("")
.setMinPoolSize(10)
.setMaxPoolSize(50);
AdvancedConnectionPool pool = new AdvancedConnectionPool(config);
try {
pool.initialize();
// 创建测试表
try (Connection conn = pool.getConnection();
Statement stmt = conn.createStatement()) {
stmt.executeUpdate("CREATE TABLE test_table (id INT PRIMARY KEY, data VARCHAR(100))");
}
// 性能测试参数
int threadCount = 10;
int operationsPerThread = 100;
System.out.println("性能测试参数:");
System.out.println(" 线程数: " + threadCount);
System.out.println(" 每线程操作数: " + operationsPerThread);
System.out.println(" 总操作数: " + (threadCount * operationsPerThread));
// 执行性能测试
long startTime = System.currentTimeMillis();
ExecutorService executor = Executors.newFixedThreadPool(threadCount);
CountDownLatch latch = new CountDownLatch(threadCount);
AtomicInteger successCount = new AtomicInteger(0);
AtomicInteger errorCount = new AtomicInteger(0);
for (int i = 0; i < threadCount; i++) {
final int threadId = i;
executor.submit(() -> {
try {
for (int j = 0; j < operationsPerThread; j++) {
try (Connection conn = pool.getConnection();
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO test_table (id, data) VALUES (?, ?)")) {
int id = threadId * operationsPerThread + j;
pstmt.setInt(1, id);
pstmt.setString(2, "Thread-" + threadId + "-Data-" + j);
pstmt.executeUpdate();
successCount.incrementAndGet();
} catch (SQLException e) {
errorCount.incrementAndGet();
}
}
} finally {
latch.countDown();
}
});
}
// 等待所有线程完成
latch.await();
executor.shutdown();
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.println("\n性能测试结果:");
System.out.println(" 执行时间: " + duration + "ms");
System.out.println(" 成功操作: " + successCount.get());
System.out.println(" 失败操作: " + errorCount.get());
System.out.println(" 平均TPS: " + (successCount.get() * 1000.0 / duration));
// 显示连接池统计
ConnectionPoolStats stats = pool.getStats();
System.out.println("\n连接池统计:");
System.out.println(" 总获取次数: " + stats.getTotalBorrowedConnections());
System.out.println(" 总归还次数: " + stats.getTotalReturnedConnections());
System.out.println(" 平均获取时间: " + stats.getAverageConnectionTime() + "ms");
} catch (Exception e) {
System.out.println("性能测试失败: " + e.getMessage());
} finally {
pool.close();
}
}
// 连接池监控
public static void demonstrateConnectionPoolMonitoring() {
System.out.println("\n=== 连接池监控 ===");
ConnectionPoolConfig config = new ConnectionPoolConfig()
.setUrl("jdbc:h2:mem:monitortest;DB_CLOSE_DELAY=-1")
.setUsername("sa")
.setPassword("")
.setMinPoolSize(3)
.setMaxPoolSize(10);
AdvancedConnectionPool pool = new AdvancedConnectionPool(config);
try {
pool.initialize();
// 启动监控线程
ScheduledExecutorService monitor = Executors.newScheduledThreadPool(1);
monitor.scheduleAtFixedRate(() -> {
ConnectionPoolStats stats = pool.getStats();
System.out.printf("[监控] 活跃:%d, 空闲:%d, 总数:%d, 等待:%d%n",
stats.getActiveConnections(),
stats.getIdleConnections(),
stats.getTotalConnections(),
stats.getWaitingThreads());
}, 0, 1, TimeUnit.SECONDS);
// 模拟连接使用
System.out.println("开始模拟连接使用...");
// 获取多个连接
Connection[] connections = new Connection[8];
for (int i = 0; i < connections.length; i++) {
connections[i] = pool.getConnection();
Thread.sleep(500); // 模拟处理时间
}
System.out.println("\n获取了8个连接，等待2秒...");
Thread.sleep(2000);
// 归还一半连接
for (int i = 0; i < connections.length / 2; i++) {
connections[i].close();
}
System.out.println("\n归还了4个连接，等待2秒...");
Thread.sleep(2000);
// 归还剩余连接
for (int i = connections.length / 2; i < connections.length; i++) {
connections[i].close();
}
System.out.println("\n归还了所有连接，等待2秒...");
Thread.sleep(2000);
monitor.shutdown();
} catch (Exception e) {
System.out.println("监控演示失败: " + e.getMessage());
} finally {
pool.close();
}
}
// 第三方连接池演示
public static void demonstrateThirdPartyConnectionPools() {
System.out.println("\n=== 第三方连接池演示 ===");
// HikariCP配置示例
System.out.println("1. HikariCP配置示例:");
System.out.println("""
// HikariCP配置
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setLeakDetectionThreshold(60000);
HikariDataSource dataSource = new HikariDataSource(config);
""");
// Druid配置示例
System.out.println("\n2. Druid配置示例:");
System.out.println("""
// Druid配置
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUsername("user");
dataSource.setPassword("password");
dataSource.setInitialSize(5);
dataSource.setMinIdle(5);
dataSource.setMaxActive(20);
dataSource.setMaxWait(60000);
dataSource.setTimeBetweenEvictionRunsMillis(60000);
dataSource.setMinEvictableIdleTimeMillis(300000);
dataSource.setValidationQuery("SELECT 1");
dataSource.setTestWhileIdle(true);
dataSource.setTestOnBorrow(false);
dataSource.setTestOnReturn(false);
""");
// C3P0配置示例
System.out.println("\n3. C3P0配置示例:");
System.out.println("""
// C3P0配置
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUser("user");
dataSource.setPassword("password");
dataSource.setInitialPoolSize(5);
dataSource.setMinPoolSize(5);
dataSource.setMaxPoolSize(20);
dataSource.setMaxIdleTime(300);
dataSource.setAcquireIncrement(2);
dataSource.setMaxStatements(100);
""");
// 连接池选择建议
System.out.println("\n4. 连接池选择建议:");
System.out.println("""
- HikariCP: 性能最优，配置简单，Spring Boot默认
- Druid: 功能丰富，监控完善，阿里巴巴开源
- C3P0: 老牌连接池，稳定可靠，配置复杂
- DBCP2: Apache项目，功能完整，性能一般
推荐优先级: HikariCP > Druid > DBCP2 > C3P0
""");
}
}
## 5. ORM框架基础
### 5.1 ORM概述
ORM（Object-Relational Mapping）对象关系映射，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。
```java
// ORM基础演示
public class ORMBasicsDemo {
public static void main(String[] args) {
demonstrateJPABasics();
demonstrateHibernateBasics();
demonstrateMyBatisBasics();
}
// JPA基础演示
public static void demonstrateJPABasics() {
System.out.println("=== JPA基础演示 ===");
// 实体类示例
System.out.println("1. JPA实体类示例:");
System.out.println("""
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "username", nullable = false, unique = true)
private String username;
@Column(name = "email")
private String email;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "created_at")
private Date createdAt;
// 构造器、getter、setter
}
""");
// Repository接口示例
System.out.println("\n2. JPA Repository示例:");
System.out.println("""
public interface UserRepository extends JpaRepository<User, Long> {
List<User> findByUsername(String username);
List<User> findByEmailContaining(String email);
@Query("SELECT u FROM User u WHERE u.createdAt > :date")
List<User> findUsersCreatedAfter(@Param("date") Date date);
@Modifying
@Query("UPDATE User u SET u.email = :email WHERE u.id = :id")
int updateUserEmail(@Param("id") Long id, @Param("email") String email);
}
""");
// 服务层示例
System.out.println("\n3. 服务层示例:");
System.out.println("""
@Service
@Transactional
public class UserService {
@Autowired
private UserRepository userRepository;
public User createUser(String username, String email) {
User user = new User();
user.setUsername(username);
user.setEmail(email);
user.setCreatedAt(new Date());
return userRepository.save(user);
}
@Transactional(readOnly = true)
public List<User> findAllUsers() {
return userRepository.findAll();
}
public void deleteUser(Long id) {
userRepository.deleteById(id);
}
}
""");
}
// Hibernate基础演示
public static void demonstrateHibernateBasics() {
System.out.println("\n=== Hibernate基础演示 ===");
// 配置示例
System.out.println("1. Hibernate配置示例:");
System.out.println("""
// hibernate.cfg.xml
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb</property>
<property name="hibernate.connection.username">user</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
</session-factory>
</hibernate-configuration>
""");
// SessionFactory使用示例
System.out.println("\n2. SessionFactory使用示例:");
System.out.println("""
public class HibernateUtil {
private static SessionFactory sessionFactory;
static {
try {
Configuration configuration = new Configuration();
configuration.configure("hibernate.cfg.xml");
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
throw new RuntimeException("SessionFactory创建失败", e);
}
}
public static SessionFactory getSessionFactory() {
return sessionFactory;
}
public static void shutdown() {
if (sessionFactory != null) {
sessionFactory.close();
}
}
}
""");
// CRUD操作示例
System.out.println("\n3. Hibernate CRUD操作:");
System.out.println("""
public class UserDAO {
public void saveUser(User user) {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
try {
session.save(user);
tx.commit();
} catch (Exception e) {
tx.rollback();
throw e;
}
}
public User getUserById(Long id) {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
try {
User user = session.get(User.class, id);
tx.commit();
return user;
} catch (Exception e) {
tx.rollback();
throw e;
}
}
public List<User> getAllUsers() {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
Transaction tx = session.beginTransaction();
try {
Query<User> query = session.createQuery("FROM User", User.class);
List<User> users = query.list();
tx.commit();
return users;
} catch (Exception e) {
tx.rollback();
throw e;
}
}
}
""");
}
// MyBatis基础演示
public static void demonstrateMyBatisBasics() {
System.out.println("\n=== MyBatis基础演示 ===");
// 配置示例
System.out.println("1. MyBatis配置示例:");
System.out.println("""
<!-- mybatis-config.xml -->
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
<property name="username" value="user"/>
<property name="password" value="password"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
""");
// Mapper接口示例
System.out.println("\n2. Mapper接口示例:");
System.out.println("""
public interface UserMapper {
void insertUser(User user);
User selectUserById(Long id);
List<User> selectAllUsers();
void updateUser(User user);
void deleteUser(Long id);
List<User> selectUsersByUsername(@Param("username") String username);
int countUsers();
}
""");
// Mapper XML示例
System.out.println("\n3. Mapper XML示例:");
System.out.println("""
<!-- UserMapper.xml -->
<mapper namespace="com.example.mapper.UserMapper">
<resultMap id="UserResultMap" type="com.example.model.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="email" column="email"/>
<result property="createdAt" column="created_at"/>
</resultMap>
<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO users (username, email, created_at)
VALUES (#{username}, #{email}, #{createdAt})
</insert>
<select id="selectUserById" parameterType="long" resultMap="UserResultMap">
SELECT * FROM users WHERE id = #{id}
</select>
<select id="selectAllUsers" resultMap="UserResultMap">
SELECT * FROM users ORDER BY created_at DESC
</select>
<update id="updateUser" parameterType="User">
UPDATE users SET
username = #{username},
email = #{email}
WHERE id = #{id}
</update>
<delete id="deleteUser" parameterType="long">
DELETE FROM users WHERE id = #{id}
</delete>
<select id="selectUsersByUsername" parameterType="string" resultMap="UserResultMap">
SELECT * FROM users WHERE username LIKE CONCAT('%', #{username}, '%')
</select>
<select id="countUsers" resultType="int">
SELECT COUNT(*) FROM users
</select>
</mapper>
""");
// 使用示例
System.out.println("\n4. MyBatis使用示例:");
System.out.println("""
public class UserService {
private SqlSessionFactory sqlSessionFactory;
public UserService() {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
throw new RuntimeException("MyBatis初始化失败", e);
}
}
public void createUser(User user) {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.insertUser(user);
session.commit();
}
}
public User getUserById(Long id) {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
return mapper.selectUserById(id);
}
}
public List<User> getAllUsers() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
return mapper.selectAllUsers();
}
}
}
""");
}
}
// 用户实体类
class User {
private Long id;
private String username;
private String email;
private Date createdAt;
// 构造器
public User() {}
public User(String username, String email) {
this.username = username;
this.email = email;
this.createdAt = new Date();
}
// Getter和Setter方法
public Long getId() { return id; }
public void setId(Long 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 Date getCreatedAt() { return createdAt; }
public void setCreatedAt(Date createdAt) { this.createdAt = createdAt; }
@Override
public String toString() {
return String.format("User{id=%d, username='%s', email='%s', createdAt=%s}",
id, username, email, createdAt);
}
}
5.2 ORM框架对比
// ORM框架对比演示
public class ORMComparisonDemo {
public static void main(String[] args) {
compareORMFrameworks();
demonstrateORMSelection();
}
public static void compareORMFrameworks() {
System.out.println("=== ORM框架对比 ===");
System.out.println("1. JPA/Hibernate:");
System.out.println("""
优点:
- 标准化的JPA规范
- 强大的对象关系映射
- 自动SQL生成
- 缓存机制完善
- 延迟加载支持
缺点:
- 学习曲线陡峭
- 复杂查询性能较差
- 配置复杂
- 调试困难
适用场景:
- 复杂的业务逻辑
- 对象关系复杂
- 快速开发需求
""");
System.out.println("\n2. MyBatis:");
System.out.println("""
优点:
- SQL可控性强
- 性能优秀
- 学习成本低
- 灵活性高
- 调试方便
缺点:
- 需要编写SQL
- 数据库移植性差
- 配置文件较多
- 缓存机制相对简单
适用场景:
- 复杂查询较多
- 性能要求高
- 需要精确控制SQL
""");
System.out.println("\n3. Spring Data JPA:");
System.out.println("""
优点:
- 简化开发
- 自动实现Repository
- 方法名查询
- 分页排序支持
- 与Spring集成完美
缺点:
- 复杂查询支持有限
- 性能调优困难
- 过度依赖框架
适用场景:
- Spring项目
- 简单CRUD操作
- 快速原型开发
""");
}
public static void demonstrateORMSelection() {
System.out.println("\n=== ORM框架选择指南 ===");
System.out.println("选择决策树:");
System.out.println("""
1. 项目规模和复杂度
- 小型项目: MyBatis或Spring Data JPA
- 大型项目: Hibernate + Spring Data JPA
2. 性能要求
- 高性能要求: MyBatis
- 一般性能要求: Hibernate
3. 团队技能
- SQL熟练: MyBatis
- OOP思维: Hibernate
4. 开发效率
- 快速开发: Spring Data JPA
- 精确控制: MyBatis
5. 维护成本
- 长期维护: Hibernate
- 短期项目: MyBatis
""");
System.out.println("\n推荐组合:");
System.out.println("""
1. 企业级应用: Spring Data JPA + Hibernate
2. 高性能应用: MyBatis + 自定义DAO
3. 微服务架构: Spring Data JPA + 简化配置
4. 传统项目: MyBatis + 手动事务管理
5. 新手项目: Spring Data JPA + H2数据库
""");
}
}
6. 数据库编程最佳实践
6.1 性能优化
// 数据库性能优化演示
public class DatabasePerformanceDemo {
public static void main(String[] args) {
demonstrateQueryOptimization();
demonstrateBatchOperations();
demonstrateConnectionOptimization();
demonstrateCachingStrategies();
}
// 查询优化
public static void demonstrateQueryOptimization() {
System.out.println("=== 查询优化 ===");
System.out.println("1. 索引优化:");
System.out.println("""
// 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_created_at ON users(created_at);
CREATE COMPOSITE INDEX idx_user_status_type ON users(status, type);
// 查询优化
// 好的查询 - 使用索引
SELECT * FROM users WHERE email = 'user@example.com';
// 坏的查询 - 全表扫描
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';
// 优化后的查询
SELECT * FROM users WHERE email = LOWER('USER@EXAMPLE.COM');
""");
System.out.println("\n2. 分页优化:");
System.out.println("""
// 传统分页 - 性能差
SELECT * FROM users ORDER BY id LIMIT 10000, 20;
// 优化分页 - 使用游标
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
// 覆盖索引分页
SELECT u.* FROM users u
INNER JOIN (
SELECT id FROM users ORDER BY id LIMIT 10000, 20
) t ON u.id = t.id;
""");
System.out.println("\n3. 查询重写:");
System.out.println("""
// 避免SELECT *
// 坏的查询
SELECT * FROM users WHERE status = 'active';
// 好的查询
SELECT id, username, email FROM users WHERE status = 'active';
// 使用EXISTS代替IN
// 坏的查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
// 好的查询
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
""");
}
// 批量操作优化
public static void demonstrateBatchOperations() {
System.out.println("\n=== 批量操作优化 ===");
try (Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:batchtest;DB_CLOSE_DELAY=-1", "sa", "")) {
// 创建测试表
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate("""
CREATE TABLE batch_test (
id INT PRIMARY KEY,
name VARCHAR(100),
value DECIMAL(10,2)
)
""");
}
// 单条插入 vs 批量插入性能对比
int recordCount = 1000;
// 单条插入
long startTime = System.currentTimeMillis();
try (PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO batch_test (id, name, value) VALUES (?, ?, ?)")) {
for (int i = 0; i < recordCount; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Name-" + i);
pstmt.setBigDecimal(3, new BigDecimal(i * 10.5));
pstmt.executeUpdate();
}
}
long singleInsertTime = System.currentTimeMillis() - startTime;
// 清空表
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate("DELETE FROM batch_test");
}
// 批量插入
startTime = System.currentTimeMillis();
try (PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO batch_test (id, name, value) VALUES (?, ?, ?)")) {
for (int i = 0; i < recordCount; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Name-" + i);
pstmt.setBigDecimal(3, new BigDecimal(i * 10.5));
pstmt.addBatch();
// 每100条执行一次批量操作
if (i % 100 == 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}
// 执行剩余的批量操作
pstmt.executeBatch();
}
long batchInsertTime = System.currentTimeMillis() - startTime;
System.out.println("批量操作性能对比:");
System.out.println(" 记录数: " + recordCount);
System.out.println(" 单条插入时间: " + singleInsertTime + "ms");
System.out.println(" 批量插入时间: " + batchInsertTime + "ms");
System.out.println(" 性能提升: " + (singleInsertTime / (double) batchInsertTime) + "倍");
} catch (SQLException e) {
System.out.println("批量操作演示失败: " + e.getMessage());
}
}
// 连接优化
public static void demonstrateConnectionOptimization() {
System.out.println("\n=== 连接优化 ===");
System.out.println("1. 连接池配置优化:");
System.out.println("""
// HikariCP优化配置
HikariConfig config = new HikariConfig();
// 核心配置
config.setMaximumPoolSize(20); // 最大连接数
config.setMinimumIdle(5); // 最小空闲连接数
config.setConnectionTimeout(30000); // 连接超时时间
config.setIdleTimeout(600000); // 空闲超时时间
config.setMaxLifetime(1800000); // 连接最大生存时间
// 性能优化
config.setLeakDetectionThreshold(60000); // 连接泄漏检测
config.setValidationTimeout(5000); // 验证超时时间
config.setInitializationFailTimeout(1); // 初始化失败超时
// 缓存优化
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
""");
System.out.println("\n2. 连接使用最佳实践:");
System.out.println("""
// 使用try-with-resources确保连接关闭
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 数据库操作
} // 自动关闭连接
// 避免长时间持有连接
// 坏的做法
Connection conn = dataSource.getConnection();
// ... 长时间的业务逻辑处理
PreparedStatement pstmt = conn.prepareStatement(sql);
// 好的做法
// ... 业务逻辑处理
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 数据库操作
}
""");
}
// 缓存策略
public static void demonstrateCachingStrategies() {
System.out.println("\n=== 缓存策略 ===");
System.out.println("1. 查询结果缓存:");
System.out.println("""
// 使用Redis缓存查询结果
public class UserService {
private RedisTemplate<String, Object> redisTemplate;
private UserRepository userRepository;
public User getUserById(Long id) {
String cacheKey = "user:" + id;
// 先从缓存获取
User user = (User) redisTemplate.opsForValue().get(cacheKey);
if (user != null) {
return user;
}
// 缓存未命中，从数据库获取
user = userRepository.findById(id).orElse(null);
if (user != null) {
// 缓存结果，设置过期时间
redisTemplate.opsForValue().set(cacheKey, user, 30, TimeUnit.MINUTES);
}
return user;
}
}
""");
System.out.println("\n2. 预编译语句缓存:");
System.out.println("""
// PreparedStatement缓存
public class StatementCache {
private final Map<String, PreparedStatement> cache = new ConcurrentHashMap<>();
private final Connection connection;
public PreparedStatement getPreparedStatement(String sql) throws SQLException {
return cache.computeIfAbsent(sql, key -> {
try {
return connection.prepareStatement(key);
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
}
}
""");
System.out.println("\n3. 二级缓存配置:");
System.out.println("""
// Hibernate二级缓存配置
@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class User {
// 实体定义
}
// 查询缓存
@Query("SELECT u FROM User u WHERE u.status = :status")
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<User> findByStatus(@Param("status") String status);
""");
}
}
6.2 安全最佳实践
// 数据库安全演示
public class DatabaseSecurityDemo {
public static void main(String[] args) {
demonstrateSQLInjectionPrevention();
demonstrateDataEncryption();
demonstrateAccessControl();
demonstrateAuditLogging();
}
// SQL注入防护
public static void demonstrateSQLInjectionPrevention() {
System.out.println("=== SQL注入防护 ===");
System.out.println("1. 危险的SQL拼接:");
System.out.println("""
// 危险代码 - 容易SQL注入
public List<User> findUsersByName(String name) {
String sql = "SELECT * FROM users WHERE name = '" + name + "'";
// 如果name = "'; DROP TABLE users; --"
// 实际执行: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
return executeQuery(sql);
}
""");
System.out.println("\n2. 安全的参数化查询:");
System.out.println("""
// 安全代码 - 使用PreparedStatement
public List<User> findUsersByName(String name) {
String sql = "SELECT * FROM users WHERE name = ?";
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
pstmt.setString(1, name); // 参数会被正确转义
return executeQuery(pstmt);
}
}
""");
System.out.println("\n3. 输入验证:");
System.out.println("""
public class InputValidator {
public static boolean isValidUsername(String username) {
// 只允许字母、数字、下划线，长度3-20
return username != null &&
username.matches("^[a-zA-Z0-9_]{3,20}$");
}
public static boolean isValidEmail(String email) {
// 简单的邮箱格式验证
return email != null &&
email.matches("^[\\w.-]+@[\\w.-]+\\.[a-zA-Z]{2,}$");
}
public static String sanitizeInput(String input) {
if (input == null) return null;
// 移除危险字符
return input.replaceAll("[<>\"'%;()&+]", "");
}
}
""");
}
// 数据加密
public static void demonstrateDataEncryption() {
System.out.println("\n=== 数据加密 ===");
System.out.println("1. 密码加密存储:");
System.out.println("""
public class PasswordUtils {
// 使用BCrypt加密密码
public static String hashPassword(String plainPassword) {
return BCrypt.hashpw(plainPassword, BCrypt.gensalt(12));
}
// 验证密码
public static boolean verifyPassword(String plainPassword, String hashedPassword) {
return BCrypt.checkpw(plainPassword, hashedPassword);
}
}
// 用户注册
public void registerUser(String username, String plainPassword) {
String hashedPassword = PasswordUtils.hashPassword(plainPassword);
String sql = "INSERT INTO users (username, password_hash) VALUES (?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
pstmt.setString(1, username);
pstmt.setString(2, hashedPassword);
pstmt.executeUpdate();
}
}
""");
System.out.println("\n2. 敏感数据加密:");
System.out.println("""
public class DataEncryption {
private static final String ALGORITHM = "AES/GCM/NoPadding";
private final SecretKey secretKey;
public DataEncryption() {
this.secretKey = generateKey();
}
// 加密敏感数据
public String encrypt(String plainText) throws Exception {
Cipher cipher = Cipher.getInstance(ALGORITHM);
cipher.init(Cipher.ENCRYPT_MODE, secretKey);
byte[] encryptedData = cipher.doFinal(plainText.getBytes());
byte[] iv = cipher.getIV();
// 将IV和加密数据组合
byte[] encryptedWithIv = new byte[iv.length + encryptedData.length];
System.arraycopy(iv, 0, encryptedWithIv, 0, iv.length);
System.arraycopy(encryptedData, 0, encryptedWithIv, iv.length, encryptedData.length);
return Base64.getEncoder().encodeToString(encryptedWithIv);
}
// 解密敏感数据
public String decrypt(String encryptedText) throws Exception {
byte[] encryptedWithIv = Base64.getDecoder().decode(encryptedText);
// 提取IV和加密数据
byte[] iv = new byte[12]; // GCM模式IV长度
byte[] encryptedData = new byte[encryptedWithIv.length - 12];
System.arraycopy(encryptedWithIv, 0, iv, 0, 12);
System.arraycopy(encryptedWithIv, 12, encryptedData, 0, encryptedData.length);
Cipher cipher = Cipher.getInstance(ALGORITHM);
GCMParameterSpec gcmSpec = new GCMParameterSpec(128, iv);
cipher.init(Cipher.DECRYPT_MODE, secretKey, gcmSpec);
byte[] decryptedData = cipher.doFinal(encryptedData);
return new String(decryptedData);
}
}
""");
}
// 访问控制
public static void demonstrateAccessControl() {
System.out.println("\n=== 访问控制 ===");
System.out.println("1. 基于角色的访问控制:");
System.out.println("""
public class AccessControlService {
public boolean hasPermission(User user, String resource, String action) {
// 检查用户角色权限
List<Role> roles = user.getRoles();
for (Role role : roles) {
List<Permission> permissions = role.getPermissions();
for (Permission permission : permissions) {
if (permission.getResource().equals(resource) &&
permission.getAction().equals(action)) {
return true;
}
}
}
return false;
}
public void checkPermission(User user, String resource, String action) {
if (!hasPermission(user, resource, action)) {
throw new AccessDeniedException(
"用户没有权限执行操作: " + action + " on " + resource);
}
}
}
// 使用示例
public void deleteUser(User currentUser, Long targetUserId) {
accessControlService.checkPermission(currentUser, "user", "delete");
// 执行删除操作
userRepository.deleteById(targetUserId);
}
""");
System.out.println("\n2. 数据库连接权限控制:");
System.out.println("""
// 不同环境使用不同的数据库用户
// 只读用户 - 用于查询操作
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON myapp.* TO 'app_readonly'@'%';
// 读写用户 - 用于一般操作
CREATE USER 'app_readwrite'@'%' IDENTIFIED BY 'readwrite_password';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_readwrite'@'%';
// 管理员用户 - 用于管理操作
CREATE USER 'app_admin'@'%' IDENTIFIED BY 'admin_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin'@'%';
""");
}
// 审计日志
public static void demonstrateAuditLogging() {
System.out.println("\n=== 审计日志 ===");
System.out.println("1. 数据库操作审计:");
System.out.println("""
public class AuditLogger {
public void logDatabaseOperation(String operation, String table,
String userId, Object data) {
AuditLog log = new AuditLog();
log.setOperation(operation);
log.setTableName(table);
log.setUserId(userId);
log.setData(data.toString());
log.setTimestamp(new Date());
log.setIpAddress(getCurrentUserIP());
// 保存审计日志
auditLogRepository.save(log);
}
// 使用AOP进行自动审计
@Around("@annotation(Auditable)")
public Object auditMethod(ProceedingJoinPoint joinPoint) throws Throwable {
String methodName = joinPoint.getSignature().getName();
Object[] args = joinPoint.getArgs();
// 记录操作开始
logDatabaseOperation("START", methodName, getCurrentUserId(), args);
try {
Object result = joinPoint.proceed();
// 记录操作成功
logDatabaseOperation("SUCCESS", methodName, getCurrentUserId(), result);
return result;
} catch (Exception e) {
// 记录操作失败
logDatabaseOperation("FAILURE", methodName, getCurrentUserId(), e.getMessage());
throw e;
}
}
}
""");
System.out.println("\n2. 审计表设计:");
System.out.println("""
CREATE TABLE audit_logs (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
operation VARCHAR(50) NOT NULL,
table_name VARCHAR(100),
user_id VARCHAR(100),
ip_address VARCHAR(45),
old_data TEXT,
new_data TEXT,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
session_id VARCHAR(100),
INDEX idx_user_timestamp (user_id, timestamp),
INDEX idx_table_timestamp (table_name, timestamp)
);
""");
}
}
7. 本章小结
7.1 技术对比总结
| 技术 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| JDBC | 性能高、控制精确 | 代码冗长、易出错 | 高性能要求、复杂SQL |
| Hibernate | 开发效率高、对象化 | 学习成本高、性能调优难 | 复杂业务逻辑 |
| MyBatis | SQL可控、性能好 | 需要写SQL、移植性差 | 复杂查询、性能敏感 |
| Spring Data JPA | 简化开发、自动化 | 复杂查询支持有限 | 快速开发、简单CRUD |
7.2 性能优化要点
- 连接池优化：合理配置连接池参数
- 查询优化：使用索引、避免全表扫描
- 批量操作：减少网络往返次数
- 缓存策略：合理使用一级、二级缓存
- 分页优化：使用游标分页代替偏移分页
7.3 安全考虑
- SQL注入防护：使用参数化查询
- 数据加密：敏感数据加密存储
- 访问控制：基于角色的权限管理
- 审计日志：记录所有数据库操作
- 连接安全：使用SSL连接、最小权限原则
7.4 开发建议
- 选择合适的技术栈：根据项目需求选择ORM框架
- 遵循最佳实践：使用连接池、参数化查询
- 性能监控：定期监控数据库性能
- 安全第一：始终考虑安全因素
- 代码规范：统一数据访问层代码风格
下一章预告：第16章将学习Java多线程编程，包括线程基础、同步机制、并发工具类、线程池等内容。
练习题
基础练习
JDBC基础操作
- 创建一个学生管理系统，实现学生信息的增删改查
- 使用PreparedStatement防止SQL注入
- 实现事务管理确保数据一致性
连接池实现
- 实现一个简单的数据库连接池
- 支持连接的获取、归还、超时处理
- 添加连接池监控功能
批量操作优化
- 比较单条插入和批量插入的性能差异
- 实现大数据量的分批处理
- 添加进度监控和错误处理
进阶练习
ORM框架应用
- 使用Hibernate实现一个博客系统
- 包含用户、文章、评论等实体关系
- 实现复杂查询和缓存优化
MyBatis实践
- 使用MyBatis实现一个电商订单系统
- 包含动态SQL、结果映射、分页查询
- 实现多表关联查询和统计报表
数据库安全
- 实现用户权限管理系统
- 包含角色、权限、资源的关系设计
- 添加操作审计和安全日志
综合项目
- 企业级数据访问层
- 设计一个通用的数据访问层框架
- 支持多数据源、读写分离、分库分表
- 包含缓存、监控、安全等功能
- 提供简单易用的API接口
提示：
- 注意资源管理，及时关闭连接
- 合理使用事务，避免长事务
- 重视安全，防止SQL注入
- 关注性能，使用连接池和批量操作
- 添加适当的日志和监控
static class SimpleConnectionPool {
private final String url;
private final String user;
private final String password;
private final java.util.Queue
public SimpleConnectionPool(String url, String user, String password, int maxPoolSize) {
this.url = url;
this.user = user;
this.password = password;
this.maxPoolSize = maxPoolSize;
this.availableConnections = new java.util.concurrent.ConcurrentLinkedQueue<>();
this.usedConnections = java.util.concurrent.ConcurrentHashMap.newKeySet();
// 初始化连接池
initializePool();
}
private void initializePool() {
for (int i = 0; i < maxPoolSize; i++) {
try {
Connection conn = DriverManager.getConnection(url, user, password);
availableConnections.offer(conn);
} catch (SQLException e) {
System.out.println("初始化连接池失败: " + e.getMessage());
break;
}
}
}
public Connection getConnection() throws SQLException {
Connection conn = availableConnections.poll();
if (conn == null) {
if (usedConnections.size() < maxPoolSize) {
conn = DriverManager.getConnection(url, user, password);
} else {
throw new SQLException("连接池已满");
}
}
// 检查连接是否有效
if (conn.isClosed()) {
conn = DriverManager.getConnection(url, user, password);
}
usedConnections.add(conn);
return conn;
}
public void releaseConnection(Connection conn) {
if (conn != null && usedConnections.remove(conn)) {
availableConnections.offer(conn);
}
}
public int getAvailableConnections() {
return availableConnections.size();
}
public void close() {
// 关闭所有连接
for (Connection conn : availableConnections) {
try {
conn.close();
} catch (SQLException e) {
// 忽略关闭异常
}
}
for (Connection conn : usedConnections) {
try {
conn.close();
} catch (SQLException e) {
// 忽略关闭异常
}
}
availableConnections.clear();
usedConnections.clear();
}
}
// 数据库元数据
public static void demonstrateDatabaseMetadata() {
System.out.println("\n=== 数据库元数据 ===");
try (Connection conn = DriverManager.getConnection(H2_URL, H2_USER, H2_PASSWORD)) {
DatabaseMetaData metaData = conn.getMetaData();
System.out.println("数据库信息:");
System.out.println(" 产品名称: " + metaData.getDatabaseProductName());
System.out.println(" 产品版本: " + metaData.getDatabaseProductVersion());
System.out.println(" 驱动名称: " + metaData.getDriverName());
System.out.println(" 驱动版本: " + metaData.getDriverVersion());
System.out.println(" JDBC版本: " + metaData.getJDBCMajorVersion() + "." + metaData.getJDBCMinorVersion());
System.out.println("\n功能支持:");
System.out.println(" 支持事务: " + metaData.supportsTransactions());
System.out.println(" 支持批量更新: " + metaData.supportsBatchUpdates());
System.out.println(" 支持保存点: " + metaData.supportsSavepoints());
System.out.println(" 支持存储过程: " + metaData.supportsStoredProcedures());
System.out.println("\nSQL关键字限制:");
System.out.println(" 最大表名长度: " + metaData.getMaxTableNameLength());
System.out.println(" 最大列名长度: " + metaData.getMaxColumnNameLength());
System.out.println(" 最大连接数: " + metaData.getMaxConnections());
// 获取数据类型信息
System.out.println("\n支持的数据类型:");
try (ResultSet rs = metaData.getTypeInfo()) {
int count = 0;
while (rs.next() && count < 5) { // 只显示前5个
System.out.println(" " + rs.getString("TYPE_NAME") +
" (SQL类型: " + rs.getInt("DATA_TYPE") + ")");
count++;
}
if (count == 5) {
System.out.println(" ...(更多类型)");
}
}
} catch (SQLException e) {
System.out.println("获取数据库元数据失败: " + e.getMessage());
}
}
}
## 15.2 SQL语句执行
### 15.2.1 Statement基础操作
```java
import java.sql.*;
import java.util.*;
// SQL语句执行演示
public class SQLExecutionDemo {
private static final String DB_URL = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
public static void main(String[] args) {
// 初始化数据库
initializeDatabase();
// Statement基础操作
demonstrateStatement();
// PreparedStatement操作
demonstratePreparedStatement();
// 批量操作
demonstrateBatchOperations();
// 结果集处理
demonstrateResultSetProcessing();
}
// 初始化数据库
public static void initializeDatabase() {
System.out.println("=== 初始化数据库 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement()) {
// 创建用户表
String createUserTable = """
CREATE TABLE IF NOT EXISTS users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
)
""";
stmt.executeUpdate(createUserTable);
System.out.println("用户表创建成功");
// 创建订单表
String createOrderTable = """
CREATE TABLE IF NOT EXISTS orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
)
""";
stmt.executeUpdate(createOrderTable);
System.out.println("订单表创建成功");
// 插入初始数据
insertInitialData(conn);
} catch (SQLException e) {
System.out.println("数据库初始化失败: " + e.getMessage());
}
}
private static void insertInitialData(Connection conn) throws SQLException {
String[] insertUsers = {
"INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 25)",
"INSERT INTO users (username, email, age) VALUES ('bob', 'bob@example.com', 30)",
"INSERT INTO users (username, email, age) VALUES ('charlie', 'charlie@example.com', 35)"
};
try (Statement stmt = conn.createStatement()) {
for (String sql : insertUsers) {
stmt.executeUpdate(sql);
}
System.out.println("初始用户数据插入成功");
}
}
// Statement基础操作
public static void demonstrateStatement() {
System.out.println("\n=== Statement基础操作 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement()) {
// 1. 执行查询
System.out.println("1. 执行查询:");
String selectSQL = "SELECT id, username, email, age FROM users WHERE age > 25";
try (ResultSet rs = stmt.executeQuery(selectSQL)) {
while (rs.next()) {
System.out.printf(" 用户: %s, 邮箱: %s, 年龄: %d%n",
rs.getString("username"),
rs.getString("email"),
rs.getInt("age"));
}
}
// 2. 执行更新
System.out.println("\n2. 执行更新:");
String updateSQL = "UPDATE users SET age = age + 1 WHERE username = 'alice'";
int updatedRows = stmt.executeUpdate(updateSQL);
System.out.println(" 更新了 " + updatedRows + " 行数据");
// 3. 执行插入
System.out.println("\n3. 执行插入:");
String insertSQL = "INSERT INTO users (username, email, age) VALUES ('david', 'david@example.com', 28)";
int insertedRows = stmt.executeUpdate(insertSQL);
System.out.println(" 插入了 " + insertedRows + " 行数据");
// 4. 获取生成的主键
String insertWithKey = "INSERT INTO users (username, email, age) VALUES ('eve', 'eve@example.com', 32)";
int result = stmt.executeUpdate(insertWithKey, Statement.RETURN_GENERATED_KEYS);
if (result > 0) {
try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
if (generatedKeys.next()) {
System.out.println(" 生成的主键: " + generatedKeys.getLong(1));
}
}
}
// 5. 执行通用SQL
System.out.println("\n4. 执行通用SQL:");
String countSQL = "SELECT COUNT(*) as user_count FROM users";
boolean hasResultSet = stmt.execute(countSQL);
if (hasResultSet) {
try (ResultSet rs = stmt.getResultSet()) {
if (rs.next()) {
System.out.println(" 用户总数: " + rs.getInt("user_count"));
}
}
}
} catch (SQLException e) {
System.out.println("Statement操作失败: " + e.getMessage());
}
}
// PreparedStatement操作
public static void demonstratePreparedStatement() {
System.out.println("\n=== PreparedStatement操作 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 1. 参数化查询
System.out.println("1. 参数化查询:");
String selectSQL = "SELECT * FROM users WHERE age BETWEEN ? AND ? ORDER BY age";
try (PreparedStatement pstmt = conn.prepareStatement(selectSQL)) {
pstmt.setInt(1, 25);
pstmt.setInt(2, 35);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf(" %s (%d岁) - %s%n",
rs.getString("username"),
rs.getInt("age"),
rs.getString("email"));
}
}
}
// 2. 参数化插入
System.out.println("\n2. 参数化插入:");
String insertSQL = "INSERT INTO users (username, email, age, is_active) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {
// 插入多个用户
String[][] users = {
{"frank", "frank@example.com", "29", "true"},
{"grace", "grace@example.com", "27", "false"},
{"henry", "henry@example.com", "31", "true"}
};
for (String[] user : users) {
pstmt.setString(1, user[0]);
pstmt.setString(2, user[1]);
pstmt.setInt(3, Integer.parseInt(user[2]));
pstmt.setBoolean(4, Boolean.parseBoolean(user[3]));
int result = pstmt.executeUpdate();
if (result > 0) {
try (ResultSet keys = pstmt.getGeneratedKeys()) {
if (keys.next()) {
System.out.println(" 插入用户 " + user[0] + ", ID: " + keys.getLong(1));
}
}
}
}
}
// 3. 参数化更新
System.out.println("\n3. 参数化更新:");
String updateSQL = "UPDATE users SET email = ? WHERE username = ?";
try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
pstmt.setString(1, "frank.new@example.com");
pstmt.setString(2, "frank");
int updatedRows = pstmt.executeUpdate();
System.out.println(" 更新了 " + updatedRows + " 行数据");
}
// 4. 处理NULL值
System.out.println("\n4. 处理NULL值:");
String insertNullSQL = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertNullSQL)) {
pstmt.setString(1, "nullage");
pstmt.setString(2, "nullage@example.com");
pstmt.setNull(3, Types.INTEGER); // 设置NULL值
int result = pstmt.executeUpdate();
System.out.println(" 插入包含NULL值的记录: " + (result > 0 ? "成功" : "失败"));
}
// 5. 日期时间处理
System.out.println("\n5. 日期时间处理:");
String insertDateSQL = "INSERT INTO orders (user_id, product_name, quantity, price, order_date) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertDateSQL)) {
pstmt.setInt(1, 1); // alice的ID
pstmt.setString(2, "笔记本电脑");
pstmt.setInt(3, 1);
pstmt.setBigDecimal(4, new java.math.BigDecimal("5999.99"));
pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
int result = pstmt.executeUpdate();
System.out.println(" 插入订单记录: " + (result > 0 ? "成功" : "失败"));
}
} catch (SQLException e) {
System.out.println("PreparedStatement操作失败: " + e.getMessage());
}
}
// 批量操作
public static void demonstrateBatchOperations() {
System.out.println("\n=== 批量操作 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 关闭自动提交以提高批量操作性能
conn.setAutoCommit(false);
// 1. Statement批量操作
System.out.println("1. Statement批量操作:");
try (Statement stmt = conn.createStatement()) {
// 添加多个SQL到批次
stmt.addBatch("INSERT INTO users (username, email, age) VALUES ('batch1', 'batch1@example.com', 20)");
stmt.addBatch("INSERT INTO users (username, email, age) VALUES ('batch2', 'batch2@example.com', 21)");
stmt.addBatch("INSERT INTO users (username, email, age) VALUES ('batch3', 'batch3@example.com', 22)");
stmt.addBatch("UPDATE users SET is_active = false WHERE age < 25");
// 执行批量操作
int[] results = stmt.executeBatch();
System.out.println(" 批量操作结果:");
for (int i = 0; i < results.length; i++) {
System.out.println(" 操作 " + (i + 1) + ": 影响 " + results[i] + " 行");
}
conn.commit();
System.out.println(" 批量操作提交成功");
} catch (SQLException e) {
conn.rollback();
System.out.println(" 批量操作失败，已回滚: " + e.getMessage());
}
// 2. PreparedStatement批量操作
System.out.println("\n2. PreparedStatement批量操作:");
String batchInsertSQL = "INSERT INTO orders (user_id, product_name, quantity, price) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(batchInsertSQL)) {
// 准备批量数据
Object[][] orders = {
{1, "手机", 2, 2999.99},
{2, "平板", 1, 1999.99},
{3, "耳机", 3, 299.99},
{1, "充电器", 2, 99.99},
{2, "保护壳", 5, 29.99}
};
for (Object[] order : orders) {
pstmt.setInt(1, (Integer) order[0]);
pstmt.setString(2, (String) order[1]);
pstmt.setInt(3, (Integer) order[2]);
pstmt.setBigDecimal(4, new java.math.BigDecimal(order[3].toString()));
pstmt.addBatch();
}
// 执行批量插入
int[] results = pstmt.executeBatch();
System.out.println(" 批量插入 " + results.length + " 个订单");
int successCount = 0;
for (int result : results) {
if (result > 0) successCount++;
}
System.out.println(" 成功插入: " + successCount + " 个订单");
conn.commit();
} catch (SQLException e) {
conn.rollback();
System.out.println(" 批量插入失败，已回滚: " + e.getMessage());
}
// 恢复自动提交
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("批量操作失败: " + e.getMessage());
}
}
// 结果集处理
public static void demonstrateResultSetProcessing() {
System.out.println("\n=== 结果集处理 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 1. 基础结果集遍历
System.out.println("1. 基础结果集遍历:");
String selectSQL = "SELECT u.username, u.email, u.age, COUNT(o.id) as order_count " +
"FROM users u LEFT JOIN orders o ON u.id = o.user_id " +
"GROUP BY u.id, u.username, u.email, u.age " +
"ORDER BY order_count DESC";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(selectSQL)) {
System.out.println(" 用户订单统计:");
while (rs.next()) {
String username = rs.getString("username");
String email = rs.getString("email");
int age = rs.getInt("age");
int orderCount = rs.getInt("order_count");
System.out.printf(" %s (%d岁): %d个订单 - %s%n",
username, age, orderCount, email);
}
}
// 2. 结果集元数据
System.out.println("\n2. 结果集元数据:");
String metaSQL = "SELECT * FROM users LIMIT 1";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(metaSQL)) {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
System.out.println(" 表结构信息:");
for (int i = 1; i <= columnCount; i++) {
System.out.printf(" 列%d: %s (%s) - 可空:%s%n",
i,
metaData.getColumnName(i),
metaData.getColumnTypeName(i),
metaData.isNullable(i) == ResultSetMetaData.columnNullable ? "是" : "否");
}
}
// 3. 可滚动结果集
System.out.println("\n3. 可滚动结果集:");
String scrollSQL = "SELECT username, age FROM users ORDER BY age";
try (Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(scrollSQL)) {
// 移动到最后一行
if (rs.last()) {
System.out.println(" 最后一行: " + rs.getString("username") + " (" + rs.getInt("age") + "岁)");
System.out.println(" 总行数: " + rs.getRow());
}
// 移动到第一行
if (rs.first()) {
System.out.println(" 第一行: " + rs.getString("username") + " (" + rs.getInt("age") + "岁)");
}
// 移动到中间
if (rs.absolute(3)) {
System.out.println(" 第3行: " + rs.getString("username") + " (" + rs.getInt("age") + "岁)");
}
}
// 4. 处理大结果集
System.out.println("\n4. 处理大结果集:");
String largeSQL = "SELECT * FROM users";
try (Statement stmt = conn.createStatement()) {
// 设置获取大小以优化内存使用
stmt.setFetchSize(10);
try (ResultSet rs = stmt.executeQuery(largeSQL)) {
int count = 0;
while (rs.next()) {
count++;
// 处理每一行...
}
System.out.println(" 处理了 " + count + " 行数据");
}
}
} catch (SQLException e) {
System.out.println("结果集处理失败: " + e.getMessage());
}
}
}
15.3 事务管理
15.3.1 事务基础
import java.sql.*;
import java.math.BigDecimal;
// 事务管理演示
public class TransactionDemo {
private static final String DB_URL = "jdbc:h2:mem:bankdb;DB_CLOSE_DELAY=-1";
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
public static void main(String[] args) {
// 初始化银行数据库
initializeBankDatabase();
// 基础事务操作
demonstrateBasicTransaction();
// 事务隔离级别
demonstrateTransactionIsolation();
// 保存点
demonstrateSavepoints();
// 分布式事务模拟
demonstrateDistributedTransaction();
}
// 初始化银行数据库
public static void initializeBankDatabase() {
System.out.println("=== 初始化银行数据库 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement()) {
// 创建账户表
String createAccountTable = """
CREATE TABLE IF NOT EXISTS accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
account_number VARCHAR(20) UNIQUE NOT NULL,
account_name VARCHAR(100) NOT NULL,
balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""";
stmt.executeUpdate(createAccountTable);
// 创建交易记录表
String createTransactionTable = """
CREATE TABLE IF NOT EXISTS transactions (
id INT PRIMARY KEY AUTO_INCREMENT,
from_account VARCHAR(20),
to_account VARCHAR(20),
amount DECIMAL(15,2) NOT NULL,
transaction_type VARCHAR(20) NOT NULL,
description VARCHAR(200),
transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'COMPLETED'
)
""";
stmt.executeUpdate(createTransactionTable);
// 插入初始账户数据
String[] insertAccounts = {
"INSERT INTO accounts (account_number, account_name, balance) VALUES ('ACC001', '张三', 10000.00)",
"INSERT INTO accounts (account_number, account_name, balance) VALUES ('ACC002', '李四', 5000.00)",
"INSERT INTO accounts (account_number, account_name, balance) VALUES ('ACC003', '王五', 8000.00)"
};
for (String sql : insertAccounts) {
stmt.executeUpdate(sql);
}
System.out.println("银行数据库初始化完成");
} catch (SQLException e) {
System.out.println("数据库初始化失败: " + e.getMessage());
}
}
// 基础事务操作
public static void demonstrateBasicTransaction() {
System.out.println("\n=== 基础事务操作 ===");
// 1. 成功的转账事务
System.out.println("1. 成功的转账事务:");
boolean success = transferMoney("ACC001", "ACC002", new BigDecimal("1000.00"));
System.out.println(" 转账结果: " + (success ? "成功" : "失败"));
// 显示账户余额
displayAccountBalances();
// 2. 失败的转账事务（余额不足）
System.out.println("\n2. 失败的转账事务（余额不足）:");
success = transferMoney("ACC002", "ACC001", new BigDecimal("10000.00"));
System.out.println(" 转账结果: " + (success ? "成功" : "失败"));
// 显示账户余额（应该没有变化）
displayAccountBalances();
// 3. 异常回滚演示
System.out.println("\n3. 异常回滚演示:");
success = transferMoneyWithException("ACC001", "ACC003", new BigDecimal("500.00"));
System.out.println(" 转账结果: " + (success ? "成功" : "失败"));
displayAccountBalances();
}
// 转账方法
private static boolean transferMoney(String fromAccount, String toAccount, BigDecimal amount) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 开始事务
conn.setAutoCommit(false);
try {
// 检查源账户余额
BigDecimal fromBalance = getAccountBalance(conn, fromAccount);
if (fromBalance.compareTo(amount) < 0) {
System.out.println(" 余额不足: 当前余额 " + fromBalance + ", 转账金额 " + amount);
conn.rollback();
return false;
}
// 从源账户扣款
String debitSQL = "UPDATE accounts SET balance = balance - ?, updated_at = CURRENT_TIMESTAMP WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(debitSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, fromAccount);
int debitResult = pstmt.executeUpdate();
if (debitResult != 1) {
throw new SQLException("扣款失败");
}
}
// 向目标账户入款
String creditSQL = "UPDATE accounts SET balance = balance + ?, updated_at = CURRENT_TIMESTAMP WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(creditSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, toAccount);
int creditResult = pstmt.executeUpdate();
if (creditResult != 1) {
throw new SQLException("入款失败");
}
}
// 记录交易
String insertTransactionSQL = "INSERT INTO transactions (from_account, to_account, amount, transaction_type, description) VALUES (?, ?, ?, 'TRANSFER', ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertTransactionSQL)) {
pstmt.setString(1, fromAccount);
pstmt.setString(2, toAccount);
pstmt.setBigDecimal(3, amount);
pstmt.setString(4, "从 " + fromAccount + " 转账到 " + toAccount);
pstmt.executeUpdate();
}
// 提交事务
conn.commit();
System.out.println(" 转账成功: " + fromAccount + " -> " + toAccount + ", 金额: " + amount);
return true;
} catch (SQLException e) {
// 回滚事务
conn.rollback();
System.out.println(" 转账失败，已回滚: " + e.getMessage());
return false;
} finally {
// 恢复自动提交
conn.setAutoCommit(true);
}
} catch (SQLException e) {
System.out.println(" 数据库连接失败: " + e.getMessage());
return false;
}
}
// 获取账户余额
private static BigDecimal getAccountBalance(Connection conn, String accountNumber) throws SQLException {
String sql = "SELECT balance FROM accounts WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, accountNumber);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return rs.getBigDecimal("balance");
} else {
throw new SQLException("账户不存在: " + accountNumber);
}
}
}
}
// 显示账户余额
private static void displayAccountBalances() {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT account_number, account_name, balance FROM accounts ORDER BY account_number")) {
System.out.println(" 当前账户余额:");
while (rs.next()) {
System.out.printf(" %s (%s): %.2f%n",
rs.getString("account_number"),
rs.getString("account_name"),
rs.getBigDecimal("balance"));
}
} catch (SQLException e) {
System.out.println(" 获取账户余额失败: " + e.getMessage());
}
}
// 带异常的转账方法（用于演示回滚）
private static boolean transferMoneyWithException(String fromAccount, String toAccount, BigDecimal amount) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
conn.setAutoCommit(false);
try {
// 扣款
String debitSQL = "UPDATE accounts SET balance = balance - ? WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(debitSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, fromAccount);
pstmt.executeUpdate();
}
System.out.println(" 已从 " + fromAccount + " 扣款 " + amount);
// 模拟异常
if (Math.random() > 0.5) {
throw new RuntimeException("模拟系统异常");
}
// 入款
String creditSQL = "UPDATE accounts SET balance = balance + ? WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(creditSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, toAccount);
pstmt.executeUpdate();
}
conn.commit();
System.out.println(" 转账完成");
return true;
} catch (Exception e) {
conn.rollback();
System.out.println(" 发生异常，事务已回滚: " + e.getMessage());
return false;
} finally {
conn.setAutoCommit(true);
}
} catch (SQLException e) {
System.out.println(" 数据库操作失败: " + e.getMessage());
return false;
}
}
// 事务隔离级别演示
public static void demonstrateTransactionIsolation() {
System.out.println("\n=== 事务隔离级别演示 ===");
// 演示不同隔离级别的效果
int[] isolationLevels = {
Connection.TRANSACTION_READ_UNCOMMITTED,
Connection.TRANSACTION_READ_COMMITTED,
Connection.TRANSACTION_REPEATABLE_READ,
Connection.TRANSACTION_SERIALIZABLE
};
String[] levelNames = {
"READ_UNCOMMITTED",
"READ_COMMITTED",
"REPEATABLE_READ",
"SERIALIZABLE"
};
for (int i = 0; i < isolationLevels.length; i++) {
System.out.println((i + 1) + ". " + levelNames[i] + ":");
demonstrateIsolationLevel(isolationLevels[i], levelNames[i]);
}
}
private static void demonstrateIsolationLevel(int isolationLevel, String levelName) {
try (Connection conn1 = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
Connection conn2 = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 设置隔离级别
conn1.setTransactionIsolation(isolationLevel);
conn2.setTransactionIsolation(isolationLevel);
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
try {
// 连接1读取初始值
BigDecimal initialBalance = getAccountBalance(conn1, "ACC001");
System.out.println(" 连接1读取初始余额: " + initialBalance);
// 连接2修改但不提交
String updateSQL = "UPDATE accounts SET balance = balance + 1000 WHERE account_number = 'ACC001'";
try (PreparedStatement pstmt = conn2.prepareStatement(updateSQL)) {
pstmt.executeUpdate();
System.out.println(" 连接2修改余额（未提交）");
}
// 连接1再次读取
BigDecimal secondRead = getAccountBalance(conn1, "ACC001");
System.out.println(" 连接1再次读取余额: " + secondRead);
// 连接2提交
conn2.commit();
System.out.println(" 连接2提交事务");
// 连接1第三次读取
BigDecimal thirdRead = getAccountBalance(conn1, "ACC001");
System.out.println(" 连接1第三次读取余额: " + thirdRead);
conn1.commit();
// 分析结果
if (initialBalance.equals(secondRead)) {
System.out.println(" " + levelName + " 防止了脏读");
} else {
System.out.println(" " + levelName + " 允许脏读");
}
if (secondRead.equals(thirdRead)) {
System.out.println(" " + levelName + " 防止了不可重复读");
} else {
System.out.println(" " + levelName + " 允许不可重复读");
}
} catch (SQLException e) {
conn1.rollback();
conn2.rollback();
System.out.println(" 隔离级别测试失败: " + e.getMessage());
} finally {
conn1.setAutoCommit(true);
conn2.setAutoCommit(true);
}
} catch (SQLException e) {
System.out.println(" 隔离级别演示失败: " + e.getMessage());
}
}
// 保存点演示
public static void demonstrateSavepoints() {
System.out.println("\n=== 保存点演示 ===");
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
conn.setAutoCommit(false);
try {
// 显示初始状态
System.out.println("初始账户状态:");
displayAccountBalances();
// 第一步操作
System.out.println("\n执行第一步操作: ACC001 -> ACC002, 500元");
String step1SQL = "UPDATE accounts SET balance = balance - 500 WHERE account_number = 'ACC001'";
try (PreparedStatement pstmt = conn.prepareStatement(step1SQL)) {
pstmt.executeUpdate();
}
String step1SQL2 = "UPDATE accounts SET balance = balance + 500 WHERE account_number = 'ACC002'";
try (PreparedStatement pstmt = conn.prepareStatement(step1SQL2)) {
pstmt.executeUpdate();
}
// 创建保存点1
Savepoint savepoint1 = conn.setSavepoint("step1_completed");
System.out.println("创建保存点1: step1_completed");
// 第二步操作
System.out.println("\n执行第二步操作: ACC002 -> ACC003, 300元");
String step2SQL = "UPDATE accounts SET balance = balance - 300 WHERE account_number = 'ACC002'";
try (PreparedStatement pstmt = conn.prepareStatement(step2SQL)) {
pstmt.executeUpdate();
}
String step2SQL2 = "UPDATE accounts SET balance = balance + 300 WHERE account_number = 'ACC003'";
try (PreparedStatement pstmt = conn.prepareStatement(step2SQL2)) {
pstmt.executeUpdate();
}
// 创建保存点2
Savepoint savepoint2 = conn.setSavepoint("step2_completed");
System.out.println("创建保存点2: step2_completed");
// 第三步操作（模拟失败）
System.out.println("\n执行第三步操作: ACC003 -> ACC001, 1000元（将失败）");
try {
// 检查余额
BigDecimal balance = getAccountBalance(conn, "ACC003");
if (balance.compareTo(new BigDecimal("1000")) < 0) {
throw new SQLException("余额不足，第三步操作失败");
}
// 这里不会执行到
String step3SQL = "UPDATE accounts SET balance = balance - 1000 WHERE account_number = 'ACC003'";
try (PreparedStatement pstmt = conn.prepareStatement(step3SQL)) {
pstmt.executeUpdate();
}
} catch (SQLException e) {
System.out.println("第三步操作失败: " + e.getMessage());
System.out.println("回滚到保存点2");
// 回滚到保存点2
conn.rollback(savepoint2);
}
// 显示回滚后的状态
System.out.println("\n回滚到保存点2后的状态:");
displayAccountBalancesInTransaction(conn);
// 决定是否提交
System.out.println("\n提交前两步操作");
conn.commit();
System.out.println("\n最终状态:");
displayAccountBalances();
} catch (SQLException e) {
System.out.println("保存点操作失败: " + e.getMessage());
conn.rollback();
} finally {
conn.setAutoCommit(true);
}
} catch (SQLException e) {
System.out.println("保存点演示失败: " + e.getMessage());
}
}
// 在事务中显示账户余额
private static void displayAccountBalancesInTransaction(Connection conn) {
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT account_number, account_name, balance FROM accounts ORDER BY account_number")) {
while (rs.next()) {
System.out.printf(" %s (%s): %.2f%n",
rs.getString("account_number"),
rs.getString("account_name"),
rs.getBigDecimal("balance"));
}
} catch (SQLException e) {
System.out.println(" 获取账户余额失败: " + e.getMessage());
}
}
// 分布式事务模拟
public static void demonstrateDistributedTransaction() {
System.out.println("\n=== 分布式事务模拟 ===");
// 模拟两个不同的数据库
String db1Url = "jdbc:h2:mem:bank1;DB_CLOSE_DELAY=-1";
String db2Url = "jdbc:h2:mem:bank2;DB_CLOSE_DELAY=-1";
// 初始化两个数据库
initializeDistributedDatabases(db1Url, db2Url);
// 执行分布式转账
boolean success = distributedTransfer(db1Url, db2Url, "ACC001", "ACC002", new BigDecimal("1000.00"));
System.out.println("分布式转账结果: " + (success ? "成功" : "失败"));
// 显示两个数据库的状态
displayDistributedState(db1Url, db2Url);
}
private static void initializeDistributedDatabases(String db1Url, String db2Url) {
// 初始化数据库1
try (Connection conn = DriverManager.getConnection(db1Url, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement()) {
stmt.executeUpdate("CREATE TABLE accounts (account_number VARCHAR(20) PRIMARY KEY, balance DECIMAL(15,2))");
stmt.executeUpdate("INSERT INTO accounts VALUES ('ACC001', 5000.00)");
} catch (SQLException e) {
System.out.println("初始化数据库1失败: " + e.getMessage());
}
// 初始化数据库2
try (Connection conn = DriverManager.getConnection(db2Url, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement()) {
stmt.executeUpdate("CREATE TABLE accounts (account_number VARCHAR(20) PRIMARY KEY, balance DECIMAL(15,2))");
stmt.executeUpdate("INSERT INTO accounts VALUES ('ACC002', 3000.00)");
} catch (SQLException e) {
System.out.println("初始化数据库2失败: " + e.getMessage());
}
}
private static boolean distributedTransfer(String db1Url, String db2Url,
String fromAccount, String toAccount, BigDecimal amount) {
Connection conn1 = null;
Connection conn2 = null;
try {
conn1 = DriverManager.getConnection(db1Url, DB_USER, DB_PASSWORD);
conn2 = DriverManager.getConnection(db2Url, DB_USER, DB_PASSWORD);
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
// 阶段1：准备阶段
System.out.println("分布式事务 - 准备阶段:");
// 检查数据库1中的账户余额
BigDecimal balance1 = getDistributedAccountBalance(conn1, fromAccount);
if (balance1.compareTo(amount) < 0) {
System.out.println(" 数据库1余额不足: " + balance1);
return false;
}
// 检查数据库2中的账户是否存在
BigDecimal balance2 = getDistributedAccountBalance(conn2, toAccount);
System.out.println(" 数据库1余额: " + balance1 + ", 数据库2余额: " + balance2);
// 阶段2：执行阶段
System.out.println("分布式事务 - 执行阶段:");
// 在数据库1中扣款
String debitSQL = "UPDATE accounts SET balance = balance - ? WHERE account_number = ?";
try (PreparedStatement pstmt = conn1.prepareStatement(debitSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, fromAccount);
int result = pstmt.executeUpdate();
if (result != 1) {
throw new SQLException("数据库1扣款失败");
}
System.out.println(" 数据库1扣款成功");
}
// 在数据库2中入款
String creditSQL = "UPDATE accounts SET balance = balance + ? WHERE account_number = ?";
try (PreparedStatement pstmt = conn2.prepareStatement(creditSQL)) {
pstmt.setBigDecimal(1, amount);
pstmt.setString(2, toAccount);
int result = pstmt.executeUpdate();
if (result != 1) {
throw new SQLException("数据库2入款失败");
}
System.out.println(" 数据库2入款成功");
}
// 阶段3：提交阶段
System.out.println("分布式事务 - 提交阶段:");
conn1.commit();
System.out.println(" 数据库1提交成功");
conn2.commit();
System.out.println(" 数据库2提交成功");
return true;
} catch (SQLException e) {
System.out.println("分布式事务失败: " + e.getMessage());
// 回滚所有数据库
try {
if (conn1 != null) {
conn1.rollback();
System.out.println(" 数据库1已回滚");
}
} catch (SQLException ex) {
System.out.println(" 数据库1回滚失败: " + ex.getMessage());
}
try {
if (conn2 != null) {
conn2.rollback();
System.out.println(" 数据库2已回滚");
}
} catch (SQLException ex) {
System.out.println(" 数据库2回滚失败: " + ex.getMessage());
}
return false;
} finally {
// 恢复自动提交并关闭连接
try {
if (conn1 != null) {
conn1.setAutoCommit(true);
conn1.close();
}
} catch (SQLException e) {
// 忽略关闭异常
}
try {
if (conn2 != null) {
conn2.setAutoCommit(true);
conn2.close();
}
} catch (SQLException e) {
// 忽略关闭异常
}
}
}
private static BigDecimal getDistributedAccountBalance(Connection conn, String accountNumber) throws SQLException {
String sql = "SELECT balance FROM accounts WHERE account_number = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, accountNumber);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return rs.getBigDecimal("balance");
} else {
throw new SQLException("账户不存在: " + accountNumber);
}
}
}
}
private static void displayDistributedState(String db1Url, String db2Url) {
System.out.println("\n分布式数据库状态:");
System.out.println("数据库1:");
try (Connection conn = DriverManager.getConnection(db1Url, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM accounts")) {
while (rs.next()) {
System.out.printf(" %s: %.2f%n",
rs.getString("account_number"),
rs.getBigDecimal("balance"));
}
} catch (SQLException e) {
System.out.println(" 获取数据库1状态失败: " + e.getMessage());
}
System.out.println("数据库2:");
try (Connection conn = DriverManager.getConnection(db2Url, DB_USER, DB_PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM accounts")) {
while (rs.next()) {
System.out.printf(" %s: %.2f%n",
rs.getString("account_number"),
rs.getBigDecimal("balance"));
}
} catch (SQLException e) {
System.out.println(" 获取数据库2状态失败: " + e.getMessage());
}
}
}
15.4 连接池技术
15.4.1 连接池原理与实现
”`java import java.sql.; import java.util.concurrent.; import java.util.concurrent.atomic.AtomicInteger; import javax.sql.DataSource; import java.io.PrintWriter; import java.util.logging.Logger;
// 高级连接池实现 public class AdvancedConnectionPoolDemo {
public static void main(String[] args) {
// 连接池配置演示
demonstrateConnectionPoolConfiguration();
// 连接池性能测试
demonstrateConnectionPoolPerformance();
// 连接池监控
demonstrateConnectionPoolMonitoring();
// 第三方连接池
demonstrateThirdPartyConnectionPools();
}
// 连接池配置演示
public static void demonstrateConnectionPoolConfiguration() {
System.out.println("=== 连接池配置演示 ===");
// 创建配置
ConnectionPoolConfig config = new ConnectionPoolConfig()
.setUrl("jdbc:h2:mem:pooltest;DB_CLOSE_DELAY=-1")
.setUsername("sa")
.setPassword("")
.setMinPoolSize(5)
.setMaxPoolSize(20)
.setMaxIdleTime(300000) // 5分钟
.setConnectionTimeout(30000) // 30秒
.setValidationQuery("SELECT 1")
.setTestOnBorrow(true)
.setTestOnReturn(false)
.setTestWhileIdle(true);
// 创建连接池
AdvancedConnectionPool pool = new AdvancedConnectionPool(config);
try {
// 初始化连接池
pool.initialize();
System.out.println("连接池配置:");
System.out.println(" 最小连接数: " + config.getMinPoolSize());
System.out.println(" 最大连接数: " + config.getMaxPoolSize());
System.out.println(" 连接超时: " + config.getConnectionTimeout() + "ms");
System.out.println(" 最大空闲时间: " + config.getMaxIdleTime() + "ms");
// 获取连接池状态
ConnectionPoolStats stats = pool.getStats();
System.out.println("\n连接池状态:");
System.out.println(" 活跃连接: " + stats.getActiveConnections());
System.out.println(" 空闲连接: " + stats.getIdleConnections());
System.out.println(" 总连接数: " + stats.getTotalConnections());
// 测试连接获取
System.out.println("\n测试连接获取:");
Connection conn1 = pool.getConnection();
Connection conn2 = pool.getConnection();
System.out.println(" 获取2个连接后:");
stats = pool.getStats();
System.out.println(" 活跃连接: " + stats.getActiveConnections());
System.out.println(" 空闲连接: " + stats.getIdleConnections());
// 归还连接
conn1.close(); // 实际上是归还到池中
conn2.close();
System.out.println(" 归还连接后:");
stats = pool.getStats();
System.out.println(" 活跃连接: " + stats.getActiveConnections());
System.out.println(" 空闲连接: " + stats.getIdleConnections());
} catch (SQLException e) {
System.out.println("连接池操作失败: " + e.getMessage());
} finally {
pool.close();
}
}
