jpql和sql的区别

项目使用jpa规范

其中既可使用面对对象查询(jpql语句)

又可使用原生sql查询;

1.(经后期验证,jpql都可以)

其中有一个区别是:

jpql查询字段为空: " cr.owner.id = null "

sql查询字段为空: " cr.owner_id is null "

//下面这个service方法查询的是实体bean,jpql语句,而不是原生sql语句    
Long recordId = (Long) commonService.excuteSql(minidSql, ExecuteType.SINGLE_RESULT);// 统计当前查询最小的资源id
this.getEntityManager().createQuery(jpql).getResultList();    

//下面这个service方法查询的是原生sql语句
getEntityManager().createNativeQuery(sql).getResultList();

在涉及到多表联合查询的时候,

我写的jpql语句:

　　　select distinct(cr) from CustomerResource as cr where 1=1   
     and cr.domain.id = 1 
     and cr.owner.department.id in 
     (1, 26, 27, 44, 263, 340, 507, 508, 599, 605, 642, 674, 675, 676) 
     or cr.owner is null 
     order by cr.id

经过jpa规范转化后的原生sql语句是:

SELECT DISTINCT t0.ID AS a1, t0.AUDITAMOUNT AS a2, t0.BIRTHDAY AS a3, t0.BORROWMONEY AS a4, t0.BORROWTIME AS a5, 
t0.CALL_ID AS a6, t0.CARDNO AS a7, t0.COUNT AS a8, t0.CUSTOMERQUALIFICATIONRECORD AS a9, t0.CUSTOMER_SOURCE AS a10, 
t0.EXPIREDATE AS a11, t0.IMPORTDATE AS a12, t0.INVESTMONEY AS a13, t0.INVESTPRODUCT AS a14, t0.INVESTRECORD AS a15, 
t0.LASTDIALDATE AS a16, t0.NAME AS a17, t0.NOSIGNTIME AS a18, t0.NOTE AS a19, t0.REPAYSTATUS AS a20, 
t0.SERVICE_RECORD_STATUS_ID AS a21, t0.SETTLEMENTDATE AS a22, t0.SEX AS a23, t0.STORECITYNAME AS a24, t0.STORENAME AS a25, 
t0.STOREPROVICENAME AS a26, t0.USER_ID AS a27, t0.USER_NAME AS a28, t0.ACCOUNTMANAGER_ID AS a29, t0.COMPANY_ID AS a30, 
t0.CUSTOMERLEVEL_ID AS a31, t0.DOMAIN_ID AS a32, t0.OWNER_ID AS a33, t0.DEFAULTADDRESS_ID AS a34 
FROM ec2_customer_resource t0, 
ec2_user t1 WHERE 
(((((1 = 1) AND (t0.DOMAIN_ID = 1)) AND (t1.department_id IN (1, 26, 27, 44, 263, 340, 507, 508, 599, 605, 642, 674, 675, 676) )) 
OR (t0.OWNER_ID IS NULL)) 
AND (t1.ID = t0.OWNER_ID)) 
ORDER BY t0.ID LIMIT 10 OFFSET 0

大家可以看到,倒数第二排被我加粗倾斜的语句,这个是jpa规范自动帮我加上去的,

但是我想查询的数据里面t0.OWNER_ID是可以为空的,

而t1.ID是不可能为空的;

这就导致了查询数据的丢失;

解决办法:

cr.owner.department.id in 
     (1, 26, 27, 44, 263, 340, 507, 508, 599, 605, 642, 674, 675, 676)

根据这里的部门id,先查询出部门管辖下的用户id,然后用

cr.owner.id in (*,*,*,*,*,*) or cr.owner is null

这样解析后生成的sql语句是没有联表的;

over...