Skip to content

UsingTheRemoteQueryCachePlugin.md

Latest commit

 

History

History
592 lines (471 loc) · 41.6 KB

UsingTheRemoteQueryCachePlugin.md

File metadata and controls

592 lines (471 loc) · 41.6 KB
Raw

Remote Query Cache Plugin

The Remote Query Cache Plugin adds a query caching layer that stores cacheable read-only query results in a remote Valkey cache instead of local JVM memory. Error responses from the database are not cached. Applications can opt‑in per query using a SQL query hint that specifies a time‑to‑live (TTL).

The Remote Query Cache Plugin evaluates hints prefixed to the queries sent by the client application. If the query hint indicates it is cacheable, the plugin performs a cache lookup in the remote cache server. If no cached result is found or the previous cached result has expired, the plugin simply sends the query to the database, which executes the query and returns the query result set. The plugin first replies back to the user with the DB response, and then asynchronously writes the result to the cache with the defined TTL.

Cache_diagram_1

Once a particular query result is cached, subsequent identical queries are served directly from the cache while the TTL is valid. The cache server is assumed to scale independently and handles data eviction, removing client‑side memory pressure constraints.

Cache_diagram_2

Plugin Availability

The plugin is available since version 3.3.0.

Prerequisites

This plugin requires the following runtime dependencies to be registered separately in the classpath:

  • org.apache.commons:commons-pool2 version 2.11.1 and newer
  • io.valkey:valkey-glide version 2.3.0 and newer

Using the Remote Query Cache Plugin

The Remote Query Cache Plugin is not loaded by default. To load the plugin, include it in the wrapperPlugins connection parameter before creating the connection and running queries.

final Properties props = new Properties();
props.setProperty(PropertyDefinition.PLUGINS.name, "remoteQueryCache");
props.setProperty("cacheEndpointAddrRw", "mycache.amazonaws.com:6379");

