Working with SQL Databases in Spring Boot Application

Hello friends here I am going to explain how to use SQL Database or Embedded Databases with Spring Boot. The Spring Framework provides extensive support for working with SQL databases. SQL Databases are an integral part of any application being development. They help in persisting application data. Spring provides a nice abstraction on top of JDBC API using JdbcTemplate and also provides great transaction management capabilities using annotation based approach. Spring provides support for any ORM tools like Hibernate. Spring Data provides an additional level of functionality, creating Repository implementations directly from interfaces and using conventions to generate queries from your method names.

Configuring a DataSource

We can configure DataSource with Spring Boot for two type databases as below

Configure Embedded Database or In Memory Database
Configure Production Database
Configure Database by using JNDI

1. Configure Embedded Database or In Memory Database

For development environment for any project it’s often convenient to select an in-memory embedded database. Obviously, in-memory databases do not provide persistent storage; you will need to populate your database when your application starts and be prepared to throw away data when your application ends. Spring Boot auto configure databases like H2, HSQL and Derby etc. You don’t need to provide any connection URLs, simply include a build dependency to the embedded database that you want to use.

Let’s see pom.xml file

<dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
   <groupId>org.hsqldb</groupId>
   <artifactId>hsqldb</artifactId>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
 </dependencies>

The spring-boot-starter-jdbc module transitively pulls tomcat-jdbc-{version}.jar which is used to configure the DataSource bean. In the above dependencies we have included the JDBC dependency – this gives us JdbcTemplate and other JDBC libraries, the org.hsqldb dependency adds embedded hsqldb.
If you have not defined any DataSource bean explicitly and if you have any embedded database driver in classpath such as H2, HSQL or Derby then SpringBoot will automatically registers DataSource bean using in-memory database settings.
These embedded DBs are in-memory and each time the application shuts down the schema and data gets erased. One way to keep schema and data in the in-memory is to populate it during application startup. This is taken care by Spring Boot.
We can have schema.sql and data.sql files in root classpath which SpringBoot will automatically use to initialize database. Spring JDBC uses these sql files to create schema and populate data into the schema.

In addition to schema.sql and data.sql, Spring Boot will load schema-${platform}.sql and data-${platform}.sql files if they are available in root classpath. One can create multiple schema.sql and data.sql files, one for each db platform. So we can have schema-hsqldb.sql, data-hsqldb.sql, schema-mysql.sql and so on. And the file to be picked is decided by the value assigned to the property spring.datasource.platform. In this post we are going to create a schema-hsqldb.sql file with the following contents:

CREATE TABLE users(userId INTEGER NOT NULL,userName VARCHAR(100) NOT NULL,userEmail VARCHAR(100) DEFAULT NULL,address VARCHAR(100) DEFAULT NULL,PRIMARY KEY (userId));

Create data.sql in src/main/resources as follows:

insert into users(userId, userName, userEmail, address) values (1000, 'Dinesh', 'dinesh@gmail.com', 'Delhi');
insert into users(userId, userName, userEmail, address) values (1001, 'Kumar', 'kumar@gmail.com', 'Greater Noida');
insert into users(userId, userName, userEmail, address) values (1002, 'Rajput', 'rajput@gmail.com', 'Noida');

Next is to create the User.java model class:

/**
 * 
 */
package com.dineshonjava.model;

/**
 * @author Dinesh.Rajput
 *
 */
public class User {
 private Integer userId;
 private String userName;
 private String userEmail;
 private String address;
 public Integer getUserId() {
  return userId;
 }
 public void setUserId(Integer userId) {
  this.userId = userId;
 }
 public String getUserName() {
  return userName;
 }
 public void setUserName(String userName) {
  this.userName = userName;
 }
 public String getUserEmail() {
  return userEmail;
 }
 public void setUserEmail(String userEmail) {
  this.userEmail = userEmail;
 }
 public String getAddress() {
  return address;
 }
 public void setAddress(String address) {
  this.address = address;
 }
 @Override
 public String toString() {
  return "User [userId=" + userId + ", userName=" + userName
    + ", userEmail=" + userEmail + ", address=" + address + "]";
 }
  
}

