Hibernate/JPA如何保證不生成多餘的SQL語句?
對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 } } |
原始碼可以在這裡找到
相關文章
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- hibernate在JPA規範中在控制檯無法出現SQL語句SQL
- Hibernate 執行原始SQL語句SQL
- php如何嫵媚地生成執行的sql語句PHPSQL
- 如何透過Hibernate/JPA的SqlResultSetMapping生成需要資料的DTO?SQLAPP
- SQL語句圖表生成工具ChartSQLSQL
- sql語句如何執行的SQL
- Hibernate annotation, JPA如何對映多個屬性為unique
- 【Tips】使用SQL生成外來鍵的SQL建立語句SQL
- 定時生成分月表sql語句SQL
- jsqlparser使用記錄---生成sql語句JSSQL
- 根據DELTA自動生成SQL語句SQL
- EFCore常規操作生成的SQL語句一覽SQL
- websphere 6.1如何將open jpa 切換為hibernate jpaWeb
- Hibernate 查詢語句
- Elasticsearch如何保證資料不丟失?Elasticsearch
- 如何寫這個sql語句?SQL
- 冗餘資料一致性,到底如何保證?
- Oracle如何複製表的sql語句OracleSQL
- 【PL/SQL】使用變數傳遞方法生成表更名的SQL語句SQL變數
- 如何使用Hibernate/JPA的JPQL/HQL查詢提取?
- SQL語句為什麼不會共享(上)SQL
- SQL語句為什麼不會共享(中)SQL
- SQL語句為什麼不會共享(下)SQL
- excel表結構生成powerDesigner模型,生成建表語句sqlExcel模型SQL
- 用LinqPad檢視Nhibernate生成的sql語句SQL
- MYSQL 中max & lpad 語句在Hibernate如何使用?MySql
- RabbitMQ-如何保證訊息不丟失MQ
- SQL語句SQL
- SQL語句IN的用法SQL
- 常用的SQL語句SQL
- 常用的SQL 語句SQL
- SQL 語句 as 的用法SQL
- oracle的sql語句OracleSQL
- 轉JPA實體註解與hibernate主鍵生成策略
- mysql如何跟蹤執行的sql語句MySql
- Sql語句密碼驗證安全漏洞(轉)SQL密碼
- 自定義註解例項實現SQL語句生成SQL