6

I'm running into an issue where my query method is in a foreach loop, and each time I'm passing in a different parameter to retrieve different information. However, after the FIRST iteration of the loop, the query data gets cached (I think) and returns the same data for subsequent loops.

Here is my code:

@Transactional(readOnly = true) public List<InitiativeReport> getInitiativeReports() throws Exception { try { List<InitiativeReport> ir = new ArrayList<InitiativeReport>(); List<Initiative> in = initiativeRepository.findAll(); for(Initiative i : in) { i.getTheme().getId(); // lazy initialize InitiativeReport report = new InitiativeReport(); report.setId(i.getId()); report.setInitiativeId(i.getInitiativeId()); report.setName(i.getName()); report.setTheme(i.getTheme()); // this is the call to the query, which is cached after the first iteration List<InitiativeProfileQuestion> q = initiativeProfileQuestionRepository.getQuestionsAndAnswerLogs(i.getInitiativeId()); report.setQuestions(q); ir.add(report); } return ir; } catch (Exception e) { throw new Exception(e); }

Here is my repository interface:

public interface InitiativeProfileQuestionRepository extends JpaRepository<InitiativeProfileQuestion, Long> { @Query("select distinct q from InitiativeProfileQuestion q " + "left join fetch q.answers " + "left join fetch q.answerLogs al " + "where al.initiative.initiativeId = ?1 " + "and al.revision = al.initiative.revision + "order by q.question asc") public List<InitiativeProfileQuestion> getQuestionsAndAnswerLogs(String initiativeId); }

Here is my application.yml file:

spring: datasource: dataSourceClassName: com.mysql.jdbc.jdbc2.optional.MysqlDataSource url: jdbc:mysql://localhost/testdb username: root password: XXXXXXXXX driverClassName: com.mysql.jdbc.Driver testOnBorrow: true validationQuery: SELECT 1 jpa: database-platform: org.hibernate.dialect.MySQLInnoDBDialect database: MYSQL openInView: false show_sql: true generate-ddl: false hibernate: ddl-auto: none naming-strategy: org.hibernate.cfg.EJB3NamingStrategy

The issue is very similar to a post I found here: Native Query (JPA ) not reset and return the same old result

However, that user is using EntityManager and I have no implementation for EntityManager in my application- I'm letting JPA do all the work and only have query annotations.

Any assistance would be appreciated!

Improve this question
6
  • Show us the class InitiativeReport Commented Sep 11, 2015 at 16:27
  • I've added the InitiativeReport and InitiativeProfileQuestion above Commented Sep 11, 2015 at 16:37
  • and the SQL invoked is? in the log, which likely would tell you if the right params are passed in to it Commented Sep 11, 2015 at 17:03
  • The SQL is correct. I know that because if I do separate GET requests that each run the query method once, they return the correct value. However, doing them all in a single GET request (with the for loop) tends to cache the result. I've also tested the query that is shown in the log with different parameters and it works. So there's some kind of weird caching going on somewhere- I just don't know where to find it, or what I can do to disable it. Commented Sep 11, 2015 at 17:10
  • I think the application may be using EntityManager for my JPA Queries, which is implementing a Level 1 Cache. I'm not sure how to clear it since I'm not explicitly calling EntityManager anywhere in my spring application. Commented Sep 11, 2015 at 17:53

4 Answers 4

Reset to default
15

Little late to the party but for those finding this now here is what you need to do to solve the issue:

When you’re querying in a loop that has already started a transaction you need to detach the entities returned from queries inside that loop that share the same id but may have different data.

Here’s an example:

@Service public class ProductService { @Autowired private ProductRepository productRepository; @Autowired private ProductWarehouseRepository productWarehouseRepository; @Autowired private EntityManager entityManager; @Transactional public List<Product> getProducts(){ List<Product> products = this.productRepository.findAll(); products.forEach(product -> { List<ProductWarehouse> warehouses = this.productWarehouseRepository.findAllByProductId(product.getId()); warehouses.forEach(warehouse -> { //THIS IS THE IMPORTANT PART //You have to detach the entity from the session this.entityManager.detach(warehouse); }); product.setWarehouses(warehouses); }); return products; } }

In this example product A can be in warehouse id 1, and so can product B, but they may have different quantities available in the warehouse.

You have to detach the entities from the session when the results returned may have collisions on the @Id column. This has to do with the way level 1 caching works in Hibernate. You can check out this link for a little more info http://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html/objectstate.html

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

2 Comments

Just adding my two cents to this answer: this is also valid on EclipseLink. Cheers.
Awesome, works like magic.
4

I ran into a very similar problem. I resolved it by changing my Entity class to add an additional @Id annotation to accurately reflect the composite primary key on the database table. You may want to give that a try.

Alternatively, take a look at my recent answer to the question you mention above (Native Query (JPA ) not reset and return the same old result). As you note, it has some similarities to this question.

Also, your InitiativeProfileQuestion shows @Id on variable id. Looks like you are querying by initiativeId. Perhaps you should be querying by id since it's annotated as the primary key.

Note: I don't understand why someone deleted this initially as not answering the question. I tried posting it as a comment, but I don't have enough reputation points to do so. Nonetheless, I do believe what I'm suggesting may resolve the questioner's problem, so I consider it an answer. I grant that it could be interpreted more as suggestions than an answer. But in truth I wouldn't call it strictly a comment, either. Shouldn't the questioner be the one to weigh in on whether it's an answer? And does my answer really fall into any of these criteria?: https://stackoverflow.com/help/deleted-answers.

Improve this answer

1 Comment

Also Late to this but in my case I had a JpaRepository extended interface, So I too didn't have easy access to an EntityManager. The query returned a list of Pojos. I was using a native query with several joins so my Pojo didn't have a true \@Id or so I thought. I was getting cached results with just one \@Id specified. I needed to use a composit-id. Once I added the IdClass with \@Embedded my issue was resolved. Hope this helps
4

I had the same problem, and I searched many web pages, and couldn't found an exact solution. Then I found the following approach can solve the problem.

Calling entityManager.clear() before querying can resolve this problem.

Improve this answer

Comments

0

It may be due to the @Id in entity is not correct. The repo will automatically cache the data with the @Id. To solve this issue. You may consider using @IdClass to address this composite key issue.

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.