四、ORM 框架
4.1 MyBatis 配置与使用
xml
<!-- application.yml -->
mybatis:
mapper-locations: classpath:mapper/**/*.xml
type-aliases-package: com.example.entity
configuration:
map-underscore-to-camel-case: true
cache-enabled: true
lazy-loading-enabled: true
log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
java
// Mapper 接口
@Mapper
public interface UserMapper {
@Select("SELECT * FROM users WHERE id = #{id}")
User findById(Long id);
@Insert("INSERT INTO users(username, age) VALUES(#{username}, #{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(User user);
@Update("UPDATE users SET username = #{username}, age = #{age} WHERE id = #{id}")
int update(User user);
@Delete("DELETE FROM users WHERE id = #{id}")
int deleteById(Long id);
// 动态 SQL
@SelectProvider(type = UserSqlProvider.class, method = "findByCondition")
List<User> findByCondition(Map<String, Object> params);
}
// SQL Provider
public class UserSqlProvider {
public String findByCondition(Map<String, Object> params) {
return new SQL() {
{
SELECT("*");
FROM("users");
if (params.get("username") != null) {
WHERE("username = #{username}");
}
if (params.get("minAge") != null) {
WHERE("age >= #{minAge}");
}
if (params.get("maxAge") != null) {
WHERE("age <= #{maxAge}");
}
}}.toString();
}
}
4.2 JPA/Hibernate 配置
yaml
spring:
jpa:
hibernate:
ddl-auto: update
show-sql: true
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL8Dialect
format_sql: true
jdbc:
batch_size: 20
order_inserts: true
order_updates: true
java
@Entity
@Table(name = "users")
@Data
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true)
private String username;
private Integer age;
@CreationTimestamp
private LocalDateTime createTime;
@UpdateTimestamp
private LocalDateTime updateTime;
}
// Repository
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
Optional<User> findByUsername(String username);
List<User> findByAgeBetween(int minAge, int maxAge);
@Query("SELECT u FROM User u WHERE u.username LIKE %:keyword%")
List<User> searchByUsername(@Param("keyword") String keyword);
@Modifying
@Transactional
@Query("UPDATE User u SET u.age = :age WHERE u.id = :id")
int updateAge(@Param("id") Long id, @Param("age") int age);
}
五、连接管理最佳实践
5.1 连接参数优化
properties
# MySQL 连接参数
jdbc:mysql://localhost:3306/mydb?
useSSL=false& # 禁用 SSL（开发环境）
serverTimezone=UTC& # 时区
useUnicode=true& # 使用 Unicode
characterEncoding=UTF-8& # 字符编码
allowPublicKeyRetrieval=true& # 允许公钥检索
connectTimeout=30000& # 连接超时（毫秒）
socketTimeout=60000& # Socket 超时
autoReconnect=true& # 自动重连
maxReconnects=3& # 最大重连次数
rewriteBatchedStatements=true& # 重写批处理语句
cachePrepStmts=true& # 缓存 PreparedStatement
prepStmtCacheSize=250& # 缓存大小
prepStmtCacheSqlLimit=2048& # SQL 缓存限制
useServerPrepStmts=true # 使用服务器端预编译
# PostgreSQL 连接参数
jdbc:postgresql://localhost:5432/mydb?
ssl=false&
connectTimeout=30&
socketTimeout=60&
tcpKeepAlive=true&
prepareThreshold=5&
preparedStatementCacheQueries=256&
preparedStatementCacheSizeMiB=5
# Oracle 连接参数
jdbc:oracle:thin:@localhost:1521:orcl?
oracle.net.CONNECT_TIMEOUT=30000&
oracle.jdbc.ReadTimeout=60000&
defaultNChar=true
5.2 连接池最佳实践
java
@Configuration
public class ConnectionPoolBestPractice {
@Bean
public DataSource optimalDataSource() {
HikariConfig config = new HikariConfig();
// 1. 合理设置连接池大小
// 公式：连接数 = ((核心数 * 2) + 有效磁盘数)
// 通常建议：10-20 个连接足够大多数应用
int cpuCores = Runtime.getRuntime().availableProcessors();
int optimalPoolSize = Math.max(5, cpuCores * 2);
config.setMaximumPoolSize(optimalPoolSize);
config.setMinimumIdle(Math.max(2, cpuCores));
// 2. 设置合理的超时时间
config.setConnectionTimeout(30000); // 30秒获取连接超时
config.setIdleTimeout(600000); // 10分钟空闲超时
config.setMaxLifetime(1800000); // 30分钟连接生命周期
// 3. 启用连接验证
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(5000);
// 4. 设置连接池名称便于监控
config.setPoolName("ProductionPool");
// 5. 启用 JMX 监控
config.setRegisterMbeans(true);
// 6. 记录慢连接获取
config.setLeakDetectionThreshold(60000); // 60秒未释放视为泄漏
return new HikariDataSource(config);
}
}
5.3 连接泄漏检测
java
@Component
public class ConnectionLeakDetector {
private static final Logger log = LoggerFactory.getLogger(ConnectionLeakDetector.class);
@Autowired
private DataSource dataSource;
// 监控连接使用情况（HikariCP 自带）
public void checkLeak() {
if (dataSource instanceof HikariDataSource) {
HikariPoolMXBean poolMXBean = ((HikariDataSource) dataSource).getHikariPoolMXBean();
log.info("Active connections: {}", poolMXBean.getActiveConnections());
log.info("Idle connections: {}", poolMXBean.getIdleConnections());
log.info("Total connections: {}", poolMXBean.getTotalConnections());
log.info("Threads waiting: {}", poolMXBean.getThreadsAwaitingConnection());
}
}
// 定期检查
@Scheduled(fixedDelay = 60000)
public void monitor() {
checkLeak();
}
}
六、事务管理
6.1 JDBC 事务
public class TransactionDemo {
public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
// 扣款
String deductSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
try (PreparedStatement pstmt = conn.prepareStatement(deductSql)) {
pstmt.setBigDecimal(1, amount);
pstmt.setLong(2, fromId);
pstmt.setBigDecimal(3, amount);
int rows = pstmt.executeUpdate();
if (rows == 0) {
throw new SQLException("余额不足");
}
}
// 加款
String addSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(addSql)) {
pstmt.setBigDecimal(1, amount);
pstmt.setLong(2, toId);
pstmt.executeUpdate();
}
// 提交事务
conn.commit();
} catch (SQLException e) {
// 回滚事务
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
throw new RuntimeException("转账失败", e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
// 使用保存点
public void complexOperation() throws SQLException {
Connection conn = null;
Savepoint savepoint = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
conn.setAutoCommit(false);
// 操作1
// ...
// 设置保存点
savepoint = conn.setSavepoint("afterStep1");
// 操作2（可能失败）
// ...
conn.commit();
} catch (SQLException e) {
if (savepoint != null) {
// 回滚到保存点
conn.rollback(savepoint);
// 执行其他操作
// ...
conn.commit();
} else {
conn.rollback();
}
throw e;
} finally {
if (conn != null) conn.close();
}
}
}
6.2 Spring 声明式事务
@Service
@Transactional
public class UserService {
@Autowired
private UserRepository userRepository;
@Autowired
private LogRepository logRepository;
// 默认事务传播行为 REQUIRED
public void createUser(User user) {
userRepository.save(user);
logRepository.save(new Log("创建用户: " + user.getUsername()));
if (user.getAge() < 0) {
throw new IllegalArgumentException("年龄无效");
}
}
// 只读事务（性能优化）
@Transactional(readOnly = true)
public User findUser(Long id) {
return userRepository.findById(id).orElse(null);
}
// 指定回滚异常
@Transactional(rollbackFor = {SQLException.class}, noRollbackFor = IllegalArgumentException.class)
public void updateUser(User user) throws SQLException {
userRepository.save(user);
if (user.getAge() > 150) {
throw new IllegalArgumentException("年龄无效"); // 不回滚
}
if (user.getAge() < 0) {
throw new SQLException("年龄无效"); // 回滚
}
}
// 事务传播行为
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void createLog(String message) {
logRepository.save(new Log(message));
}
// 事务超时
@Transactional(timeout = 30)
public void batchInsert(List<User> users) {
for (User user : users) {
userRepository.save(user);
}
}
// 隔离级别
@Transactional(isolation = Isolation.REPEATABLE_READ)
public User getUserWithLock(Long id) {
return userRepository.findByIdForUpdate(id).orElse(null);
}
}
6.3 事务传播行为
@Service
public class TransactionPropagationDemo {
// REQUIRED：支持当前事务，如果没有则新建（默认）
@Transactional(propagation = Propagation.REQUIRED)
public void required() {}
// SUPPORTS：支持当前事务，如果没有则以非事务方式执行
@Transactional(propagation = Propagation.SUPPORTS)
public void supports() {}
// MANDATORY：支持当前事务，如果没有则抛出异常
@Transactional(propagation = Propagation.MANDATORY)
public void mandatory() {}
// REQUIRES_NEW：新建事务，挂起当前事务
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void requiresNew() {}
// NOT_SUPPORTED：以非事务方式执行，挂起当前事务
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void notSupported() {}
// NEVER：以非事务方式执行，如果有事务则抛出异常
@Transactional(propagation = Propagation.NEVER)
public void never() {}
// NESTED：如果当前存在事务，则在嵌套事务内执行
@Transactional(propagation = Propagation.NESTED)
public void nested() {}
}
