Spring Boot - store session ID in MySQL database

In this article, we would like to show how in Spring Boot 2, store session in MySQL database, preventing losing session when the server is restarted.

Note: the presented solution was tested under Spring Boot 2.4 that was working with Tomcat 9 and MySQL 8.

Simple steps:

1. Add JDBC session dependency to pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

...
	<dependencies>
...
		<dependency>
			<groupId>org.springframework.session</groupId>
			<artifactId>spring-session-jdbc</artifactId>
			<version>2.4.3</version>
		</dependency>
...
    </dependencies>
...
</project>

Note: change 2.4.3 to your spring version.

2. Enable JDBC storage in application.properties file:

...

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/my_database

...

spring.session.store-type=jdbc
spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-@@platform@@.sql
spring.session.jdbc.initialize-schema=embedded
spring.session.jdbc.table-name=SPRING_SESSION

...

Notes:

• my_database will be shared between session store and spring boot application,
• spring.session.jdbc.initialize-schema=embedded disables tables auto creation - in step 3 you can find how to create tables by self (next step).

3. Execute the following query on MySQL database:

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)
);

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
);

Note: the query is located under path /org/springframework/session/jdbc/schema-mysql.sql.

See also

1. Spring Boot - persist session