Working with SQL Databases in Spring Boot Application

Configuring a DataSource

1. Configure Embedded Database or In Memory Database
2. Configure Production Database
3. Configure Database by using JNDI

 1. Configure Embedded Database or In Memory Database

<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.

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

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

/**
 * 
 */
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 + "]";
 }
  
}

/**
 * 
 */
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;
    }

}

/**
 * 
 */
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;
    }
}

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

2. Configure Production Database

• 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.

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

# 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

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

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

3. Connection to a JNDI DataSource

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

JPA & Spring Data with Spring Boot

• 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.

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

@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

package com.dineshonjava.domain;

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

public interface UserRepository extends CrudRepository {
    
}

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

Summary