Running a nativeQuery in a loop doesn't return correct data

Question

I am trying to run native query in a loop, the query displays the correct sql syntax but the output is always the same.

    for (int i=0; i<translations.size(); i++) {
        Query query = entityManager.createNativeQuery("Select * from " + translations.get(i).getName(), MyModel.class);
        rows = (List<MyModel>)query.getResultList();
        // rest of the function...
    }

now in the console I can see the Hibernate statements like:

Hibernate: Select * from translation1
Hibernate: Select * from translation2
Hibernate: Select * from translation3

but the variable "rows" always contains the result of the first select statement i.e. rows of translation1 table.

Any ideas why in the console it shows that it is selecting from other tables too but in reality it always gets data from translation1 table?

not really possible to answer this without seeing everywhere else that rows is used. For example, why isn't rows declared within the for loop? — matt b
– matt b, Commented Oct 18, 2011 at 19:41
@axtavt yes all tables have same ids and same column names except for one column, rowText, this contains the text in different languages depending on the transalation table. — Rizwan
– Rizwan, Commented Oct 18, 2011 at 20:05

axtavt · Accepted Answer · 2011-10-18 20:11:25Z

2

If all tables have the same set of ids, it's an expected behaviour.

Hibernate session cache guarantees that there can be only one instance of an entity of a particular type with a particular id inside a session. Since entities are resolved via the session cache even in the case of a native query, you get the same instances.

So, you have several options:

rethink your database shema
construct objects from query result manually
forcibly clear the session cache by calling clear() or detach()

answered Oct 18, 2011 at 20:11

axtavt

243k42 gold badges518 silver badges486 bronze badges

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

2 Comments

Rizwan Over a year ago

thanks for the help, I can't change db schema, entityManager.clear() fixed the issue.

axtavt Over a year ago

@user908452: Note that clear() removes all entities from the session cache, that may create unexpected side effects if some of these entities was loaded by different methods. Perhaps it would be better to use detach() to explicitly remove only the entities you just loaded.

Shivan Dragon · Accepted Answer · 2011-10-18 19:38:03Z

0

Are you sure that the rows variable doesn't actually contain the LAST of your returned results? how do you initialize rows and what do you do with the variable inside the loop? If you want to get all the results from all the queries you have to add each one (inside the loop) to a list/set variable declared BEFORE the loop starts.

Or you could use some proper SQL and do:

SELECT * FROM Table1 [JOIN/UNION] Table2 etc...

answered Oct 18, 2011 at 19:38

Shivan Dragon

15.2k10 gold badges69 silver badges104 bronze badges

1 Comment

Rizwan Over a year ago

I am sure the rows variable does NOT contain the last data, I was initializing it outside of the loop - but I tried to initializing it inside of the loop too and still it didn't work. I iterate over the rows ArrayList and add it to a Map of a Map.

Collectives™ on Stack Overflow

Running a nativeQuery in a loop doesn't return correct data

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related