Caching

About

When using JDBC directly (or via frameworks like Spring JDBC), caching can occur at multiple levels. These caches are essential for improving performance, reducing network round-trips, and avoiding repetitive database operations. Unlike JPA/Hibernate, JDBC does not provide automatic entity-level caching, but other forms of caching still play a significant role.

1. Prepared Statement Cache (Statement Caching)

  • JDBC allows frequently executed SQL queries to be prepared once and reused, improving performance by avoiding repeated parsing and planning.

  • Most Connection Pool implementations (like HikariCP) support prepared statement caching.

  • This cache stores compiled SQL statements in memory so that re-execution doesn't recompile the SQL.

Best practice:

  • Use cachePrepStmts=true, prepStmtCacheSize, and prepStmtCacheSqlLimit with your JDBC driver.

  • Example (for MySQL):

    spring.datasource.hikari.data-source-properties.cachePrepStmts=true
    spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
    spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=2048

2. ResultSet Caching

  • JDBC does not cache results by default.

  • If you need caching of query results, you must implement it manually (e.g., in memory using a Map, or use a caching layer like Spring Cache or Caffeine).

  • Useful when the data changes rarely (e.g., lookup tables like countries, currencies).

Best practice:

  • Avoid caching large or frequently-changing results.

  • Use a caching abstraction to refresh data based on a time-to-live (TTL) policy.

3. Connection Pool Cache

  • Datasource connection pooling (like in HikariCP) caches physical DB connections.

  • This is not data caching, but resource caching, ensuring that new DB connections are not created on every request.

  • It drastically reduces latency and overhead of creating TCP connections and handshakes.

Best practice:

  • Always use a connection pool like HikariCP.

  • Monitor connection pool health to avoid saturation or idle connection leaks.

4. Database-side Caching

  • Many databases (like Oracle, PostgreSQL, MySQL) have internal caching (e.g., buffer cache, query plan cache, disk block cache).

  • These work automatically and optimize repeated access to data or plans.

Best practice:

  • Design queries and indexing to benefit from these DB caches.

  • Reduce frequent full-table scans and ensure proper use of bind parameters.

5. Application-level Caching with JDBC

  • In JDBC applications, you can use manual or framework-based caching (e.g., Spring Cache abstraction with providers like Ehcache, Redis, or Caffeine).

  • For example, caching the result of a heavy JDBC query using Spring’s @Cacheable.

@Cacheable("employeeSummary")
public List<EmployeeSummary> getEmployeeSummary() {
    return jdbcTemplate.query("SELECT * FROM employee_summary", rowMapper);
}

Last updated

Was this helpful?