Creating query in Hibernate

Question

In hibernate how to create this query

"select test_type_nmbr from test_table where test_type_name in 
(select Test_type_name from test_table where test_type_nmbr in('111','222' ))". 

Here suppose in the database you have values like the following:

test_type_nmbr | test_type_name
------------------------------- 
111            | gre 
222            | gmat 
333            | gre 

Now you want to get all the test_type_ nmbr having "gre" as the test_type_name (i.e. '111' and '333') and you have only 111 test_type_nmbr with you.

Do I need to use 2 different callbacks criterias or can I do in 1? If 1 then please let me know how.

Answer 1

Well I found the answer and have implemented it using detached criteria. Here I am using detached criteria to store my sub-query and if later on if I want to use this sub-query I can use it again using its name.

Let these numbers be present in a list called testTypeList having('111','222').

    final Criteria criteria = session.createCriteria(Test_Table.class,"testTable1");

    final DetachedCriteria detachedCriteria =  DetachedCriteria.forClass(Test_Table.class,
              "testTable2"); // testTable1 and testTable2 are aliases
     detachedCriteria.add(Restrictions.in("testTable2.testTypeNmbr", testTypeList));
          final ProjectionList projectionList1 = Projections.projectionList();
          projectionList1.add(Projections.property("testTable2.testTypeName "));
     detachedCriteria.setProjection(projectionList1);

     criteria.add(Property.forName("testTable1.testTypeName").in(detachedCriteria));
          final ProjectionList projectionList2 = Projections.projectionList();
          projectionList2.add(Projections.property("testTable1.testTypeName "));
     criteria.setProjection(projectionList2);

return criteria.list(); // It will return all the test-numbers having test names that testTypeList contains.

Answer 2

try this query

select a.test_type_nmbr from test_table a
join test_table b on a.Test_type_name=b.Test_type_name
 where b.test_type_nmbr in('111','222' )