// Create a connection and run a query
Connection conn = DriverManager.getConnection("jdbc:aws-wrapper:postgresql://mydb.amazonaws.com:5432/postgres", props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("/* CACHE_PARAM(ttl=300s) */ select * from mytable where id = 1");
...

Database multi-tenancy opt-in

Caution

Database multi-tenancy is not an intended use case of this plugin. cacheEnableDatabaseMultiTenancy is a best-effort starting point for applications that explicitly need it, not a security boundary or authorization firewall. Applications that enable it accept the risk of possible cache authorization bypass, cache poisoning across tenants, and other security issues. See Security scope and application requirements.

Enable this setting only when query visibility depends exclusively on the supported PostgreSQL or MySQL/MariaDB session state documented below. This protection is not automatic tenant detection and does not cover arbitrary database authorization mechanisms:

props.setProperty("cacheEnableDatabaseMultiTenancy", "true");

cacheEnableDatabaseMultiTenancy is an application connection property. The plugin does not automatically detect whether an application uses supported database session state for tenant isolation.

Recommended: separate tenants with cacheKeyPrefix

If your application needs tenant-separated caching, prefer giving each tenant its own cache keyspace over relying on database session-state tracking. Create one connection pool or DataSource per tenant, and set a unique cacheKeyPrefix for each:

props.setProperty("cacheKeyPrefix", tenantCode); // unique per tenant, max 10 characters

The prefix is fixed for every connection created with these properties, so connections from a tenant's pool must serve only that tenant. This approach does not depend on cacheEnableDatabaseMultiTenancy or on SQL statement detection.

Configuration Parameters

Parameter Available Since Version Value Required Description Default Value
cacheEndpointAddrRw 3.3.0 String Yes The cache read-write server endpoint address. null
cacheEndpointAddrRo 3.3.0 String No The cache read-only server endpoint address. This is an optional parameter to allow performing read operations from read replica cache nodes. null
cacheUseSSL 3.3.0 Boolean No Whether to use SSL for cache connection. true
cacheTlsCaCertPath 3.3.0 String No File path to the CA certificate (PEM) file for verifying the cache server's TLS certificate. Mainly for using self signed TLS certificates for testing. null
cacheUsername 3.3.0 String No Username for Valkey cache regular authentication. null
cachePassword 3.3.0 String No Password for Valkey cache regular authentication. null
cacheName 3.3.0 String No Explicit cache name for ElastiCache IAM authentication. null
cacheIamRegion 3.3.0 String No AWS region for ElastiCache IAM authentication. null
cacheMaxQuerySize 3.3.0 Integer No The max length of the query for remote caching. 16384
cacheEnableDatabaseMultiTenancy 4.5.0 Boolean No Adds supported PostgreSQL role/search-path state or MySQL/MariaDB account/role/database state to cache keys as best-effort hardening. Not a security boundary. false
cacheConnectionTimeoutMs 3.3.0 Integer No Cache connection request timeout duration in milliseconds. 2000
cacheConnectionPoolSize 3.3.0 Integer No Cache connection pool size. 20
cacheKeyPrefix 3.3.0 String No Optional static prefix for separating cache keyspaces (max 10 characters). Use a distinct prefix per tenant to separate tenant cache entries; see Recommended: separate tenants with cacheKeyPrefix. null
failWhenCacheDown 3.3.0 Boolean No Whether to throw SQLException on cache failures under Degraded mode or make queries fall back to the database. false
cacheInFlightWriteSizeLimitBytes 3.3.0 Integer No Maximum in-flight write size in Bytes to the cache server before triggering degraded mode. 50MB
cacheHealthCheckInHealthyState 3.3.0 Boolean No Whether to run health checks (pings) in healthy state. false
cacheAllowStreamSource 4.3.0 Boolean No Whether SQLXML.getSource(StreamSource.class) is allowed for XML values retrieved from the cache. See XML Columns below. false
cacheAllowUrl 4.3.0 Boolean No Whether java.net.URL values are allowed to be reconstructed from the cache. See URL Columns below. false

Overall Design

  • Designed for caching read‑only queries that produce a ResultSet, this plugin is typically good for certain types of database application workloads when the data in the table does not change very often.
  • User application can enable remote query caching functionality with minimal amount of application code changes. The application needs to enable the remote query caching plugin and specify the cache server’s endpoint when creating the JDBC connection, and prefix the query string with a SQL query hint containing the TTL.
  • Eligible queries that contain a caching query hint prefix will have their responses cached with the specified TTL. Further requests with the same query get their result served from the cache instead of the backend database.
  • For safety/security of caching operations, SQL queries longer than a specified length threshold (defaulted to 16K) will not be eligible for caching.
  • Cache writes are asynchronous to reduce query latency but may lead to a brief time window where the first request is served from the database while a background write occurs to write the query result into the cache server.
  • The cache server is responsible for item evictions and scaling; the client does not enforce an in‑JVM size limit for cached data.

Cacheability and query hints

The plugin uses SQL query hints to determine cacheability of the query and TTL. A query hint has the format: /* CACHE_PARAM(ttl=300s) */, with:

  • Case‑insensitive CACHE_PARAM with TTL parameter in seconds. TTL values <= 0 are treated as malformed and the query will not get cached. Large TTL values are allowed, but the plugin enforces a maximum TTL of 180 days to prevent indefinite caching.
  • Placement at the start of the SQL statement, as a prefix. A hint anywhere else is ignored, and the query is not cached.
  • Absence of such caching query hint makes the query un-cacheable.

Invalidation of cached entries

This is primarily done via a configured TTL for each cached entry, with an upper bound of 180 days to avoid caching entries permanently. User should define the TTL based on how frequently the underlying query data changes. User can bypass reading responses from the cache for queries that require stronger consistency.

In the case when the configured TTL is too long and causes stale data to be returned from the cache, there are a couple of options to mitigate this issue:

  • Specify a configurable cache key prefix to allow static keyspace separation within a shared cache cluster. The prefix can segregate the keyspace of one application use case from another, allowing users to scan for and delete only keys with that prefix from the Valkey server without affecting other application use cases. The prefix does not track database authorization state; enable cacheEnableDatabaseMultiTenancy when query visibility depends on supported database authorization/session state.
  • Flushing all data from the Valkey server (via a FLUSHALL command) so that it can be re-hydrated from the database again with fresh values.

Query result correctness

Every query cache entry is indexed by a hashed caching key containing:

  • Configured database username - different database users can have different permissions on various tables.
  • Tracked database catalog/schema name - same table name can exist in a different database catalog/schema which contains different data
  • The SQL query string

When cacheEnableDatabaseMultiTenancy=true, the key additionally contains:

  • For PostgreSQL, the current session user, effective role, configured search path, and resolved search path
  • For MySQL and MariaDB, the database-reported session user, authenticated account, active roles, and current database

The key does not include the database server or cluster endpoint, so connections to different database servers that share a cache cluster must use distinct cacheKeyPrefix values or separate cache clusters. The configured database username is the user connection property. When an authentication plugin, such as AWS Secrets Manager or federated authentication, supplies the username instead, the configured username is empty, and only cacheEnableDatabaseMultiTenancy=true adds the database-reported user to the key.

Nothing else is part of the key. Do not cache a query whose result can depend on anything else, including:

  • Bound parameter values that the target driver does not include in its statement text, such as binary, stream, or large-object parameters.
  • Statement settings such as Statement.setMaxRows(...).
  • Database or schema changes made with SQL (USE, SET search_path) when cacheEnableDatabaseMultiTenancy is disabled. Only the connection's initial catalog/schema and JDBC setCatalog(...)/setSchema(...) calls are tracked.

When cacheEnableDatabaseMultiTenancy is disabled, the key parts are joined with _, so names that contain _ can produce the same key, for example schema tenant_a with user reader and schema tenant with user a_reader. Use distinct cacheKeyPrefix values for such names.

When database multi-tenancy protection is enabled, the PostgreSQL authorization session state is acquired from the database and updated after statements such as SET ROLE, SET SESSION AUTHORIZATION, SET search_path, their corresponding RESET commands, successful JDBC Connection.setSchema(...) calls, and transaction completion. Transaction completion invalidates transaction-local state so that it is reacquired before caching resumes. If this state cannot be determined, the query bypasses both cache reads and cache writes.

Database multi-tenancy protection is disabled by default. When cacheEnableDatabaseMultiTenancy=false, the plugin preserves legacy cache eligibility, transaction behavior, method subscriptions, and cache-key format. It does not acquire or inspect authorization session state.

Applications whose query visibility depends on PostgreSQL roles or search paths, or on MySQL/MariaDB authenticated accounts, active roles, or current databases, must set cacheEnableDatabaseMultiTenancy=true. This setting does not make arbitrary custom session settings or hidden function side effects safe to cache. When enabled, caching is limited to supported database dialects whose authorization state can be determined safely. PostgreSQL, MySQL, and MariaDB are currently supported. Other dialects, or a supported dialect whose state is unavailable, bypass both cache reads and writes.

Enabling this protection reads authorization state when a physical connection is established or switched, after recognized authorization-state changes, and at transaction completion. Normal cache hits and cache misses do not issue an authorization-state query. MySQL servers that do not support CURRENT_ROLE() require one fallback query when authorization state is refreshed. Enabling this protection also reduces cacheability: the bypass and disable rules below apply, a connection that disables caching does not use the cache again, and each authorization context has separate cache entries, which lowers hit rates.

When database multi-tenancy protection is enabled, the MySQL and MariaDB authorization session state is acquired from the database and updated after operations such as SET ROLE, USE, RESET CONNECTION, and successful JDBC Connection.setCatalog(...) calls. MySQL servers that do not support roles omit only the active-role component. Opaque statements and session-variable use such as CALL, DO, user-defined variable references (@variable, with or without surrounding whitespace), HANDLER, and dynamically prepared SQL (EXECUTE) disable remote query caching for that connection. An account name with an unquoted user or role part, such as SET ROLE tenant_a@localhost, cannot be distinguished from a user-defined variable reference and also disables caching for the connection; use the quoted form (SET ROLE 'tenant_a'@'localhost') to keep the statement tracked. PostgreSQL EXECUTE (including EXPLAIN ... EXECUTE) and cursor FETCH do the same. Session-dependent queries, such as PostgreSQL SHOW, current_setting(...), currval(...), lastval(), and the pg_settings, pg_prepared_statements, and pg_cursors catalog relations, and MySQL/MariaDB SHOW, system variable reads (@@variable), LAST_INSERT_ID(), FOUND_ROWS(), and ROW_COUNT(), bypass the cache for that query only. Setting a non-authorization session variable without @, such as MySQL/MariaDB SET sql_mode = ... or PostgreSQL SET datestyle = ..., is not detected; queries whose results depend on such settings should not be cached.

When database multi-tenancy protection is enabled, statements containing MySQL or MariaDB executable comments (/*! ... */ or /*M! ... */) bypass remote cache reads and writes. The SQL is still executed normally. Remote caching is disabled for that connection after execution, including when execution fails, because the executable contents may have changed authorization state that cannot be tracked safely.

When database multi-tenancy protection is enabled, callable statements, multi-statement queries, and queries executed inside a transaction bypass both cache reads and cache writes. Their results can depend on uncommitted data or session state that cannot be safely represented by the cache key. If the physical connection changes while a cache miss is executed, the returned database result is not written to the cache because it may use a different authorization context. Batch execution disables remote query caching for the connection before the batch runs because earlier entries may change session state even if a later entry fails. Creating temporary relations or tables, including PostgreSQL CREATE [OR REPLACE] TEMP, SELECT INTO TEMP, and objects in pg_temp, and MySQL/MariaDB CREATE TEMPORARY TABLE, disables remote query caching for the connection because temporary objects can change name resolution without changing the authorization-state cache key. If a recognized non-batch authorization-state change fails, the plugin invalidates its authorization snapshot because an earlier command may already have changed the session. Opaque failed operations mark the state untracked. The original JDBC exception is preserved. An unknown authorization state bypasses caching until a later refresh succeeds. An untracked state cannot be represented safely and remains disabled for that wrapper connection. Returning a connection to an application connection pool does not by itself clear the untracked state. When database multi-tenancy protection is disabled, the plugin preserves legacy behavior: callable and multi-statement queries remain eligible for caching, while transaction queries bypass cache reads but may write their database results to the cache.

Security scope and application requirements

Warning

Database multi-tenancy is not an intended use case of the Remote Query Cache Plugin. Tracking more of the database authorization context at the driver layer is possible, but each additional check adds database round trips or makes more queries ineligible for caching. Fully tracking it would require revalidating every cache hit against the database, which would remove most of the latency benefit of caching. cacheEnableDatabaseMultiTenancy=true therefore trades completeness for performance: it is a best-effort cache security hardening measure, not a security boundary or authorization firewall. Within the documented usage below, it is designed to keep cached results for different supported tenant contexts separate; it does not detect every operation affecting authorization, row visibility, or object resolution. Applications that enable it accept the risk of possible cache authorization bypass, cache poisoning across tenants, and other security issues.

Applications should use remote query caching for database multi-tenancy only when all tenant-affecting session state is changed through the supported operations documented below, each executed on the wrapper connection as a JDBC call or as SQL that is not combined with other statements in the same call:

  • PostgreSQL: SET ROLE, SET SESSION AUTHORIZATION, SET search_path, SET SCHEMA, their supported RESET forms, and JDBC Connection.setSchema(...).
  • MySQL and MariaDB: SET ROLE (quote account names that include a host, for example 'tenant_a'@'%'), USE, RESET CONNECTION, and JDBC Connection.setCatalog(...).

Recognized opaque or unsupported operations—including PostgreSQL set_config(...), qualified custom settings, CALL/DO, EXECUTE, and FETCH; MySQL/MariaDB user variables, EXECUTE, and HANDLER; and executable comments, batches, and temporary-object creation—conservatively disable remote caching. This detection is pattern-based and not exhaustive, and it may differ between dialects; an operation that is not recognized is not thereby supported. For example, settings changed with plain SET, such as MySQL/MariaDB sql_mode or PostgreSQL datestyle, are not detected. State changes hidden inside SQL functions, stored procedure internals, connection initialization SQL, target-driver-specific APIs, or other mechanisms may not be observable by the plugin.

If query visibility depends on state outside the documented PostgreSQL role/search-path state or MySQL/MariaDB account/role/database state, do not cache those queries. Use outside these documented paths is unsupported. This includes tenant-supplied or otherwise untrusted SQL, using one wrapper connection from multiple threads at the same time, and proxies or poolers that can switch the underlying database session, such as PgBouncer in transaction pooling mode. Applications that do so accept the risk that cached results could be reused across tenant contexts and must not rely on this feature as an authorization boundary. Where cross-tenant disclosure is unacceptable, do not cache the query, or separate tenants by database user, cacheKeyPrefix, or cache cluster.

Internal connection pools (connectionPoolType) return the database session to the pool without resetting it, and the next wrapper connection starts with caching enabled again. Do not use them with statements that disable caching, such as PostgreSQL set_config(...) or MySQL/MariaDB SET @variable.

Anyone who can connect to the cache cluster can read and overwrite every cached result. cacheKeyPrefix only separates keys; it does not restrict access. Protect the cache cluster as you would the database.

Warning

Cache hits do not query the database to revalidate authorization. External changes such as GRANT, REVOKE, role-membership changes, or row-level security policy changes do not invalidate entries when the tracked cache-key state remains unchanged. Previously cached results may remain available until their TTL expires or they are evicted or explicitly removed.

Warning

Database multi-tenancy protection is disabled by default to preserve legacy behavior. Enable cacheEnableDatabaseMultiTenancy when query visibility depends on supported PostgreSQL effective roles or search paths, or MySQL/MariaDB authenticated accounts, active roles, or current databases. Leaving it disabled removes cache-key isolation based on these values.

Cache connection pooling

The plugin uses a dedicated connection pool to Valkey server with configurable connection timeout and the pool size. In addition, it supports TLS and non TLS connections

Cache Authentication

The plugin supports plain username/password authentication for generic Valkey deployments, and IAM authentication for AWS ElastiCache.

Cache Health Monitoring and Failure Handling

The plugin includes a health monitoring subsystem to avoid cascading failures when the cache is unhealthy.

  • CacheMonitor and states
    • Tracks cache health with states such as HEALTHY, SUSPECT, and DEGRADED.
    • Performs background health checks and tracks error types and memory pressure.
  • When the cache is unhealthy, the plugin can:
    • Bypass cache reads and writes, effectively operating as if caching were disabled,
    • Fail fast on cache access while still letting database queries proceed.
    • Errors from cache initialization or operations are treated as cache misses so that the database path still functions.

Limitations

  • The plugin currently doesn't support large objects as they are typically not optimal for caching. This includes:
    • CLOB (Character Large Object): ResultSet.getClob() and ResultSet.getNClob()
    • BLOB (Binary Large Object): ResultSet.getBlob()

Telemetry / Operational Visibility

When telemetry is enabled and a metrics backend is configured through telemetryMetricsBackend, this plugin submits the following metrics:

Metric Name Metric type Description
remoteQueryCache.cache.hit Counter Total number of queries with cache hits
remoteQueryCache.cache.miss Counter Total number of queries with cache misses
remoteQueryCache.cache.totalQueries Counter Total number of total queries evaluated for caching
remoteQueryCache.cache.malformedHints Counter Total number of queries with malformed query hints
remoteQueryCache.cache.bypass Counter Total number of queries that are evaluated but bypassed caching
remoteQueryCache.cache.error Counter Total number of errors encountered when processing cached queries
remoteQueryCache.cache.stateTransition Counter Total number of state transitions for a particular cache cluster endpoint
remoteQueryCache.cache.healthCheck.success Counter Total number of successful health checks to a particular cache cluster endpoint
remoteQueryCache.cache.healthCheck.failure Counter Total number of failed health checks to a particular cache cluster endpoint
remoteQueryCache.cache.healthCheck.consecutiveSuccess Gauge Max number of consecutive health check successes across clusters
remoteQueryCache.cache.healthCheck.consecutiveFailure Gauge Max number of consecutive health check failures across clusters

Cache lookups and the database queries behind a cache miss are also recorded as trace segments (jdbc-cache-lookup and jdbc-database-query).

See Monitoring for the metrics submitted by other plugins.

Query Performance with Caching

When querying for a single record out of a database table running PG/MySQL with 400K records and 1.3KB of data per record. The client application runs on an EC2 instance in the same VPC as the Valkey cache server and the database server. The observations are:

  • With an indexed lookup such as primary key lookup in a database table, the database operates like a key/value, leverages the buffered cache, and can operate on par with the speed of an in-memory key/value cache. The average latency with caching and without caching are both in low single-digit millisecond range without noticeable differences. As the number of concurrent client connection increases, the Valkey cache appears to scale better than PG/MySQL with significantly less overall CPU consumption.
  • With more expensive queries such as non-indexed queries, the query execution time in database can be significantly worse compared to doing the query lookup in the cache server. In our experiment, performing a query based on a column without index can take up to ~100ms to execute in PG/MySQL while consuming significant amount of CPU. With caching we are able to achieve single digit millisecond latency with only 1-2% of engine CPU in Valkey, which is > 90% reduction in latency and > 10x improvement to query performance. (Note: there are other complex query scenarios when caching becomes useful such as multi-table join operations)
  • A cache miss will lead to higher latency than querying the database directly due to the network RTT to do the lookup in the cache being added on top of a regular database query. Maximize cache hit rate and reduce the miss rate in order to benefit from caching.

Performance_results

Using Hibernate framework with AWS Advanced JDBC wrapper caching

Hibernate is the most widely adopted Object-Relational Mapping (ORM) framework in the Java ecosystem. It simplifies database interactions by mapping Java objects to relational database tables. It handles the conversion of data between Java object-oriented code and the relational database, reducing the need for developers to write tedious SQL and JDBC boilerplate code. Hibernate is also a popular implementation of the Jakarta Persistence API (JPA).

There are 2 ways to enable the remote query caching plugin in the AWS JDBC driver and get the query hint embedded into the underlying SQL string that is sent to the database:

Set a static query hint on the Entity class using @QueryHint annotation with @NamedQuery

See the following Hibernate code sample snippet (tested on Hibernate v5.6.15 and above)

import javax.persistence.*;

@Entity
@NamedQuery(
    name = "Plane.findAll",
    query = "SELECT p FROM Plane p",
    hints = @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_COMMENT, value = "+CACHE_PARAM(ttl=250s)")
)
@Table(name = "Plane")
public class Plane {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(nullable = false, unique = true)
  private String name;

