Hibernate Criteria API join to another table only with id

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.

Answer

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.