Spring Session with JDBC

1. Overview

In this quick tutorial, we’ll learn how to use the Spring session JDBC to persist session information to a database.

For demonstration purposes, we’ll be using an in-memory H2 database.

2. Configuration Options

The easiest and fastest way to create our sample project is by using Spring Boot. However, we’ll also show a non-boot way to set things up.

Hence, you don’t need to complete both sections 3 and 4. Just pick one depending on whether or not we are using Spring Boot to configure Spring Session.

3. Spring Boot Configuration

First, let’s look at the required configuration for Spring Session JDBC.

3.1. Maven Dependencies

First, we need to add these dependencies to our project:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency> 
<dependency>
    <groupId>org.springframework.session</groupId>
    <artifactId>spring-session-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>runtime</scope>
</dependency>

Our application runs with Spring Boot, and the parent pom.xml provides versions for each entry. The latest version of each dependency can be found here: spring-boot-starter-web, spring-boot-starter-test, spring-session-jdbc, and h2.

Surprisingly, the only configuration property that we need to enable Spring Session backed by a relational database is in the application.properties:

spring.session.store-type=jdbc

4. Standard Spring Config (no Spring Boot)

Let’s also have a look at the integrating and configuring spring-session without Spring Boot – just with plain Spring.

4.1. Maven Dependencies

First, if we’re adding spring-session-jdbc to a standard Spring project, we’ll need to add spring-session-jdbc and h2 to our pom.xml (last two dependencies from the snippet in the previous section).

4.2. Spring Session Configuration

Now let’s add a configuration class for Spring Session JDBC:

@Configuration
@EnableJdbcHttpSession
public class Config
  extends AbstractHttpSessionApplicationInitializer {

    @Bean
    public EmbeddedDatabase dataSource() {
        return new EmbeddedDatabaseBuilder()
          .setType(EmbeddedDatabaseType.H2)
          .addScript("org/springframework/session/jdbc/schema-h2.sql").build();
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

As we can see, the differences are minimal. Now we have to define our EmbeddedDatabase and PlatformTransactionManager beans explicitly – Spring Boot does it for us in the previous configuration.

The above ensures that  Spring bean by the name of springSessionRepositoryFilter is registered with our Servlet Container for every request.

5. A Simple App

Moving on, let’s look at a simple REST API that saves demonstrates session persistence. 

5.1. Controller

First, let’s add a Controller class to store and display information in the HttpSession:

@Controller
public class SpringSessionJdbcController {

    @GetMapping("/")
    public String index(Model model, HttpSession session) {
        List<String> favoriteColors = getFavColors(session);
        model.addAttribute("favoriteColors", favoriteColors);
        model.addAttribute("sessionId", session.getId());
        return "index";
    }

    @PostMapping("/saveColor")
    public String saveMessage
      (@RequestParam("color") String color, 
      HttpServletRequest request) {
 
        List<String> favoriteColors 
          = getFavColors(request.getSession());
        if (!StringUtils.isEmpty(color)) {
            favoriteColors.add(color);
            request.getSession().
              setAttribute("favoriteColors", favoriteColors);
        }
        return "redirect:/";
    }

    private List<String> getFavColors(HttpSession session) {
        List<String> favoriteColors = (List<String>) session
          .getAttribute("favoriteColors");
        
        if (favoriteColors == null) {
            favoriteColors = new ArrayList<>();
        }
        return favoriteColors;
    }
}

6. Testing Our Implementation

Now that we have an API with a GET and POST method, let’s write tests to invoke both methods.

In each case, we should be able to assert that the session information is persisted in the database. To verify this, we’ll query the session database directly.

Let’s first set things up:

@RunWith(SpringRunner.class)
@SpringBootTest(
  webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@FixMethodOrder(MethodSorters.NAME_ASCENDING)
public class SpringSessionJdbcApplicationTests {

    @LocalServerPort
    private int port;

    @Autowired
    private TestRestTemplate testRestTemplate;

    private List<String> getSessionIdsFromDatabase() 
      throws SQLException {
 
        List<String> result = new ArrayList<>();
        ResultSet rs = getResultSet(
          "SELECT * FROM SPRING_SESSION");
        
        while (rs.next()) {
            result.add(rs.getString("SESSION_ID"));
        }
        return result;
    }

    private List<byte[]> getSessionAttributeBytesFromDb() 
      throws SQLException {
 
        List<byte[]> result = new ArrayList<>();
        ResultSet rs = getResultSet(
          "SELECT * FROM SPRING_SESSION_ATTRIBUTES");
        
        while (rs.next()) {
            result.add(rs.getBytes("ATTRIBUTE_BYTES"));
        }
        return result;
    }

    private ResultSet getResultSet(String sql) 
      throws SQLException {
 
        Connection conn = DriverManager
          .getConnection("jdbc:h2:mem:testdb", "sa", "");
        Statement stat = conn.createStatement();
        return stat.executeQuery(sql);
    }
}

Note the use of @FixMethodOrder(MethodSorters.NAME_ASCENDING) to control the order of test case execution. Read more about it here.

Let’s begin by asserting that the session tables are empty in the database:

@Test
public void whenH2DbIsQueried_thenSessionInfoIsEmpty() 
  throws SQLException {
 
    assertEquals(
      0, getSessionIdsFromDatabase().size());
    assertEquals(
      0, getSessionAttributeBytesFromDatabase().size());
}

Next, we test the GET endpoint:

@Test
public void whenH2DbIsQueried_thenOneSessionIsCreated() 
  throws SQLException {
 
    assertThat(this.testRestTemplate.getForObject(
      "http://localhost:" + port + "/", String.class))
      .isNotEmpty();
    assertEquals(1, getSessionIdsFromDatabase().size());
}

When the API is invoked for the first time, a session is created and persisted in the database. As we can see, there is only one row in the SPRING_SESSION table at this point.

Finally, we test the POST endpoint by providing a favorite color:

@Test
public void whenH2DbIsQueried_thenSessionAttributeIsRetrieved()
  throws Exception {
 
    MultiValueMap<String, String> map = new LinkedMultiValueMap<>();
    map.add("color", "red");
    this.testRestTemplate.postForObject(
      "http://localhost:" + port + "/saveColor", map, String.class);
    List<byte[]> queryResponse = getSessionAttributeBytesFromDatabase();
    
    assertEquals(1, queryResponse.size());
    ObjectInput in = new ObjectInputStream(
      new ByteArrayInputStream(queryResponse.get(0)));
    List<String> obj = (List<String>) in.readObject();
    assertEquals("red", obj.get(0));
}

As expected, SPRING_SESSION_ATTRIBUTES table persists the favorite color. Notice that we have to deserialize the contents of ATTRIBUTE_BYTES to a list of String objects since Spring does object serialization when persisting session attributes in the database.

7. How Does It Work?

Looking at the controller, there’s no indication of database persisting the session information. All the magic is happening in one line we added in the application.properties.

That is, when we specify spring.session.store-type=jdbc, behind the scenes, Spring Boot will apply a configuration that is equivalent to manually adding @EnableJdbcHttpSession annotation.

This creates a Spring Bean named springSessionRepositoryFilter that implements a SessionRepositoryFilter.

Another key point is that the filter intercepts every HttpServletRequest and wraps it into a SessionRepositoryRequestWrapper.

It also calls the commitSession method to persist the session information.

8. Session Information Stored in H2 Database

By adding the below properties, we could take a look at the tables where the session information is stored from the URL –  http://localhost:8080/h2-console/:

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

9. Conclusion

Spring Session is a powerful tool for managing HTTP sessions in a distributed system architecture. Spring takes care of the heavy lifting for simple use cases by providing a predefined schema with minimal configuration. At the same time, it offers the flexibility to come up with our design on how we want to store session information.

Finally, for managing authentication information using Spring Session you can refer to this article – Guide to Spring Session.