  public Plane() {}

  public Plane(String name) {
    this.name = name;
  }
}

Example Hibernate program:

import org.hibernate.*;
import org.hibernate.boot.registry.*;
import org.hibernate.cfg.Configuration;

public void main() {
  final Configuration configuration = new Configuration();
  configuration.addAnnotatedClass(Plane.class);
  // Regular configurations
  ...

  // Enable query caching plugin
  configuration.setProperty("hibernate.use_sql_comments", "true");
  configuration.setProperty("hibernate.connection.wrapperPlugins", "remoteQueryCache");
  configuration.setProperty("hibernate.connection.cacheEndpointAddrRw", "mycache.amazonaws.com:6379");

  // Run the query
  try (StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
      .applySettings(configuration.getProperties())
      .build()) {
    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
    try (Session session = sessionFactory.openSession()) {
      // Run named query using annotation-based hint
      List<Plane> planes = session.createNamedQuery("Plane.findAll", Plane.class).getResultList();
      System.out.println("Planes from named query: " + planes.size());
    }
  }
}

Setting a dynamic query hint at runtime using the Query.setHint() method.

See the following Hibernate code sample snippet (tested on Hibernate v5.6.15 and above)

import javax.persistence.*;

@Entity
@Table(name = "Users")
public class Users {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  @Column(nullable = false)
  private String email;

  @Column(nullable = false)
  private String name;

  @Column(nullable = false, unique = true)
  private String phone;
}

Example Hibernate program:

import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.boot.registry.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

public void main() {
  final Configuration configuration = new Configuration();
  configuration.addAnnotatedClass(Users.class);
  // Regular configurations 
  ...
  // Caching enablement
  configuration.setProperty("hibernate.use_sql_comments", "true");
  configuration.setProperty("hibernate.connection.wrapperPlugins", "remoteQueryCache");
  configuration.setProperty("hibernate.connection.cacheEndpointAddrRw", "mycache.amazonaws.com:6379");

  // Issues the query
  try (StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
    .applySettings(configuration.getProperties())
    .build()) {
    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
    try (Session session = sessionFactory.openSession()) {
      // Find User with ID 100
      CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
      CriteriaQuery<Users> criteriaQuery = criteriaBuilder.createQuery(Users.class);
      Root<Users> root = criteriaQuery.from(Users.class);
      criteriaQuery.select(root).where(criteriaBuilder.equal(root.get("id"), 100));

      // Issue the query with caching query hint
      Query<Users> query = session.createQuery(criteriaQuery);
      query.setHint(org.hibernate.annotations.QueryHints.COMMENT, "CACHE_PARAM(ttl=100s)");
      Users user = query.uniqueResult();
    }
  }
}

This effectively generates the following underlying SQL with the caching related query hint prefixed in front which can then be handled by our caching plugin. i.e.

/* CACHE_PARAM(ttl=100s) */ select user0_.id as id1_2_, user0_.email as email2_2_,
    user0_.name as name3_2_, user0_.phone as phone4_2_ 
  from Users user0_ 
  where user0_.id=100

Using Spring JDBC framework with AWS Advanced JDBC wrapper caching

Here is a minimal Spring JDBC example program that uses the Remote Query Cache Plugin.

import static software.amazon.jdbc.plugin.cache.CacheConnection.CACHE_RW_ENDPOINT_ADDR;

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import software.amazon.jdbc.PropertyDefinition;

public void main() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("software.amazon.jdbc.Driver");
    dataSource.setUrl(ConnectionStringHelper.getWrapperUrl());
    dataSource.setUsername("username");
    dataSource.setPassword("password");


    Properties props = new Properties();
    // Regular configurations 
    ...
    // Caching enablement
    props.setProperty(PropertyDefinition.PLUGINS.name, "remoteQueryCache");
    props.setProperty(CACHE_RW_ENDPOINT_ADDR.name, "mycache.amazonaws.com:6379");

    dataSource.setConnectionProperties(props);
    
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Integer id = jdbcTemplate.queryForObject("/* CACHE_PARAM(ttl=100s) */ SELECT id from mytable where name = 'A'", Integer.class);
    assertEquals(10, id);
}

Alternatively, you can pass SQL hints with PreparedStatement in Spring JDBC, keep the hint in the static SQL string and continue to bind only dynamic values as parameters.

import static software.amazon.jdbc.plugin.cache.CacheConnection.CACHE_RW_ENDPOINT_ADDR;

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import software.amazon.jdbc.PropertyDefinition;

public void main(String status) {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("software.amazon.jdbc.Driver");
    dataSource.setUrl(ConnectionStringHelper.getWrapperUrl());
    dataSource.setUsername("username");
    dataSource.setPassword("password");

    Properties props = new Properties();
    // Regular configurations 
    ...
    // Caching enablement
    props.setProperty(PropertyDefinition.PLUGINS.name, "remoteQueryCache");
    props.setProperty(CACHE_RW_ENDPOINT_ADDR.name, "mycache.amazonaws.com:6379");
    dataSource.setConnectionProperties(props);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String SQL_FIND = "/* CACHE_PARAM(ttl=60s) */ SELECT * FROM customers WHERE status = ?";
    jdbcTemplate.query(
        con -> {
          PreparedStatement ps = con.prepareStatement(SQL_FIND);
          ps.setString(1, status);   // user input as parameter
          return ps;
        },
        (rs, rowNum) -> new Customer(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("status")
        )
    );
}

