Joining tables without relation using JPA criteria

Question

I have two tables with no modeled relation:

Table comm with columns:

name
date
code

Table persondesc with columns:

code
description

Relationship between the two tables is many to one (many comm to one persondesc):

com.code = persondesc.code

These two tables are mapped with annotations but I have no relation declared.

What I'm trying to is to select comm table ordered by persondesc.description.

How can I do this JPA and Hibernate?

what is "Hibernate criteria" and "using hibernate" relevance here? are you not using the JPA API? — Neil Stockton, Jun 04 '15 at 10:17
chsdk - it's not the same question. in my question two object/tables are mapped in the Java annotations in Snukker question one of the object/tables or are not mapped at all "not mapped in the Java annotations or xml." — Idan, Jun 04 '15 at 11:36
See my answer: https://stackoverflow.com/questions/720502/hibernate-criteria-joining-table-without-a-mapped-association/76720561#76720561 — Ali Sohrabi, Jul 19 '23 at 10:59

score 42 · Answer 1 · edited Oct 13 '20 at 12:16

42

So if your classes have no "relation", then you do a query like

SELECT a FROM A a
CROSS JOIN B b
WHERE a.someField = b.otherField
ORDER BY b.anotherField

Which can be achieved using JPA Criteria, something like

CriteriaBuilder cb = emf.getCriteriaBuilder();
CriteriaQuery<A> query = cb.createQuery(A.class);
Root<A> aRoot = query.from(A.class);
Root<B> bRoot = query.from(B.class);
aRoot.alias("a");
bRoot.alias("b");

query.select(aRoot)
  .where(cb.equal(aRoot.get(A_.someField), bRoot.get(B_.otherField))
  .orderBy(cb.asc(bRoot.get(B_.anotherField)));

... Or just redesign your classes and do your developers a favour.

edited Oct 13 '20 at 12:16

Jacob van Lingen

8,989
7
48
78

answered Jun 04 '15 at 12:31

Neil Stockton

11,383
3
34
29

5

Do those `.alias()` matter? – Jin Kwon Oct 28 '16 at 05:47
they matter if your JPA provider uses them in the generated SQL so that it is more readable. But then you may be unlucky and have a JPA provider that does what it feels like and creates ugly names in the SQL – Neil Stockton Oct 28 '16 at 06:33
@Neil Stockton _"... Or just redesign your classes and do your developers a favour."_ What if you have a base module that contains common entities and then someone writes a module that uses the common entities and also contains entity extension classes, e.g. `CommonEntity` and `CommonEntityDimensionData`. Assuming that `CommonEntity` can not be modified it does not know about the extension class. – ltlBeBoy Aug 30 '17 at 10:38
5

Where do 'A_' and 'B_' come from. When I try this, I get "Cannot resolve symbol 'A_'" – slashdottir Apr 13 '19 at 00:33
1

@slashdottir it's metamodel https://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html/metamodel.html#metamodel-static – Mike Mike Jul 17 '19 at 23:21
How would you do this if I wanted an outer join? – T3rm1 Jul 29 '22 at 07:58

Jacob van Lingen · Answer 2 · 2021-10-20T06:37:25.473

1

Hibernate 5.1 introduced explicit joins on unrelated entities for JPQL. So now you can just write a JOIN like native SQL:

List<Comm> results = entityManager
  .createQuery("""SELECT c FROM Comm c
    JOIN PersonDesc pd ON c.code = pd.code
    ORDER BY pd.description""", Comm.class)
  .getResultList();

Click here for more detailed example.

edited Oct 20 '21 at 06:37

answered Oct 13 '20 at 12:54

Jacob van Lingen

8,989
7
48
78

score 0 · Answer 3 · answered Sep 27 '21 at 08:33

One of the simplest solution is to create view. Then create an Entity class for that view and execute query against view.

View:

create or replace view view_comm_persondesc as select c.name, c.date, c.code, p.description from comm c inner join persondesc p on c.code = p.code;

Code

@Entity(name = "view_comm_persondesc")
public class ViewCommPerson{ 
    @Id
    private String code;
    private String name;
    private Date date; 
    private String description;

    ... All Getters/Setters ...

}



    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ViewCommPerson> query = cb.createQuery(ViewCommPerson.class);
    Root<ViewCommPerson> root = query.from(ViewCommPerson.class);
    // You can add your filter here
    List<ViewCommPerson> result = entityManager.createQuery(query).getResultList();

Hope it servers your use case.

score -1 · Answer 4 · answered May 30 '19 at 13:49

In case you need to sort by column which is in another table, you can create "fake" dependency with disabled insertable and updatable attributes. Domain model would looks like this:

Primary entity:

@Entity
public class Comm {

    @Id
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "date")
    private Date date;

    @Column(name = "code")
    private String code;

    @OneToOne(fetch = FetchType.LAZY) // @ManyToOne is also possible
    @JoinColumn(name = "code", referencedColumnName = "code", insertable = false, updatable = false)
    private PersonDesc personDesc;

}

Entity with required data for sorting:

@Entity
public class PersonDesc {

    @Id
    private String code;

    @Column(name = "description")
    private String description;

}

After you define your domain model, it possible to create criteria query:

CriteriaBuilder cb = emf.getCriteriaBuilder();
CriteriaQuery<Comm> cq = cb.createQuery(Comm.class);
Root<Comm> root = cq.from(Comm.class);
Join<Comm, PersonDesc> leftJoin = root.join("personDesc", JoinType.LEFT);
cq.select(root);
cq.orderBy(cb.asc(root.get("personDesc.description")));

Joining tables without relation using JPA criteria

4 Answers4

Linked