Hi folks,
I am trying to use criteria API to achieve following query
Code:
SELECT P.* FROM PRODUCT P, ORDERS O WHERE P.PID=O.PID AND O.STATUS=1
I have two tables product and orders, and i want to see all products against each order status, also order detail for each product, for same in my product.hbm.xml i have a set property for orders named OrderDetails
I am using criteria in following way
Code:
criteria = oSession.createCriteria(Product.class);
criteria.createCriteria("OrderDetails","ord",CriteriaSpecification.INNER_JOIN);
criteria.add(Restrictions.in("ord.statusID.id", Ids));
criteria.setMaxResults(50);
1st problemwhen i look at the generated query it gives me query like this
Code:
SELECT P.*, O.* FROM PRODUCT P, ORDERS O WHERE P.PID=O.PID AND O.STATUS=1
2nd problemThis is returning me duplicate records as well, so to overcome this i had put
Code:
criteria.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );
however even after setting pagesize as 50, this returns me less then 50 rows even though there are 100+ rows available with this criteria in database.
Basically criteria is not fetching the next page rows if resultset is shorter then defined page size.
Any help would be greatly appreciated.
Login
FAQ
Search