Hibernate/HQL to achieve SQL WHERE ... IN (SELECT...) query

Question

What is the best way using Hibernate to achieve an equivalent of the following SQL query that involves a subquery:

SELECT cat.description, cat.code FROM
  THING_CATEGORY cat
WHERE cat.code IN (
  SELECT thing.CATEGORY_CODE FROM THING thing
  WHERE thing.COUNTRY_CODE IN ('AU', 'US', 'UK')
)

It's possible the best approach is to pass it on directly to the database to be done natively in my database's flavour of SQL (PL/SQL) but I haven't actually been able to figure out how it's done in a Hibernate Criterion object so I specifically want to know that.

Please note: The THING table is absolutely massive and has many columns so I do NOT want to pull down all the THING entities from the database to achieve this logic as it will not be performant. The logic to restrict the THING_CATEGORY results must be done inside the database where it can be optimised. As it is, when I do the query with raw PL/SQL it takes almost a whole second to get the result.

To give an idea of the entities (note that THING_CATEGORY doesn't have a reference back to THING):

@Entity @Table(name = "THING")
public class Thing
{
  private String categoryCode;
  @Column(name = "CATEGORY_CODE", nullable = false, length = 3)
  public String getCategoryCode() { return this.categoryCode; }

  private String countryCode;
  @Column(name = "COUNTRY_CODE", nullable = false, length = 2)
  public String getCountryCode() { return this.countryCode; }

  ...
}

@Entity @Table(name = "THING_CATEGORY")
public class ThingCategory
{
  private String code;
  @Id @Column(name = "CODE", unique = true, nullable = false, length = 3)
  public String getCode() { return this.code; }

  ...
}

Answer 1

As I was about to post the question I realised the answer is to use a Hibernate DetachedCriteria to contain the THING restriction subquery. And then to add that into the criteria for THING_CATEGORY main query.

So the Hibernate code looks like this:

public List<ThingCategory> getRestrictedByCountrySet(List<String> countryCodes)
{
  Criterion thingCriterion = Restrictions.in("countryCode", countryCodes);
  DetachedCriteria thingSubQuery = DetachedCriteria.forClass(Thing.class)
    .add(thingCriterion)
    .setProjection(Projections.property("categoryCode");

  Criteria thingCategoryCriteria = getHbSession().createCriteria(ThingCategory.class)
    .add(Property.forName("code").in(thingSubQuery));

  return thingCategoryCriteria.list();
}