Subquery function in Hibernate

Question

I am trying to use subquery function in my Hibernate query, but I'am an idiot :) Structure of my DB tables is:

CREATE TABLE standard (
  id VARCHAR(12) NOT NULL ,
  title VARCHAR(99) NOT NULL ,
  description VARCHAR(999) NOT NULL ,
  revision VARCHAR(99) NOT NULL ,
  annotation TEXT NULL ,
  PRIMARY KEY (id) );

CREATE TABLE article (
  id VARCHAR(12) NOT NULL ,
  type INT(2) NOT NULL ,
  classTitle VARCHAR(99) NULL ,
  classDescription VARCHAR(999) NULL ,
  standard_id VARCHAR(12) NULL ,
  pubdate DATETIME NOT NULL ,
  title VARCHAR(99) NULL ,
  thumbnail TEXT NULL ,
  text TEXT NULL ,  
  PRIMARY KEY (id) );

What I'm going to do is to get standard, which contains articles with specified type. My thought was that I filter articles by its type, then group them by its standardId and then get standards through its ids.

Something like this :)

DetachedCriteria subquery = DetachedCriteria.forClass(Article.class)
                             .add(Restrictions.eq("type", Constants.ARTICLE_TYPE_INTERPRETATION))
                              .setProjection(Projections.groupProperty("standardId"));

List<Standard> stds = (List<Standard>) session.createCriteria(Standard.class)
            .add(Restrictions.idEq(Subqueries.exists(subquery))).list();

But this query gives me all of the standards in database despite the fact, that subquery returns only standard that I want. I know where the problem is : I can't write the Restriction that will filter standard by its ids returned by subqery.

Can someone give me a hint how to do it ?

Many thanks !

Ondrej

EDIT: Thanks to @Rahul Agrawal

List<Standard> stds = (List<Standard>) session.createCriteria(Standard.class)
            .add(Subqueries.propertyIn("id", subquery)).list();

Rahul Agrawal · Accepted Answer · 2012-08-09 09:40:22Z

Add an IN condition. To add a "where field in subquery", two separate criteria are required. One for the main (root) entity, and the other to retrieve the values for the IN list. The following retrives the orders for which it has an order item which the product has a stock level of ZERO. The result transformer is used to only retrieve one row per entity, instead of having multiple rows as a result of the join on a one-to-many relationship. ?

DetachedCriteria ids = DetachedCriteria.forClass(ProductStock.class, "stock");
ids.add(Restrictions.eq("stock.stockLevel", 0));
ids.setProjection(Property.forName("productId"));

DetachedCriteria criteria = DetachedCriteria.forClass(Order.class, "order");
criteria.createAlias("order.orderItems", "items", CriteriaSpecification.LEFT_JOIN);
criteria.add(Subqueries.propertyIn("items.productId", ids));
criteria.setResultTransformer(Criteria.ROOT_ENTITY);

For more details refer : http://devgrok.blogspot.in/2008/11/hibernates-criteria-api.html

Rahul Agrawal · Accepted Answer · 2012-08-09 09:17:42Z

0

I think you are looking for something this ..

List<Standard> stds = (List<Standard>) session.createCriteria(Standard.class)
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .add(Restrictions.idEq(Subqueries.exists(subquery))).list();

answered Aug 9, 2012 at 9:17

Rahul Agrawal

8,97118 gold badges50 silver badges60 bronze badges

1 Comment

s0vet Over a year ago

No, I don't want to DISTINCT result, I want to get objects from DB according to its ids saved in subqery :) I don't know if I am explaining my problem well.

Collectives™ on Stack Overflow

Subquery function in Hibernate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related