下面是我现在的程序:
List<Predicate> conditionList = new ArrayList<Predicate>();
conditionList.add(cb.equal(jobRoot.get(Job_.getSingularAttribute("opOffice",Integer.class)), branch));
//-------------------
if (jobno != null && !"".equals(jobno.trim())) {
Metamodel metamodel = emf.getMetamodel();
EntityType<File> Verification_ = metamodel.entity(File.class);
Join<Export, File> verificationj = jobRoot.join(Job_.getSingularAttribute("file" , File.class),JoinType.LEFT);
conditionList.add(cb.equal(verificationj.get(Verification_.getSingularAttribute("fileNo" , String.class)),jobno));
conditionList.add(cb.equal(verificationj.get(Verification_.getSingularAttribute("fileType" , String.class)),"单据"));
}
//-------------------
cq.where(conditionList.toArray(new Predicate[0]));
// 排序
cq.orderBy(cb.asc(jobRoot.get(Job_.getSingularAttribute("jobNo",String.class))));
TypedQuery<Export> tq = em.createQuery(cq);
// 业务条数 limit
tq.setMaxResults(limit);
List<Export> entities = tq.getResultList();
**********************************************************************************************************这是控制台打印出来的SQL语句:
select
portj0_.ID as ID14_,
portj0_1_.CREATED_BY as CREATED2_14_,
portj0_1_.DATE_CREATED as DATE3_14_,
portj0_1_.DATE_MODIFIED as DATE4_14_
from
EXPORT portj0_
inner join
JOB portj0_1_
on portj0_.ID=portj0_1_.ID
left outer join
FILE file1_
on portj0_.ID=file1_.JOB_ID
where
(
portj0_1_.JOB_NO like ?
)
and file1_.FILE_NO=?
and file1_.FILE_TYPE=?
order by
portj0_1_.JOB_NO
**********************************************************************************************************
而我想要的SQL效果是:
select
portj0_.ID as ID14_,
portj0_1_.CREATED_BY as CREATED2_14_,
portj0_1_.DATE_CREATED as DATE3_14_,
portj0_1_.DATE_MODIFIED as DATE4_14_
from
EXPORT portj0_
inner join
JOB portj0_1_
on portj0_.ID=portj0_1_.ID
left outer join
FILE file1_
on portj0_.ID=file1_.JOB_ID
and file1_.FILE_NO=?
and file1_.FILE_TYPE=?
where
(
portj0_1_.JOB_NO like ?
)
order by
portj0_1_.JOB_NO
**********************************************************************************************************
应该怎么写JPA条件才可以得到我想要的SQL效果呢?(不要写显式的SQL语句(JPQL))
在线等待啊... ...
List<Predicate> conditionList = new ArrayList<Predicate>();
conditionList.add(cb.equal(jobRoot.get(Job_.getSingularAttribute("opOffice",Integer.class)), branch));
//-------------------
if (jobno != null && !"".equals(jobno.trim())) {
Metamodel metamodel = emf.getMetamodel();
EntityType<File> Verification_ = metamodel.entity(File.class);
Join<Export, File> verificationj = jobRoot.join(Job_.getSingularAttribute("file" , File.class),JoinType.LEFT);
conditionList.add(cb.equal(verificationj.get(Verification_.getSingularAttribute("fileNo" , String.class)),jobno));
conditionList.add(cb.equal(verificationj.get(Verification_.getSingularAttribute("fileType" , String.class)),"单据"));
}
//-------------------
cq.where(conditionList.toArray(new Predicate[0]));
// 排序
cq.orderBy(cb.asc(jobRoot.get(Job_.getSingularAttribute("jobNo",String.class))));
TypedQuery<Export> tq = em.createQuery(cq);
// 业务条数 limit
tq.setMaxResults(limit);
List<Export> entities = tq.getResultList();
**********************************************************************************************************这是控制台打印出来的SQL语句:
select
portj0_.ID as ID14_,
portj0_1_.CREATED_BY as CREATED2_14_,
portj0_1_.DATE_CREATED as DATE3_14_,
portj0_1_.DATE_MODIFIED as DATE4_14_
from
EXPORT portj0_
inner join
JOB portj0_1_
on portj0_.ID=portj0_1_.ID
left outer join
FILE file1_
on portj0_.ID=file1_.JOB_ID
where
(
portj0_1_.JOB_NO like ?
)
and file1_.FILE_NO=?
and file1_.FILE_TYPE=?
order by
portj0_1_.JOB_NO
**********************************************************************************************************
而我想要的SQL效果是:
select
portj0_.ID as ID14_,
portj0_1_.CREATED_BY as CREATED2_14_,
portj0_1_.DATE_CREATED as DATE3_14_,
portj0_1_.DATE_MODIFIED as DATE4_14_
from
EXPORT portj0_
inner join
JOB portj0_1_
on portj0_.ID=portj0_1_.ID
left outer join
FILE file1_
on portj0_.ID=file1_.JOB_ID
and file1_.FILE_NO=?
and file1_.FILE_TYPE=?
where
(
portj0_1_.JOB_NO like ?
)
order by
portj0_1_.JOB_NO
**********************************************************************************************************
应该怎么写JPA条件才可以得到我想要的SQL效果呢?(不要写显式的SQL语句(JPQL))
在线等待啊... ...