[FIXED] How to create temporary tables in Hibernate?

Issue

Goal

1. Invoke a CREATE TEMPORARY TABLE statement in Hibernate without using native SQL. That means using HQL or Hibernate APIs only.
2. Save objects to the temporary table.
3. Invoke a stored procedure which makes use of existing tables and the temporary table.
4. DROP the temporary table when finished. (I know it's not necessary, but I think it's a good habit to do so.)

Background

1. I'm very familiar with SQL but new to Hibernate.
2. I'm forced to use Hibernate in a project because of, you know, someone's decision.
3. I'm going to save a web form to an Oracle database.
4. The web form contains a table full of text fields (designed by someone else), one in each cell.
5. When the user clicks Save, the values MUST be saved in a single transaction.
6. The web form is backed up by a database view.
7. The database view is created from a database table using the EAV pattern. (It is done so because the columns are somehow dynamic.)
8. Each text field in the web form is modeled by one row in the database table.
9. Displaying the web form uses SELECT statements on the view.
10. Updating the web form uses UPDATE statements on the view, which calls the INSTEAD OF trigger of the view.
11. Only changed values are updated. There is an audit trail for each update.
12. If any of the values are updated by another user without the user's notice, the transaction is rolled back. Here is an example for such a scenario: (I) the value of a is 4 when the user displays the web form (II) another user updates the same field to 5 (III) the first user updates the field to 2 and submits the web form.

Originally Proposed Solution

1. Use AJAX (jQuery) to detect changes in the text fields, and submit only those changed by the user.
2. However, changes made by another user need to be detected in the database.

Solution Supposed to Work Better

1. When the user clicks Save, create a temporary table (a temporary table is a table only seen by the current session / connection, and is dropped automatically when the session is closed / upon disconnection) and save the objects (cells) into the temporary table.
2. Start a transaction.
3. Lock some of the existing tables (or only related rows, for performance).
4. Compare the submitted data with the existing data.
5. If any unnoticed change was made, rollback the transaction.
6. Update the necessary rows.
7. Commit the transaction and unlock the tables.
8. Drop the temporary table.

Is there any ideas?

Solution

This does not answer your exact requirements but given that there have been no attempts at an answer... have you considered using a temporary CSV table using something like http://csvjdbc.sourceforge.net/.

While it does not conform to requirement of doing it through hibernate, it is database agnostic and cross platform.

Answered By - P Hemans
Answer Checked By - Terry (JavaFixing Volunteer)