How to create temporary tables in Hibernate?

14,017

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.

Share:
14,017
Siu Ching Pong -Asuka Kenji-
Author by

Siu Ching Pong -Asuka Kenji-

Siu Ching-Pong (better known as “Asuka Kenji”) is a technology enthusiast who enjoys contributing to open source projects and participating in programming contests. He has been a programmer for 30+ years, and still keeps himself up-to-date with cutting-edge technologies, such as blockchain (mainly Hyperledger Fabric and Ethereum), functional programming (in Scala, Erlang, Java, etc.), asynchronous I/O (with Go a.k.a. Golang, JavaScript on Node.js, etc.), NoSQL (with Redis, MongoDB, DynamoDB, CouchDB, etc.), and cloud platforms (mainly AWS and IBM Cloud a.k.a. Bluemix), to handle today’s challenge of tamper resistance, multicore utilization, massive concurrency, and horizontal scalability. Kenji has 10+ years of work experience in the Information Technology industry, 3 years of which in a management position. He rescued several startup companies from months of unproductiveness and years of improper practices, and helped them to get on track in a year. Please check his career portfolio below for information about projects involved (connection, i.e. friend status, is needed). Kenji likes working in a UNIX environment, partly because he makes heavy use of the command line. He aligns with the UNIX philosophy of compositing small units that are testable, debuggable, benchmark-able and improvable. He is good at data modeling (with OOP, SQL, NoSQL, etc.) and implementing domain-specific languages (DSLs). Kenji has good analytical and problem-solving skills. He has a strong sense of responsibility, and he is able to meet quality standards as well as schedules.

Updated on June 04, 2022

Comments

  • Siu Ching Pong -Asuka Kenji-
    Siu Ching Pong -Asuka Kenji- almost 2 years

    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?