对SQL语句如果没有计数和断言的情况下,很容易失去对当前场景背后执行的SQL的控制,从而导致性能损失。
本应用是计数和断言“幕后”触发的SQL语句的示例。计数SQL语句非常有用,以确保您的代码不会生成比你预计的更多的SQL(例如,通过声明预期语句的数量可以轻松检测到N + 1)。
第一步:在Maven的pom.xml中添加依赖datasource-proxy和Vlad Mihalcea的db-util:
<dependency> <groupId>net.ttddyy</groupId> <artifactId>datasource-proxy</artifactId> <version>${datasource-proxy.version}</version> </dependency> <dependency> <groupId>com.vladmihalcea</groupId> <artifactId>db-util</artifactId> <version>${db-util.version}</version> </dependency>
|
第二步:使用countQuery()创建ProxyDataSourceBuilder:
@Component public class DatasourceProxyBeanPostProcessor implements BeanPostProcessor {
private static final Logger logger = Logger.getLogger(DatasourceProxyBeanPostProcessor.class.getName());
@Override public Object postProcessAfterInitialization(Object bean, String beanName) {
if (bean instanceof DataSource) {
logger.info(() -> "DataSource bean has been found: " + bean);
final ProxyFactory proxyFactory = new ProxyFactory(bean);
proxyFactory.setProxyTargetClass(true); proxyFactory.addAdvice(new ProxyDataSourceInterceptor((DataSource) bean));
return proxyFactory.getProxy(); } return bean; }
@Override public Object postProcessBeforeInitialization(Object bean, String beanName) { return bean; }
private static class ProxyDataSourceInterceptor implements MethodInterceptor {
private final DataSource dataSource;
public ProxyDataSourceInterceptor(final DataSource dataSource) { super(); this.dataSource = ProxyDataSourceBuilder.create(dataSource) .name("DATA_SOURCE_PROXY") .logQueryBySlf4j(SLF4JLogLevel.INFO) .multiline() .countQuery() .build(); }
@Override public Object invoke(final MethodInvocation invocation) throws Throwable {
final Method proxyMethod = ReflectionUtils. findMethod(this.dataSource.getClass(), invocation.getMethod().getName());
if (proxyMethod != null) { return proxyMethod.invoke(this.dataSource, invocation.getArguments()); }
return invocation.proceed(); } } }
|
第三步:通过SQLStatementCountValidator.reset()重置计数器:
@SpringBootApplication public class CountSQLStatementsApplication {
@Autowired private UserService userService;
public static void main(String args) { SpringApplication.run(CountSQLStatementsApplication.class, args); }
@Bean public ApplicationRunner init() { return args -> {
userService.userOperationsWithoutTransactional(); SQLStatementCountValidator.reset(); userService.userOperationsWithTransactional();
// allow the transaction to commit // a total of 2 statements instead of 5 as in the case of no explicit transaction assertInsertCount(1); assertUpdateCount(0); assertDeleteCount(1); assertSelectCount(0); }; } }
|
第四步:通过assertInsert{Update/ Delete/Select}Count(long expectedNumberOfSql断言INSERT,UPDATE,DELETE,和SELECT:
@Service public class UserService {
@Autowired private UserRepository userRepository;
public void userOperationsWithoutTransactional() { User user = new User();
user.setName("Jacky Francisco"); user.setCity("Banesti"); user.setAge(24);
SQLStatementCountValidator.reset();
userRepository.save(user); // 1 insert user.setCity("Craiova"); userRepository.save(user); // 1 update userRepository.delete(user); // 1 delete
// at this point there is no transaction running // a total of 5 statements, not very good assertInsertCount(1); assertUpdateCount(1); assertDeleteCount(1); assertSelectCount(2); }
@Transactional public void userOperationsWithTransactional() { User user = new User();
user.setName("Jacky Francisco"); user.setCity("Banesti"); user.setAge(24); userRepository.save(user); // 1 insert user.setCity("Craiova"); userRepository.save(user); // update not triggered since a delete follows userRepository.delete(user); // 1 delete } }
|
源代码可以在这里找到