Goal
- Invoke a
CREATE TEMPORARY TABLEstatement in Hibernate without using native SQL. That means using HQL or Hibernate APIs only. - Save objects to the temporary table.
- Invoke a stored procedure which makes use of existing tables and the temporary table.
DROPthe temporary table when finished. (I know it's not necessary, but I think it's a good habit to do so.)
Background
- I'm very familiar with SQL but new to Hibernate.
- I'm forced to use Hibernate in a project because of, you know, someone's decision.
- I'm going to save a web form to an Oracle database.
- The web form contains a table full of text fields (designed by someone else), one in each cell.
- When the user clicks
Save, the values MUST be saved in a single transaction. - The web form is backed up by a database view.
- The database view is created from a database table using the EAV pattern. (It is done so because the columns are somehow dynamic.)
- Each text field in the web form is modeled by one row in the database table.
- Displaying the web form uses
SELECTstatements on the view. - Updating the web form uses
UPDATEstatements on the view, which calls theINSTEAD OFtrigger of the view. - Only changed values are updated. There is an audit trail for each update.
- 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 is4when the user displays the web form(II)another user updates the same field to5(III)the first user updates the field to2and submits the web form.
Originally Proposed Solution
- Use AJAX (jQuery) to detect changes in the text fields, and submit only those changed by the user.
- However, changes made by another user need to be detected in the database.
Solution Supposed to Work Better
- 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. - Start a transaction.
- Lock some of the existing tables (or only related rows, for performance).
- Compare the submitted data with the existing data.
- If any unnoticed change was made, rollback the transaction.
- Update the necessary rows.
- Commit the transaction and unlock the tables.
- Drop the temporary table.
Is there any ideas?