0

I am getting a weird issue while using the hibernate Criteria for a inner query.

Below is the code snippet.

Criteria criteria = session.createCriteria(Restaurant.class);
criteria.addOrder(Order.asc("nid"));
DetachedCriteria innerIDs = DetachedCriteria.forClass(InnerTable.class).setProjection(Property.forName("tempId") );
criteria.add(Subqueries.in("id", innerIDs));
criteria.list()

This above code generated the SQL Script as:

1.    SELECT columns names
2.    FROM table name
3.    WHERE   ? IN (SELECT   this_.TEMP_ID AS y0_ FROM   global_temp_id this_)

In the line# 3, instead of getting the column name, I am getting ? (question mark). I tried even putting Property.forName("id") in Subqueries.in instead "id" still the same issue.

I have not mapped any relation to these 2 tables in hbm.xml files.

santu
  • 665
  • 2
  • 7
  • 23

1 Answers1

2

What you see is normal. Criteria queries are transformed to SQL queries executed using prepared statements. The id is a parameter of the statement.

If you want to select restaurants whose ID is in the subquery, then you need

criteria.add(Subqueries.propertyIn("id", innerIDs));

As the javadoc indicates, in() considers the first argument as a literal value:

Creates a criterion which checks that the value of a literal is IN the values in the subquery result.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Thank you so much. It's working. But could not understand the difference between Subqueries.in and Subqueries.propertyIn. Could you please explain bit? When should I use Subqueries.in() ? – santu May 05 '14 at 09:20
  • 1
    When you want to look for a literal in the result of a subquery. Like in `select restaurant from Restaurant restaurant where 'Michelin' in (select critique.name from Critique critique where critique.restaurant.id = restaurant.id)` – JB Nizet May 05 '14 at 09:43