Why L2 cache doesn't work in Spring Boot?

Hibernate ORM
1

I do not understand why my Hibernate L2 cache doesn’t work. I have Spring Boot application with following configuration

 jpa:
    database-platform: org.hibernate.dialect.PostgreSQL10Dialect
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        cache :
          use_second_level_cache: true
          region :
            factory_class: org.hibernate.cache.ehcache.EhCacheRegionFactory
          use_query_cache : true
          provider_class : net.sf.ehcache.hibernate.SingletonEhCacheProvider
          generate_statistics : true
          use_structured_entries : true

Following entity mush be cached

@Entity
@Table(name = "employees", schema = "security", catalog = "")
//@SQLDelete(sql = "update security.employees set deleted_ts = current_timestamp where id=?")
//@Where(clause = "deleted_ts is null")
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
class Employee : BaseUuidEntity(), CompanyRelated,UserRelated{

    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    var user: User? = null

    @ManyToOne
    @JoinColumn(name = "company_id")
    var company: Company? = null

    @Column(name = "owner")
    var owner: Boolean? = null

    @ManyToOne
    @JoinColumn(name = "role_id")
    var role: Role? = null

    @ElementCollection
    @CollectionTable(
        name = "employee_project",
        schema = "security",
        joinColumns = [JoinColumn(name = "employee_id")]
    )
    @Column(name = "project_id")
    var projectIds: MutableSet<UUID>? = null


    override fun getObjUserId(): UUID? {
        return user?.id
    }

    override fun getObjCompanyId(): UUID? {
        return company?.id
    }

}

But in logs in second page request I have repeated query:

Hibernate: 
    select
        employee0_.id as id1_37_,
        employee0_.created_by as created_2_37_,
        employee0_.created_ts as created_3_37_,
        employee0_.deleted_by as deleted_4_37_,
        employee0_.deleted_ts as deleted_5_37_,
        employee0_.updated_by as updated_6_37_,
        employee0_.updated_ts as updated_7_37_,
        employee0_.company_id as company_9_37_,
        employee0_.owner as owner8_37_,
        employee0_.role_id as role_id10_37_,
        employee0_.user_id as user_id11_37_ 
    from
        security.employees employee0_ 
    left outer join
        security.employee_project projectids1_ 
            on employee0_.id=projectids1_.employee_id 
    where
        projectids1_.project_id=?

The queries are called this way

...
employeeRepository.findByProjectIds(project.id!!)
...
interface EmployeeRepository : JpaSpecRepository<Employee> {
...
    @PostFilter("hasObjectPermission(filterObject, 'Employee', 'View')")
    fun findByProjectIds(projectId: UUID): MutableList<Employee>
...

You can see full log here L2 cache doesn't work · GitHub Please teach me how to turn on Hibernate L2 cache?

Probably a bit more TRACE level logs make situation clean:

2022-07-31 10:41:01,085 TRACE [http-nio-8080-exec-8] org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [1] as [OTHER] - [dcc5ff7b-ec8b-45d7-be0f-bcf6a55f1206]
2022-07-31 10:41:01,086 DEBUG [http-nio-8080-exec-8] org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler: Filtering with expression: hasObjectPermission(filterObject, 'Employee', 'View')
2022-07-31 10:41:01,087 DEBUG [http-nio-8080-exec-8] org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler: Filtering collection with 0 elements
2022-07-31 10:41:01,087 DEBUG [http-nio-8080-exec-8] org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler: Retaining elements: []
2022-07-31 10:41:01,088 DEBUG [http-nio-8080-exec-8] org.hibernate.engine.jdbc.spi.SqlStatementLogger: 
    select
        employee0_.id as id1_37_,
        employee0_.created_by as created_2_37_,
2

See the Hibernate documentation for details about how to enable query caching.

As for Spring Data, I guess you will have to add a query hint somehow. Something like this:

@QueryHints(
  value = { @QueryHint(name = "org.hibernate.cacheable", value = "true")},
  forCounting = false)
3

This answer is correct. But I have uncommented

@SQLDelete(sql = "update security.employees set deleted_ts = current_timestamp where id=?")
@Where(clause = "deleted_ts is null")

And L2 cache works unstable - sometimes it caches everything, sometimes two “project_id” are missed in cache and logs shows queries. Why cache is unstable?

4

I don’t know what you mean by “unstable”, but do you understand that Hibernate manages the cache behind the scenes for you? It will invalidate entries if they become possibly invalid due to insert/update/delete statements. Maybe you configured a TTL for your cache which will remove entries after some fixed time?