Bulk update oddities

JeremyG · **Joined:** Wed Jun 15, 2005 7:14 pm **Posts:** 28

I'm using Hibernate 3.1.3 with a mysql database and I'm trying to perform the equivalent of the SQL statement:

Code:

update product set is_visible = is_visible_temp;

However I try to do this in HQL by using:

Code:

update Product p set p.isVisible = p.isVisibleTemp;

but it doesn't update any of the records. I could paint the temp fields to false at the beginning and then do a subselect like

Code:

update Product p set p.isVisible where p.id in (select p.id from Product p where p.isVisibleTemp = true)

but that's kind of a work around.
Does anyone know why the first HQL statement I listed does not work?
Thanks!
Jeremy

JeremyG · **Joined:** Wed Jun 15, 2005 7:14 pm **Posts:** 28

I lied. I end up having to do the query:

Code:

update Product p set p.isVisible where p.id in (select p.id from Product p where p.isVisibleTemp = true)

but even that doesn't work for some reason.
Any idea as to why it doesn't?

damnhandy · **Posted:** Wed Apr 19, 2006 6:54 am

Three questions:

1.) Have you checked to see what SQL is being generated by Hibernate?

2.) What is going on between sessionFactory.openSession() and session.close()?

3.) Are you running this code within a transaction?

Ryan-

JeremyG · **Joined:** Wed Jun 15, 2005 7:14 pm **Posts:** 28

Problem still remains, see below.

The first query I suggested:

Code:

update product set is_visible = is_visible_temp;

works fine in mysql. However, the query with the subselect does not work in mysql 5. From their Update documentation:

Quote:

Currently, you cannot update a table and select from the same table in a subquery.

http://dev.mysql.com/doc/refman/5.0/en/update.html

So I hadn't tested the subselect query in mysql 5, just assuming that it would work fine. I guess the newness of subselects in mysql 5 is showing.
Thanks for the suggestion to show the SQL. I should have used that before! I am using the Hibernate Tools in eclipse and was able to see that my query first was incorrect:

Code:

update Product p set p.isVisible where p.id in (select p.id from Product p where p.isVisibleTemp = true)

should have a value to set isVisible to like this:

Code:

update Product p set p.isVisible = true where p.id in (select p.id from Product p where p.isVisibleTemp = true)

Silly me. When I fixed that, the Hibernate Dynamic Query Translator gave me the SQL query that would be run. I tried to run that in mysql 5 and got the error I mentioned above. It looks like I'll have to try to get the other update to work.
So I tried to get this query to run in the Hibernate Console:

Code:

update Product p set p.isVisible = p.isVisibleTemp

First, the Hibernate Dynamic Query Translator translated the HQL into the following SQL:

Code:

update product set is_visible=is_visible_temp

When I run the HQL in the Hibernate Console, it does not work, though it returns no error. However when I run the translated SQL statement that it gives me on the mysql commandline, it works fine.
I guess my problem remains but for a different reason. Any idea as to why Hibernate gets the right SQL but that SQL doesn't update the records correctly?

damnhandy · **Posted:** Thu Apr 20, 2006 7:03 am

I don't know that the Hibernate Console can execute HQL updates. I have not been able to get that to work either. For these types of tests, I have only been able to execute them via code in a JUnit or other type of class. You can also enable SQL logging of your JUnits as well.That should reveal more as to what is happening.

Ryan-

JeremyG · **Joined:** Wed Jun 15, 2005 7:14 pm **Posts:** 28

BAH!
Bulk updates and cached copies will be the death of me.
After debugging through it with just a JDBC PreparedStatement I noticed that I was performing the bulk update before persisting some changes out to those same products that were in cache.
So... those products that were in cache were unaware of any bulk update changes so they reset a lot of the changes that were performed.
So now I am making sure that bulk updates occur before cached copies are retrieved and after cached copies have all been persisted.
Whew. Another world crisis has been resolved.

damnhandy · **Posted:** Thu Apr 20, 2006 8:19 pm

Oh really? So bulk updates using HQL do not purge/update the cache? That I was not aware of at all.

Ryan-

JeremyG · **Joined:** Wed Jun 15, 2005 7:14 pm **Posts:** 28

Yep, I can't immediately find it in the hibernate manual, but I read it somewhere just to be sure of the side-effects when I started using them. I tried to be careful when I was doing the bulk delete before getting the database stuff, but I had forgotten about it when it came time to update the stuff at the end of my process. It makes sense though - if one does a hibernate bulkUpdate or delete, should hibernate just flush itself and re-get all of the data it has in cache, because potentially much has changes with cascades and things not necessarily known to hibernate from the bulkUpdate query string.
Anyway, yeah - kind of crazy, but thanks for the insights.