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.

I dont think the sub query is needed when it is the same table — Matt, Jun 22 '15 at 09:54
Why would you use two queries to achieve something you can do with only one query? — cнŝdk, Jun 24 '15 at 07:58
Well can you write this query using one. I will try to explain you what I wanted. Here suppose in the database you have values like the following: — Piyush Chandra, Jun 24 '15 at 08:46
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. — Piyush Chandra, Jun 24 '15 at 08:57
If you can create any query which don't have to use inner query then please let me know. — Piyush Chandra, Jun 24 '15 at 08:59

score 0 · Answer 1 · answered Jun 22 '15 at 09:49

0

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' )

answered Jun 22 '15 at 09:49

Mukesh Kalgude

4,814
2
17
32

1

the op wants something else – Ameya Deshpande Jun 22 '15 at 09:51

score 0 · Answer 2 · answered Jun 24 '15 at 07:51

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.

Creating query in Hibernate

2 Answers2