Up-to-Date Cache With EclipseLink and Oracle

One of the most useful features provided by ORM libraries is a second-level cache, usually called L2. An L2 object cache reduces database access for entities and their relationships. It is enabled by default in the most popular JPA implementations like Hibernate or EclipseLink. That won’t be a problem unless a table inside a database is not modified directly by third-party applications, or by the other instance of the same application in a clustered environment. One of the available solutions to this problem is an in-memory data grid, which stores all data in memory, and is distributed across many nodes inside a cluster. Such a tool like Hazelcast or Apache Ignite have been described several times in my blog. If you are interested in one of that tools I recommend you read one of my previous article about it: Hazelcast Hot Cache with Striim.

However, we won’t discuss it in this article. Today, I would like to talk about the Continuous Query Notification feature provided by Oracle Database. It solves a problem with updating or invalidating a cache when data changes in the database. Oracle JDBC drivers have provided support for it since 11gRelease 1. This functionality is based on receiving invalidation events from the JDBC drivers. Fortunately, EclipseLink extends that feature in their solution called EclipseLink Database Change Notification. In this article, I’m going to show you how to implement it using Spring Data JPA together with the EclipseLink library.

How it Works

The most useful functionality provided by the Oracle Database Continuous Query Notification is an ability to raise database events when rows in a table are modified. It enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. To detect modifications, EclipseLink DCN uses Oracle ROWID to intercept changes in the table. A ROWID is included in all queries for a DCN-enabled class. EclipseLink also retrieves a ROWID of the saved entity after an insert operation, and maintains a cache index on that ROWID. It also selects the database transaction ID once for each transaction to avoid invalidating the cache during the processing of a transaction.

When a database sends a notification, it usually contains the following information:

• Names of the modifying objects, for example the name of a changed table.
• Type of change. The possible values are INSERT, UPDATE, DELETE, ALTER TABLE, or DROP TABLE.
• Oracle’s ROWID of the changed record.

Running Oracle Database Locally

Before starting working on our sample application, we need to have an Oracle database installed. Fortunately, there are some Docker images with Oracle Standard Edition 12c. The command visible below starts Oracle XE and exposes it on the default 1521 port. It is also possible to use the web console available under port 9080.

$ docker run -d --name oracle -p 9080:8080 -p 1521:1521 sath89/oracle-12c

We need to have a sysdba role in order to be able to grant the privilege CHANGE NOTIFICATION to our database. The default password for the user system is oracle.

GRANT CHANGE NOTIFICATION TO PIOMIN;

You may use any Oracle client, like Oracle SQL Developer, to connect with a database or just log into a web console. Since I run Docker on Windows, it is available on my laptop under the address http://192.168.99.100:9080/em. Of course, it is Oracle, so you need to settle in for a long haul and wait until it starts. You can observe the progress of the installation by running the command docker logs -f oracle. When you finally see a “100% complete” log entry, you may grant the required privileges to the existing user or create a new one with a set of needed privileges and proceed to the next step.

Sample Application

The sample application source code is available on GitHub under the address https://github.com/piomin/sample-eclipselink-jpa.git. It is a Spring Boot application that uses Spring Data JPA as a data access layer implementation. Because the default JPA provider used in that project is EclipseLink, we should remember to exclude Hibernate libraries from the starters spring-boot-starter-data-jpa and spring-boot-starter-web. Besides a standard EclipseLink library for JPA, we also have to include an EclipseLink implementation for Oracle database (org.eclipse.persistence.oracle) and the Oracle JDBC driver.

<dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.jpa</artifactId>
    <version>2.7.1</version>
</dependency>
<dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.oracle</artifactId>
    <version>2.7.1</version>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.1</version>
</dependency>

The next step is to provide connection settings to Oracle databases launched as a Docker container. Do not try to do it through application.yml properties because Spring Boot by default uses HikariCP for connection pooling. This, in turn, causes a conflict with Oracle datasources during application bootstrap. The following datasource declaration would work successfully.

@Bean
public DataSource dataSource() {
    final DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    dataSource.setUrl("jdbc:oracle:thin:@192.168.99.100:1521:xe");
    dataSource.setUsername("piomin");
    dataSource.setPassword("Piot_123");
    return dataSource;
}

EclipseLink With Database Change Notification

EclipseLink needs some specific configuration settings to successfully work with Spring Boot and Spring Data JPA. These settings may be provided inside the @Configuration class that extends the JpaBaseConfiguration class. First, we should set EclipseLinkJpaVendorAdapter as the default JPA vendor adapter. Then, we may configure some additional JPA settings like detailed logging level or automatic creation of database objects during application startup. However, the most important things for us in the fragment of source code visible below are the Oracle Continuous Query Notification settings.

EclipseLink CQN support is enabled by the OracleChangeNotificationListener listener, which integrates with Oracle JDBC in order to received database change notifications. The full class name of the listener should be passed as a value of the eclipselink.cache.database-event-listener property. EclipseLink, by default, enables L2 caching for all entities, and respectively all tables in the persistence unit are registered for a change notification. You may exclude some of them by using the databaseChangeNotificationType attribute of the @Cache annotation on the selected entity.

@Configuration
@EnableAutoConfiguration
public class JpaConfiguration extends JpaBaseConfiguration {

    protected JpaConfiguration(DataSource dataSource, JpaProperties properties, ObjectProvider jtaTransactionManager, ObjectProvider transactionManagerCustomizers) {
        super(dataSource, properties, jtaTransactionManager, transactionManagerCustomizers);
    }

    @Override
    protected AbstractJpaVendorAdapter createJpaVendorAdapter() {
        return new EclipseLinkJpaVendorAdapter();
    }

    @Override
    protected Map getVendorProperties() {
        HashMap map = new HashMap();
        map.put(PersistenceUnitProperties.WEAVING, InstrumentationLoadTimeWeaver.isInstrumentationAvailable() ? "true" : "static");
        map.put(PersistenceUnitProperties.DDL_GENERATION, "create-or-extend-tables");
        map.put(PersistenceUnitProperties.LOGGING_LEVEL, SessionLog.FINEST_LABEL);
        map.put(PersistenceUnitProperties.DATABASE_EVENT_LISTENER, "org.eclipse.persistence.platform.database.oracle.dcn.OracleChangeNotificationListener");
        return map;
    }

}

It is worth mentioning that EclipseLink’s CQN integration has some important limitations:

• Changes to an object’s secondary tables will not trigger it to be invalidated unless a version is used and updated in the primary table.
• Changes to an object’s OneToMany, ManyToMany, and ElementCollection relationships will not trigger it to be invalidated unless a version is used and updated in the primary table.

The conclusion from these limitations is obvious. We should enable optimistic locking by including an @Version in our entities. The column with @Version in the primary table will always be updated, and the object will always be invalidated. There are three entities implemented. Entity Order is in a many-to-one relationship with the Product and Customer entities. All these classes have the @Version feature enabled.

@Entity
@Table(name = "JPA_ORDER")
public class Order {

    @Id
    @SequenceGenerator(sequenceName = "SEQ_ORDER", allocationSize = 1, initialValue = 1, name = "orderSequence")
    @GeneratedValue(generator = "orderSequence", strategy = GenerationType.SEQUENCE)
    private Long id;
    @ManyToOne
    private Customer customer;
    @ManyToOne
    private Product product;
    @Enumerated
    private OrderStatus status;
    private int count;

    @Version
    private long version;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    public OrderStatus getStatus() {
        return status;
    }

    public void setStatus(OrderStatus status) {
        this.status = status;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public long getVersion() {
        return version;
    }

    public void setVersion(long version) {
        this.version = version;
    }

    @Override
    public String toString() {
        return "Order [id=" + id + ", product=" + product + ", status=" + status + ", count=" + count + "]";
    }

}

Testing

After launching your application, you see the following logs generated with the Finest level.

[EL Finest]: connection: 2018-03-23 15:45:50.591--ServerSession(465621833)--Thread(Thread[main,5,main])--Registering table [JPA_PRODUCT] for database change event notification.
[EL Finest]: connection: 2018-03-23 15:45:50.608--ServerSession(465621833)--Thread(Thread[main,5,main])--Registering table [JPA_CUSTOMER] for database change event notification.
[EL Finest]: connection: 2018-03-23 15:45:50.616--ServerSession(465621833)--Thread(Thread[main,5,main])--Registering table [JPA_ORDER] for database change event notification.

The registrations are stored in the table user_change_notification_regs, which is available for your application’s user (PIOMIN).

$ SELECT regid, table_name FROM user_change_notification_regs;
     REGID TABLE_NAME
---------- ---------------------------------------------------------------
       326 PIOMIN.JPA_PRODUCT
       326 PIOMIN.JPA_CUSTOMER
       326 PIOMIN.JPA_ORDER

Our sample application exposes Swagger documentation of API, which may be accessed under address http://localhost:8090/swagger-ui.html. You can create or find some entities using it. If you try to find the same entity several times, you would see that the only first invoke generates SQL query in logs, while all others are taken from a cache. Now, try to change that record using any Oracle’s client like Oracle SQL Developer, and verify if the cache has been successfully refreshed.

Summary

When I first heard about Oracle Database Change Notification supported by EclipseLink JPA vendor, my expectations were really high. It is a very interesting solution that guarantees an automatic cache refresh after changes performed on database tables by third-party applications, avoiding your cache. However, I had some problems with that solution during tests. In some cases, it just doesn’t work, and the detection of errors was really troublesome. It would be fine if such a solution could be also available for databases other than Oracle and JPA vendors like Hibernate.