在项目中用到的是springdataJPA连接数据库进行操作,但是JPA中的hql语句不能够满足业务要求,因而需要用到原生sql
但是有一个问题:
@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(