Next is to create a service class UserService which makes use of JdbcTemplate to insert data and retrieve data from hsqldb. There are two methods in the service class- createUser and findAllUsers. createUser adds a new row to the user table and findAllUsers fetches all the rows in the user table. Below is the UserService.java class definition:

/**
 * 
 */
package com.dineshonjava.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dineshonjava.model.User;
import com.dineshonjava.utils.UserRowMapper;

/**
 * @author Dinesh.Rajput
 *
 */
@Service
public class UserService {
 
 @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Transactional(readOnly=true)
    public List<User> findAll() {
        return jdbcTemplate.query("select * from users", 
                new UserRowMapper());
    }
 
    @Transactional(readOnly=true)
    public User findUserById(int id) {
        return jdbcTemplate.queryForObject(
            "select * from users where userId=?",
            new Object[]{id}, new UserRowMapper());
    }
 
    public User create(final User user) 
    {
        final String sql = "insert into users(userId,userName,userEmail,address) values(?,?,?,?)";
 
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, user.getUserId());
                ps.setString(2, user.getUserName());
                ps.setString(3, user.getUserEmail());
                ps.setString(4, user.getAddress());
                return ps;
            }
        }, holder);
 
        int newUserId = holder.getKey().intValue();
        user.setUserId(newUserId);
        return user;
    }
}


/**
 * 
 */
package com.dineshonjava.utils;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.dineshonjava.model.User;

/**
 * @author Dinesh.Rajput
 *
 */
public class UserRowMapper implements RowMapper<User>{

  @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setUserId(rs.getInt("userId"));
        user.setUserName(rs.getString("userName"));
        user.setUserEmail(rs.getString("userEmail"));
        user.setAddress(rs.getString("address"));
        return user;
    }

}

Now creating main application class and controller create users into user table and fetching all users from the table as json format.

/**
 * 
 */
package com.dineshonjava.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.dineshonjava.model.User;
import com.dineshonjava.service.UserService;

/**
 * @author Dinesh.Rajput
 *
 */
@RestController
public class UserController {
 
 @Autowired
 UserService userService;
 
 @RequestMapping("/")
    User home(User user) {
  user = userService.create(user);
        return user;
    }
 
 @RequestMapping("/users")
    List<User> findAllUsers() {
  List<User> users = userService.findAll();
        return users;
    }
}

Now let’s see main class of application SpringBootDataBaseApplication.java

package com.dineshonjava;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootDataBaseApplication {

 public static void main(String[] args) {
  SpringApplication.run(SpringBootDataBaseApplication.class, args);
 }
}

Following is the project structure whatever we made.

Now run this application as Spring Boot application in STS.

Whenever hit following URL then new row has been created into table suppose.

http://localhost:8080/?userId=1003&userName=Arnav&userEmail=arnav@gmail.com&address=Noida

One row has been created let’s see all data with following URL

http://localhost:8080/users

This display all users from database as JSON format in the browser as below

We have learned how to get started quickly with Embedded database. What if we want to use Non-Embedded databases like MySQL, Oracle or PostgreSQL etc? In-memory databases have lot of restriction and are useful in the early stages of the application and that too in local environments. As the application development progresses we would need data to be present even after application ends.

2. Configure Production Database

In the production environment In Memory database is not good choice because of there are lots of limitations. For production environment we can use any database like MySQL, DB2, Oracle, PostgreSQL etc. Production database connections can also be auto-configured using a pooling DataSource. Here’s the algorithm for choosing a specific implementation:

First we prefer the Tomcat pooling DataSource for its performance and concurrency, so if that is available we always choose it.
Otherwise, if HikariCP is available we will use it.
If Tomcat pooling datasource and HikariCP are not available then we can choose Commons DBCP, but we don’t recommend it in production.
Lastly, if Commons DBCP2 is available we will use it.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa ‘starters’ you will automatically get a dependency to tomcat-jdbc.

