Hibernate/JPA如何保證不生成多餘的SQL語句？

banq發表於2019-02-11
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        
    }
}


原始碼可以在這裡找到

相關文章