[id='configuring-sql-cache-stores-query_{context}']
= Using SQL queries to load data and manipulate persistent storage

SQL query cache stores let you load caches from multiple database tables, including from sub-columns in database tables, and perform insert, update, and delete operations.

.Prerequisites

* Have JDBC connection details. +
You can add JDBC connection factories directly to your cache configuration. +
For remote caches in production environments, you should add managed datasources to {brandname} Server configuration and specify the JNDI name in the cache configuration.
* Remote caches: Copy database drivers to the `server/lib` directory in your {brandname} Server installation.
+
[TIP]
====
Use the [command]`install` command with the {brandname} Command Line Interface (CLI) to download the required drivers to the `server/lib` directory, for example:

[source,bash,options="nowrap",subs=attributes+]
----
install org.postgresql:postgresql:42.1.3
----
====
* Embedded caches: Add the `infinispan-cachestore-sql` dependency to your `pom` file and make sure database drivers are on your application classpath.
+
[source,xml,options="nowrap",subs=attributes+]
----
include::dependencies_maven/persistence_sql_store.xml[]
----

.Procedure

. Create a cache store configuration that uses SQL queries.
+
.Declarative
[source,xml,options="nowrap",subs=attributes+,role="primary"]
----
query-jdbc-store xmlns="urn:infinispan:config:store:jdbc:{schemaversion}"
----
+
.Programmatic
[source,xml,options="nowrap",subs=attributes+,role="secondary"]
----
persistence().addStore(QueriesJdbcStoreConfigurationBuilder.class)
----

. Specify the database dialect with either `dialect=""` or `dialect()`, for example `dialect="H2"` or `dialect="postgres"`.
. Configure the SQL cache store with the properties you require, for example:
+
* To use the same cache store across your cluster, set `shared="true"` or `shared(true)`.
+
* To create a read only cache store, set `read-only="true"` or `.ignoreModifications(true)`.

. Define SQL query statements that load caches with data and modify database tables with the `queries` element or the `queries()` method.
+
[%autowidth,cols="1,1",stripes=even]
|===
|Query statement |Description

|`SELECT`
|Loads a single entry into caches.
You can use wildcards but must specify parameters for keys.
You can use labelled expressions.

|`SELECT ALL`
|Loads multiple entries into caches.
You can use the `*` wildcard if the number of columns returned match the key and value columns.
You can use labelled expressions.

|`SIZE`
|Counts the number of entries in the cache.

|`DELETE`
|Deletes a single entry from the cache.

|`DELETE ALL`
|Deletes all entries from the cache.

|`UPSERT`
|Modifies entries in the cache.

|===
+
[NOTE]
====
`DELETE`, `DELETE ALL`, and `UPSERT` statements do not apply to read only cache stores but are required if cache stores allow modifications.

Parameters in `DELETE` statements must match parameters in `SELECT` statements exactly.

Variables in `UPSERT` statements must have the same number of uniquely named variables that `SELECT` and `SELECT ALL` statements return.
For example, if `SELECT` returns `foo` and `bar` this statement must take only `:foo` and `:bar` as variables.
However you can apply the same named variable more than once in a statement.

SQL queries can include `JOIN`, `ON`, and any other clauses that the database supports.
====
. Add the `schema` element or the `.schemaJdbcConfigurationBuilder()` method and provide your Protobuf schema details if your database tables contain composite keys or values.

[role="_additional-resources"]
.Additional resources
* link:{config_docs}#jdbc-connection-factories_persistence[JDBC connection factories]
* link:{javadocroot}/org/infinispan/persistence/jdbc/common/DatabaseType.html[DatabaseType Enum lists supported database dialects]
* link:{configdocroot}infinispan-cachestore-sql-config-{schemaversion}.html[{brandname} SQL store configuration reference]
