Languages
[Edit]
EN

Spring Boot - store session ID in MySQL database

8 points
Created by:
Yusef-Ewing
239

In this article, we would like to show how in Spring Boot, store session in MySQL database preventing losing session (logging-out user too) 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 with session store and spring boot application,
  • using spring.session.jdbc.initialize-schema=embedded we need to create tables in database by self - check step 3¬†(next step).

3. Execute on MySQL database following code:

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 code is located under path /org/springframework/session/jdbc/schema-mysql.sql.

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