Storing Spring Sessions in Database using JDBC

In this post, We will take a look at setting up Spring Boot Session module using database/JDBC as the backend.

Introduction

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.

Spring session dependencies

First, you need to add the spring session jdbc to your spring boot application. You also need to add jdbc or JPA dependency. In this case, I’m using JPA.

<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>Code language: HTML, XML (xml)

By default, spring boot can auto-detect the session implementation based on the dependencies. If you have dependencies for two session stores, then you will have to specify one.

spring.session.store-type=jdbcCode language: Properties (properties)

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, here is the create script for MySQL.

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;Code language: SQL (Structured Query Language) (sql)

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

Verifying our setup

For instance, I have used in-memory H2 database. So, spring boot will create the database tables for me.

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=trueCode language: Properties (properties)

Once the application starts, we can see that the application creates entries in these tables.

Spring Session JDBC in action

Spring session store is pretty much a key value pair. Here, the key is the session id and the value is the session object. That is, We can store this object anywhere by simply serializing them. In our case, the spring module will use JDBC to store the session data.

The columns like LAST_ACCESS_TIME and EXPIRY_TIME make sure that the session expires properly. 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 < ?]Code language: plaintext (plaintext)

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 * * * *Code language: Properties (properties)

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 spring session table names

We can override the default table names 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_SESSIONCode language: Properties (properties)

However,The auto configuration can’t create custom table names. So, 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 section and your work is complete.

Summary

So far, We learned how to add Spring Session and how to configure it to use JDBC as session store. If you liked this article, you would also like to read the following write-ups.

• Storing Spring sessions in a Separate Database
• Spring Session using Redis as Session Store
• Using JdbcTemplate with Spring Boot
• Customizing spring Session Cookies
• Session Tracking modes in Spring security

The full example is available in this GitHub Repository.