SpringBoot Session JDBC 사용시 테이블명 변경하는 방법

 

구글링해서 많이 검색되는 블로그에서는 spring.session.jdbc.table-name 설정을 변경하면 된다고 하지만 실제로는 되지 않는다.

# application.properties
spring.session.store-type=jdbc
spring.session.jdbc.table-name=SPRING_ADMIN_SESSION
spring.session.jdbc.initialize-schema=always

변경할 SpringSession 테이블

• SPRING_SESSION -> SPRING_ADMIN_SESSION
• SPRING_SESSION_ATTRIBUTES -> SPRING_ADMIN_SESSION_ATTRIBUTES

세션설정 클래스(예:HttpSessionConfig) - @EnableJdbcHttpSession 어노테이션에 tableName을 설정하여야 SpringSessionJDBC 를 사용하는 테이블명이 정상적으로 변경된다.

import org.springframework.context.annotation.Configuration;
import org.springframework.session.jdbc.config.annotation.web.http.EnableJdbcHttpSession;

/**
 * SpringSession을 사용하는 테이블명 변경
 */
@Configuration
@EnableJdbcHttpSession(tableName = "SPRING_ADMIN_SESSION")
public class HttpSessionConfig{
	
}

하지만 SpringBoot Application 구동시 SPRING_ADMIN_SESSION 테이블이 없다고 Exception이 발생된다.

Caused by: java.sql.SQLSyntaxErrorException: (conn=141959) Table 'SPRING_ADMIN_SESSION' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:158) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:266) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:163) ~[mariadb-java-client-2.7.5.jar:na]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:677) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:616) ~[spring-jdbc-5.1.9.RELEASE.jar:5.1.9.RELEASE]
	... 57 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'SPRING_ADMIN_SESSION' doesn't exist
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220) ~[mariadb-java-client-2.7.5.jar:na]
	... 63 common frames omitted
Caused by: java.sql.SQLException: Table 'SPRING_ADMIN_SESSION' doesn't exist
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1695) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1557) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1520) ~[mariadb-java-client-2.7.5.jar:na]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318) ~[mariadb-java-client-2.7.5.jar:na]
	... 64 common frames omitted

spring.session.jdbc.initialize-schema=always 옵션으로 자동으로 테이블이 생성될 줄 알았으나 테이블이 생성되지 않는다.

SpringSession 라이브러리에 schema-mysql.sql 을 참조, 테이블명을 변경하여 생성하면 된다.

[SpringSession] schema-mysql.sql

-- DROP TABLE SPRING_ADMIN_SESSION_ATTRIBUTES;
-- DROP TABLE SPRING_ADMIN_SESSION;
  
CREATE TABLE SPRING_ADMIN_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_ADMIN_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

CREATE UNIQUE INDEX SPRING_ADMIN_SESSION_IX1 ON SPRING_ADMIN_SESSION (SESSION_ID);
CREATE INDEX SPRING_ADMIN_SESSION_IX2 ON SPRING_ADMIN_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_ADMIN_SESSION_IX3 ON SPRING_ADMIN_SESSION (PRINCIPAL_NAME);

CREATE TABLE SPRING_ADMIN_SESSION_ATTRIBUTES (
	SESSION_PRIMARY_ID CHAR(36) NOT NULL,
	ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
	ATTRIBUTE_BYTES BLOB NOT NULL,
	CONSTRAINT SPRING_ADMIN_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
	CONSTRAINT SPRING_ADMIN_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_ADMIN_SESSION(PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

테스트

Application 구동/로그인 후 SPRING_ADMIN_SESSION을 조회하면 데이터가 생성되는 것을 확인할 수 있다.

[SpringSession] SPRING_ADMIN_SESSION 조회

정리

1. application.properties 에 변경할 테이블명 정의(spring.session.jdbc.table-name)
2. 세션설정 클래스에 변경할 테이블명정의(@EnableJdbcHttpSession)
3. 데이터베이스에 DDL 적용(테이블생성)

개발환경

• SpringBoot 2.1.7 / SpringSession 2.1.8
• JDK 11
• MariaDB 10.3.x

참고

• SpringSession JDBC 공식사이트 : https://docs.spring.io/spring-session/reference/guides/boot-jdbc.html