How to create temporary tables in Hibernate?

Question

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?

Embay

Oracle temporary tables are not dropped when the session closes. They are permanent. The data is only visible when the session is alive. Once the session closes its data disappears, but the table remains. – Toyatoyama

Answer 1

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.

Taoism

Answer 2

I see it is a classic scenario to use the optimistic lock concept. It is achieved with the help of a new field in your entity called version which is an increasing number or timestamp. @version annotation (in hibernate) will ensure you have an incremental version number.

Here is a dry run of the concept.

1. (I) the value of a is 4 when the user displays the web form - fetch the version of the record along with other fields. Let's say the version

2. (II) another user updates the same field to 5 - Another user would change the version to some other number. Let's say Version 2.

3. (III) the first user updates the field to 2 and submits the webform.

   • while saving the record the update statement will look like this -

    update user_table
    set somefield = value
    where version = 1  --(as web form contains version 1 of the record)

This way you are not replacing someone else's record. Hibernate does help you with a little bit more here. It catches this scenario and throws an OptimisticLockException and you can use it to take any exceptional decision.

Lookin