1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
/**
*
* select *
* from tirt t
* inner join location l on l.loc_code = TIRT_LOC_CODE_NORM(t.loc_code1)
* inner join location_group d on t.loc_code_disposition = d.loc_group_code
* where l.REF_SITE != d.REF_SITE
*
*
* @param criteria
* @param paginationCriteria
* @return
*/
@Override
public List<TirtEntity> findTirt(
temSearchCriteria criteria,
PaginationCriteria paginationCriteria) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<TirtEntity> cq =
cb.createQuery(TirTEntity.class);
Root<TirTEntity> root = cq.from(TirTEntity.class);
Join<TirTEntity, Location> joinTbLoc = root.join("loc_code1", JoinType.INNER);
Join<TirTEntity, LocationGroup> joinTbLocGroup = root.join("loc_code_disposition", JoinType.INNER);
Expression <TirTEntity> expLocCodeEqual =
cb.function(
"TIRT_LOC_CODE_NORM", TirTEntity.class,
root.get(TirTEntity_.currentLocation).get(TTDTirTiresLocationEmbeeded_.locCode1));
joinTbLoc.in(expLocCodeEqual);
Predicate dispTirTireEqualLocGroupCode = cb.equal(root.get(TirTEntity_.locCodeDisposition),
joinTbLocGroup.get(TbLocationGroup_.code));
Predicate compareSite = cb.notEqual(joinTbLoc.get(TbLocation_.refSite), joinTbLocGroup.get(TbLocationGroup_.refSite));
Date fromDate = supprOneYearToDateTime();
Predicate dispositionDateInfOneYear = cb.greaterThan(
root.get(TirTEntity_.dispositionDate), fromDate);
Predicate pAnd = cb.and(dispTirTireEqualLocGroupCode,compareSite, dispositionDateInfOneYear);
cq.where(pAnd); //Step 5
TypedQuery<TirTEntity> q = em.createQuery(cq);
q.setFirstResult(paginationCriteria.getOffsetRecord());
q.setMaxResults(paginationCriteria.getPageSize());
return q.getResultList();
} |
