Hibernate Criteria API join to another table only with id

Question

I am trying to join from one table to another by using the Hibernate Criteria API. The problem is that table Contract has only salesId as defined as long (so no FK). Then I am not sure how to associate Contract with Sales. Better to explain in an example below.

The schema is the following:

Table Contract:  
----------------------
   ID  |   salesId   |
----------------------
    1  |      1      |
    2  |      2      |
----------------------

Table Sales:
----------------------
   ID  |     code    |
----------------------
    1  |   SALES_1   |
    2  |   SALES_2   |
----------------------

In Contract entity I have only private Long salesId;

And what I need to achieve is something like this:

getSession().createCriteria(Contract.class)
            .createAlias("salesId", "s") // somehow connect to Sales table
            .add(Restrictions.eq("s.code", salesCode)); // salesCode is param

So I can't use directly createAlias ↓, because it's unknown to hibernate

.createAlias("sales", "s")

I can't change the model in order to create FK between the tables. Is there a way how to solve this? I would like to avoid SQL string concatenation.

EDIT: The reason why I am using Hibernate Criteria API is that other query parameters (not shown here) are optional and then they can't be part of the SQL query. So that's why I can't use HQL.

score 2 · Accepted Answer · answered Feb 15 '18 at 16:40

This might be your solution in hibernate 5:

Criteria criteria  = getSession().createCriteria(Contract.class);

Criterion subquery = Restrictions.sqlRestriction("{alias}.salesId = (select id from TABLE_SALES where code = ?)", salesCode, StandardBasicTypes.STRING); 

criteria.add(subquery);

You want to use it for dynamic queries then you can use that criterion on custom Restrictions depending on your business logic.

criteria.add(Restrictions.and(subquery)) -- example
criteria.add(Restrictions.or(subquery)) -- example

PD: TABLE_SALES must be your Sales table name in the database, you can also pre-append its schema.

Cheers.

Thanks Wilder! As @elfuego already pointed out, a subquery is the way to go (even for some higher performance cost). Similar solution https://stackoverflow.com/questions/720502/hibernate-criteria-joining-table-without-a-mapped-association/2102080#2102080 Cheers — Zis, Feb 16 '18 at 09:23

score 1 · Answer 2 · answered Feb 14 '18 at 10:21

1

Why don't you want to use HQL and write a query like this:

select * from Contact t1 join Sales t2 where t1.salesId=t2.ID

answered Feb 14 '18 at 10:21

el fuego

775
8
16

the problem is, that other params in the query are optional. So if some parameter is null it can't be in the sql query. That's the reason I choose criteria and using ifs in the code. – Zis Feb 14 '18 at 10:25
But it's not a problem, strings in Java are dynamic =) You can write `String query = "select ... where t1.salesId=t2.ID"` and then `if (something) query += " and something=:param"; if (somethingElse) query += " and somethingElse=:param2";`, and then add parameters to the query conditionally as well. – el fuego Feb 14 '18 at 11:14
Thanks el fuego, but that's the reason why we have Criteria API - to avoid this 'concatenation hell'. Don't forget that you have to put IFs for setParameters as well, which brings another complexity to the code (in case of large amount of optional parameters). – Zis Feb 14 '18 at 11:26
You shouldn't have _another_ IFs, you may wrap them into a list and use the same IF, and then add them to a query in a loop. And you anyway need one IF per condition even with criteria. – el fuego Feb 14 '18 at 11:37
Thanks for the answer. Yup you need IF per condition with criteria, but definitely something like `if (status != null) { criteria.add(Restrictions.eq("status", status)); }` is way way more readable rather then a loop of variables or concatenation (which must be doubled for query and setParam). The problem with a loop is that you can have more complex IFs. How would you do that in the loop when one param could be null, the other must not be empty Collection, etc? Maybe this is more about programming style and it's really far away from the title of the topic. – Zis Feb 14 '18 at 11:41
1

No porblem. Afaik, it's impossible to do join with an non-linked table via Criteria, at least, directly via join, so my solution is the most straight-ahead. Also you may look at this answer: https://stackoverflow.com/questions/720502/hibernate-criteria-joining-table-without-a-mapped-association, especially at the answer with `DetachedCriteria`, it may work. – el fuego Feb 14 '18 at 11:51
Seems you are right about that it's not possible in Criteria. Thanks for thoughts and the link. I am actually using `DetachedCriteria` and it seems the solution could work. Although I am not sure about performance because of that sub-query. Anyway I'll give it a go and will see. Thanks – Zis Feb 14 '18 at 12:32

score 0 · Answer 3 · answered Sep 07 '20 at 11:41

It is not a SQL "join" but you can combine DetachedCriteria and Projection to create an external SQL "in" (or "not in") :

criteria.add(Restrictions.eq("table1ColumnName", XXX));
    
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(OtherMappedClass.class);
detachedCriteria.setProjection(Property.forName("sameColumnName"));
detachedCriteria.add(Restrictions.eq("table2ColumnName", XXX)););
criteria.add(Property.forName("sameColumnName").in(detachedCriteria));

Hibernate Criteria API join to another table only with id

3 Answers3