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

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.
* Generate Protobuf schema for any composite keys or composite values and register your schemas with {brandname}.
+
[TIP]
====
{brandname} recommends generating Protobuf schema with the ProtoStream processor.
For remote caches, you can register your schemas by adding them through the {brandname} Console, CLI, or REST API.
====

.Procedure

. Add database drivers to your {brandname} deployment.
+
* 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,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[]
----

. Open your {brandname} configuration for editing.
. Add a SQL query cache store.
+
.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 add Protobuf schema configuration for composite keys or values.
.. Specify the package name with the `package` attribute or `package()` method.
.. Specify composite values with the `message-name` attribute or `messageName()` method.
.. Specify composite keys with the `key-message-name` attribute or `keyMessageName()` method.
.. Set a value of `true` for the `embedded-key` attribute or `embeddedKey()` method if your schema includes keys within values.

. Save the changes to your configuration.

[role="_additional-resources"]
.Additional resources
* link:{encoding_docs}[Cache encoding and marshalling: Generate Protobuf schema and register them with {brandname}]
* link:{code_tutorials_root}/infinispan-remote/persistence[Persistence code tutorial with remote caches]
* 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]
