转载

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
<b>public</b> <b>class</b> DatasourceProxyBeanPostProcessor implements BeanPostProcessor {

    <b>private</b> <b>static</b> <b>final</b> Logger logger
            = Logger.getLogger(DatasourceProxyBeanPostProcessor.<b>class</b>.getName());

    @Override
    <b>public</b> Object postProcessAfterInitialization(Object bean, String beanName) {

        <b>if</b> (bean instanceof DataSource) {

            logger.info(() -> <font>"DataSource bean has been found: "</font><font> + bean);

            <b>final</b> ProxyFactory proxyFactory = <b>new</b> ProxyFactory(bean);

            proxyFactory.setProxyTargetClass(<b>true</b>);
            proxyFactory.addAdvice(<b>new</b> ProxyDataSourceInterceptor((DataSource) bean));

            <b>return</b> proxyFactory.getProxy();
        }
        <b>return</b> bean;
    }

    @Override
    <b>public</b> Object postProcessBeforeInitialization(Object bean, String beanName) {
        <b>return</b> bean;
    }

    <b>private</b> <b>static</b> <b>class</b> ProxyDataSourceInterceptor implements MethodInterceptor {

        <b>private</b> <b>final</b> DataSource dataSource;

        <b>public</b> ProxyDataSourceInterceptor(<b>final</b> DataSource dataSource) {
            <b>super</b>();
            <b>this</b>.dataSource = ProxyDataSourceBuilder.create(dataSource)
                    .name(</font><font>"DATA_SOURCE_PROXY"</font><font>)
                    .logQueryBySlf4j(SLF4JLogLevel.INFO)
                    .multiline()
                    .countQuery()
                    .build();
        }

        @Override
        <b>public</b> Object invoke(<b>final</b> MethodInvocation invocation) throws Throwable {

            <b>final</b> Method proxyMethod = ReflectionUtils.
                    findMethod(<b>this</b>.dataSource.getClass(),
                            invocation.getMethod().getName());

            <b>if</b> (proxyMethod != <b>null</b>) {
                <b>return</b> proxyMethod.invoke(<b>this</b>.dataSource, invocation.getArguments());
            }

            <b>return</b> invocation.proceed();
        }
    }
}
</font>

第三步：通过SQLStatementCountValidator.reset()重置计数器:

@SpringBootApplication
<b>public</b> <b>class</b> CountSQLStatementsApplication {

    @Autowired
    <b>private</b> UserService userService;

    <b>public</b> <b>static</b> <b>void</b> main(String args) {
        SpringApplication.run(CountSQLStatementsApplication.<b>class</b>, args);
    }

    @Bean
    <b>public</b> ApplicationRunner init() {
        <b>return</b> args -> {

            userService.userOperationsWithoutTransactional();
            
            SQLStatementCountValidator.reset();      
            userService.userOperationsWithTransactional();

            <font><i>// allow the transaction to commit</i></font><font>
            </font><font><i>// a total of 2 statements instead of 5 as in the case of no explicit transaction</i></font><font>
            assertInsertCount(1);
            assertUpdateCount(0);
            assertDeleteCount(1);
            assertSelectCount(0);
        };
    }
}
</font>

第四步：通过assertInsert{Update/ Delete/Select}Count(long expectedNumberOfSql断言INSERT，UPDATE，DELETE,和SELECT：

@Service
<b>public</b> <b>class</b> UserService {

    @Autowired
    <b>private</b> UserRepository userRepository;

    <b>public</b> <b>void</b> userOperationsWithoutTransactional() {
        User user = <b>new</b> User();

        user.setName(<font>"Jacky Francisco"</font><font>);
        user.setCity(</font><font>"Banesti"</font><font>);
        user.setAge(24);

        SQLStatementCountValidator.reset();

        userRepository.save(user);   </font><font><i>// 1 insert</i></font><font>
        user.setCity(</font><font>"Craiova"</font><font>);
        userRepository.save(user);   </font><font><i>// 1 update</i></font><font>
        userRepository.delete(user); </font><font><i>// 1 delete</i></font><font>

        </font><font><i>// at this point there is no transaction running</i></font><font>
        </font><font><i>// a total of 5 statements, not very good</i></font><font>
        assertInsertCount(1);
        assertUpdateCount(1);
        assertDeleteCount(1);
        assertSelectCount(2);
    }

    @Transactional
    <b>public</b> <b>void</b> userOperationsWithTransactional() {
        User user = <b>new</b> User();

        user.setName(</font><font>"Jacky Francisco"</font><font>);
        user.setCity(</font><font>"Banesti"</font><font>);
        user.setAge(24);
              
        userRepository.save(user);   </font><font><i>// 1 insert</i></font><font>
        user.setCity(</font><font>"Craiova"</font><font>);
        userRepository.save(user);   </font><font><i>// update not triggered since a delete follows</i></font><font>
        userRepository.delete(user); </font><font><i>// 1 delete        </i></font><font>
    }
}
</font>

源代码可以在这里找到

原文 https://www.jdon.com/51758

正文到此结束