Solve the problem of springdataJPA supporting native sql
- 2021-09-12 01:29:33
- OfStack
springdataJPA support for native sql
In the project, springdataJPA is used to connect to the database for operation, but the hql statement in JPA can not meet the business requirements, so the native sql is needed
But there is one problem:
@Query(value = "SELECT ppd.* FROM zt_productionplandetails AS ppd \n" +
" \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" +
" \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" +
" \tWHERE ppd.enabled = TRUE \n" +
" \tAND ppd.`status`=1 \n" +
"\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" +
" \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" +
" \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" +
" \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" +
" \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )"
,nativeQuery = true)
Page<ProductionPlanDetails> findNewPlan(@Param("productName") String productName, @Param("empName") String empName, @Param("endDate") String endDate, @Param("startDate") String startDate, @Param("clientName") String clientName, Pageable pageable);
When using this sql, an error will be reported
java.sql.SQLSyntaxErrorException: Unknown column 'ppd' in 'field list'
It means that the field for ppd can't be found, but it is obvious here that ppd is an alias
After viewing the project to start the query data call, the sql is:
select count(ppd) FROM zt_productionplandetails AS ppd
LEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id
LEFT JOIN zt_employee e ON e.id=ppd.employeeId
WHERE ppd.enabled = TRUE
AND ppd.`status`=1
AND IF(? !='', sp.clientName LIKE ?, 1 = 1 )
AND IF( ? !='', sp.productName LIKE ?, 1 = 1 )
AND IF( ? != '', e.name LIKE ?, 1 = 1 )
AND IF( ? != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=?, 1 = 1 )
AND IF( ? != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=?, 1 = 1 )
The problem is found here. When the project executes sql, the query is count (ppd). The query document knows that by default, jpa will add count () when executing the query sql
So make the following changes:
@Query(value = "SELECT ppd.* FROM zt_productionplandetails AS ppd \n" +
" \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" +
" \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" +
" \tWHERE ppd.enabled = TRUE \n" +
" \tAND ppd.`status`=1 \n" +
"\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" +
" \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" +
" \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" +
" \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" +
" \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )",
countQuery = "SELECT count(*) FROM zt_productionplandetails AS ppd \n" +
" \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" +
" \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" +
" \tWHERE ppd.enabled = TRUE \n" +
" \tAND ppd.`status`=1 \n" +
"\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" +
" \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" +
" \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" +
" \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" +
" \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )"
,nativeQuery = true)
Add countQuery parameters
Solve the problem ~
Spring Data JPA Write Native sql Statement
When using Spring Data JPA, we usually only need to inherit JpaRepository to get most of the commonly used methods of adding, deleting and modifying. Sometimes we need to customize some query methods, and we can write custom HQL statements
However, when using Spring Data JPA, we usually only need to inherit JpaRepository to get most of the commonly used methods of adding, deleting and modifying. Sometimes we need to customize some query methods, and we can write custom HQL statements
@Query(value = " Customize sql Statement ", nativeQuery = true)
List<Long> findFriendsByUserId(Long userId);
As above, just add nativeQuery = true after the query statement