We can configure the database properties in application.properties file so that SpringBoot will use those jdbc parameters to configure DataSource bean.

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/dojdb
spring.datasource.username=root
spring.datasource.password=root

For any reason if you want to have more control and configure DataSource bean by yourself then you can configure DataSource bean in a Configuration class. If you register DataSource bean then SpringBoot will not configure DataSource automatically using AutoConfiguration.

You could also customize many additional settings for connection pooling either tomcat connection pooling (spring.datasource.tomcat.*) or any else like HikariCP (spring.datasource.hikari.*), DBCP (spring.datasource.dbcp.*) and DBCP2 (spring.datasource.dbcp2.*) as following:

# 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

Using another Connection Pooling library

By default Spring Boot pulls in tomcat-jdbc-{version}.jar and uses org.apache.tomcat.jdbc.pool.DataSource to configure DataSource bean. Spring Boot check the availability of classes in the classpath in following order by default:

org.apache.tomcat.jdbc.pool.DataSource
com.zaxxer.hikari.HikariDataSource
org.apache.commons.dbcp.BasicDataSource
org.apache.commons.dbcp2.BasicDataSource

If you want to override default behavior suppose you want use HikariDataSource then you can exclude tomcat-jdbc and add HikariCP dependency as follows:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <exclusions>
        <exclusion>
        <groupId>org.apache.tomcat</groupId>
        <artifactId>tomcat-jdbc</artifactId>
        </exclusion>
    </exclusions>
</dependency>
 
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

With this dependency configuration SpringBoot will use HikariCP to configure DataSource bean.

3. Connection to a JNDI DataSource

If you are deploying your Spring Boot application to an Application Server you might want to configure and manage your DataSource using your Application Servers built-in features and access it using JNDI.

The spring.datasource.jndi-name property can be used as an alternative to the spring.datasource.url, spring.datasource.username and spring.datasource.password properties to access the DataSource from a specific JNDI location.

For example, the following section in application.properties shows how you can access a Tomcat AS defined DataSource:

spring.datasource.jndi-name=java:tomcat/datasources/users

JPA & Spring Data with Spring Boot

In this section we will see how the same can be achieved using Java Persistance API. Spring Data provides excellent mechanism to achieve the persistence using JPA. The Java Persistence API is a standard technology that allows you to ‘map’ objects to relational databases. The spring-boot-starter-data-jpa POM provides a quick way to get started. It provides the following key dependencies:

Hibernate — One of the most popular JPA implementations.
Spring Data JPA — Makes it easy to implement JPA-based repositories.
Spring ORMs — Core ORM support from the Spring Framework.

Update pom.xml with adding following line for Spring Data JPA implementation.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Entity Classes

Next is to create an entity class that maps to the underlying table. Let us create User as shown below:

@Entity
public class User implements Serializable{
@Id
 private Integer userId;
private String userName;
 private String userEmail;
private String address
    // setters & getters}

Spring Data JPA Repositories

Next is to create a repository class that will provide us with basic APIs to interact with db and also provide facility to add new APIs to interact with db. We will be using the CrudRepository provided by spring data. It provides us with APIs to do CRUD operations and some find operations like findAll, findOne, count.

package com.dineshonjava.domain;

import org.springframework.data.domain.*;
import org.springframework.data.repository.*;

public interface UserRepository extends CrudRepository {
    
}

With Spring Data we do not need to write and query for inserting data to the table. In above UserRepository class can have all default method provided by the CrudRepository.

@Service
public class UserService {
 
@Autowired
    private UserRepository userRepository;
 
    @Transactional(readOnly=true)
    public List<User> findAll() {
        return userRepository.findAll();
    }
}

Summary

In this article we saw how we moved from in-memory database to installed databases and also saw how we could use JdbcTemplate and JPA to interact with the db. We didn’t have to write any sort of XML configuration and everything was managed by auto configuration provided by SpringBoot. Overall, you would have got good idea on how to use SQL Databases and Spring Boot together for persisting the application data.

For application https://github.com/DOJ-SoftwareConsultant/SpringBootDataBase

Happy Spring Boot Learning!!! 🙂

Spring Boot Related Topics