Hibernate/JPA如何保证不生成多余的SQL语句?

19-02-11 banq
         

对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        
    }
}

源代码可以在这里找到