JPA-Eclipselink caching nested tables (entities)-CodePudding

I am using Java JPA-Eclipselink persistence (v2.6.9) in combination with Apache Tomcat Web Server. I am fetching from database large amount of data from nested tables on each web service invocation. Data in the database is constantly being expanded with new data. We predict that number of fetches are going to increase, so we ran stress test and find out that expected traffic is going to cause database clogging. In order to avoid clogging, I would like to implement temporary caching in a way that eclipse link should fetch data and hold it for a minute in a cache and fetch new data after a minute. If I understood right I am looking for L2 caching in Eclipselink. I have tried several options, but eclipse link is always fetching data from database on each call of web service method.

Could you please help me make eclipselink use caching in intervals of 60 seconds?

Please find below my attempts.

Simplified enities

@Entity
@Cache(
type=CacheType.SOFT, // Cache everything until the JVM decides memory is low.
size=64000,  // Use 64,000 as the initial cache size.
expiry=36000000  // 10 minutes   
      )
@Table(name="SITUATION_DATA")
@NamedQuery(name="SituationData.findAll", query="SELECT s FROM SituationData s")
public class DatexSituationData implements Serializable {
private static final long serialVersionUID = 1L;

//bi-directional many-to-one association to SituationRecord
@OneToMany(mappedBy="datexSituationData", cascade = CascadeType.PERSIST, fetch = 
FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecord> situationRecords; 
}


@Entity
@Table(name="SituationRecord")
@NamedQuery(name="SituationRecord.findAll", query="SELECT s FROM SituationRecord s")
public class SituationRecord implements Serializable {
private static final long serialVersionUID = 1L;

@OneToMany(mappedBy="situationRecord", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecordComment> situationRecordComment;

@OneToMany(mappedBy="situationRecord", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecordTypeElement> situationRecordTypeElements;

//bi-directional many-to-one association to SituationLocation
@ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
@JoinColumn(name="ID_LOKACIJE")
private SituationLocation situationLocation;

//bi-directional many-to-one association to DatexSituationData
@ManyToOne()
@JoinColumns({
  @JoinColumn(name="SITUATION_ID", referencedColumnName="ID", nullable=false),
  @JoinColumn(name="SITUATION_VERSION", referencedColumnName="VERSION", nullable=false)
})
private DatexSituationData datexSituationData;  
}

 @Entity
@Table(name="SITUATION_LOCATIONS")
@NamedQuery(name="SituationLocation.findAll", query="SELECT s FROM SituationLocation s")
public class SituationLocation implements Serializable {
private static final long serialVersionUID = 1L;

@Id 
GeneratedValue(strategy=GenerationType.SEQUENCE, generator="situation_location_seq")
@SequenceGenerator(name="situation_location_seq", sequenceName="SEQ_SITUATION_LOCATION", 
allocationSize=1)
@Column(name="ID_LOKACIJE", unique=true, nullable=false)    
private long idLokacije;

//bi-directional many-to-one association to SituationRecord
@OneToMany(mappedBy="situationLocation", cascade = CascadeType.PERSIST) 
private List<SituationRecord> situationRecords; 
}

Fetching from database

String sQuery =         
        "SELECT ds FROM SituationData ds where CONCAT(ds.id.version, ds.id.id) in (select 
CONCAT(max(FUNC('TO_NUMBER', ds1.id.version)), ds1.id.id) from SituationData ds1 group by 
ds1.id.id)";        
            
EntityManager em = Emf.getInstance().getFactory().createEntityManager();    
Query q = em.createQuery(sQuery, DatexSituationData.class);                                 
    
q.setHint("eclipselink.join-fetch", "ds.situationRecords");         
q.setHint("eclipselink.join-fetch", "ds.situationRecords.situationLocation");
q.setHint("eclipselink.join-fetch", "ds.situationRecords.situationRecordTypeElements");
q.setHint("eclipselink.join-fetch", 
"ds.situationRecords.situationRecordGeneralPublicCommentMappings.
   situationRecordGeneralPublicComments");
q.setHint("eclipselink.sql.hint", "/*  leading(t1 t0) */");
        
q.setHint("eclipselink.query-results-cache", "true");
q.setHint("eclipselink.query-results-cache.expiry", "120000");
        
lResult = q.getResultList();

Persistence.xml

I have tried following properties

<!--property name="eclipselink.query-results-cache" value="true"/>
<property name="eclipselink.query-results-cache.expiry" value="120000"/>

<property name="eclipselink.cache.shared.default" value="true"/-->

<property name="eclipselink.cache.shared.default" value="false"/>
<property name="eclipselink.cache.shared.SituationData" value="true"/>
<property name="eclipselink.cache.type.SituationData" value="SOFT"/>
<property name="eclipselink.cache.size.SituationData" value="9400000"/>

CodePudding user response：

The settings you have put on your entity are for entity caching, but complex queries still need to use the database to figure out which entities match read all/list queries. You should still see improvements though as executing this query multiple times will use that entity cache - while the data is still 'fetched' from the DB, you are avoiding the overhead of building/refreshing instances. A better test might be to check the overheads involved in:

EntityManager em1 = Emf.getInstance().getFactory().createEntityManager();
EntityManager em2 = Emf.getInstance().getFactory().createEntityManager();  

DatexSituationData data1 = em1.find(DatexSituationData.class, 1L);
DatexSituationData data2 = em2.find(DatexSituationData.class, 1L);

The first find will go to the database and create instances to populate the shared and local caches, while the second should just use the shared cache to create a copy for its local EntityManager cache. This is a test that shows if your shared cache and its settings are working without other complexities.

Some issues with your settings and what you've shown though:

Entity class (and name) is "DatexSituationData" while you have named queries using "SituationData"
"eclipselink.cache.shared.SituationData" again is for SituationData
"eclipselink.cache.shared.default" is false. This turns off the cache for all entities, which will cause some confusion in your app as SituationData/DatexSituationData might be referenced in the shared cache, but its referenced SituationRecords won't forcing DB hits to load.

You've also set "query-results-cache" options as a query hint passed to the query. This is described in the docs and is what you may want, as it will allow the query results, based on the parameters used, to be cached. Limitations from the documentation:

Only named queries can have their results cached, dynamic queries cannot use the query results cache. As well, if you modify a named query before execution, such as setting hints or properties, then it cannot use the cached results.

So to test query caching, you should use only a named query with no changes to it:

EntityManager em1 = Emf.getInstance().getFactory().createEntityManager();
Query namedQuery = em.createNamedQuery("DatexSituationData.findAll", DatexSituationData.class);
namedQuery.getResultList();
em.clear();
namedQuery = em.createNamedQuery("DatexSituationData.findAll", DatexSituationData.class);
namedQuery.getResultList();

If you need to add query hints, such as for fetch joins and the query result caching itself, add them in the query definition:

@NamedQuery(name="DatexSituationData.complexFind", 
            query="SELECT ds fetch join ds.situationRecords FROM DatexSituationData ds where CONCAT(ds.id.version, ds.id.id) in (select 
CONCAT(max(FUNC('TO_NUMBER', ds1.id.version)), ds1.id.id) from SituationData ds1 group by 
ds1.id.id)",
            hints={@QueryHint(name="eclipselink.join-fetch", value="ds.situationRecords.situationLocation"), 
            @QueryHint(name="eclipselink.join-fetch", value="ds.situationRecords.situationRecordTypeElements"), 
            @QueryHint(name="eclipselink.join-fetch", value="ds.situationRecords.situationRecordGeneralPublicCommentMappings.situationRecordGeneralPublicComments"), 
            @QueryHint(name="eclipselink.sql.hint", value="/*  leading(t1 t0) */"), 
            @QueryHint(name="eclipselink.query-results-cache", value="true"), 
            @QueryHint(name="eclipselink.query-results-cache.expiry", value="120000")})//2 minutes

or add named queries in the runtime:

Query namedQueryToSave = em.createQuery(yourQueryString, DatexSituationData.class); 
//add all hints

em.getEntityManagerFactory().addNamedQuery("DatexSituationData.complexFind", namedQueryToSave);
em.close();

Either way, then you can execute the query. Using the previous test in a single em using "DatexSituationData.complexFind" should then show a hit to the database on the first run, but not the second - as long as they are less than 2 minutes apart.