jpa 默认生成sql语句_Hibernate/JPA如何保证不生成多余的SQL语句？

对SQL语句如果没有计数和断言的情况下，很容易失去对当前场景背后执行的SQL的控制，从而导致性能损失。

本应用是计数和断言“幕后”触发的SQL语句的示例。计数SQL语句非常有用，以确保您的代码不会生成比你预计的更多的SQL(例如，通过声明预期语句的数量可以轻松检测到N + 1)。

第一步：在Maven的pom.xml中添加依赖datasource-proxy和Vlad Mihalcea的db-util：

net.ttddyy

datasource-proxy

${datasource-proxy.version}

com.vladmihalcea

db-util

${db-util.version}

第二步：使用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 transactionassertInsertCount(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 insertuser.setCity("Craiova");

userRepository.save(user);// 1 updateuserRepository.delete(user);// 1 delete// at this point there is no transaction running// a total of 5 statements, not very goodassertInsertCount(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 insertuser.setCity("Craiova");

userRepository.save(user);// update not triggered since a delete followsuserRepository.delete(user);// 1 delete}

}

源代码可以在这里找到