Databases
StackOverflow2013
Postsdbo
POJoining with specific ON-clause with JPA 2 Criteria API
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoining with specific ON-clause with JPA 2 Criteria API
    primarykey
    data
    text
    <p>Is it somehow possible to do one of the following queries with JPA 2 Criteria API? Basicly, there are a number of Templates which are used by Profiles. Each User in the system has any number of Profiles, and two active ones. I'd like to get ALL templates and the number of users that owns active profiles that uses it. </p> <p>Alternative 1</p> <pre><code>SELECT t.id, count(p.id) FROM template t LEFT JOIN profile p ON p.template_id = t.id LEFT JOIN users u ON (u.active_profile_id = p.id OR u.active_personal_profile_id = p.id) GROUP BY t.id </code></pre> <p>Alternative 2</p> <pre><code>SELECT t.id, count(p.id) FROM COMPETENCE.template t LEFT JOIN COMPETENCE.profile p ON (p.template_id = t.id) LEFT JOIN users u1 on u1.active_profile_id = p.id LEFT JOIN users u2 on u2.active_personal_profile_id = p.id GROUP BY t.id </code></pre> <p>If I skip the requirement to get all templates, I can simply use User and Profile as Roots and the following would suffice:</p> <pre><code>CriteriaQuery&lt;Tuple&gt; cq = cb.createTupleQuery(); Root&lt;User&gt; from = cq.from(User.class); Root&lt;Profile&gt; from2 = cq.from(Profile.class); Join&lt;Profile, Template&gt; join = from2.join(Profile_.template); cq.where(cb.or( cb.equal(from.get(User_.activeCompetenceProfile), from2), cb.equal(from.get(User_.activePersonalProfile), from2) )); cq.groupBy(join.get(Template_.id)); cq.multiselect(join.get(Template_.id), cb.count(from2.get(Profile_.id))); List&lt;Tuple&gt; tuples = em.createQuery(cq).getResultList(); </code></pre> <p>But to get to get all templates, I believe I cannot use a WHERE clause, but an outer join is needed as in the first queries. However, alternative 1 seems impossible since you cannot set a specific ON clause for joins, and alternative 2 seems impossible since both User and Template needs to be Root at the same time. To get all templates, I need to use Template as root, and join it with Profile. This goes well, but I cannot join Profile with User since the ON parameter is stored in User and not in Profile. From my knowledge, you cannot join a table with another on a parameter stored in the second table.</p> <p>The task is of course solvable in round-about ways such as doing a second query selecting all templates and merge it with the result of the first query, but if it was possible to do in a single query, that would create cleaner code.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRasmus Franke
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POJoining with specific ON-clause with JPA 2 Criteria API
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJoining with specific ON-clause with JPA 2 Criteria API
      1. USuser902383
      1. VTFavorite
    3. VO
      singulars
      1. POJoining with specific ON-clause with JPA 2 Criteria API
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI get the feeling a lot, that JPA's criteria query wasn't made for arbitrarily complex SQL. If you want to map your SQL to Java more accurately, I would recommend http://www.jooq.org as an alternative, if you can still switch, of course
      singulars
      1. POJoining with specific ON-clause with JPA 2 Criteria API
      1. USLukas Eder
    2. COAbit too late for that since my project is pretty much complete :). I also like to use ORMs rather than hassle with stored procedures and similar.
      singulars
      1. POJoining with specific ON-clause with JPA 2 Criteria API
      1. USRasmus Franke
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload