Database and cache

Content:

  1. Introduction
  2. Topologies
  3. Examples
  4. Performance
  5. Warning

Introduction:

In the article Distributed Cache we went through the basic theory behind cache and saw some code examples on how access various distributed caches from various languages.

In this article we will see some more code examples. And these code examples will be more specific in showing how to use cache to speedup database access.

Topologies:

We will look at the 5 topologies:

No cache:

Simple setup with no cache:

No cache

Local app cache:

Setup where the application code use a local in memory cache:

Local app cache

Local persistence framework cache:

Setup where the persistence framework use a local in memory cache:

This type of cache only works in single node scenarios.

Local persitence framework cache

This cache is activated via some form of confirguration.

This type of cache only works in single node scenarios.

Remote app cache:

Setup where the application code use a remote cache server:

Remote app cache

This type of cache works in both single node and cluster scenarios.

Remote persistence framework cache:

Setup where the persistence framework use a remote cache server:

Remote persistence framework cache

This cache is activated via some form of confirguration.

This type of cache works in both single node and cluster scenarios.

Examples:

Basis:

Common code:

DAL.java: 

package dbcache;

public interface DAL {
    public Big getBig(int id);
    public Integer getCounter(String id);
    public void incrementCounter(String id);
    public void close();
}

Big.java: 

package dbcache;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="big")
public class Big implements Serializable {
    private static final long serialVersionUID = 1L;
    private int id;
    private String data1;
    private String data2;
    public Big() {
        this(0, "", "");
    }
    public Big(int id, String data1, String data2) {
        this.id = id;
        this.data1 = data1;
        this.data2 = data2;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="data1")
    public String getData1() {
        return data1;
    }
    public void setData1(String data1) {
        this.data1 = data1;
    }
    @Column(name="data2")
    public String getData2() {
        return data2;
    }
    public void setData2(String data2) {
        this.data2 = data2;
    }
}

(the annotations are only needed for JPA)

Counter.java: 

package dbcache;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="counter")
public class Counter implements Serializable {
    private static final long serialVersionUID = 1L;
    private String id;
    private Integer val;
    public Counter() {
        this("", 0);
    }
    public Counter(String id, Integer val) {
        this.id = id;
        this.val = val;
    }
    @Id
    @Column(name="id")
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    @Column(name="val")
    public Integer getVal() {
        return val;
    }
    public void setVal(Integer val) {
        this.val = val;
    }
}

(the annotations are only needed for JPA) 

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DBCache.DAL
{
    [Table("big")]
    public class Big 
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("id")]
        public int Id { get; set; }
        [Column("data1")]
        public string Data1 { get; set; }
        [Column("data2")]
        public string Data2 { get; set; }
    }
    [Table("counter")]
    public class Counter 
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("id")]
        public string Id { get; set; }
        [Column("val")]
        public int Val { get; set; }
    }
    public interface IDAL
    {
        Big GetBig(int id);
        int? GetCounter(String id);
        void IncrementCounter(String id);
        void Close();
    }
}

(attributes are only needed for EF)

Persistence:

We will look at the following persistence frameworks:

ORM frameworks operate with two levels of cache:

level 1 cache
caching of data within the same session/connection/transaction
level 2 cache
caching of data between different sessions/connections/transactions

Level 2 cache is what provide the persistence framework cache described about.

Code:

DAL_JDBC.java: 

package dbcache;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.pool2.impl.GenericObjectPool;

public class DAL_JDBC implements DAL {
    private static final String POOL_PREFIX = "jdbc:apache:commons:dbcp:";
    private static final String POOL_NAME = "test";
    private static final String POOL = POOL_PREFIX + POOL_NAME;
    public DAL_JDBC(String constr, String usr, String pwd) throws SQLException, ClassNotFoundException {
        Class.forName("org.apache.commons.dbcp2.PoolingDriver");
        PoolingDriver driver = (PoolingDriver)DriverManager.getDriver(POOL_PREFIX);
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(new DriverManagerConnectionFactory(constr, usr, pwd), null);
        GenericObjectPool<PoolableConnection> cp = new GenericObjectPool<PoolableConnection>(pcf);
        pcf.setPool(cp);
        driver.registerPool(POOL_NAME, cp);
    }
    @Override
    public Big getBig(int id) {
        Big res = null;
        try {
            try(Connection con = DriverManager.getConnection(POOL)) {
                try(PreparedStatement pstmt =  con.prepareStatement("SELECT id,data1,data2 FROM big WHERE id = ?")) {
                    pstmt.setInt(1, id);
                    try(ResultSet rs = pstmt.executeQuery()) {
                        if(rs.next()) {
                            res = new Big(rs.getInt(1), rs.getString(2), rs.getString(3));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
    @Override
    public Integer getCounter(String id) {
        Integer res = null;
        try {
            try(Connection con = DriverManager.getConnection(POOL)) {
                try(PreparedStatement pstmt =  con.prepareStatement("SELECT val FROM counter WHERE id = ?")) {
                    pstmt.setString(1, id);
                    try(ResultSet rs = pstmt.executeQuery()) {
                        if(rs.next()) {
                            res = rs.getInt(1);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
    @Override
    public void incrementCounter(String id) {
        try {
            try(Connection con = DriverManager.getConnection(POOL)) {
                try(PreparedStatement pstmt =  con.prepareStatement("UPDATE counter SET val = val + 1 WHERE id = ?")) {
                    pstmt.setString(1, id);
                    pstmt.executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Override
    public void close() {
        try {
            PoolingDriver driver = (PoolingDriver)DriverManager.getDriver(POOL_PREFIX);
            driver.closePool(POOL_NAME);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Note that since JDBC by default does not provide a connection pool and database performance are really bad in many cases without connection pool, then Apache DBCP is used to provide connection pool.

DAL_JPA.java: 

package dbcache;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class DAL_JPA implements DAL {
    private EntityManagerFactory emf;
    public DAL_JPA(String confnam) {
        emf = Persistence.createEntityManagerFactory(confnam);
    }
    @Override
    public Big getBig(int id) {
        EntityManager em = emf.createEntityManager();
        Big res = em.find(Big.class, id);
        em.close();
        return res;
    }
    @Override
    public Integer getCounter(String id) {
        EntityManager em = emf.createEntityManager();
        Counter res = em.find(Counter.class, id);
        em.close();
        return res.getVal();
    }
    @Override
    public void incrementCounter(String id) {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        Counter res = em.find(Counter.class, id);
        res.setVal(res.getVal() + 1);
        em.getTransaction().commit();
        em.close();
    }
    @Override
    public void close() {
        emf.close();
    }
}

META-INF/persistence.xml: 

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="hibernate_mysql_nocache">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>dbcache.Big</class>
      <class>dbcache.Counter</class>
      <exclude-unlisted-classes/>
      <properties>
          <!--<property name="show_sql">true</property>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
      </properties>
   </persistence-unit>
   <persistence-unit name="hibernate_mysql_localcache">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>dbcache.Big</class>
      <class>dbcache.Counter</class>
      <exclude-unlisted-classes/>
      <shared-cache-mode>ALL</shared-cache-mode>
      <properties>
          <!--<property name="show_sql">true</property>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
          <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.jcache.JCacheRegionFactory"/>
          <property name="hibernate.javax.cache.provider" value="org.ehcache.jsr107.EhcacheCachingProvider"/>
          <property name="hibernate.javax.cache.uri" value="file:/C:/Work/ehcache.xml"/>
      </properties>
   </persistence-unit>
   <persistence-unit name="hibernate_mysql_remotecache">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>dbcache.Big</class>
      <class>dbcache.Counter</class>
      <exclude-unlisted-classes/>
      <shared-cache-mode>ALL</shared-cache-mode>
      <properties>
          <!--<property name="show_sql">true</property>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
          <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.jcache.JCacheRegionFactory"/>
          <property name="hibernate.javax.cache.provider" value="org.apache.ignite.cache.CachingProvider"/>
          <property name="hibernate.javax.cache.uri" value="file:/C:/Work/ignite.xml"/>
      </properties>
   </persistence-unit>
</persistence>

The lines: 

      <shared-cache-mode>ALL</shared-cache-mode>
          ...
          <property name="hibernate.cache.use_second_level_cache" value="true"/>
          <property name="hibernate.cache.use_query_cache" value="true"/>

is what enable level 2 cache.

Possible shared-cache-mode values are:

NONE
don't cache any entities
ALL
cache all entities
ENABLE_SELECTIVE
only cache entities with @Cacheable or @Cacheable(true)
DISABLE_SELECTIVE
cache all entities except those with @Cacheable(false)

The lines: 

          <property name="hibernate.cache.region.factory_class" value="..."/>
          <property name="hibernate.javax.cache.provider" value="..."/>
          <property name="hibernate.javax.cache.uri" value="..."/>

define what cache to use. 

using System;
using System.Data;
using System.Data.Common;

namespace DBCache.DAL
{
    public class DAL_ADONET : IDAL
    {
        private string prov;
        private string constr;
        public DAL_ADONET(string prov, string constr)
        {
            this.prov = prov;
            this.constr = constr;
        }
        public Big GetBig(int id)
        {
            Big res = null;
            using(IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection()) 
            {
                con.ConnectionString = constr;
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT id,data1,data2 FROM big WHERE id = @id";
                    cmd.Connection = con;
                    IDbDataParameter pid = cmd.CreateParameter();
                    pid.ParameterName = "@id";
                    pid.DbType = DbType.Int32;
                    pid.Value = id;
                    cmd.Parameters.Add(pid);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            res = new Big { Id = (int)rdr["id"], Data1 = (string)rdr["data1"], Data2 = (string)rdr["data2"] };
                        }
                    }                   
                }
            }
            return res;
        }
        public int? GetCounter(String id)
        {
            int? res = null;
            using(IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection()) 
            {
                con.ConnectionString = constr;
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT val FROM counter WHERE id = @id";
                    cmd.Connection = con;
                    IDbDataParameter pid = cmd.CreateParameter();
                    pid.ParameterName = "@id";
                    pid.DbType = DbType.String;
                    pid.Value = id;
                    cmd.Parameters.Add(pid);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            res = (int?)rdr["val"];
                        }
                    }                   
                }
            }
            return res;
        }
        public void IncrementCounter(String id)
        {
            using(IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection()) 
            {
                con.ConnectionString = constr;
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "UPDATE counter SET val = val + 1 WHERE id = @id";
                    cmd.Connection = con;
                    IDbDataParameter pid = cmd.CreateParameter();
                    pid.ParameterName = "@id";
                    pid.DbType = DbType.String;
                    pid.Value = id;
                    cmd.Parameters.Add(pid);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Close()
        {
        }
    }
}

using System;
using System.Data.Entity;
using System.Linq;

namespace DBCache.DAL
{
    public class MyDbContext : DbContext
    {
        public MyDbContext(string constrkey) : base(constrkey)
        {
        }
        public DbSet<Big> Big { get; set; }
        public DbSet<Counter> Counter { get; set; }
    }
    public class DAL_EF : IDAL
    {
        private string constrkey;
        public DAL_EF(string constrkey)
        {
            this.constrkey = constrkey;
        }
        public Big GetBig(int id)
        {
            Big res = null;
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                res = db.Big.FirstOrDefault(o => o.Id == id);
            }
            return res;
        }
        public int? GetCounter(String id)
        {
            int? res = null;
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                Counter temp = db.Counter.FirstOrDefault(o => o.Id == id);
                if(temp != null)
                {
                    res = temp.Val;
                }
            }
            return res;
        }
        public void IncrementCounter(String id)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                Counter temp = db.Counter.FirstOrDefault(o => o.Id == id);
                temp.Val = temp.Val + 1;
                db.SaveChanges();
            }
        }
        public void Close()
        {
        }
    }
}

app.config: 

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        ...
    </configSections>
    <connectionStrings>
        <add name="MySQL_test" connectionString="Server=localhost;Database=test;User Id=root;Password=" providerName="MySql.Data.MySqlClient" />
    </connectionStrings>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        <providers>
            <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
        </providers>
    </entityFramework>
    ...
</configuration>

using System;
using System.Linq;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Linq;

namespace DBCache.DAL
{
    public class DAL_NHib : IDAL
    {
        private ISessionFactory sf;
        public DAL_NHib(string cfgfnm, string mapfnm)
        {
            Configuration cfg = new Configuration();
            cfg.Configure(cfgfnm);
            cfg.AddXmlFile(mapfnm);
            sf =  cfg.BuildSessionFactory();
        }
        public Big GetBig(int id)
        {
            Big res = null;
            using(ISession s = sf.OpenSession())
            {
                res = s.QueryOver<Big>().Where(o => o.Id == id).Cacheable().SingleOrDefault();
            }
            return res;
        }
        public int? GetCounter(String id)
        {
            int? res = null;
            using(ISession s = sf.OpenSession())
            {
                Counter temp = s.QueryOver<Counter>().Where(o => o.Id == id).Cacheable().SingleOrDefault();
                if(temp != null)
                {
                    res = temp.Val;
                }
            }
            return res;
        }
        public void IncrementCounter(String id)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    Counter temp = s.QueryOver<Counter>().Where(o => o.Id == id).Cacheable().SingleOrDefault();
                    temp.Val = temp.Val + 1;
                    s.Update(temp);
                    tx.Commit();
                }
            }
        }
        public void Close()
        {
            sf.Close();
        }
    }
}

app.config: 

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        ...
        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
    </configSections>
    <log4net debug="false">
        <appender name="console" type="log4net.Appender.ConsoleAppender, log4net">
            <threshold value="DEBUG" />
            <layout type="log4net.Layout.PatternLayout,log4net">
                <param name="ConversionPattern" value="%-30c %d %-5p %m%n" />
            </layout>
        </appender>
        <logger name="NHibernate">
            <level value="DEBUG" />
            <appender-ref ref="console" />
        </logger>
    </log4net>
    ...
</configuration>

nhibcfg_nocache.xml: 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
        <property name="connection.connection_string">Server=localhost;Database=test;User Id=root;Password=</property>
        <property name="dialect">NHibernate.Dialect.MySQL55Dialect</property>
        <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
        <!--<property name="show_sql">true</property>-->
        <property name="hbm2ddl.keywords">none</property>
    </session-factory>
</hibernate-configuration>

nhibcfg_localcache.xml: 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
        <property name="connection.connection_string">Server=localhost;Database=test;User Id=root;Password=</property>
        <property name="dialect">NHibernate.Dialect.MySQL55Dialect</property>
        <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
        <!--<property name="show_sql">true</property>-->
        <property name="hbm2ddl.keywords">none</property>
        <property name="cache.use_second_level_cache">true</property>
        <property name="cache.use_query_cache" >true</property>
        <property name="cache.provider_class">NHibernate.Caches.RtMemoryCache.RtMemoryCacheProvider, NHibernate.Caches.RtMemoryCache</property>
    </session-factory>
</hibernate-configuration>

nhibcfg_remotecache.xml: 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
        <property name="connection.connection_string">Server=localhost;Database=test;User Id=root;Password=</property>
        <property name="dialect">NHibernate.Dialect.MySQL55Dialect</property>
        <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
        <!--<property name="show_sql">true</property>-->
        <property name="hbm2ddl.keywords">none</property>
        <property name="cache.use_second_level_cache">true</property>
        <property name="cache.use_query_cache" >true</property>
        <property name="cache.provider_class">NHibernate.Caches.Redis.RedisCacheProvider, NHibernate.Caches.Redis</property>
    </session-factory>
</hibernate-configuration>

The lines: 

        <property name="cache.use_second_level_cache">true</property>
        <property name="cache.use_query_cache" >true</property>

is what enable level2 cache.

The lines: 

        <property name="cache.provider_class">...</property>

define what what cache to use.

nhibmap.xml: 

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" >
    <class name="DBCache.DAL.Big,DAL" table="big">
        <cache usage="read-write"/>
        <id name="Id">
            <column name="id"/>
        </id>
        <property name="Data1">
            <column name="data1"/>
        </property>
        <property name="Data2">
            <column name="data2"/>
        </property>
    </class>
    <class name="DBCache.DAL.Counter,DAL" table="counter">
        <cache usage="read-write"/>
        <id name="Id">
            <column name="id"/>
        </id>
        <property name="Val">
            <column name="val"/>
        </property>
    </class>
</hibernate-mapping>

Caches:

We will look at the following caches:

Code:

DAL_Cache.java: 

package dbcache;

import javax.cache.Cache;
import javax.cache.CacheManager;
import javax.cache.spi.CachingProvider;

// this class must be subclassed to setup and teardown a JCache implementation properly
public abstract class DAL_Cache implements DAL {
    protected CachingProvider cp;
    protected CacheManager cm;
    protected Cache<Integer,Big> big_cache;
    protected Cache<String,Integer> counter_cache;
    private DAL real;
    public DAL_Cache(DAL real) {
        this.real = real;
    }
    @Override
    public Big getBig(int id) {
        Big res = big_cache.get(id);
        if(res == null) {
            res = real.getBig(id);
            big_cache.put(id,  res);
        }
        return res;
    }
    @Override
    public Integer getCounter(String id) {
        Integer res = counter_cache.get(id);
        if(res == null) {
            res = real.getCounter(id);
            counter_cache.put(id, res);
        }
        return res;
    }
    @Override
    public void incrementCounter(String id) {
        real.incrementCounter(id);
        counter_cache.put(id, real.getCounter(id));
    }
    @Override
    public void close() {
        counter_cache.close();
        big_cache.close();
        cm.close();
        cp.close();
        real.close();
    }
}

DAL_LocalCache.java: 

package dbcache;

import javax.cache.Caching;
import javax.cache.configuration.MutableConfiguration;

public class DAL_LocalCache extends DAL_Cache {
    public DAL_LocalCache(DAL real) {
        super(real);
        // this implementation use EHCache which is a very common choice for local cache
        cp = Caching.getCachingProvider("org.ehcache.jsr107.EhcacheCachingProvider");
        cm = cp.getCacheManager();
        big_cache = cm.getCache("local_big_cache");
        if(big_cache == null) {
            big_cache = cm.createCache("local_big_cache", new MutableConfiguration<Integer,Big>());
        }
        counter_cache = cm.getCache("local_counter_cache");
        if(counter_cache == null) {
            counter_cache = cm.createCache("local_counter_cache", new MutableConfiguration<String,Integer>());
        }
    }
}

DAL_RemoteCache.java: 

package dbcache;

import java.net.URI;
import java.net.URISyntaxException;

import javax.cache.Caching;
import javax.cache.configuration.MutableConfiguration;

public class DAL_RemoteCache extends DAL_Cache {
    public DAL_RemoteCache(DAL real) throws URISyntaxException {
        super(real);
        // this implementation use Apache Ignite which is a relative common choice for remote cache
        cp = Caching.getCachingProvider("org.apache.ignite.cache.CachingProvider");
        cm = cp.getCacheManager(new URI("file:/C:/Work/ignite.xml"), null, null);
        big_cache = cm.getCache("remote_big_cache");
        if(big_cache == null) {
            big_cache = cm.createCache("remote_big_cache", new MutableConfiguration<Integer,Big>());
        }
        counter_cache = cm.getCache("remote_counter_cache");
        if(counter_cache == null) {
            counter_cache = cm.createCache("remote_counter_cache", new MutableConfiguration<String,Integer>());
        }
    }
}

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Runtime.Caching;

using StackExchange.Redis;

namespace DBCache.DAL
{
    // for local cache the builtin .NET MemoryCache is used
    public class DAL_LocalCache : IDAL
    {
        private MemoryCache big_cache;
        private MemoryCache counter_cache;
        private IDAL real;
        public DAL_LocalCache(IDAL real)
        {
            this.real = real;
            big_cache = new MemoryCache("local_big_cache", new NameValueCollection());
            counter_cache = new MemoryCache("local_counter_cache", new NameValueCollection());
        }
        public Big GetBig(int id)
        {
            Big res = (Big)big_cache.Get(id.ToString(), null);
            if(res == null)
            {
                res = real.GetBig(id);
                big_cache.Set(id.ToString(), res, DateTimeOffset.MaxValue, null);
            }
            return res;
        }
        public int? GetCounter(String id)
        {
            int? res = (int?)counter_cache.Get(id, null);
            if(res == null)
            {
                res = real.GetCounter(id);
                counter_cache.Set(id, res, DateTimeOffset.MaxValue, null);
            }
            return res;
        }
        public void IncrementCounter(String id)
        {
            real.IncrementCounter(id);
            counter_cache.Set(id, real.GetCounter(id), DateTimeOffset.MaxValue, null);
        }
        public void Close()
        {
            big_cache.Dispose();
            counter_cache.Dispose();
            real.Close();
        }
    }
    // for remote cache Redis is used
    public class DAL_RemoteCache : IDAL
    {
        private const int REMOTE_BIG_CACHE = 1;
        private const int REMOTE_COUNTER_CACHE = 2;
        private ConnectionMultiplexer con;
        private IDatabase big_cache;
        private IDatabase counter_cache;
        private IDAL real;
        // we need to serialize and deserialize - for production usage something more robust is needed
        private static string Big2String(Big o) 
        {
            return o.Id + "," + o.Data1 + "," + o.Data2;
        }
        private static Big String2Big(string s)
        {
            if(s == null) return null;
            string[] parts = s.Split(',');
            return new Big { Id = int.Parse(parts[0]), Data1 = parts[1], Data2 = parts[2] };
        }
        public DAL_RemoteCache(IDAL real)
        {
            this.real = real;
            con = ConnectionMultiplexer.Connect("localhost:6379");
            big_cache = con.GetDatabase(REMOTE_BIG_CACHE);
            counter_cache = con.GetDatabase(REMOTE_COUNTER_CACHE);
        }
        public Big GetBig(int id)
        {
            Big res = String2Big((string)big_cache.StringGet(id.ToString()));
            if(res == null)
            {
                res = real.GetBig(id);
                big_cache.StringSet(id.ToString(), Big2String(res));
            }
            return res;
        }
        public int? GetCounter(String id)
        {
            int? res = (int?)counter_cache.StringGet(id);
            if(res == null)
            {
                res = real.GetCounter(id);
                counter_cache.StringSet(id, res);
            }
            return res;
        }
        public void IncrementCounter(String id)
        {
            real.IncrementCounter(id);
            counter_cache.StringSet(id, real.GetCounter(id));
        }
        public void Close()
        {
            real.Close();
        }
    }
}

Performance:

To test performance we will use the data access classes from previous section.

Database table "big" will consist of 100000 rows.

Test program:

PerfTest.java: 

package dbcache;

import java.net.URISyntaxException;
import java.sql.SQLException;
import java.util.Random;

public class PerfTest {
    private static final int NIT = 5;
    private static final int REP = 10;
    private static final int N = REP * Setup.NREC;
    private static final int NTHREADS = 10;
    private static final Random rng = new Random();
    public static void test (String lbl, DAL dal) throws InterruptedException {
        System.out.println(lbl + ":");
        for(int k = 0; k < NIT; k++) {
            long t1 = System.currentTimeMillis();
            Thread[] t = new Thread[NTHREADS];
            for(int j = 0; j < t.length; j++) {
                t[j] = new Thread(() -> {
                    for(int i = 0; i < N / NTHREADS; i++) {
                        int id = rng.nextInt(Setup.NREC) + 1;
                        try {
                            Big o = dal.getBig(id);
                            if(o.getId() != id) throw new RuntimeException("Ooops");
                        } catch(Exception ex) {
                            System.out.println(ex.getMessage());
                        }
                    }
                });
            }
            for(int j = 0; j < t.length; j++) t[j].start();
            for(int j = 0; j < t.length; j++) t[j].join();
            long t2 = System.currentTimeMillis();
            System.out.printf("%d random record reads in %.1f seconds = %d records per second\n",  N, (t2 - t1) / 1000.0, N * 1000 / (t2 - t1));
        }
        dal.close();
    }
    public static void main(String[] args) throws SQLException, URISyntaxException, ClassNotFoundException, InterruptedException {
        test("Plain JDBC - no app cache", new DAL_JDBC("jdbc:mysql://localhost/test", "root", ""));
        test("Plain JDBC - local app cache", new DAL_LocalCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        test("Plain JDBC - remote app cache", new DAL_RemoteCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        test("JPA - no app cache, no Hibernate level 2 cache", new DAL_JPA("hibernate_mysql_nocache"));
        test("JPA - local app cache, no Hibernate level 2 cache", new DAL_LocalCache(new DAL_JPA("hibernate_mysql_nocache")));
        test("JPA - remote app cache, no Hibernate level 2 cache", new DAL_RemoteCache(new DAL_JPA("hibernate_mysql_nocache")));
        test("JPA - no app cache, local Hibernate level 2 cache", new DAL_JPA("hibernate_mysql_localcache"));
        test("JPA - no app cache, remote Hibernate level 2 cache", new DAL_JPA("hibernate_mysql_remotecache"));
    }
}

using System;
using System.Threading;

using DBCache.DAL;

namespace DBCache.PerfTest
{
    public class Setup
    {
        internal const int NREC = 10000;
        // all work done in Java
    }
    public class Program
    {
        private const int NIT = 5;
        private const int REP = 10;
        private const int N = REP * Setup.NREC;
        private const int NTHREADS = 10;
        private static readonly Random rng = new Random();
        public static void Test(string lbl, IDAL dal)
        {
            Console.WriteLine(lbl + ":");
            for(int k = 0; k < NIT; k++)
            {
                DateTime dt1 = DateTime.Now;
                Thread[] t = new Thread[NTHREADS];
                for(int j = 0; j < t.Length; j++)
                {
                    t[j] = new Thread(() => {
                        for(int i = 0; i < N; i++)
                        {
                            int id = rng.Next(Setup.NREC) + 1;
                            Big o = dal.GetBig(id);
                            if(o.Id != id) throw new Exception("Ooops");
                        }
                    });
                }
                for(int j = 0; j < t.Length; j++) t[j].Start();
                for(int j = 0; j < t.Length; j++) t[j].Join();
                DateTime dt2 = DateTime.Now;
                Console.WriteLine("{0} random record reads in {1:F1} seconds = {2} records per second",  N, (dt2 - dt1).TotalSeconds, (int)(N / (dt2 - dt1).TotalSeconds));
            }
            dal.Close();
        }
        public static void Main(string[] args)
        {
            StackExchange.Redis.ConnectionMultiplexer con = StackExchange.Redis.ConnectionMultiplexer.Connect("localhost:6379");
            NHibernate.Caches.Redis.RedisCacheProvider.SetConnectionMultiplexer(con);
            ////log4net.Config.XmlConfigurator.Configure();
            Test("ADO.NET - no app cache", new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password="));
            Test("ADO.NET - local app cache", new DAL_LocalCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Test("ADO.NET - remote app cache", new DAL_RemoteCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Test("EF - no app cache", new DAL_EF("MySQL_test"));
            Test("EF - local app cache", new DAL_LocalCache(new DAL_EF("MySQL_test")));
            Test("EF - remote app cache", new DAL_RemoteCache(new DAL_EF("MySQL_test")));
            Test("NHibernate - no app cache, no Hibernate level 2 cache", new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml"));
            Test("NHibernate - local app cache, no Hibernate level 2 cache", new DAL_LocalCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Test("NHibernate - remote app cache, no Hibernate level 2 cache", new DAL_RemoteCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Test("NHibernate - no app cache, local Hibernate level 2 cache", new DAL_NHib(@"C:\Work\nhibcfg_localcache.xml", @"C:\Work\nhibmap.xml"));
            Test("NHibernate - no app cache, remote Hibernate level 2 cache", new DAL_NHib(@"C:\Work\nhibcfg_remotecache.xml", @"C:\Work\nhibmap.xml"));
        }
    }
}

Results: 

Plain JDBC - no app cache:
1000000 random record reads in 122.8 seconds = 8141 records per second
1000000 random record reads in 122.1 seconds = 8191 records per second
1000000 random record reads in 121.9 seconds = 8203 records per second
1000000 random record reads in 122.2 seconds = 8185 records per second
1000000 random record reads in 122.2 seconds = 8180 records per second
Plain JDBC - local app cache:
1000000 random record reads in 17.2 seconds = 57997 records per second
1000000 random record reads in 1.3 seconds = 760456 records per second
1000000 random record reads in 1.3 seconds = 747384 records per second
1000000 random record reads in 1.4 seconds = 740740 records per second
1000000 random record reads in 1.4 seconds = 718390 records per second
Plain JDBC - remote app cache:
1000000 random record reads in 16.8 seconds = 59658 records per second
1000000 random record reads in 8.4 seconds = 118525 records per second
1000000 random record reads in 8.4 seconds = 119417 records per second
1000000 random record reads in 8.4 seconds = 118371 records per second
1000000 random record reads in 8.4 seconds = 118497 records per second
JPA - no app cache, no Hibernate level 2 cache:
1000000 random record reads in 105.2 seconds = 9504 records per second
1000000 random record reads in 99.6 seconds = 10040 records per second
1000000 random record reads in 99.5 seconds = 10053 records per second
1000000 random record reads in 99.5 seconds = 10054 records per second
1000000 random record reads in 99.9 seconds = 10011 records per second
JPA - local app cache, no Hibernate level 2 cache:
1000000 random record reads in 12.9 seconds = 77345 records per second
1000000 random record reads in 1.8 seconds = 569151 records per second
1000000 random record reads in 1.8 seconds = 553403 records per second
1000000 random record reads in 1.9 seconds = 526592 records per second
1000000 random record reads in 1.9 seconds = 537056 records per second
JPA - remote app cache, no Hibernate level 2 cache:
1000000 random record reads in 18.2 seconds = 54857 records per second
1000000 random record reads in 9.3 seconds = 107851 records per second
1000000 random record reads in 9.4 seconds = 106010 records per second
1000000 random record reads in 9.5 seconds = 105741 records per second
1000000 random record reads in 9.2 seconds = 108166 records per second
JPA - no app cache, local Hibernate level 2 cache:
1000000 random record reads in 16.3 seconds = 61360 records per second
1000000 random record reads in 3.4 seconds = 291120 records per second
1000000 random record reads in 3.5 seconds = 285959 records per second
1000000 random record reads in 3.5 seconds = 284495 records per second
1000000 random record reads in 3.6 seconds = 279251 records per second
JPA - no app cache, remote Hibernate level 2 cache:
1000000 random record reads in 45.2 seconds = 22136 records per second
1000000 random record reads in 48.0 seconds = 20842 records per second
1000000 random record reads in 25.4 seconds = 39328 records per second
1000000 random record reads in 42.4 seconds = 23611 records per second
1000000 random record reads in 15.6 seconds = 64188 records per second

The results are more or less as expected. Local cache is 25-100 times faster than no cache. Remote cache is 5-10 times gaster than no cache. 

ADO.NET - no app cache:
100000 random record reads in 82.3 seconds = 1214 records per second
100000 random record reads in 78.2 seconds = 1278 records per second
100000 random record reads in 73.4 seconds = 1363 records per second
100000 random record reads in 74.2 seconds = 1348 records per second
100000 random record reads in 75.3 seconds = 1327 records per second
ADO.NET - local app cache:
100000 random record reads in 1.2 seconds = 86127 records per second
100000 random record reads in 0.5 seconds = 204906 records per second
100000 random record reads in 0.4 seconds = 236953 records per second
100000 random record reads in 0.5 seconds = 203240 records per second
100000 random record reads in 0.4 seconds = 249362 records per second
ADO.NET - remote app cache:
100000 random record reads in 10.5 seconds = 9482 records per second
100000 random record reads in 10.3 seconds = 9712 records per second
100000 random record reads in 10.2 seconds = 9780 records per second
100000 random record reads in 10.3 seconds = 9729 records per second
100000 random record reads in 10.3 seconds = 9692 records per second
EF - no app cache:
100000 random record reads in 370.2 seconds = 270 records per second
100000 random record reads in 347.7 seconds = 287 records per second
100000 random record reads in 350.2 seconds = 285 records per second
100000 random record reads in 349.4 seconds = 286 records per second
100000 random record reads in 352.4 seconds = 283 records per second
EF - local app cache:
100000 random record reads in 0.4 seconds = 243888 records per second
100000 random record reads in 0.5 seconds = 200791 records per second
100000 random record reads in 0.5 seconds = 211852 records per second
100000 random record reads in 0.4 seconds = 238081 records per second
100000 random record reads in 0.4 seconds = 243295 records per second
EF - remote app cache:
100000 random record reads in 9.5 seconds = 10564 records per second
100000 random record reads in 9.8 seconds = 10183 records per second
100000 random record reads in 10.3 seconds = 9701 records per second
100000 random record reads in 10.0 seconds = 10002 records per second
100000 random record reads in 8.9 seconds = 11273 records per second
NHibernate - no app cache, no Hibernate level 2 cache:
100000 random record reads in 287.2 seconds = 348 records per second
100000 random record reads in 297.6 seconds = 335 records per second
100000 random record reads in 300.5 seconds = 332 records per second
100000 random record reads in 310.4 seconds = 322 records per second
100000 random record reads in 294.6 seconds = 339 records per second
NHibernate - local app cache, no Hibernate level 2 cache:
100000 random record reads in 0.4 seconds = 259052 records per second
100000 random record reads in 0.5 seconds = 208321 records per second
100000 random record reads in 0.4 seconds = 245084 records per second
100000 random record reads in 0.6 seconds = 177609 records per second
100000 random record reads in 0.4 seconds = 231468 records per second
NHibernate - remote app cache, no Hibernate level 2 cache:
100000 random record reads in 9.9 seconds = 10106 records per second
100000 random record reads in 9.1 seconds = 11042 records per second
100000 random record reads in 9.1 seconds = 10961 records per second
100000 random record reads in 9.0 seconds = 11122 records per second
100000 random record reads in 9.2 seconds = 10922 records per second
NHibernate - no app cache, local Hibernate level 2 cache:
100000 random record reads in 202.0 seconds = 495 records per second
100000 random record reads in 203.7 seconds = 490 records per second
100000 random record reads in 197.5 seconds = 506 records per second
100000 random record reads in 208.5 seconds = 479 records per second
100000 random record reads in 199.9 seconds = 500 records per second
NHibernate - no app cache, remote Hibernate level 2 cache:
100000 random record reads in 653.1 seconds = 153 records per second
100000 random record reads in 579.1 seconds = 172 records per second
100000 random record reads in 578.0 seconds = 173 records per second
100000 random record reads in 647.6 seconds = 154 records per second
100000 random record reads in 618.3 seconds = 161 records per second

The results are more or less as expected. Local cache is 25-100 times faster than no cache. Remote cache is 5-10 times gaster than no cache. Only exception is NHibernate level 2 cache that does not provide the expected performance improvements - maybe I have not configured it properly, but it is the numbers I get.

Warning:

Remember the notes in the beginning about local caches not working in cluster scenarios.

Local caches performs better than remote caches, but they can provide incorrect results when used with a cluster.

If your application runs in a cluster (or other multi node setup) or may need to do so in the near future, then choose remote cache.

To illustrate the point then we will see an example with two processes accessing the database using the previous data access classes.

Monitor:

A monitor process that prints some database values every 5 seconds:

Utility.java: 

package dbcache;

import java.net.URISyntaxException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Utility {
    protected static List<String> labels = new ArrayList<String>();
    protected static List<String> counters = new ArrayList<String>();
    protected static List<DAL> dals = new ArrayList<DAL>();
    private static void add(String label, String counter, DAL dal) {
        labels.add(label);
        counters.add(counter);
        dals.add(dal);
    }
    protected static void addAll() throws ClassNotFoundException, SQLException, URISyntaxException {
        add("Plain JDBC - no app cache", "c1", new DAL_JDBC("jdbc:mysql://localhost/test", "root", ""));
        add("Plain JDBC - local app cache", "c2", new DAL_LocalCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        add("Plain JDBC - remote app cache", "c3", new DAL_RemoteCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        add("JPA - no app cache, no Hibernate level2 cache", "c4", new DAL_JPA("hibernate_mysql_nocache"));
        add("JPA - local app cache, no Hibernate level2 cache", "c5", new DAL_LocalCache(new DAL_JPA("hibernate_mysql_nocache")));
        add("JPA - remote app cache, no Hibernate level2 cache", "c6", new DAL_RemoteCache(new DAL_JPA("hibernate_mysql_nocache")));
        add("JPA - no app cache, local Hibernate level2 cache", "c7", new DAL_JPA("hibernate_mysql_localcache"));
        add("JPA - no app cache, remote Hibernate level2 cache", "c8", new DAL_JPA("hibernate_mysql_remotecache"));
    }
    protected static void closeAll() {
        for(DAL dal : dals) {
            dal.close();
        }
    }
}

Monitor.java: 

package dbcache;

import java.net.URISyntaxException;
import java.sql.SQLException;

public class Monitor extends Utility {
    private static void monitor() throws InterruptedException {
        while(true) {
            for(int i = 0; i < counters.size(); i++) {
                System.out.printf("%s : %d\n", labels.get(i), dals.get(i).getCounter(counters.get(i)));
            }
            System.out.println("====");
            Thread.sleep(10000);
        }
    }
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException, URISyntaxException {
        addAll();
        monitor();
        closeAll();
    }
}

using System;
using System.Collections.Generic;

using DBCache.DAL;

namespace DBCache.Util
{
    public abstract class Utility
    {
        protected static IList<string> labels = new List<string>();
        protected static IList<string> counters = new List<string>();
        protected static IList<IDAL> dals = new List<IDAL>();
        private static void Add(string label, string counter, IDAL dal)
        {
            labels.Add(label);
            counters.Add(counter);
            dals.Add(dal);
        }
        protected static void AddAll()
        {
            Add("ADO.NET - no app cache", "c1", new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password="));
            Add("ADO.NET - local app cache", "c2", new DAL_LocalCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Add("ADO.NET - remote app cache", "c3", new DAL_RemoteCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Add("EF - no app cache", "c4", new DAL_EF("MySQL_test"));
            Add("EF - local app cache", "c5", new DAL_LocalCache(new DAL_EF("MySQL_test")));
            Add("EF - remote app cache", "c6", new DAL_RemoteCache(new DAL_EF("MySQL_test")));
            Add("NHibernate - no app cache, no Hibernate level 2 cache", "c7", new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml"));
            Add("NHibernate - local app cache, no Hibernate level 2 cache", "c8", new DAL_LocalCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Add("NHibernate - remote app cache, no Hibernate level 2 cache", "c9", new DAL_RemoteCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Add("NHibernate - no app cache, local Hibernate level 2 cache", "c10", new DAL_NHib(@"C:\Work\nhibcfg_localcache.xml", @"C:\Work\nhibmap.xml"));
            Add("NHibernate - no app cache, remote Hibernate level 2 cache", "c11", new DAL_NHib(@"C:\Work\nhibcfg_remotecache.xml", @"C:\Work\nhibmap.xml"));
        }
        protected static void CloseAll()
        {
            foreach(IDAL dal in dals)
            {
                dal.Close();
            }
        }
    }
}

using System;
using System.Threading;

using DBCache.Util;

namespace DBCache.Monitor
{
    public class Program : Utility
    {
        private static void Monitor()
        {
            while(true) {
                for(int i = 0; i < counters.Count; i++)
                {
                    Console.WriteLine("{0} : {1}", labels[i], dals[i].GetCounter(counters[i]));
                }
                Console.WriteLine("====");
                Thread.Sleep(10000);
            }
        }
        public static void Main(string[] args)
        {
            StackExchange.Redis.ConnectionMultiplexer con = StackExchange.Redis.ConnectionMultiplexer.Connect("localhost:6379");
            NHibernate.Caches.Redis.RedisCacheProvider.SetConnectionMultiplexer(con);
            AddAll();
            Monitor();
            CloseAll();
        }
    }
}

Increment:

An increment process that updates the same database values:

Utility.java: 

package dbcache;

import java.net.URISyntaxException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Utility {
    protected static List<String> labels = new ArrayList<String>();
    protected static List<String> counters = new ArrayList<String>();
    protected static List<DAL> dals = new ArrayList<DAL>();
    private static void add(String label, String counter, DAL dal) {
        labels.add(label);
        counters.add(counter);
        dals.add(dal);
    }
    protected static void addAll() throws ClassNotFoundException, SQLException, URISyntaxException {
        add("Plain JDBC - no app cache", "c1", new DAL_JDBC("jdbc:mysql://localhost/test", "root", ""));
        add("Plain JDBC - local app cache", "c2", new DAL_LocalCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        add("Plain JDBC - remote app cache", "c3", new DAL_RemoteCache(new DAL_JDBC("jdbc:mysql://localhost/test", "root", "")));
        add("JPA - no app cache, no Hibernate level2 cache", "c4", new DAL_JPA("hibernate_mysql_nocache"));
        add("JPA - local app cache, no Hibernate level2 cache", "c5", new DAL_LocalCache(new DAL_JPA("hibernate_mysql_nocache")));
        add("JPA - remote app cache, no Hibernate level2 cache", "c6", new DAL_RemoteCache(new DAL_JPA("hibernate_mysql_nocache")));
        add("JPA - no app cache, local Hibernate level2 cache", "c7", new DAL_JPA("hibernate_mysql_localcache"));
        add("JPA - no app cache, remote Hibernate level2 cache", "c8", new DAL_JPA("hibernate_mysql_remotecache"));
    }
    protected static void closeAll() {
        for(DAL dal : dals) {
            dal.close();
        }
    }
}

Increment.java: 

package dbcache;

import java.net.URISyntaxException;
import java.sql.SQLException;

public class Increment extends Utility {
    private static void increment() {
        for(int i = 0; i < counters.size(); i++) {
            dals.get(i).incrementCounter(counters.get(i));
        }
    }
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException, URISyntaxException {
        addAll();
        increment();
        closeAll();
    }
}

using System;
using System.Collections.Generic;

using DBCache.DAL;

namespace DBCache.Util
{
    public abstract class Utility
    {
        protected static IList<string> labels = new List<string>();
        protected static IList<string> counters = new List<string>();
        protected static IList<IDAL> dals = new List<IDAL>();
        private static void Add(string label, string counter, IDAL dal)
        {
            labels.Add(label);
            counters.Add(counter);
            dals.Add(dal);
        }
        protected static void AddAll()
        {
            Add("ADO.NET - no app cache", "c1", new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password="));
            Add("ADO.NET - local app cache", "c2", new DAL_LocalCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Add("ADO.NET - remote app cache", "c3", new DAL_RemoteCache(new DAL_ADONET("MySql.Data.MySqlClient", "Server=localhost;Database=test;User Id=root;Password=")));
            Add("EF - no app cache", "c4", new DAL_EF("MySQL_test"));
            Add("EF - local app cache", "c5", new DAL_LocalCache(new DAL_EF("MySQL_test")));
            Add("EF - remote app cache", "c6", new DAL_RemoteCache(new DAL_EF("MySQL_test")));
            Add("NHibernate - no app cache, no Hibernate level 2 cache", "c7", new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml"));
            Add("NHibernate - local app cache, no Hibernate level 2 cache", "c8", new DAL_LocalCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Add("NHibernate - remote app cache, no Hibernate level 2 cache", "c9", new DAL_RemoteCache(new DAL_NHib(@"C:\Work\nhibcfg_nocache.xml", @"C:\Work\nhibmap.xml")));
            Add("NHibernate - no app cache, local Hibernate level 2 cache", "c10", new DAL_NHib(@"C:\Work\nhibcfg_localcache.xml", @"C:\Work\nhibmap.xml"));
            Add("NHibernate - no app cache, remote Hibernate level 2 cache", "c11", new DAL_NHib(@"C:\Work\nhibcfg_remotecache.xml", @"C:\Work\nhibmap.xml"));
        }
        protected static void CloseAll()
        {
            foreach(IDAL dal in dals)
            {
                dal.Close();
            }
        }
    }
}

using System;

using DBCache.Util;

namespace DBCache.Increment
{
    public class Program : Utility
    {
        private static void Increment()
        {
            for(int i = 0; i < counters.Count; i++)
            {
                dals[i].IncrementCounter(counters[i]);
            }
        }
        public static void Main(string[] args)
        {
            StackExchange.Redis.ConnectionMultiplexer con = StackExchange.Redis.ConnectionMultiplexer.Connect("localhost:6379");
            NHibernate.Caches.Redis.RedisCacheProvider.SetConnectionMultiplexer(con);
            AddAll();
            Increment();
            CloseAll();
        }
    }
}

Result:

Java monitor output when running increment: 

Plain JDBC - no app cache : 0
Plain JDBC - local app cache : 0
Plain JDBC - remote app cache : 0
JPA - no app cache, no Hibernate level2 cache : 0
JPA - local app cache, no Hibernate level2 cache : 0
JPA - remote app cache, no Hibernate level2 cache : 0
JPA - no app cache, local Hibernate level2 cache : 0
JPA - no app cache, remote Hibernate level2 cache : 0
====
Plain JDBC - no app cache : 0
Plain JDBC - local app cache : 0
Plain JDBC - remote app cache : 0
JPA - no app cache, no Hibernate level2 cache : 0
JPA - local app cache, no Hibernate level2 cache : 0
JPA - remote app cache, no Hibernate level2 cache : 0
JPA - no app cache, local Hibernate level2 cache : 0
JPA - no app cache, remote Hibernate level2 cache : 0
====
Plain JDBC - no app cache : 1
Plain JDBC - local app cache : 0
Plain JDBC - remote app cache : 1
JPA - no app cache, no Hibernate level2 cache : 1
JPA - local app cache, no Hibernate level2 cache : 0
JPA - remote app cache, no Hibernate level2 cache : 1
JPA - no app cache, local Hibernate level2 cache : 0
JPA - no app cache, remote Hibernate level2 cache : 1
====
Plain JDBC - no app cache : 1
Plain JDBC - local app cache : 0
Plain JDBC - remote app cache : 1
JPA - no app cache, no Hibernate level2 cache : 1
JPA - local app cache, no Hibernate level2 cache : 0
JPA - remote app cache, no Hibernate level2 cache : 1
JPA - no app cache, local Hibernate level2 cache : 0
JPA - no app cache, remote Hibernate level2 cache : 1
====
Plain JDBC - no app cache : 2
Plain JDBC - local app cache : 0
Plain JDBC - remote app cache : 2
JPA - no app cache, no Hibernate level2 cache : 2
JPA - local app cache, no Hibernate level2 cache : 0
JPA - remote app cache, no Hibernate level2 cache : 2
JPA - no app cache, local Hibernate level2 cache : 0
JPA - no app cache, remote Hibernate level2 cache : 2
====

Monitor does not detect the increments when using local cache.

C# monitor output when running increment: 

ADO.NET - no app cache : 0
ADO.NET - local app cache : 0
ADO.NET - remote app cache : 0
EF - no app cache : 0
EF - local app cache : 0
EF - remote app cache : 0
NHibernate - no app cache, no Hibernate level 2 cache : 0
NHibernate - local app cache, no Hibernate level 2 cache : 0
NHibernate - remote app cache, no Hibernate level 2 cache : 0
NHibernate - no app cache, local Hibernate level 2 cache : 0
NHibernate - no app cache, remote Hibernate level 2 cache : 0
====
ADO.NET - no app cache : 0
ADO.NET - local app cache : 0
ADO.NET - remote app cache : 0
EF - no app cache : 0
EF - local app cache : 0
EF - remote app cache : 0
NHibernate - no app cache, no Hibernate level 2 cache : 0
NHibernate - local app cache, no Hibernate level 2 cache : 0
NHibernate - remote app cache, no Hibernate level 2 cache : 0
NHibernate - no app cache, local Hibernate level 2 cache : 0
NHibernate - no app cache, remote Hibernate level 2 cache : 0
====
ADO.NET - no app cache : 1
ADO.NET - local app cache : 0
ADO.NET - remote app cache : 1
EF - no app cache : 1
EF - local app cache : 0
EF - remote app cache : 1
NHibernate - no app cache, no Hibernate level 2 cache : 1
NHibernate - local app cache, no Hibernate level 2 cache : 0
NHibernate - remote app cache, no Hibernate level 2 cache : 1
NHibernate - no app cache, local Hibernate level 2 cache : 0
NHibernate - no app cache, remote Hibernate level 2 cache : 1
====
ADO.NET - no app cache : 1
ADO.NET - local app cache : 0
ADO.NET - remote app cache : 1
EF - no app cache : 1
EF - local app cache : 0
EF - remote app cache : 1
NHibernate - no app cache, no Hibernate level 2 cache : 1
NHibernate - local app cache, no Hibernate level 2 cache : 0
NHibernate - remote app cache, no Hibernate level 2 cache : 1
NHibernate - no app cache, local Hibernate level 2 cache : 0
NHibernate - no app cache, remote Hibernate level 2 cache : 1
====
ADO.NET - no app cache : 2
ADO.NET - local app cache : 0
ADO.NET - remote app cache : 2
EF - no app cache : 2
EF - local app cache : 0
EF - remote app cache : 2
NHibernate - no app cache, no Hibernate level 2 cache : 2
NHibernate - local app cache, no Hibernate level 2 cache : 0
NHibernate - remote app cache, no Hibernate level 2 cache : 2
NHibernate - no app cache, local Hibernate level 2 cache : 0
NHibernate - no app cache, remote Hibernate level 2 cache : 2
====

Monitor does not detect the increments when using local cache.

The conclusion may seem obvious, but it is important to always remember it.

Article history:

Version Date Description
1.0 November 29th 2020 Initial version

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj