Spring session using JDBC

When running multiple instances of the same application, sharing the session data can be a good idea. In this post we will take a look at using a database as a session store for spring boot application.

Session store is usually a key-value map that contains a session id and the UserDetails of the currently logged-in user. With the Spring Sessions module, we get to supply different type of session stores.

To let spring boot use database as a session store, you need to bring in the spring session jdbc dependencies to your spring boot application.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.session</groupId>
    <artifactId>spring-session-jdbc</artifactId>
</dependency>

Note that the spring-session-jdbc requires either spring-boot-starter-data-jpa or spring-boot-starter-jdbc for its database auto-configurations. As in my example, I’m already using JPA for connecting to the database I chose to use spring-boot-starter-data-jpa.

By default, spring boot can auto-detect the session implementation based on the dependencies. If there are more than one types available, you need to specify which session store to be used as shown below.

spring.session.store-type=jdbc

Setting up the tables for storing session

When using embedded servers, spring boot automatically creates two tables called SPRING_SESSION and SPRING_SESSION_ATTRIBUTES. This behaviour is controlled by the spring.session.jdbc.initialize-schema Configuration. This configuration can be set to one of embedded, always or never and the default is embedded.

If you are using databases like MySQL, Postgres or Oracle, you may need to set the initialize-scema setting to never and create these tables manually. For example, the create tables script for MySQL is shown below.

CREATE TABLE SPRING_SESSION
(
    PRIMARY_ID            CHAR(36) NOT NULL,
    SESSION_ID            CHAR(36) NOT NULL,
    CREATION_TIME         BIGINT   NOT NULL,
    LAST_ACCESS_TIME      BIGINT   NOT NULL,
    MAX_INACTIVE_INTERVAL INT      NOT NULL,
    EXPIRY_TIME           BIGINT   NOT NULL,
    PRINCIPAL_NAME        VARCHAR(100),
    CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);

CREATE TABLE SPRING_SESSION_ATTRIBUTES
(
    SESSION_PRIMARY_ID CHAR(36)     NOT NULL,
    ATTRIBUTE_NAME     VARCHAR(200) NOT NULL,
    ATTRIBUTE_BYTES    BLOB         NOT NULL,
    CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
    CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION (PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Here you can find the table creation scripts for different databases.

Verifying our setup

For my example, I have used in-memory H2 database and hence, Spring boot will initialize the schema for me. Here is my configuration for the same.

spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=root
spring.datasource.password=root
# To enable h2 console to check embedded database
spring.h2.console.enabled=true

Once the application starts, we can see that the application creates entries in SPRING_SESSION and SPRING_SESSION_ATTRIBUTES tables with the details of users who had logged in.

How does it work?

Spring session store is a data structure that stores and updates session information. with the help of spring session jdbc, this session information can be written as database table entries. Whenever a new session is created, both SPRING_SESSION and SPRING_SESSION_ATTRIBUTES will get new entries with the help of JDBC templates auto-configured from spring-boot-starter-data-jpa or spring-boot-starter-jdbc.

The columns like CREATION_TIME, LAST_ACCESS_TIME, MAX_INACTIVE_INTERVAL, EXPIRY_TIME make sure the user session doesn’t go over the allocated time. A housekeeping process will keep looking for expired records and delete them if necessary. You can see these operations when running the application with debug=true.

2021-01-03 16:48:00.002 DEBUG 18824 --- [pool-1-thread-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]

By default, the housekeeping runs every minute. If you want to change it let’s say every 3 minutes, you need to configure the application as shown below.

spring.session.jdbc.cleanup-cron=0 */3 * * * *

The more you do clean up you get a cleaner session store. However, cleaning up every second might be an overkill.

So choose an optimal time that fits for your server.

Changing session table names

The default table names can be changed using spring.session.jdbc.table-name property. For example, you can instruct spring sessions to use the tables USER_SESSION and USER_SESSION_ATTRIBUTES using the following configuration.

spring.session.jdbc.table-name=USER_SESSION

For this configuration to work, you need to create the USER_SESSION and USER_SESSION_ATTRIBUTES tables manually. Just replace the table names in the script from setting up the tables and your work is complete.

If you plan on using a different database for storing session details, I would like you to take a look at Using separate Database for Spring Sessions.

The full example is at the GitHub link below.