0

I run following code intend to update the least record in the table on Hibernate 3.6.7 final (JPA 2.0?) :

Query query = em.createQuery("UPDATE MyTable a SET a.isEnable=1 WHERE a.isEnable=0 ORDER BY a.id DESC").setMaxResults(1);
query.executeUpdate();

but hibernate ignores ORDER BY when generating sql.

Is ORDER BY for SELECT use only in JPQL? How to execute UPDATE query with ORDER BY in JPA?

thanks for any help.

petertc
  • 3,607
  • 1
  • 31
  • 36
  • 4
    Why do you need to update the entries in a special order? That should be of no use... – V G Dec 18 '13 at 09:29
  • Absolutely. The update should be considered atomic, so that state of the database when the update is in progress (some rows updated, some not yet) will never be visible to you. You'll only see the end result, and the order in which they were updated cannot possibly be of any interest. – NickJ Dec 18 '13 at 09:32
  • 1
    UPDATE ... ORDER BY is meaningless. And it's not valid SQL. Think carefully about what UPDATE does. You have a WHERE clause, that matches some subset of the rows in your table. You change the value of some field in those rows, all in a single atomic operation. What would ORDER BY do? There's nothing that it could reasonably mean. – Dawood ibn Kareem Dec 18 '13 at 09:34
  • I want to update just one row in the table with the biggest id number. I learn this statement from [the answer](http://stackoverflow.com/a/9220572/1271412), and it works on my MySQL client. – petertc Dec 18 '13 at 09:39
  • @okwap why didn't you ask exactly that question? Because what you tried to do makes no sense... – V G Dec 18 '13 at 10:14
  • @AndreiI I have described the requirement in my original question: "update the least record in the table". – petertc Dec 19 '13 at 12:20
  • I gave you an answer. The title of the question is really bad, that's why you didn't get an answer so far. Also vote the answers that you get to your questions and accept them! – V G Dec 19 '13 at 12:41

1 Answers1

0

To update the record with the last ID in a table you do the following:

TypedQuery<MyEntity> query = em.createQuery("SELECT a FROM MyEntity a WHERE a.isEnable=0 ORDER BY a.id DESC", MyEntity.class);
query.setMaxResults(1);

List<MyEntity> resultList = query.getResultList();
if (resultList.size()>0) {
    resultList.get(0).setEnabled(true);
    //eventually you can to em.flush();
}
V G
  • 18,822
  • 6
  • 51
  • 89
  • It works will, except I have better to add query.setLockMode(LockModeType.PESSIMISTIC_WRITE) before query.getResultList() to prevent race condition problems. Besides, its not really ideal to run two queries instead of one update operation. – petertc Dec 20 '13 at 06:38