Hi everyone,

Hibernate fixes extremely well in the whole application except for the following part. Here is the problem:

One of the functionalities of my application is allowing users to reduce a set of data from certain tables in the database. The application administrators choose what tables they want to involve in the reductions during setup and users can use our user interface chooses what fields and values they want them to get involve with the reduction. The users can create numerious reductions through our interface.

The next step is translating all these inputs into various select SQL queries and move the returning data to a system data for future processing. The problem I have right now is that when I am using Hibernate functionality, I need to perform a session.find(select query) to load all the select data first, and then generates individual session.save for individual object returns from session.find into the system table. This is extremely expensive because my select query might involve more than 1 million records sometime. I want to know is there ANY way that I don't need to select out the data through session.find and save individual data through session.save into the system table.

I know in SQL you can perform insert into table1 (v1, v2) values (?, ?) select v4, v5 from table4, table5 where table4.id = table5.id and table4.v6=1 and table5.v7=2 which I don't need to extract the data out from the database and insert into the system table.

I wonder if Hibernate has a similar solution so that the application does not need to extract out data from database before inserting into another table. I am very close to my deadline and this is something I need to solve rather quickly.

I know I can use direct JDBC call but I really don't want to because my application involves various database and customerize this part to use JDBC call will be rather expensive on development.


Could someone give me some pointers on this issue on how to transfer large amount data through Hibernate without performing a session.find and a session.save??

Thanks
Vivian

This is simply not an object-oriented problem. There are no objects here ;) In your description of the problem, you talked about tables and data, not about anything that looks remotely like an object.

Use direct SQL/JDBC, because thats what its good at. ORM makes no sense for this problem.

Btw, this sounds like the perfect use case for a stored procedure and a minimum SQL abstraction layer on top.

My question right now is if I need to use direct JDBC call, can I use Hibernate's query translator to translate HQL to direct SQL statement and extract the SQL statement from Hibernate??

Why would you want to do such a thing?

Because I really need to do insert select and not extracting out those data and I really need the mapping capability of Hibernate for the rest of the application and this is the only place that performing data transfer from tables to table and require efficieny, if Hibernate cannot perform a insert select, at least, I want to extract out the select statement so that I can generate the insert select and execute a direct JDBC call.

Just write your SQL Query by hand and execute it manually through a connection obtained from hibernate.