EN
Spring Boot - store session ID in MySQL database
8 points
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:
xxxxxxxxxx1
2
<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">3
<modelVersion>4.0.0</modelVersion>4
5
...6
<dependencies>7
...8
<dependency>9
<groupId>org.springframework.session</groupId>10
<artifactId>spring-session-jdbc</artifactId>11
<version>2.4.3</version>12
</dependency>13
...14
</dependencies>15
...16
</project>Note: change
2.4.3to your spring version.
2. Enable JDBC storage in application.properties file:
xxxxxxxxxx1
...2
3
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver4
spring.datasource.url=jdbc:mysql://localhost:3306/my_database5
6
...7
8
spring.session.store-type=jdbc9
spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-@@platform@@.sql10
spring.session.jdbc.initialize-schema=embedded11
spring.session.jdbc.table-name=SPRING_SESSION12
13
...Notes:
my_databasewill be shared between session store and spring boot application,spring.session.jdbc.initialize-schema=embeddeddisables 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:
xxxxxxxxxx1
CREATE TABLE SPRING_SESSION (2
PRIMARY_ID CHAR(36) NOT NULL,3
SESSION_ID CHAR(36) NOT NULL,4
CREATION_TIME BIGINT NOT NULL,5
LAST_ACCESS_TIME BIGINT NOT NULL,6
MAX_INACTIVE_INTERVAL INT NOT NULL,7
EXPIRY_TIME BIGINT NOT NULL,8
PRINCIPAL_NAME VARCHAR(100),9
CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)10
);11
12
CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);13
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);14
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);15
16
CREATE TABLE SPRING_SESSION_ATTRIBUTES (17
SESSION_PRIMARY_ID CHAR(36) NOT NULL,18
ATTRIBUTE_NAME VARCHAR(200) NOT NULL,19
ATTRIBUTE_BYTES BLOB NOT NULL,20
CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),21
CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE22
);Note: the query is located under path
/org/springframework/session/jdbc/schema-mysql.sql.
