Languages
[Edit]
EN

Spring Boot - store session ID in MySQL database

8 points
Created by:
Yusef-Ewing
389

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

Alternative titles

  1. Spring Boot - persist session (IDs stored in MySQL database)
  2. Spring Boot - save session in MySQL and prevent session loosing on Tomcat restart
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.
Native Advertising
ūüöÄ
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

‚̧ԳŹūüíĽ ūüôā

Join