I have two tables that looks like:
Table_0
ID Description
5 description5
6 description6
7 description7
8 description8
Table_1
ID Table_0_ID Status
1 5 LOADED
2 6 LOADED
3 7 LOADED
4 7 LOADED
Table_2
ID Table_1_ID
1 1
2 2
3 3
4 4
my expected result when I invoke findAll query is that it eliminates duplicates so result will return (for both tables) three rows (table_1 id from 1 to 3).
I wrote both named query and criteria builder query but the last one seems to be by 4 times faster. I wonder why. Am I making a mistake?
Here is the code:
query = "SELECT OBJECT(sb) FROM Table_2 sb WHERE sb.Table_1.id IN (SELECT MAX(maxsr.id) FROM Table_1 maxsr WHERE maxsr.status = LOADED GROUP BY maxsr.Table_0.id)")
criteria builder:
final EntityManager em = getEntityManager();
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Table_2> criteriaQuery = cb.createQuery(Table_2.class);
final Root<Table_2> root = criteriaQuery.from(Table_2.class);
Predicate p = getPredicateOnList(data, cb, root, gateSession);
if (p != null) {
criteriaQuery.where(p);
}
final Query q = getEntityManager().createQuery(criteriaQuery);
return q.getResultList();
}
method getPredicateOnList
private Predicate getPredicateOnList(final PaginationData data, final CriteriaBuilder cb, final Root<Table_2> root) {
final Join<Table_2, Table_1> readingJoin = root.join("Table_1");
boolean filterUnloaded = false;
boolean selectMax = true;
for (KeyValuePair pair : data.getRequestParams()) {
if (pair.getKey().equalsIgnoreCase("filterUnloaded")) {
filterUnloaded = ParsingUtils.parseBoolean(pair.getValue(), false);
}
if (pair.getKey().equalsIgnoreCase("selectMax")) {
selectMax = ParsingUtils.parseBoolean(pair.getValue(), true);
}
}
Predicate predicate = null;
if (selectMax) {
List<Long> maxReadingIds = getMaxReadingIds(gateSession.getId(), filterUnloaded);
if (maxReadingIds == null || maxReadingIds.isEmpty()) {
//do nothing
} else {
predicate = readingJoin.get("id").in(maxReadingIds);
}
}
return predicate;
}
method getMaxReadingIds
private List<Long> getMaxReadingIds(Long sessionId, boolean filterUnloaded) {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Long> maxReadingIdQuery = cb.createQuery(Long.class);
final Root<Table_1> Table_1Root = maxReadingIdQuery.from(Table_1.class);
final Path<Long> idGet = Table_1Root.get("id");
maxReadingIdQuery.select(cb.greatest(idGet));
final Join<Table_1, Table_0> join = Table_1Root.join("Table_0");
maxReadingIdQuery.groupBy(join.get("id"));
Predicate predicate = null;
if (filterUnloaded) {
predicate = cb.equal(Table_1Root.get("status"), LOADED);
}
//omiss sessionId parameter
if (predicate != null) {
maxReadingIdQuery.where(predicate);
}
final Query q = getEntityManager().createQuery(maxReadingIdQuery);
return q.getResultList();
}
(I made some semplification, if code is not well defined please tell me and I supply more information) Both results are correct but criteria builder is faster.