Security Considerations

The Remote Query Cache Plugin uses Java deserialization to reconstruct cached query results from the cache server. Since cache data is treated as untrusted input, deserialization is restricted to a set of known-safe types. If your query results contain third-party types (e.g. from database extensions like PGVector), you must register them via Driver.skipWrappingForType() or Driver.skipWrappingForPackage() to allow deserialization. Ensure that any registered classes have safe deserialization behavior and cannot be used as part of a gadget chain attack. See Enable Third Party Classes and Packages for details.

XML Columns

XML values retrieved from a cached result set are exposed as java.sql.SQLXML. When you obtain a source via SQLXML.getSource(DOMSource.class), getSource(SAXSource.class), or getSource(StAXSource.class), the driver parses the XML with DTDs and external entity resolution disabled, following the OWASP XML External Entity Prevention Cheat Sheet.

SQLXML.getSource(StreamSource.class) is disabled by default. Because a StreamSource returns the XML unparsed, the driver cannot control how the value is subsequently parsed downstream, so this path is not permitted for XML retrieved from the cache. Calling getSource(StreamSource.class) on a cached SQLXML value throws SQLException with the message StreamSource is not allowed for XML values retrieved from cached results....

How to fix: prefer DOMSource, SAXSource, or StAXSource, which the driver parses securely on your behalf. If your application specifically requires a StreamSource, set the plugin property cacheAllowStreamSource=true to restore the previous passthrough behavior; in that case the consumer of the returned StreamSource is responsible for configuring its own parser or transformer securely.

URL Columns

java.net.URL is not deserialized from the cache by default. Attempting to read a cached value of type java.net.URL throws SQLException with the message Deserialization of class [java.net.URL] is not allowed....

How to fix: prefer java.net.URI where possible, which has value-based equality and does not perform network resolution. If your application specifically requires java.net.URL and you accept the risk that cached URL values participate in DNS resolution when compared or hashed, set the plugin property cacheAllowUrl=true to opt in.

Other Example Programs

DatabaseConnectionWithCacheExample demonstrates how to enable and configure Remote Query Cache Plugin with the AWS Advanced JDBC Wrapper.