With the development of technology, persistent layer framework is becoming more and more mature, from Mybatis to JPA, new technology is to make us more convenient and simple to use. JPA, for example, is favored by programmers because its built-in approach can satisfy most CRUDS in daily development, but there are pros and cons.

Recently I encountered a problem with slow query efficiency in development.

Problem Description:

1. In the development environment, there is a business, the list page plus the search box function, and then the list data is using JPA findAll() method to query 20,000 data, amazingly, a whole 7 seconds,JPA although easy to use, but findAll is equivalent to SELECT * FROM TB where A, B, C, such full table scan is certainly inefficient, and it is still not fast after all the fields in the table are checked once, but only a few fields are needed in the business, which is actually a huge consumption of resources.

2. Customize how to concatenate and null WHERE conditions in SQL statements, similar to if NULL in Mybatis.

3. Customize how to perform paging in SQL statements.

Solution:

Select a,b,c from (select a,b,c from); select a,b,c from (select a,b,c from) Select * from TB select * from TB select * from TB Performance problem solved.

SQL > select * from ‘where’; SQL > select * from ‘where’;

@Query(value = " select name,age,sex where if(? 1! = '',name = ? 1, 1 = 1) and the if (? 2! = null,age = ? 2, 1 = 1)", nativeQuery = true)
 List<Object[]> countCase(String name, Integer age, String sex)
Copy the code

if(? 1! =”,name=? 1,1=1) Indicates the parameter name. If the value is not “” (the String type is null instead of null), the parameter name is passed. If the value is null,1=1 indicates that the parameter is true.

3. Check the documentation and decide to implement JpaRepository Pageable directly:

Declare native count queries for pagination at the query method using [@query]

(docs. Spring. IO/spring – data…).

@Query(value = " select name,age,sex where if(? 1! = '',name = ? 1, 1 = 1) and the if (? 2! = null,age = ? 2, 1 = 1) / * # pageable * /",countQuery = "select count(*) where if(? 1! = '',name = ? 1, 1 = 1) and the if (? 2! = null,age = ? 2, 1 = 1)",nativeQuery = true)
 Page<Object[]> countCase(String name, Integer age, String sex,Pageable pageable)
Copy the code

CountQuery is used to get the total number of entries.

**/#pageable/** must be added here, presumably JPA may need pageable character to start the project normally, if not added, the startup will be abnormal.

This is limited to mysql