Hibernate querying Temporary Data

ccclark · **Joined:** Wed Feb 25, 2009 10:35 am **Posts:** 1

Our issue is the user inputs a list of values which are a unique index into a table. But we need to return the results in the order entered by the user and we need to return not found values. Also we need to be able to page the results.

The normal solution would be to insert the values in a temporary table or use a stored procedure to create an Oracle virtual table of the values. But I haven't seen any good resources on how to use those solutions with hibernate.

input:
X
A
E
F
D

expected output
X and X's Entity
A and A's Entity
E and No Entity Found
F and F's Entity
D and D's Entity

Any help would be greatly appreciated. Thanks

littypreethkr · **Posted:** Thu Feb 26, 2009 3:12 am

Yes, the usual solution is to have a stored procedure for making temporary table and all. But the problem is that You cant have paging if you are using stored procedures in hibernate. But as you are using Oracle may be you can use the DECODE function. So you can write HQL like:

Code:

String HQL = "select c, c.parent from Child c left outer join fetch c.parent where c.name in ('E','X','A') order by decode(c.name, 'E', 1, 'X', 2, 'A', 3, 4)";
List lst = session.createQuery(HQL).setMaxResults(10).setFirstResult(0).list();

littypreethkr · **Posted:** Thu Feb 26, 2009 5:26 am

Hey I was just thinking about this and it came to my mind that if you feel like using ANSI standard SQL only then you have the "case when" construct instead of DECODE function. Like:

Code:

order by case when c.name='E' then 1 when c.name='X' then 2 when c.name='A' then 3 end