Hibernate Community • View topic - Need help with subqueries(max, not exists, etc.)

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Need help with subqueries(max, not exists, etc.)

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

Revenge

Post subject: Need help with subqueries(max, not exists, etc.)

Posted: Wed Feb 08, 2012 4:41 pm

Newbie

Joined: Wed Feb 08, 2012 12:42 pm
Posts: 2

Hi all,

I have a table like this...

MyTable
id val1 val2 refId
1 11 22 null
2 11 22 123
3 11 22 null
4 33 44 null
5 234 345 null
6 33 44 null
7 111 222 null
8 1112 2234 999
9 331 444 null

I need to create a query (Criteria, DetachedCriteria, etc.) that extracts:
- all the records where val1 like '11%' and val2 like '22%' and refId is not null
- only the records with the max(id) where val1 like '11%' and val2 like '22%' and refId is null but only if not exists a refId not null (for the same val1 and val2 values)

I try to write a sql statement to explain it (this is only one way to write this query):
SELECT *
FROM MyTable a
where val1 like '11%' and val2 like '22%'
and (refId is not null or id in
(select max(id) FROM MyTable b WHERE b.val1=a.val1 and b.val2=a.val2 and refId is null and not exists
(SELECT 1 FROM MyTable c WHERE c.val1=b.val1 and c.val2=b.val2 and refId is not null)))

it must return the records with id 2 and 8

using val1 like '33%' and val2 like '44%'

it must return the records with id 6 and 9

thanks in advance,
Revenge

Top

pb00067

Post subject: Re: Need help with subqueries(max, not exists, etc.)

Posted: Fri Feb 10, 2012 6:05 am

Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 990

With such a complicate query I would simply use the SQLQuery api,
without worry about how to translate this in Criteria.

Code:

SQLQuery query = hibernatesession.createSQLQuery("SELECT * FROM MyTable a where val1 like '11%' and val2 like '22%' and (refId is not null or id in (select max(id) FROM MyTable b WHERE b.val1=a.val1 and b.val2=a.val2 and refId is null and not exists
(SELECT 1 FROM MyTable c WHERE c.val1=b.val1 and c.val2=b.val2 and refId is not null)))");

query.addEntity("b", MyTable.class);
return query.list();

Top

Revenge

Post subject: Re: Need help with subqueries(max, not exists, etc.)

Posted: Fri Feb 10, 2012 2:23 pm

Newbie

Joined: Wed Feb 08, 2012 12:42 pm
Posts: 2

hi,

I solved the problem using a Criteria and 2 DetachetCriteria

Code:

DetachedCriteria subQueryNotExists = DetachedCriteria.forClass(MyTable.class, "tab1")
      .add(Property.forName("tab1.val1").eqProperty("tab2.val1"))
       .add(Property.forName("tab1.val2").eqProperty("tab2.val2"))
       .add(Restrictions.isNotNull("tab1.refId"))
       .setProjection(Projections.id());
       
       
       DetachedCriteria subQueryIn = DetachedCriteria.forClass(MyTable.class, "tab2")
       .add(Property.forName("tab2.val1").eqProperty("tab3.val1"))
       .add(Property.forName("tab2.val2").eqProperty("tab3.val2"))
       .add(Restrictions.isNull("tab2.refId"))
       .add(Subqueries.notExists(subQueryNotExists))
       .setProjection(Projections.max("tab2.id"));
      
       
      Criterion cr = Restrictions.isNotNull("tab3.refId");
      cr = Restrictions.or(cr, Property.forName("tab3.id").in(subQueryIn));
       
      Criteria criteria = getSession().createCriteria(MyTable.class, "tab3");
      criteria.add(Restrictions.like("tab3.val1", val1, matchMode));
      criteria.add(Restrictions.like("tab3.val2", val2, matchMode));
      criteria.add(cr);

thanks anyway

bye

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum