How can I execute an update (named query) in Hibernate Template?

Question

I have a namedQuery like this:

@NamedQueries ({ ...

@NamedQuery(name = "myUpdate", query = "update User set country = 'EN' where user.id = :id")

... })

In dao layer

getHibernateTemplate().bulkUpdate(...?)

UPDATE

Query query = sessionFactory.getCurrentSession.getNamedQuery("myUpdate");
getHibernateTemplate.bulkUpdate(query.getQueryString(), id);

I get an error:

Hibernate: update User, set country=EN where id = 2343 ORA-00971: missing SET keyword

Anybody now how can resolve this problem?

UPDATE 2

@NamedQuery(name = "myUpdate", query =
 "update User set country = 'EN' where
 user.profile.id = ?")

OK

  @NamedQuery(name = "myUpdate", query =
     "update User set country = 'EN' where
     user.profile.name = ?")

NOT OK :(

Augusto · Accepted Answer · 2011-03-20 10:52:33Z

4

Unfortunately, that feature is missing in spring, as the named queries are supposed to be used only to retrieve data. One thing you can do is (this is a bit of a work around)

Session session = getHibernateTemplate().getSession();
Query query = session.getNamedQuery("myUpdate");
String update = query.getQueryString();
getHibernateTemplate().bulkUpdate(update, [params]);

I would put that in some kind of helper, so your DAO logic doesn't have to go around spring too.

edit

there's a dangling comma between User and set "update User , set country=EN where"

answered Mar 20, 2011 at 10:52

Augusto

30.2k6 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tomasz-mer Over a year ago

It's works. Thanks! But unfortually I have another problem. See UPDATE

tomasz-mer Over a year ago

That is ridiculous. I do not have a comma there. The query is exactly the same as the Stack. :(

Augusto Over a year ago

if hibernate is adding that, then it's very odd. Can you get the result of query.getQueryString()?. And if that's correct, try the query update User set country = 'EN' where id = :id

Dominik · Accepted Answer · 2018-07-04 10:42:23Z

0

Actually this is a very old question but I had the same problem today. I realized that the update does not work since you cannont have a join inside of a simple UPDATE. That is also the reason why the comma is added. Hibernate tries to rewrite the query like this:

UPDATE User u, Profile p SET u.country = 'EN' where p.name = ? AND p.id = u.profile.id

To solve the issue you need to select the ids from the second table yourself.

@NamedQuery(name = "myUpdate", query = ""
  + " UPDATE User u "
  + " SET country = 'EN' "
  + " WHERE u.profile.id IN ( "
  + "   SELECT p.id "
  + "   FROM Profile p "
  + "   WHERE p.name = ? "
  + " )"

answered Jul 4, 2018 at 10:42

Dominik

8199 silver badges20 bronze badges

Collectives™ on Stack Overflow

How can I execute an update (named query) in Hibernate Template?

2 Answers 2

edit

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

edit

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related