Seting list in hibernate native query does not work

Question

I am new to Hibernate and based upon the documentation for native query, I expected following code to execute.

public boolean markStatusById(List<Integer> list, String newStatus) throws Exception {
        boolean statusUpdated = false;
        try {
            entityManager.createQuery("UPDATE MyTableEntity SET status = :newStatus WHERE my_table_id in (?1)",
                    MyTableEntity .class)
                    .setParameter(newStatus, newStatus)
                    .setParameter(1, list);
            statusUpdated = true;
        } catch (IllegalArgumentException | TransactionRequiredException e) {
            throw new Exception(CLASS_NAME,"markStatusById", e);
        }
        return statusUpdated;
    }

However, for some reason, this does not work.

What is the correct way to achieve what is intended in above query. One of My Reference

score 0 · Answer 1 · answered Dec 09 '17 at 15:43

0

Once you define your query, you need to run it. For an update use:

int updateCount = entityManager.executeUpdate();

A small tip on the side is, before you set the statusUpdated, you would want to check whether updateCount is > 0.

And one more tip based on the JPA recommendations, when you want to run a DML query (UPDATE / DELETE) it is advised to run that operation is a separate transaction. In your case, if you are using spring for example would be to set the propagation level on that method to REQUIRES_NEW:

@Transactional(propagation=Propagation.REQUIRES_NEW)
public boolean markStatusById(List<Integer> list, String newStatus)

this is due to the fact that those kind of queries ommit the persistence context which is not updated.

answered Dec 09 '17 at 15:43

Maciej Kowalski

25,605
12
54
63

Thanks for your reply and valuable suggestions. However, I was executing my query earlier too, just missed to place it in my question. The real issue is setting the list. If I run the same query with hardcoded IDs, it runs fine. The only issue is setting the IN parameter dynamically. – Saurabh Tiwari Dec 09 '17 at 16:30
did you try without the parentheses? also try with a named param instead of a positional – Maciej Kowalski Dec 09 '17 at 16:45
also you are mixing named and positional.. try to stick to one strategy – Maciej Kowalski Dec 09 '17 at 16:45
I tried without parenthesis , and also with named and positional parameters separately , but did not work. – Saurabh Tiwari Dec 09 '17 at 16:51
also, why do you pass the MyTableEntity .class as the second param? – Maciej Kowalski Dec 09 '17 at 17:11
and .. this is not a native query but a JPQL query as you use createQuery – Maciej Kowalski Dec 09 '17 at 17:12

Ramandeep Kaur · Answer 2 · 2018-07-23T11:04:40.197

0

you can use 

entityManager.createQuery("UPDATE MyTableEntity SET status = :newStatus WHERE my_table_id in (?1)",
                    MyTableEntity .class)
                    .setParameter(newStatus, newStatus)
                    .setParameterList(1, list);
            statusUpdated = true;

edited Jul 23 '18 at 11:04

answered Jul 23 '18 at 10:57

Ramandeep Kaur

111
1
9

Did this work for you ? I tried this but it didn't work. Also what is the "list" referring to in `.setParameter("list", list)` ? – Saurabh Tiwari Jul 23 '18 at 11:01
Can you share the structure of MyTableEntity class? – Ramandeep Kaur Jul 23 '18 at 11:07
It has just got an Id column and two String columns (status and name) and no dependency on any other table. – Saurabh Tiwari Jul 23 '18 at 11:10
entityManager.createQuery("UPDATE MyTableEntity SET status = :newStatus WHERE my_table_id in (:list)", MyTableEntity .class) .setParameter(newStatus, newStatus) .setParameterList(list, list); statusUpdated = true; – Ramandeep Kaur Jul 23 '18 at 11:25

Seting list in hibernate native query does not work

2 Answers2