How to use Hibernate second level cache with JPA?

Question

I implement an attribute-based persistence mechanism based on an entity. All access to the database is through Hibernate. I have a table containing paths for nodes, it is extremely simple, just id and path (string). The paths will be small in number, about a few thousand.

There are millions of rows in the main table, and instead of repeating the paths, I normalized the paths to their own table. Following is the behavior I want when pasting into the main table

1) Check if the path exists in the path table (query through the entity manager using the path value as a parameter)

2) if it does not exist, inserts and receives the identifier (saved through the entity manager)

3) enter id as the value of the foreign key in the main row of the table and paste it into the main table.

This will happen thousands of times for a set of domain objects that correspond to many rows in the main table and some other tables. Thus, the steps described above are repeated using a single transaction as follows:

EntityTransaction t = entityManager.getTransaction(); t.begin(); //perform steps given above, check, and then persist etc.. t.commit(); 

When I complete step 2, it introduces a huge performance hit for the overall operation. This is a begging for caching, because after some time this table will contain no more than 10-20 thousand records with very rare new inserts. I tried to do this with Hibernate and lost almost 2 days.

I am using Hibernate 4.1, with JPA and ECache annotations. I tried to enable query caching, even using the same request object in all the inserts, as shown below:

 Query call = entityManager.createQuery("select pt from NodePath pt " + "where pt.path = :pathStr)"); call.setHint("org.hibernate.cacheable", true); call.setParameter("pathStr", pPath); List<NodePath> paths = call.getResultList(); if(paths.size() > 1) throw new Exception("path table should have unique paths"); else if (paths.size() == 1){ NodePath path = paths.get(0); return path.getId(); } else {//paths null or has zero size NodePath newPath = new NodePath(); newPath.setPath(pPath); entityManager.persist(newPath); return newPath.getId(); } 

The NodePath object is annotated as follows:

 @Entity @Cacheable @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) @Table(name = "node_path", schema = "public") public class NodePath implements java.io.Serializable { 

The query cache is used, as far as I can see from the statistics, but not used for the second level cache:

 queries executed to database=1 query cache puts=1 query cache hits=689 query cache misses=1 .... second level cache puts=0 second level cache hits=0 second level cache misses=0 entities loaded=1 .... 

A simple handwritten cache hash table works as expected, drastically reducing overall time. I assume that I cannot start Hibernate caching due to the nature of my operations.

How to use the second level cache of sleep mode with this setting? For the record, this is my xml persistence:

http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd "version =" 2.0 ">

 <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>...</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode> <properties> <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" /> <property name="hibernate.connection.password" value="zyx" /> <property name="hibernate.connection.url" value="jdbc:postgresql://192.168.0.194:5432/testdbforml" /> <property name="hibernate.connection.username" value="postgres"/> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> <property name="hibernate.search.autoregister_listeners" value="false"/> <property name="hibernate.jdbc.batch_size" value="200"/> <property name="hibernate.connection.autocommit" value="false"/> <property name="hibernate.generate_statistics" value="true"/> <property name="hibernate.cache.use_structured_entries" value="true"/> <property name="hibernate.cache.use_second_level_cache" value="true"/> <property name="hibernate.cache.use_query_cache" value="true"/> <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory"/> </properties> 

Answer 1

Ok, I found it. My problem was that in the cached request, only the query result identifiers were stored in the cache, and it (probably) returned to db to get the actual values, and not to get them from the second level cache.

The problem, of course, is that the request did not put these values ​​in the second-level cache, because they were not selected using the primary identifier. So the solution is to use a method that will put the values ​​in the second level cache, and with hibernate 4.1 I will be able to do this using a natural id. Here is a function that either inserts or returns a value from the cache, just in case it helps someone else:

 private UUID persistPath(String pPath) throws Exception{ org.hibernate.Session session = (Session) entityManager.getDelegate(); NodePath np = (NodePath) session.byNaturalId(NodePath.class).using("path", pPath).load(); if(np != null) return np.getId(); else {//no such path entry, so let create one NodePath newPath = new NodePath(); newPath.setPath(pPath); entityManager.persist(newPath); return newPath.getId(); } }