Hello!
We have a scenario where the user can select multiple employeeID's that we need to show line items on in one shot. The number of items in the selection could possibly be 1000's. To execute such a query, we would have to use an IN clause e.g. SELECT name, date, salary FROM EMPLOYEE WHERE empID IN (x,y,z..........)

Now the IN length of the SQL will become very large & many databases have limits on the SQL statement length. We currently insert the selected mployee IDs into a temporary table & perform a JOIN with EMPLOYEE table to get the result set back. Can we do this using Hibernate? Are there any plan to support such a functionality?

Max Rydahl Andersen of JBOSS advised me to use createSQLQuery(), but I am not sure how that will solve the problem. The issue is that one cannot create tables on the fly in the database for every query being executed for a user, the database will be overwhelmed & that is not a scalable solution anyway.

The answer is to make a some kind of temporary table or tempaorary rows in a scratch table so that you can use IN with a sub-select. There are many ways to do this. Max is just suggesting a way that you can add the non-mapped temporary table to your Hibernate query.

Well, maybe then breakup your in clause to query say 500 id's at a time. and then combine the results from the iteration

- Shishir

Is this something is available in Hibernate? Can Hibernate create temporary table & manage it?

no (as i ave written to you before ,) - and how should/could it ?

temporary tables is something that is normally used when you have a query that is hard to otherwise make performant and often require special tweaking steps - not something that is easy to standardize, nor very usefull since its normally rather trivial with jdbc.

remember Hibernate is a ORM, not a low-level data manipulation tool (that is what you have jdbc and sql for)

Hibernate would be overkill for managing temporary tables. Just use JDBC.