How to Configure Spring Session with JDBC for MySQL

build.gradle

buildscript {
	ext {
		springBootVersion = '1.5.2.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'application'
apply plugin: 'org.springframework.boot'

jar {
	baseName = 'logbook-server'
	version = '0.0.1-SNAPSHOT'
}

sourceCompatibility = 1.8

repositories {
	mavenCentral()
}


dependencies {
	compile('org.springframework.boot:spring-boot-starter-data-jpa')
	compile('org.springframework.session:spring-session')
	compile('org.springframework.boot:spring-boot-starter-thymeleaf')
	compile('org.springframework.boot:spring-boot-starter-web')
	compile('org.springframework.boot:spring-boot-starter-web-services')
	compile('org.springframework.boot:spring-boot-starter-websocket')
	runtime('org.springframework.boot:spring-boot-devtools')
	runtime('mysql:mysql-connector-java')
	compileOnly('org.springframework.boot:spring-boot-configuration-processor')
	compileOnly('org.projectlombok:lombok')
	testCompile('org.springframework.boot:spring-boot-starter-test')

	// https://mvnrepository.com/artifact/joda-time/joda-time
	compile group: 'joda-time', name: 'joda-time', version: '2.9.7'

	// https://mvnrepository.com/artifact/net.time4j/time4j-parent
	compile group: 'net.time4j', name: 'time4j-parent', version: '4.25'

	// https://mvnrepository.com/artifact/org.apache.commons/commons-lang3
	compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.5'

	// https://mvnrepository.com/artifact/org.bouncycastle/bcprov-jdk15on
	compile group: 'org.bouncycastle', name: 'bcprov-jdk15on', version: '1.56'

	// https://mvnrepository.com/artifact/com.google.code.gson/gson
	compile group: 'com.google.code.gson', name: 'gson', version: '2.8.0'

	// https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-tomcat
	compile group: 'org.springframework.boot', name: 'spring-boot-starter-tomcat'

	// https://mvnrepository.com/artifact/commons-io/commons-io
	compile group: 'commons-io', name: 'commons-io', version: '2.5'
}

application.properties

server.port=13602
spring.session.store-type=jdbc
server.compression.enabled=true
server.use-forward-headers=true
server.compression.mime-types=application/json,application/xml,text/html,text/xml,text/plain,text/css,application/javascript
logging.file=logbook-server.log
spring.datasource.url=jdbc:mysql://localhost/logbook
spring.datasource.username=root
spring.datasource.password=12345
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.tomcat.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=50

# Validate the connection before borrowing it from the pool.
spring.datasource.tomcat.test-on-borrow=true

spring.jpa.hibernate.ddl-auto=create

HttpSessionConfig.java

@Configuration
@EnableJdbcHttpSession
public class HttpSessionConfig {

}

then execute this script on MySQL

CREATE TABLE SPRING_SESSION (
        SESSION_ID CHAR(36),
        CREATION_TIME BIGINT NOT NULL,
        LAST_ACCESS_TIME BIGINT NOT NULL,
        MAX_INACTIVE_INTERVAL INT NOT NULL,
        PRINCIPAL_NAME VARCHAR(100),
        CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (SESSION_ID)
) ENGINE=InnoDB;

CREATE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (LAST_ACCESS_TIME);

CREATE TABLE SPRING_SESSION_ATTRIBUTES (
        SESSION_ID CHAR(36),
        ATTRIBUTE_NAME VARCHAR(200),
        ATTRIBUTE_BYTES BLOB,
        CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_ID, ATTRIBUTE_NAME),
        CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_ID) REFERENCES SPRING_SESSION(SESSION_ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX SPRING_SESSION_ATTRIBUTES_IX1 ON SPRING_SESSION_ATTRIBUTES (SESSION_ID);

References
http://docs.spring.io/spring-session/docs/current/reference/html5/#api-jdbcoperationssessionrepository-storage
http://stackoverflow.com/questions/37398905/spring-session-with-jdbc-configuration-table-test-spring-session-doesnt-exis

Related