Criteria query cache caching in Eclipselink?

Question

Iam using criteria queries with EclipseLink as JPA . I need to cache the query results based on their parameters . When I used query.setHint("eclipselink.QUERY_RESULTS_CACHE", "TRUE") it still fires query to the database. How can I use it in context of criteria queries ?

Here I set my query and their hints and addNamedQuery

Query query = psEntityManager.getEntityManager().createQuery(criteriaQuery);// Creating Query to supply Values
        query.setHint("eclipselink.QUERY_RESULTS_CACHE", "TRUE");
        query.setHint(QueryHints.QUERY_TYPE,QueryType.ReadObject);
        psEntityManager.getEntityManager().getEntityManagerFactory().addNamedQuery("query1", query);

Here is my output :

Query1-----------
[EL Fine]: sql: 2013-10-10 21:33:35.495--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, ADDRESSLINE1, ADDRESSLINE2, PHONENUMBER, fkCityId, fkPersonId FROM ADDRESS WHERE (fkPersonId = ?)
    bind => [2]
[EL Fine]: sql: 2013-10-10 21:33:35.527--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, CITYNAME, PINCODE, fkStateId FROM CITY WHERE (PRIMARYKEY = ?)
    bind => [2]
[EL Fine]: sql: 2013-10-10 21:33:35.528--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, STATENAME FROM STATE WHERE (PRIMARYKEY = ?)
    bind => [1]
[EL Fine]: sql: 2013-10-10 21:33:35.531--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, AGE, DOB, FIRSTNAME, LASTNAME, SEX, TIMESTAMP, fkDepartmentId FROM PERSON WHERE (PRIMARYKEY = ?)
    bind => [2]
[EL Fine]: sql: 2013-10-10 21:33:35.541--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, DEPTNAME FROM DEPARTMENT WHERE (PRIMARYKEY = ?)
    bind => [2]
[EL Fine]: sql: 2013-10-10 21:33:35.547--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, CITYNAME, PINCODE, fkStateId FROM CITY WHERE (PRIMARYKEY = ?)
    bind => [5]
[EL Fine]: sql: 2013-10-10 21:33:35.548--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, STATENAME FROM STATE WHERE (PRIMARYKEY = ?)
    bind => [3]
[EL Fine]: sql: 2013-10-10 21:33:35.551--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, CITYNAME, PINCODE, fkStateId FROM CITY WHERE (PRIMARYKEY = ?)
    bind => [8]
[EL Fine]: sql: 2013-10-10 21:33:35.553--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, STATENAME FROM STATE WHERE (PRIMARYKEY = ?)
    bind => [4]
Query2-----------
[EL Fine]: sql: 2013-10-10 21:33:35.557--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, ADDRESSLINE1, ADDRESSLINE2, PHONENUMBER, fkCityId, fkPersonId FROM ADDRESS WHERE (fkPersonId = ?)
    bind => [2]
Query3-----------
[EL Fine]: sql: 2013-10-10 21:33:35.56--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, ADDRESSLINE1, ADDRESSLINE2, PHONENUMBER, fkCityId, fkPersonId FROM ADDRESS WHERE (fkPersonId = ?)
    bind => [2]
Query4-----------
[EL Fine]: sql: 2013-10-10 21:33:35.563--ServerSession(14144548)--Connection(7200601)--SELECT PRIMARYKEY, ADDRESSLINE1, ADDRESSLINE2, PHONENUMBER, fkCityId, fkPersonId FROM ADDRESS WHERE (fkPersonId = ?)
    bind => [2]

Are you storing this query as a named query? The EMF in JPA 2.1 provides addNamedQuery to store named queries dynamically — Chris, Oct 10 '13 at 14:30
execute the same query with the same parameters in the same context multiple times in a row - does it hit the database each time? As for links, check the JPA 2.1 spec. You can create the query and add it to the EMF using addNamedQuery so it can be looked up and reused with the createNamedQuery api. — Chris, Oct 10 '13 at 15:35
@Chris thanks!! I did but not working ,it still fired queries multiple times. Any other solution please.. — Achyut, Oct 10 '13 at 15:46
can you show the code and what you are using to verify the results? — Chris, Oct 10 '13 at 16:00
@Chris I updated please see edits (query1 ,query2, ... )are the queries fired multiple times bind for the same value — Achyut, Oct 10 '13 at 16:06

score 1 · Accepted Answer · answered Oct 10 '13 at 16:32

1

Remove the query.setHint(QueryHints.QUERY_TYPE,QueryType.ReadObject), or call it first. The query_type changes the underlying query object, and not all the hints might be copied into the new object. It seems unnecessary anyway.

answered Oct 10 '13 at 16:32

Chris

20,138
2
29
43

no effect after removing it. I am using this code block whenever I create query using criteia . – Achyut Oct 10 '13 at 16:43
you didn't show how you are executing the query multiple times. – Chris Oct 10 '13 at 17:41
I mean for each query Iam creating the query using criteria builder and use the above code block and then getResult from that. – Achyut Oct 10 '13 at 17:46
This means you are not reusing your named query. Create the query once, execute it multiple times. em.createNamedQuery("query1") – Chris Oct 10 '13 at 18:14
Yes! it is creating the query again and again and set hints for it, got it thank you very much!! – Achyut Oct 10 '13 at 18:18

Criteria query cache caching in Eclipselink?

1 Answers1