{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"0415b385-befb-4edd-a90b-0f8d8c121aed"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"cbd19aa3-6864-443e-a7df-8940a21e2c5a"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"The JDBCStorageService is a compatible replacement for the JPAStorageService and provides a ","type":"text"},{"text":"StorageService ","type":"text","marks":[{"type":"link","attrs":{"href":"https://shibboleth.atlassian.net/wiki/spaces/IDP5/pages/3199509576"}}]},{"text":"on top of an RDBMS. It communicates directly with the database rather than using Hibernate ORM which has issues with reliability and the lack of provenance of its software artifacts.","type":"text"}]},{"type":"paragraph","content":[{"text":"It is possible to swap between the JPA and JDBC storage service (if running an older version of our software), and indeed to have different versions running on different nodes.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"The JPA Storage Service was removed from V5 of the IdP. Please migrate prior to upgrading the IdP.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Plugin Installation","type":"text"}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"Starting with IdP 4.2 you can the install the latest plugin version supported on your IdP version with","type":"text"},{"type":"hardBreak"},{"text":".\\plugin.sh -I net.shibboleth.plugin.storage.jdbc","type":"text","marks":[{"type":"code"}]}]}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"8e582239-aa69-4d53-ab31-68f9fcc233cb"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[262.0]},"content":[{"type":"paragraph","content":[{"text":"Plugin ID","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[65.0]},"content":[{"type":"paragraph","content":[{"text":"Module(s)","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[101.0]},"content":[{"type":"paragraph","content":[{"text":"Latest Version","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[259.0]},"content":[{"type":"paragraph","content":[{"text":"Bug Reporting","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[262.0]},"content":[{"type":"paragraph","content":[{"text":"net.shibboleth.plugin.storage.jdbc","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[65.0]},"content":[{"type":"paragraph","content":[{"text":"None","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[101.0]},"content":[{"type":"paragraph","content":[{"text":"2.1.0","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[259.0]},"content":[{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"https://shibboleth.atlassian.net/browse/JJDBC"}},{"text":" ","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"For a detailed guide on how to install plugins, see ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"https://shibboleth.atlassian.net/wiki/spaces/IDP4/pages/1294074003"}}]},{"text":". In summary, use the ","type":"text"},{"text":"plugin","type":"text","marks":[{"type":"code"}]},{"text":" command that ships with the IdP to install the plugin from either a local file pre-downloaded, from a URL, or (preferred) by plugin ID.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Preparation","type":"text"}]},{"type":"paragraph","content":[{"text":"If not upgrading from a JPAStorageService configuration, then you need to:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create the database table for the plugin to use.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download the appropriate JDBC driver.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"(Optionally, but recommended) Select a Connection Pooling implementation. DBCP2, included with the IdP software, is suggested as it requires no additional software.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"(If you are migrating from the JPAStorageService you do not need to make any changes to your database and you can use the same configuration for the DataSource as you did for the JPAStorageService. See more below.)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Creating the Database","type":"text"}]},{"type":"paragraph","content":[{"text":"Example Schemas are shown below.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Whatever you do, you MUST ensure the context and id columns are case-sensitively handled and compared. That is a requirement of the API that will be using the database. This is frequently NOT the default behavior of databases such as MySQL.","type":"text"}]},{"type":"paragraph","content":[{"text":"The specific examples that follow should NOT be assumed to be functional, as they likely are the product of different sources, varying amounts of testing (including none), and may not be current. Drivers get updated frequently and JDBC and database bugs appear and disappear with regularity. When in doubt, always grab new ones when problems appear.","type":"text"}]}]},{"type":"expand","attrs":{"title":"MySQL"},"content":[{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE TABLE StorageRecords (\n context varchar(255) NOT NULL,\n id varchar(255) NOT NULL,\n expires bigint DEFAULT NULL,\n value text NOT NULL,\n version bigint NOT NULL,\n PRIMARY KEY (context, id)\n);","type":"text"}]}]},{"type":"expand","attrs":{"title":"PostgreSQL or H2"},"content":[{"type":"codeBlock","content":[{"text":"CREATE TABLE StorageRecords (\n context varchar(255) NOT NULL,\n id varchar(255) NOT NULL,\n expires bigint DEFAULT NULL,\n value text NOT NULL,\n version bigint NOT NULL,\n PRIMARY KEY (context, id)\n);","type":"text"}]}]},{"type":"expand","attrs":{"title":"Oracle"},"content":[{"type":"codeBlock","content":[{"text":"CREATE TABLE StorageRecords(\n context varchar2(255) NOT NULL,\n id varchar2(255) NOT NULL,\n expires number(19,0),\n value clob NOT NULL,\n version number(19,0) NOT NULL,\n PRIMARY KEY (context, id)\n);","type":"text"}]}]},{"type":"expand","attrs":{"title":"IBM DB2"},"content":[{"type":"codeBlock","content":[{"text":"CREATE TABLE StorageRecords (\n context varchar(255) NOT NULL,\n id varchar(255) NOT NULL,\n expires bigint DEFAULT NULL,\n value clob NOT NULL,\n version bigint NOT NULL,\n PRIMARY KEY (context, id)\n);","type":"text"}]}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"The value column ","type":"text"},{"text":"must","type":"text","marks":[{"type":"strong"}]},{"text":" be arbitrarily wide to allow the JDBC storage service to back IdP Sessions","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Switching from the JPAStorageService","type":"text"}]},{"type":"paragraph","content":[{"text":"If you are currently running with the JPAStorageService you can reconfigure to use the JDBCStorageService relatively easily","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Locate the configuration (search for the class name ","type":"text"},{"text":"org.opensaml.storage.impl.JPAStorageService","type":"text","marks":[{"type":"code"}]},{"text":"in your configuration.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Remove the EntityManagerFactory bean, taking note of the ","type":"text"},{"text":"dataSource","type":"text","marks":[{"type":"code"}]},{"text":" property.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Remove also the VendorAdapter bean if present.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Change the StorageService bean","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Replace ","type":"text"},{"text":"class=\"org.opensaml.storage.impl.JPAStorageService\"","type":"text","marks":[{"type":"code"}]},{"text":" with ","type":"text"},{"text":"parent=\"shibboleth.JDBCStorageService\"","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Remove the constructor parameter and instead add a pointer to the dataSource you noted above ","type":"text"},{"text":"p:dataSource-ref=\"....\"","type":"text","marks":[{"type":"code"}]}]}]}]}]}]},{"type":"paragraph","content":[{"text":"At this stage you should be able to test the configuration. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Once it works you can change bean names appropriately and add any extra configuration as detailed below.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Configuring and Using JDBC DataSources","type":"text"}]},{"type":"paragraph","content":[{"text":"Using JDBC requires defining a Spring bean representing the ","type":"text"},{"text":"DataSource","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":" instance the IdP will create and inject into, among other places, this plugin’s StorageService definition. Note that a JDBC driver class is not the same as a ","type":"text"},{"text":"DataSource","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":". Generally a database driver will provide one or more ","type":"text"},{"text":"DataSource","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":" implementation classes to choose from and this is the class you will need to create via a Spring bean to inject into other objects.","type":"text"}]},{"type":"paragraph","content":[{"text":"A data source will typically look like this (with most of the settings depending on the particular driver:","type":"text"}]},{"type":"codeBlock","content":[{"text":"","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"JDBC Driver","type":"text"}]},{"type":"paragraph","content":[{"text":"You need to locate, download and verify the JDBC driver for your database and place it in ","type":"text"},{"text":"edit-webapp/WEB-INF/lib","type":"text","marks":[{"type":"em"}]},{"text":".","type":"text"},{"text":" ","type":"text","marks":[{"type":"strong"}]},{"text":"After populating ","type":"text"},{"text":"edit-webapp/WEB-INF/lib","type":"text","marks":[{"type":"em"}]},{"text":" you should execute ","type":"text"},{"text":"bin/build.sh","type":"text","marks":[{"type":"em"}]},{"text":" or ","type":"text"},{"text":"bin/build.bat","type":"text","marks":[{"type":"em"}]},{"text":" as appropriate for your environment.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Connection Pooling","type":"text"}]},{"type":"paragraph","content":[{"text":"For higher loads, we generally assume the use of a ","type":"text"},{"text":"DataSource","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":" that provides connection pooling. The Commons DBCP 2 library is included with the IdP and can be used for this purpose without downloading additional libraries.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Tomcat’s JDBC Pooling driver has been observed to cause connection leaks and is not supported.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The following libraries provide connection pooling functionality:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Commons DBCP 2","type":"text","marks":[{"type":"link","attrs":{"href":"http://commons.apache.org/proper/commons-dbcp/"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"HikariCP","type":"text","marks":[{"type":"link","attrs":{"href":"https://github.com/brettwooldridge/HikariCP"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"BoneCP","type":"text","marks":[{"type":"link","attrs":{"href":"https://github.com/wwadge/bonecp"}}]},{"text":" - Repository Marked Archived on Jun 3, 2024","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"As with the driver above, if you choose to use a third party pooling library not supplied with the software, you will need to add its jar(s) to ","type":"text"},{"text":"edit-webapp/WEB-INF/lib","type":"text","marks":[{"type":"em"}]},{"text":" and rebuild the warfile.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Storage Service Configuration","type":"text"}]},{"type":"paragraph","content":[{"text":"Once a ","type":"text"},{"text":"DataSource","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":" bean is in place, you need to add the definition of a bean inheriting from ","type":"text"},{"text":"shibboleth.JDBCStorageService","type":"text","marks":[{"type":"strong"}]},{"text":" into an appropriate configuration file (usually ","type":"text"},{"text":"global.xml","type":"text","marks":[{"type":"em"}]},{"text":"). The options you can provide to the bean are detailed below.","type":"text"}]},{"type":"expand","attrs":{"title":"Behavioral Options"},"content":[{"type":"paragraph","content":[{"text":"The behavior of the Storage Service is controlled by the following options","type":"text"}]},{"type":"table","attrs":{"layout":"default","localId":"bc16a37f-7fd6-4a51-9980-da21ddac35fa"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"Java Bean Property","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"Default","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"dataSource","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Bean ID","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"Required","type":"text","marks":[{"type":"em"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"Bean ID of the ","type":"text"},{"text":"DataSource ","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/javax/sql/DataSource?module=java.sql"}}]},{"text":"to use","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"cleanupInterval","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Duration","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"“PT10M” (or the value of the property ","type":"text"},{"text":"idp.storage.cleanupInterval","type":"text","marks":[{"type":"code"}]},{"text":" if it is set)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The time between one cleanup and another. A value of 0 indicates that no cleanup will be performed.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"retryableErrors","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Comma-delimited list","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"A comma separate list of SQL errors which will cause a failed transaction to be retried (a maximum of transactionRetry times)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"transactionIsolation","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"8 (Connection.TRANSACTION_SERIALIZABLE)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The level of transactional isolation required as described for the ","type":"text"},{"text":"Connection ","type":"text","marks":[{"type":"link","attrs":{"href":"http://shibboleth.net/cgi-bin/java-jdk.cgi/java/sql/Connection?module=java.sql"}}]},{"text":"Interface","type":"text"},{"type":"hardBreak"},{"text":"Starting in V2.0.0, if 0 (TRANSACTION_NONE) is specified then the transactional isolation is not set at the Connection level","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"transactionRetries","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Integer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"3","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"Number of retries if insertion fails due to database transaction bugs","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"verify","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Boolean","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"true","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"Whether to verify the database connection on startup","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"localLocking","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Boolean","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"false","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"Whether to do thread level locking to arbitrate access (for this IdP) to the the database. This can be useful in high contention situations when multiple transaction retries are happening.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"contextSize","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Integer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"255","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The size of the ‘context’ column in you database. ","type":"text"},{"text":"Only change this if you are using a non-standard DDI","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"keySize","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Integer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"255","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The size of the ‘key’ column in you database. ","type":"text"},{"text":"Only change this if you are using a non-standard DDI","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"valueSize","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Integer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"Integer.MAX_SIZE","type":"text","marks":[{"type":"code"}]},{"text":" (2","type":"text"},{"text":"31","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":")","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The mazimum size of the ‘value’ column in you database. ","type":"text"},{"text":"Only change this if you are using a non-standard DDI","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[206.0]},"content":[{"type":"paragraph","content":[{"text":"queryTimeout","type":"text"},{"text":" 2.1","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[137.0]},"content":[{"type":"paragraph","content":[{"text":"Duration","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[358.0]},"content":[{"type":"paragraph","content":[{"text":"PT5S","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[560.0]},"content":[{"type":"paragraph","content":[{"text":"The timeout for the operations","type":"text"}]}]}]}]}]},{"type":"expand","attrs":{"title":"SQL Options"},"content":[{"type":"paragraph","content":[{"text":"It is possible to redefine any or all all the SQL statements that are sent to the database by the JDBCStorageService. The following is the list ","type":"text"}]},{"type":"table","attrs":{"layout":"default","localId":"174ad961-35e1-4efe-9026-8da47c42fa53"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"Option Property Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"Default","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"Notes","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"preCreateQuerySQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT expires FROM StorageRecords WHERE context =? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to query the state of the table prior to creating a new record.","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as createCreateRecordSQL or createUpdateRecordSQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"createCreateRecordSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"INSERT INTO StorageRecords(context, id, expires, value, version) VALUES (?, ?, ?, ?, 1)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to create a new record.","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as preCreateQuerySQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"createUpdateRecordSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"UPDATE StorageRecords SET value=?, version=1, expires=? WHERE context=? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to create a update an expired record (instead of a create)","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as preCreateQuerySQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"deleteByContextExpiredSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"DELETE FROM StorageRecords WHERE context = ? AND expires < ?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to “reap” away expired records for a given context","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"deleteByContextSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"DELETE FROM StorageRecords WHERE context = ?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to remove all records for a given context","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"deleteByExpiredSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"DELETE FROM StorageRecords WHERE expires < ?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to remove all expired records (as part of the cleanup task)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"preDeleteQuerySQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT version FROM StorageRecords WHERE context =? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to determine whether the a record is the correct one to be deleted.","type":"text"}]},{"type":"paragraph","content":[{"text":"Issues in the same transaction as deleteRecordSQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"deleteRecordSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"DELETE FROM StorageRecords WHERE context=? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to delete a specific record.","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as preDeleteQuerySQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"preUpdateQuerySQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to determine the state of a record prior to its update","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as updateRecordSQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"updateRecordSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"UPDATE StorageRecords SET value=?, version=?, expires=? WHERE context=? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to update a specific record","type":"text"}]},{"type":"paragraph","content":[{"text":"Issued in the same transaction as preUpdateQuerySQL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"readAllByContextSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT id, expires, value, version FROM StorageRecords WHERE context = ?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to return all the records associated with a specific context","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"readAllSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT context, id, expires, value, version FROM StorageRecords","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to return all the records","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"readContextsSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT context FROM StorageRecords","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to return all the context names","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"readRecordSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to read a specified record.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[254.0]},"content":[{"type":"paragraph","content":[{"text":"updateExpiresByContextSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[445.0]},"content":[{"type":"paragraph","content":[{"text":"UPDATE StorageRecords SET expires = ? WHERE context = ? AND expires > ?","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[562.0]},"content":[{"type":"paragraph","content":[{"text":"The SQL to refresh the expiration of all currently unexpired records.","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"paragraph","content":[{"text":"In the example below use of Commons DBCP is demonstrated in the DataSource bean. When using other Connection Pool implementations change the class and properties appropriately (e.g., HikariCP: ","type":"text"},{"text":"class=\"com.zaxxer.hikari.HikariDataSource\" p:jdbcUrl=\"...\"","type":"text","marks":[{"type":"code"}]},{"text":")","type":"text"}]},{"type":"codeBlock","content":[{"text":" \n\n ","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Example conversion from JPAStorageService","type":"text"}]},{"type":"expand","attrs":{"title":"Old JPA Configuration"},"content":[{"type":"codeBlock","content":[{"text":"\n\n\n \n \n \n \n \n \n \n\n\n\n \n","type":"text"}]}]},{"type":"expand","attrs":{"title":"After Initial conversion"},"content":[{"type":"codeBlock","content":[{"text":"","type":"text"}]}]},{"type":"paragraph"}],"version":1}