JPA Query Returning Same Result for different Parameter

Question

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!

I've added the InitiativeReport and InitiativeProfileQuestion above — user3397214
– user3397214, 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 — Neil Stockton
– Neil Stockton, 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. — user3397214
– user3397214, 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. — user3397214
– user3397214, Commented Sep 11, 2015 at 17:53

Woodchuck · Accepted Answer · 2021-05-31 13:52:37Z

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

edited May 31, 2021 at 13:52

Woodchuck

4,5623 gold badges53 silver badges97 bronze badges

answered Mar 23, 2018 at 14:32

phpslightly

3243 silver badges6 bronze badges

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

2 Comments

CarlosGoncalves Over a year ago

Just adding my two cents to this answer: this is also valid on EclipseLink. Cheers.

Ayejuni Ilemobayo Kings Over a year ago

Awesome, works like magic.

Community · Accepted Answer · 2017-05-23 12:34:54Z

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.

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Apr 7, 2016 at 0:20

Woodchuck

4,5623 gold badges53 silver badges97 bronze badges

1 Comment

Heath Lilley Over a year ago

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

Paul Roub · Accepted Answer · 2018-08-29 00:49:30Z

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.

edited Aug 29, 2018 at 0:49

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Aug 29, 2018 at 0:10

Anndy Dong

511 silver badge5 bronze badges

Comments

Amos Yau · Accepted Answer · 2025-07-23 09:33:53Z

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.

answered Jul 23 at 9:33

Amos Yau

1

1 Comment

Community Jul 23 at 9:36

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.

Collectives™ on Stack Overflow

JPA Query Returning Same Result for different Parameter

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related