A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
JDBC and Relational Databases
Unnecessary INNER JOIN with Criteria
Joined: Jan 08, 2013
Jan 22, 2013 06:01:43
I'm working on a method in which a given table needs to join with another and with a field of the second table still another comparison do with it. Something like:
SELECT * FROM tab1 INNER JOIN tab2 WHERE tab2.id = (SELECT MAX(id) FROM tab2)
The method is as follows:
CriteriaBuilder cb = manager.getCriteriaBuilder(); CriteriaQuery<Subs> c = cb.createQuery(Subs.class); Root<Subs> subs = c.from(Subs.class); Subquery<Number> sq = c.subquery(Number.class); Root<Auth> auth = sq.from(Auth.class); sq.select(cb.max(auth.get("authId").as(Number.class))); sq.where(cb.equal(auth.get("target"), target)); List<Predicate> predicates = new ArrayList<Predicate>(); if (target != null) predicates.add(cb.equal(subs.get("target"), target)); predicates.add(subs.get("Auths").get("authId").in(sq)); c.where(predicates.toArray(new Predicate[predicates.size()]));
And the only mapping that exists is unidirectional, from Subs to Auth:
@OneToMany(fetch=FetchType.EAGER) @JoinColumn(name="SubsIdAuth") private List<Auth> Auths;
This works, but OpenJPA does the following:
FROM tab_subs t0 INNER JOIN tab_auth t1 ON t0.CodSubs = t1.SubsId INNER JOIN tab_auth t5 ON t0.CodSubs = t5.SubsId
Does anyone know how to avoid this second join?
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: Unnecessary INNER JOIN with Criteria
Better sql statement?
Select ... from ...
Enthuware question on inner join
OpenJPA-problem with polymorphic unidirectional relation with single tabled inheritance
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2015