Spring Boot + Spring JDBC + H2 Database Example

← Back to Spring Boot Tutorial

In this tutorial, we will learn how to create a Spring boot application that connects to an H2 database using Spring JDBC. You’ll build an application using Spring’s JdbcTemplate to access data stored in an in-memory H2 database.

H2 is one of the popular in-memory databases written in Java. It can be embedded in Java applications or run in the client-server mode.

Note: We configure the H2 database with Spring boot to create and use an in-memory database in runtime, generally for unit testing or POC purposes. Remember an in-memory database is created/initialized when an application starts up; and destroyed when the application shuts down.

In this tutorial, we will learn -

• How to use Spring Boot Started JDBC?
• How to connect a Spring Boot project to a database using Spring JDBC?
• How to write a simple repository class with all the CRUD methods?
• How to execute basic queries using Spring JDBC?
• How to create a project using Spring Boot, Spring JDBC, and H2?
• How to use a spring-boot-starter-jdbc starter for using JDBC with the H2 database.

Tools and Technologies used

1. Spring boot 2+

2. Spring JDBC

3. Maven 3+

4. JDK 1.8

5. IDE - Eclipse or STS

6. H2 database

Development Steps

1. Create a Spring Boot Application
2. Maven dependencies
3. Database Setup
4. Creating Student Bean
5. Create Employee JDBC Repository
6. Run Application

1. Create a Spring Boot Application

There are many ways to create a Spring Boot application. You can refer below articles to create a Spring Boot application.

>> Create Spring Boot Project With Spring Initializer
>> Create Spring Boot Project in Spring Tool Suite [STS]

2. Maven dependencies

We are using H2 database so let's add H2 dependency to our pom.xml:

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

Here is a complete pom.xml looks like:

<?xml version="1.0" encoding="UTF-8"?>
<project
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>net.guides.springboot2</groupId>
    <artifactId>springboot2-jdbc-crud-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot2-jpa-crud-example</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath />
        <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3. Database Setup

We will create a table called employee with a few simple columns. We can initialize a schema by creating a schema.sql file in the resources.

create table employees
(
   id integer not null,
   first_name varchar(255) not null, 
   last_name varchar(255) not null,
   email_address varchar(255) not null,
   primary key(id)
);

4. Database and Logging Configuration

By default, Spring Boot configures the application to connect to an in-memory store with the username sa and an empty password. We use default configuration so no need to configure in an application.properties file. Let's just configure logging and JPA properties.

logging.level.org.hibernate.stat=debug
# Show all queries
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=trace

H2 provides a web interface called H2 Console to see the data. So you can enable h2 console in the application.properties with: spring.h2.console.enabled=true

5. Creating Employee Bean

Let's create a simple Employee bean to pass as method arguments.

package net.guides.springboot2.jdbc.model;

public class Employee {

    private long id;
    private String firstName;
    private String lastName;
    private String emailId;

    public Employee() {

    }

    public Employee(long id, String firstName, String lastName, String emailId) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.emailId = emailId;
    }

    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmailId() {
        return emailId;
    }
    public void setEmailId(String emailId) {
        this.emailId = emailId;
    }
}

6. Create Employee JDBC Repository

We would want to start with creating a simple repository. To talk to the database we will use a JdbcTemplate. Spring Boot Auto Configuration sees H2 in the classpath. It understands that we would want to talk to an in-memory database. It auto-configures a data source and also a JdbcTemplate connecting to that data source.

package net.guides.springboot2.jdbc.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.jdbc.model.Employee;

@Repository
public class EmployeeJDBCRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    class EmployeeRowMapper implements RowMapper < Employee > {
        @Override
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            employee.setId(rs.getLong("id"));
            employee.setFirstName(rs.getString("first_name"));
            employee.setLastName(rs.getString("last_name"));
            employee.setEmailId(rs.getString("email_address"));
            return employee;
        }
    }

    public List < Employee > findAll() {
        return jdbcTemplate.query("select * from employees", new EmployeeRowMapper());
    }

    public Optional < Employee > findById(long id) {
        return Optional.of(jdbcTemplate.queryForObject("select * from employees where id=?", new Object[] {
                id
            },
            new BeanPropertyRowMapper < Employee > (Employee.class)));
    }

    public int deleteById(long id) {
        return jdbcTemplate.update("delete from employees where id=?", new Object[] {
            id
        });
    }

    public int insert(Employee employee) {
        return jdbcTemplate.update("insert into employees (id, first_name, last_name, email_address) " + "values(?, ?, ?, ?)",
            new Object[] {
                employee.getId(), employee.getFirstName(), employee.getLastName(), employee.getEmailId()
            });
    }

    public int update(Employee employee) {
        return jdbcTemplate.update("update employees " + " set first_name = ?, last_name = ?, email_address = ? " + " where id = ?",
            new Object[] {
                employee.getFirstName(), employee.getLastName(), employee.getEmailId(), employee.getId()
            });
    }
}

7. Run Application

To keep things simple we will make the Application class implement CommandLineRunner and implement the run method to test JDBC methods:

package net.guides.springboot2.jdbc;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import net.guides.springboot2.jdbc.model.Employee;
import net.guides.springboot2.jdbc.repository.EmployeeJDBCRepository;

@SpringBootApplication
public class Application implements CommandLineRunner {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
 
    @Autowired
    private EmployeeJDBCRepository employeeRepository;
 
    @Override
    public void run(String... args) throws Exception {

        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10011L, "Ramesh", "Fadatare", "ramesh@gmail.com")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10012L, "John", "Cena", "john@gmail.com")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10013L, "tony", "stark", "stark@gmail.com")));
  
        logger.info("Employee id 10011 -> {}", employeeRepository.findById(10011L));

        logger.info("Update 10003 -> {}", employeeRepository.update(new Employee(10011L, "ram", "Stark", "ramesh123@gmail.com")));

        employeeRepository.deleteById(10013L);

        logger.info("All users -> {}", employeeRepository.findAll());
    }
 
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Output

← Back to Spring Boot Tutorial