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!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With