SpringBoot集成电科金仓数据库（下）：事务管理与高级应用深度解析
引言
在上一篇文章中，我们系统性地探讨了SpringBoot与Kingbase数据库的基础集成，涵盖了环境搭建、依赖配置、数据访问层设计等核心内容。本篇将在此基础上深入挖掘高级特性和最佳实践，重点解析事务管理机制、连接池优化策略、异常处理体系以及复杂查询场景的实现方案。
事务管理作为企业级应用的核心需求，直接关系到数据一致性和系统可靠性；连接池的合理配置对系统性能有着决定性影响；而完善的异常处理机制则是保障系统稳定性的重要屏障。本文将结合Kingbase数据库的特性和SpringBoot框架的优势，为开发者提供一套完整的高级应用解决方案。
一、事务管理深度解析
1.1 Spring事务管理机制
Spring框架提供了强大而灵活的事务管理抽象，主要支持两种事务管理方式：
编程式事务管理：通过TransactionTemplate或PlatformTransactionManager直接控制事务边界 声明式事务管理：通过@Transactional注解以声明的方式定义事务行为
1.2 声明式事务实现详解
以下是一个完整的Service层实现，展示了事务管理的最佳实践：
package com.example.service;
import com.example.dao.UserDao;
import com.example.entity.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
/**
* 用户服务实现类
* 演示声明式事务管理的各种用法
*/
@Service
public class UserService {
private static final Logger logger = LoggerFactory.getLogger(UserService.class);
private final UserDao userDao;
/**
* 构造函数注入，推荐使用构造器注入而非字段注入
*/
@Autowired
public UserService(UserDao userDao) {
this.userDao = userDao;
}
/**
* 基本事务示例：更新用户名
* 使用默认的事务隔离级别和传播行为
*/
@Transactional
public void updateUserName(Long id, String newName) {
logger.info("开始更新用户ID: {} 的用户名为: {}", id, newName);
User user = userDao.selectUserById(id);
if (user == null) {
logger.warn("用户ID: {} 不存在，跳过更新", id);
throw new IllegalArgumentException("用户不存在");
}
String oldName = user.getName();
user.setName(newName);
userDao.updateUser(user);
logger.info("用户ID: {} 用户名从 '{}' 更新为 '{}'", id, oldName, newName);
}
/**
* 批量插入用户 - 演示事务的原子性
* 如果任何一条记录插入失败，整个操作将回滚
*/
@Transactional(
propagation = Propagation.REQUIRED,
isolation = Isolation.DEFAULT,
rollbackFor = Exception.class,
timeout = 30
)
public void batchInsertUsers(List<User> users) {
if (users == null || users.isEmpty()) {
logger.warn("批量插入的用户列表为空");
return;
}
logger.info("开始批量插入 {} 个用户", users.size());
for (int i = 0; i < users.size(); i++) {
User user = users.get(i);
try {
userDao.insertUser(user);
logger.debug("成功插入第 {} 个用户: {}", i + 1, user.getName());
} catch (Exception e) {
logger.error("插入第 {} 个用户失败: {}", i + 1, user.getName(), e);
throw new RuntimeException("批量插入失败", e);
}
}
logger.info("批量插入完成，共插入 {} 个用户", users.size());
}
/**
* 复杂业务操作：转账示例
* 演示多个数据库操作在一个事务中
*/
@Transactional
public void transferBalance(Long fromUserId, Long toUserId, Double amount) {
if (amount <= 0) {
throw new IllegalArgumentException("转账金额必须大于0");
}
// 检查用户是否存在
User fromUser = userDao.selectUserById(fromUserId);
User toUser = userDao.selectUserById(toUserId);
if (fromUser == null || toUser == null) {
throw new IllegalArgumentException("用户不存在");
}
// 检查余额是否足够（这里假设User实体有balance字段）
// 实际项目中需要相应的字段和逻辑
logger.info("用户 {} 向用户 {} 转账金额 {}", fromUserId, toUserId, amount);
// 执行转账操作
// debit(fromUserId, amount);
// credit(toUserId, amount);
logger.info("转账操作完成");
}
/**
* 只读事务示例
* 优化查询性能，避免不必要的事务开销
*/
@Transactional(readOnly = true)
public List<User> getAllUsersWithReadOnly() {
logger.debug("执行只读查询操作");
return userDao.selectAllUsers();
}
/**
* 嵌套事务示例
* 使用PROPAGATION_NESTED传播行为
*/
@Transactional(propagation = Propagation.NESTED)
public void nestedOperation(User user) {
logger.info("执行嵌套事务操作");
userDao.insertUser(user);
// 嵌套事务中的异常只会回滚当前嵌套的操作，不会影响外部事务
}
}
1.3 事务隔离级别详解
不同的业务场景需要不同的事务隔离级别：
/**
* 事务隔离级别配置示例
*/
@Service
public class TransactionIsolationService {
@Autowired
private UserDao userDao;
/**
* 读未提交 - 最低的隔离级别，可能发生脏读
*/
@Transactional(isolation = Isolation.READ_UNCOMMITTED)
public User readUncommittedExample(Long id) {
return userDao.selectUserById(id);
}
/**
* 读已提交 - 防止脏读，但可能发生不可重复读
*/
@Transactional(isolation = Isolation.READ_COMMITTED)
public User readCommittedExample(Long id) {
return userDao.selectUserById(id);
}
/**
* 可重复读 - 防止脏读和不可重复读
*/
@Transactional(isolation = Isolation.REPEATABLE_READ)
public User repeatableReadExample(Long id) {
return userDao.selectUserById(id);
}
/**
* 序列化 - 最高隔离级别，完全串行化执行
*/
@Transactional(isolation = Isolation.SERIALIZABLE)
public User serializableExample(Long id) {
return userDao.selectUserById(id);
}
}
1.4 事务传播行为详解
/**
* 事务传播行为示例
*/
@Service
public class TransactionPropagationService {
@Autowired
private UserDao userDao;
/**
* REQUIRED - 默认传播行为，如果当前存在事务，则加入该事务
*/
@Transactional(propagation = Propagation.REQUIRED)
public void requiredExample(User user) {
userDao.insertUser(user);
}
/**
* REQUIRES_NEW - 总是新建一个事务，暂停当前存在的事务
*/
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void requiresNewExample(User user) {
userDao.insertUser(user);
}
/**
* NESTED - 嵌套事务，Savepoint机制
*/
@Transactional(propagation = Propagation.NESTED)
public void nestedExample(User user) {
userDao.insertUser(user);
}
/**
* MANDATORY - 必须在一个已有的事务中执行，否则抛出异常
*/
@Transactional(propagation = Propagation.MANDATORY)
public void mandatoryExample(User user) {
userDao.insertUser(user);
}
/**
* NEVER - 必须不在事务中执行，否则抛出异常
*/
@Transactional(propagation = Propagation.NEVER)
public List<User> neverExample() {
return userDao.selectAllUsers();
}
}
二、连接池深度优化
2.1 HikariCP配置详解
HikariCP是SpringBoot默认的连接池实现，以下是完整的配置说明：
spring:
datasource:
# 基本连接配置
driver-class-name: com.kingbase8.Driver
url: jdbc:kingbase8://localhost:54321/test?charSet=UTF-8
username: system
password: your_secure_password_here
# HikariCP连接池配置
hikari:
# 连接池名称
pool-name: KingbaseHikariCP
# 连接池大小配置
maximum-pool-size: 20
minimum-idle: 5
# 超时配置
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
validation-timeout: 5000
# 连接测试配置
connection-test-query: SELECT 1
leak-detection-threshold: 60000
# 其他优化配置
auto-commit: true
read-only: false
initialization-fail-timeout: 1
isolate-internal-queries: false
allow-pool-suspension: false
# 连接属性
data-source-properties:
cachePrepStmts: true
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
useServerPrepStmts: true
useLocalSessionState: true
rewriteBatchedStatements: true
cacheResultSetMetadata: true
cacheServerConfiguration: true
elideSetAutoCommits: true
maintainTimeStats: false
# 监控相关配置
management:
endpoints:
web:
exposure:
include: health,info,metrics,prometheus
metrics:
tags:
application: kingbase-demo
endpoint:
health:
show-details: always
db:
enabled: true
2.2 连接池监控与调优
通过Spring Boot Actuator监控连接池状态：
/**
* 连接池监控服务
*/
@Service
public class ConnectionPoolMonitorService {
@Autowired
private DataSource dataSource;
/**
* 获取连接池状态信息
*/
public Map<String, Object> getPoolStatus() {
Map<String, Object> status = new HashMap<>();
if (dataSource instanceof HikariDataSource) {
HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
HikariPoolMXBean poolMXBean = hikariDataSource.getHikariPoolMXBean();
status.put("activeConnections", poolMXBean.getActiveConnections());
status.put("idleConnections", poolMXBean.getIdleConnections());
status.put("threadsAwaitingConnection", poolMXBean.getThreadsAwaitingConnection());
status.put("totalConnections", poolMXBean.getTotalConnections());
status.put("connectionTimeout", hikariDataSource.getConnectionTimeout());
status.put("idleTimeout", hikariDataSource.getIdleTimeout());
status.put("maxLifetime", hikariDataSource.getMaxLifetime());
status.put("maximumPoolSize", hikariDataSource.getMaximumPoolSize());
status.put("minimumIdle", hikariDataSource.getMinimumIdle());
}
return status;
}
/**
* 动态调整连接池配置
*/
public void adjustPoolConfiguration(int maxPoolSize, int minIdle) {
if (dataSource instanceof HikariDataSource) {
HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
hikariDataSource.setMaximumPoolSize(maxPoolSize);
hikariDataSource.setMinimumIdle(minIdle);
}
}
}
2.3 多数据源配置
在实际项目中，可能需要配置多个数据源：
/**
* 多数据源配置类
*/
@Configuration
public class MultipleDataSourceConfig {
@Bean
@Primary
@ConfigurationProperties("spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
@ConfigurationProperties("spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
对应的配置文件：
spring:
datasource:
primary:
driver-class-name: com.kingbase8.Driver
url: jdbc:kingbase8://primary-host:54321/primary_db
username: primary_user
password: primary_password
hikari:
maximum-pool-size: 15
minimum-idle: 3
secondary:
driver-class-name: com.kingbase8.Driver
url: jdbc:kingbase8://secondary-host:54321/secondary_db
username: secondary_user
password: secondary_password
hikari:
maximum-pool-size: 10
minimum-idle: 2
三、异常处理体系构建
3.1 全局异常处理机制
package com.example.handler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import javax.servlet.http.HttpServletRequest;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
/**
* 全局异常处理器
* 统一处理各种类型的异常，提供友好的错误响应
*/
@RestControllerAdvice
public class GlobalExceptionHandler {
private static final Logger logger = LoggerFactory.getLogger(GlobalExceptionHandler.class);
/**
* 数据库访问异常处理
*/
@ExceptionHandler(DataAccessException.class)
public ResponseEntity<Map<String, Object>> handleDataAccessException(
DataAccessException e, HttpServletRequest request) {
logger.error("数据库访问异常: {}", e.getMessage(), e);
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.INTERNAL_SERVER_ERROR,
"数据库操作失败",
e.getMessage(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(errorResponse);
}
/**
* 空结果异常处理
*/
@ExceptionHandler(EmptyResultDataAccessException.class)
public ResponseEntity<Map<String, Object>> handleEmptyResultDataAccessException(
EmptyResultDataAccessException e, HttpServletRequest request) {
logger.warn("查询结果为空: {}", e.getMessage());
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.NOT_FOUND,
"请求的资源不存在",
e.getMessage(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.NOT_FOUND).body(errorResponse);
}
/**
* 重复键异常处理
*/
@ExceptionHandler(DuplicateKeyException.class)
public ResponseEntity<Map<String, Object>> handleDuplicateKeyException(
DuplicateKeyException e, HttpServletRequest request) {
logger.warn("数据重复异常: {}", e.getMessage());
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.CONFLICT,
"数据已存在，请勿重复添加",
e.getMessage(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.CONFLICT).body(errorResponse);
}
/**
* SQL语法异常处理
*/
@ExceptionHandler(BadSqlGrammarException.class)
public ResponseEntity<Map<String, Object>> handleBadSqlGrammarException(
BadSqlGrammarException e, HttpServletRequest request) {
logger.error("SQL语法错误: {}", e.getMessage(), e);
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.INTERNAL_SERVER_ERROR,
"系统内部错误，请联系管理员",
"SQL执行错误",
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(errorResponse);
}
/**
* 数据完整性异常处理
*/
@ExceptionHandler(DataIntegrityViolationException.class)
public ResponseEntity<Map<String, Object>> handleDataIntegrityViolationException(
DataIntegrityViolationException e, HttpServletRequest request) {
logger.error("数据完整性 violation: {}", e.getMessage(), e);
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.BAD_REQUEST,
"数据完整性约束 violation",
e.getMessage(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(errorResponse);
}
/**
* 通用异常处理
*/
@ExceptionHandler(Exception.class)
public ResponseEntity<Map<String, Object>> handleGenericException(
Exception e, HttpServletRequest request) {
logger.error("未处理的异常: {}", e.getMessage(), e);
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.INTERNAL_SERVER_ERROR,
"服务器内部错误",
e.getMessage(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(errorResponse);
}
/**
* 构建统一的错误响应格式
*/
private Map<String, Object> buildErrorResponse(
HttpStatus status, String message, String detail, String path) {
Map<String, Object> errorResponse = new HashMap<>();
errorResponse.put("timestamp", LocalDateTime.now());
errorResponse.put("status", status.value());
errorResponse.put("error", status.getReasonPhrase());
errorResponse.put("message", message);
errorResponse.put("detail", detail);
errorResponse.put("path", path);
return errorResponse;
}
}
3.2 自定义异常体系
/**
* 自定义业务异常基类
*/
public class BusinessException extends RuntimeException {
private final String errorCode;
private final String errorMessage;
public BusinessException(String errorCode, String errorMessage) {
super(errorMessage);
this.errorCode = errorCode;
this.errorMessage = errorMessage;
}
public BusinessException(String errorCode, String errorMessage, Throwable cause) {
super(errorMessage, cause);
this.errorCode = errorCode;
this.errorMessage = errorMessage;
}
// Getter方法
public String getErrorCode() { return errorCode; }
public String getErrorMessage() { return errorMessage; }
}
/**
* 数据不存在异常
*/
public class DataNotFoundException extends BusinessException {
public DataNotFoundException(String message) {
super("DATA_NOT_FOUND", message);
}
}
/**
* 数据验证异常
*/
public class ValidationException extends BusinessException {
public ValidationException(String message) {
super("VALIDATION_ERROR", message);
}
}
/**
* 自定义异常处理器
*/
@ExceptionHandler(BusinessException.class)
public ResponseEntity<Map<String, Object>> handleBusinessException(
BusinessException e, HttpServletRequest request) {
logger.warn("业务异常: {}", e.getErrorMessage());
Map<String, Object> errorResponse = buildErrorResponse(
HttpStatus.BAD_REQUEST,
e.getErrorMessage(),
e.getErrorCode(),
request.getRequestURI()
);
return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(errorResponse);
}
四、复杂查询与高级功能
4.1 高级查询实现
/**
* 高级查询服务
*/
@Repository
public class AdvancedQueryService {
private final JdbcTemplate jdbcTemplate;
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
public AdvancedQueryService(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
}
/**
* 分页查询实现
*/
public Page<User> selectUsersByPage(int pageNum, int pageSize, String sortBy, String sortDir) {
// 验证参数
if (pageNum < 1) pageNum = 1;
if (pageSize < 1) pageSize = 10;
// 计算偏移量
int offset = (pageNum - 1) * pageSize;
// 构建排序条件
String orderBy = String.format("%s %s", sortBy, sortDir.toUpperCase());
// 查询数据
String dataSql = String.format(
"SELECT * FROM sys_user ORDER BY %s LIMIT ? OFFSET ?",
orderBy
);
List<User> users = jdbcTemplate.query(
dataSql,
new BeanPropertyRowMapper<>(User.class),
pageSize,
offset
);
// 查询总数
String countSql = "SELECT COUNT(*) FROM sys_user";
long total = jdbcTemplate.queryForObject(countSql, Long.class);
// 计算总页数
int totalPages = (int) Math.ceil((double) total / pageSize);
return new Page<>(users, pageNum, pageSize, total, totalPages);
}
/**
* 命名参数查询示例
*/
public User selectUserByNamedParameters(String name, String email) {
String sql = "SELECT * FROM sys_user WHERE name = :name AND email = :email";
Map<String, Object> params = new HashMap<>();
params.put("name", name);
params.put("email", email);
try {
return namedParameterJdbcTemplate.queryForObject(
sql,
params,
new BeanPropertyRowMapper<>(User.class)
);
} catch (EmptyResultDataAccessException e) {
return null;
}
}
/**
* 批量操作示例
*/
public int[] batchInsertUsers(List<User> users) {
String sql = "INSERT INTO sys_user (name, email, create_time, update_time) VALUES (?, ?, ?, ?)";
return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
User user = users.get(i);
ps.setString(1, user.getName());
ps.setString(2, user.getEmail());
ps.setTimestamp(3, Timestamp.valueOf(user.getCreateTime()));
ps.setTimestamp(4, Timestamp.valueOf(user.getUpdateTime()));
}
@Override
public int getBatchSize() {
return users.size();
}
});
}
/**
* 复杂条件查询
*/
public List<User> searchUsers(UserSearchCriteria criteria) {
StringBuilder sql = new StringBuilder("SELECT * FROM sys_user WHERE 1=1");
Map<String, Object> params = new HashMap<>();
if (StringUtils.hasText(criteria.getName())) {
sql.append(" AND name LIKE :name");
params.put("name", "%" + criteria.getName() + "%");
}
if (StringUtils.hasText(criteria.getEmail())) {
sql.append(" AND email LIKE :email");
params.put("email", "%" + criteria.getEmail() + "%");
}
if (criteria.getStartDate() != null) {
sql.append(" AND create_time >= :startDate");
params.put("startDate", criteria.getStartDate());
}
if (criteria.getEndDate() != null) {
sql.append(" AND create_time <= :endDate");
params.put("endDate", criteria.getEndDate());
}
// 排序
if (StringUtils.hasText(criteria.getSortBy())) {
sql.append(" ORDER BY ").append(criteria.getSortBy());
if ("desc".equalsIgnoreCase(criteria.getSortDir())) {
sql.append(" DESC");
} else {
sql.append(" ASC");
}
}
// 分页
if (criteria.getPageSize() > 0) {
sql.append(" LIMIT :limit OFFSET :offset");
params.put("limit", criteria.getPageSize());
params.put("offset", (criteria.getPageNum() - 1) * criteria.getPageSize());
}
return namedParameterJdbcTemplate.query(
sql.toString(),
params,
new BeanPropertyRowMapper<>(User.class)
);
}
}
/**
* 分页结果封装类
*/
public class Page<T> {
private List<T> content;
private int pageNum;
private int pageSize;
private long totalElements;
private int totalPages;
// 构造器、getter、setter省略
}
/**
* 查询条件封装类
*/
public class UserSearchCriteria {
private String name;
private String email;
private LocalDate startDate;
private LocalDate endDate;
private String sortBy;
private String sortDir;
private int pageNum = 1;
private int pageSize = 10;
// getter、setter省略
}
4.2 Spring Data JDBC高级应用
/**
* Spring Data JDBC Repository示例
*/
public interface UserRepository extends CrudRepository<User, Long>, PagingAndSortingRepository<User, Long> {
// 基本查询方法
Iterable<User> findByNameContaining(String keyword);
Iterable<User> findByEmailEndingWith(String domain);
// 分页查询
Page<User> findByNameContaining(String keyword, Pageable pageable);
// 排序查询
List<User> findByNameContainingOrderByCreateTimeDesc(String keyword);
// 使用@Query注解自定义查询
@Query("SELECT * FROM sys_user WHERE create_time BETWEEN :startDate AND :endDate")
List<User> findUsersByCreateTimeBetween(@Param("startDate") LocalDateTime start,
@Param("endDate") LocalDateTime end);
// 统计查询
@Query("SELECT COUNT(*) FROM sys_user WHERE name LIKE :keyword")
long countByNameContaining(@Param("keyword") String keyword);
// 更新操作
@Modifying
@Query("UPDATE sys_user SET email = :email WHERE id = :id")
int updateUserEmail(@Param("id") Long id, @Param("email") String email);
}
/**
* 自定义Repository实现
*/
public interface CustomUserRepository {
List<User> findActiveUsers();
void bulkUpdateUserStatus(List<Long> ids, String status);
}
/**
* 自定义Repository实现类
*/
public class CustomUserRepositoryImpl implements CustomUserRepository {
private final JdbcTemplate jdbcTemplate;
public CustomUserRepositoryImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public List<User> findActiveUsers() {
String sql = "SELECT * FROM sys_user WHERE status = 'ACTIVE'";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}
@Override
public void bulkUpdateUserStatus(List<Long> ids, String status) {
String sql = "UPDATE sys_user SET status = ? WHERE id = ?";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, status);
ps.setLong(2, ids.get(i));
}
@Override
public int getBatchSize() {
return ids.size();
}
});
}
}
/**
* 扩展Repository接口
*/
public interface ExtendedUserRepository extends UserRepository, CustomUserRepository {
}
五、完整配置与测试案例
5.1 完整配置文件
# application.yml 完整配置
spring:
# 数据源配置
datasource:
driver-class-name: com.kingbase8.Driver
url: jdbc:kingbase8://localhost:54321/test?charSet=UTF-8
username: system
password: your_secure_password_here
hikari:
pool-name: KingbaseHikariCP
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
validation-timeout: 5000
connection-test-query: SELECT 1
# JPA配置（可选）
jpa:
show-sql: true
hibernate:
ddl-auto: none
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialect
format_sql: true
use_sql_comments: true
# 事务管理配置
transaction:
default-timeout: 30
rollback-on-commit-failure: false
# MVC配置
mvc:
throw-exception-if-no-handler-found: true
web:
resources:
add-mappings: false
# 日志配置
logging:
level:
com.example: DEBUG
org.springframework.jdbc.core.JdbcTemplate: DEBUG
org.springframework.transaction: TRACE
pattern:
console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"
# Actuator监控配置
management:
endpoints:
web:
exposure:
include: health,info,metrics,prometheus
endpoint:
health:
show-details: always
show-components: always
metrics:
enabled: true
metrics:
export:
prometheus:
enabled: true
tags:
application: kingbase-demo
5.2 高级测试案例
/**
* 高级集成测试类
*/
@SpringBootTest
@ActiveProfiles("test")
@Transactional
@TestMethodOrder(OrderAnnotation.class)
class AdvancedIntegrationTest {
@Autowired
private UserService userService;
@Autowired
private AdvancedQueryService advancedQueryService;
@Autowired
private ExtendedUserRepository userRepository;
@Autowired
private JdbcTemplate jdbcTemplate;
@BeforeEach
void setUp() {
// 初始化测试数据
jdbcTemplate.execute("TRUNCATE TABLE sys_user RESTART IDENTITY CASCADE");
List<User> testUsers = Arrays.asList(
new User(null, "张三", "zhangsan@example.com"),
new User(null, "李四", "lisi@example.com"),
new User(null, "王五", "wangwu@example.com"),
new User(null, "赵六", "zhaoliu@example.com"),
new User(null, "测试用户", "test@example.com")
);
advancedQueryService.batchInsertUsers(testUsers);
}
@Test
@Order(1)
void testTransactionRollback() {
// 测试事务回滚
List<User> users = Arrays.asList(
new User(null, "正常用户", "normal@example.com"),
new User(null, null, "invalid@example.com") // 名称为空，应该触发异常
);
assertThrows(Exception.class, () -> userService.batchInsertUsers(users));
// 验证数据没有插入（事务回滚）
long count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM sys_user", Long.class);
assertEquals(5, count); // 只有初始的5条记录
}
@Test
@Order(2)
void testPagination() {
Page<User> page1 = advancedQueryService.selectUsersByPage(1, 2, "id", "asc");
assertEquals(2, page1.getContent().size());
assertEquals(3, page1.getTotalPages());
assertEquals(5, page1.getTotalElements());
Page<User> page2 = advancedQueryService.selectUsersByPage(2, 2, "id", "asc");
assertEquals(2, page2.getContent().size());
assertEquals("王五", page2.getContent().get(0).getName());
}
@Test
@Order(3)
void testAdvancedSearch() {
UserSearchCriteria criteria = new UserSearchCriteria();
criteria.setName("测试");
criteria.setPageNum(1);
criteria.setPageSize(10);
List<User> results = advancedQueryService.searchUsers(criteria);
assertEquals(1, results.size());
assertEquals("测试用户", results.get(0).getName());
}
@Test
@Order(4)
void testSpringDataJdbc() {
// 测试Spring Data JDBC功能
Page<User> page = userRepository.findByNameContaining("用户", PageRequest.of(0, 10));
assertEquals(1, page.getContent().size());
long count = userRepository.countByNameContaining("%用户%");
assertEquals(1, count);
}
@Test
@Order(5)
void testCustomRepository() {
List<User> activeUsers = userRepository.findActiveUsers();
assertNotNull(activeUsers);
// 这里可以根据实际业务逻辑添加更多断言
}
@Test
@Order(6)
void testExceptionHandling() {
// 测试异常处理
assertThrows(DataNotFoundException.class, () -> {
userService.updateUserName(999L, "不存在的用户");
});
}
}
六、性能优化与最佳实践
6.1 SQL性能优化建议
- 索引优化：为经常查询的字段创建合适的索引
- 查询优化：避免SELECT *，只查询需要的字段
- 批量操作：使用批量插入和更新减少数据库往返次数
- 连接池调优：根据实际负载调整连接池参数
6.2 应用层优化建议
- 缓存策略：合理使用Spring Cache减少数据库访问
- 异步处理：对耗时操作使用@Async注解异步执行
- 连接管理：及时关闭数据库连接，避免资源泄漏
- 监控告警：集成监控系统，及时发现性能问题
6.3 安全最佳实践
- SQL注入防护：使用预编译语句，避免字符串拼接
- 密码加密：数据库密码使用加密存储
- 权限控制：遵循最小权限原则，限制数据库用户权限
- 审计日志：记录重要操作日志，便于审计和故障排查
总结
通过上下两篇的详细探讨，我们全面掌握了SpringBoot集成Kingbase数据库的各个方面。从基础的环境搭建、依赖配置，到高级的事务管理、连接池优化、异常处理体系，再到复杂的查询场景和性能优化策略，我们构建了一个完整的企业级应用数据访问解决方案。
关键要点总结：
- 事务管理：合理使用@Transactional注解，根据业务需求选择合适的事务隔离级别和传播行为
- 连接池优化：深入理解HikariCP配置参数，根据实际场景调优
- 异常处理：建立完善的异常处理体系，提供友好的错误信息
- 查询优化：掌握高级查询技巧，提升数据访问性能
- 监控维护：集成监控系统，保障系统稳定运行
这些知识和技术不仅适用于Kingbase数据库，对于其他关系型数据库也有很好的参考价值。在实际项目中，需要根据具体业务需求和系统特点，灵活运用这些技术，构建高效、稳定、可维护的数据访问层。
