1

I'm using Java with Hibernate. I want to:

  1. Save my data to the database
  2. Run sql to verify the result
  3. If the result is valid, then commit, otherwise rollback

So, is it possible to save the result to the database without commit, so that I can use sql / hql to verify data and rollback if needed?

My actual scenario is quite complicated, the simplified version is:

  • PERSON joins PERSON_CAR joins CAR joins CAR_SEAT joins SEAT
  • Make changes and commit everything
  • If any PERSON has more than 10 SEATs, I want to show errors

If I could save everything to the database first, then I can write SQL with GROUP BY and HAVING statement to aggregate the data and only return the ones that exceed.

Max
  • 1,064
  • 9
  • 23
  • Show your code man what you have tried so far ? – Sudhir Ojha Apr 07 '18 at 08:25
  • http://what-when-how.com/hibernate/transactions-and-concurrency-hibernate/ – Ng Sharma Apr 07 '18 at 08:26
  • @SudhirOjha this is just a plan, if this plan is possible, it would be fast and accurate. Otherwise my 2nd plan is to 1) fetch everything in java memory, 2) mimic the changes, 3) count seats by person, 4) commit or rollback. – Max Apr 07 '18 at 08:36
  • 1
    That's what flush() is for. But Hibernate should flush automatically before executing a query anyway. – JB Nizet Apr 07 '18 at 08:36
  • @AK.Sharma. Which bit should I read then? I'm aware of e.g. http://lh4.ggpht.com/_NNjxeW9ewEc/TLSH3VuCK6I/AAAAAAAAKhk/dUGy9o0D-7Y/s1600-h/tmp9D85_thumb3.png – Max Apr 07 '18 at 08:37
  • @Max wouldn't be better to create a store procedure for that and run it in Hibernate as transactional? – AR1 Apr 07 '18 at 08:38
  • @JBNizet I have just read the below based on what you said, looks like this may just work! I'll give it a go https://stackoverflow.com/a/26976077/2810746 – Max Apr 07 '18 at 08:42
  • @AR1 our application needs to maintain the logic so that it's as database agnostic as possible. We want to be able to use a different database by only just change the connection details and the database driver. – Max Apr 07 '18 at 08:56
  • Leaving aside whether it is possible or not (certainly possible), the method in java that would traverse this data structure in memory and check how much PERSON has SEATs without writing to the database and then querying the database would be very easy to implement and probably 100-500 times faster. – krokodilko Apr 07 '18 at 09:11
  • @krokodilko The example I put here is a simplified version. There are multiple areas of the application can cause the change of the data structure, and some of them are batch procedures e.g. adding some joins (person_car and car_seat) and remove some other joins in one go. I want to avoid implementing the validation for each scenario. Ultimately what matters to me is getting a list of PERSONs that exceeds the count, regardless where in the system changes these several tables. – Max Apr 07 '18 at 09:40
  • One more question - Can two or more database sessions simultaneously change the SEATS number for the same PERSON, and each session must see changes made in other sessions to be able to verify the total SEAT number? If so, then this is impossible, the session can not see uncommited changes from other sessions. You can do it only in one session (the session only sees your uncommited changes, does not see changes from other sessions). – krokodilko Apr 07 '18 at 09:56
  • @krokodilko Good point! Luckily we don't need to deal with multiple databases for this, otherwise we'll have to handle it in java memory. – Max Apr 07 '18 at 11:18
  • Then, you've already got the answer form @JB Nizet: `yes, it's possible, use the flush(). But Hibernate should flush automatically before executing a query anyway.` – krokodilko Apr 07 '18 at 11:55

1 Answers1

0

Yes, there is session.flush() function, which could be used for that purpose.

Dusan Markovic
  • 11
  • 1
  • 4