Spring-Session Grails Plugin (Part 3)

Hi Folks hope you’re doing well!

I was quit busy since my last blog post. So this post is part of Spring-Session Grails Plugin blog series which will cover JDBC as your datastore.

In this blog post I’ll explain how you can use JDBC as your session store. To use JDBC as your datastore you need to create two tables in your database. Spring Session doesn’t use any type of ORM tool. You’ll need to create two table one is for session and other for session attributes and the format will be like <SESSION TABLE> and <SESSION TABLE>_ATTRIBUTES”.

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)
);

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 BYTEA,
 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
);

CREATE INDEX SPRING_SESSION_ATTRIBUTES_IX1 ON SPRING_SESSION_ATTRIBUTES (SESSION_ID);

To change datastore you need to add property in your Config.groovy.

springsession.sessionStore=SessionStore.JDBC

This will set JDBC as your datastore. By default it’ll try to connect H2 Database. Let’s checkout the some config properties with their default values.

Note: Some of the common properties explained in part 1. Those will work same for jdbc datastore.

springsession.jdbc.driverClassName="org.h2.Driver" // Driver class default is H2 driver
springsession.jdbc.url="jdbc:h2:~/test" // JDBC Connection string
springsession.jdbc.username="" // JDBC username. Default is "".
springsession.jdbc.password="" // JDBC password. Default is "".
springsession.jdbc.tableName="SessionData" // table name to store sessions

springsession.jdbc.pool.maxActive=10 // Connection pool max active
springsession.jdbc.pool.maxTotal=20 // Connection pool max total
springsession.jdbc.pool.minIdle=3 // Connection pool min idle
springsession.jdbc.pool.maxWaitMillis=10000 // Connection wait time
springsession.jdbc.pool.defaultAutoCommit=true // autocommit true by default
springsession.jdbc.pool.defaultReadOnly=false // read only sessions
springsession.jdbc.pool.defaultTransactionIsolation=Connection.TRANSACTION_READ_COMMITTED // transaction isolation  
springsession.jdbc.pool.validationQuery="SELECT 1" // Validate connection query  

By default it uses Java serialization. To use Json Serialization please visit to first blog of this series. First 2 steps will be same but in 3^rd step Register my module class with spring-session plugin you will have to use jdbc specific jackson.modules property.

springsession.jdbc.jackson.modules = ['demo.SimpleModule']