Spring Boot + MyBatis CRUD + H2 Database Example

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

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.
MyBatis removes the need for manually writing code to set parameters and retrieve results. It provides simple XML or Annotation-based configuration to map Java POJOs to a database. In this example, we will use MyBatis annotations for configuration to map Java POJOs to a database.
In this tutorial, we will learn -
  • How to connect a Spring Boot project to the database using myBatis?
  • How to write a simple repository class with all the CRUD methods to execute queries using myBatis?
  • How to execute basic queries using myBatis?
  • How to create a project using Spring Boot, myBatis and H2?

Tools and Technologies used

1. Spring boot 2+
2. MyBatis
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. Database and Logging Configuration
  5. Creating Student Bean
  6. Create Employee JDBC Repository
  7. 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.

2. Maven dependencies

<?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-mybatis-crud-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot2-mybatis-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.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.1</version>
        </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 employees 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

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

5. Creating Employee Bean

Let's create a simple Employee bean:
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 MyBatis Repository

We would want to start with creating a simple repository. @Mapper indicates that this is a MyBatis mapper class.
package net.guides.springboot2.jdbc.repository;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

@Mapper
public interface EmployeeMyBatisRepository {
    @Select("select * from employees")
    public List < Employee > findAll();

    @Select("SELECT * FROM employees WHERE id = #{id}")
    public Employee findById(long id);

    @Delete("DELETE FROM employees WHERE id = #{id}")
    public int deleteById(long id);

    @Insert("INSERT INTO employees(id, first_name, last_name,email_address) " +
        " VALUES (#{id}, #{firstName}, #{lastName}, #{emailId})")
    public int insert(Employee employee);

    @Update("Update employees set first_name=#{firstName}, " +
        " last_name=#{lastName}, email_address=#{emailId} where id=#{id}")
    public int update(Employee employee);
}
Let's understand the above code. The findById method to retrieve an employee by id:
 @Select("SELECT * FROM employees WHERE id = #{id}")
 public Employee findById(long id);
@Select("SELECT * FROM employees WHERE id = #{id}") - @Select highlights that this is a select query. #{id} is used to indicate that id is a parameter to this query.
You can observe that are defining a simple interface method without the implementation.
@Select("select * from employees")
public List<Employee> findAll();
@Select("select * from student") - Since we want to return a list of students, we do not need to pass any parameters. It's a simple @Select query.

7. Run Application

To keep things simple we will make the Application class implement CommandLineRunner and implement 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.EmployeeMyBatisRepository;

@SpringBootApplication
public class Application implements CommandLineRunner {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private EmployeeMyBatisRepository employeeRepository;

    @Override
    public void run(String...args) throws Exception {

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

        logger.info("Employee id 10011 -> {}", employeeRepository.findById(10011 L));

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

        employeeRepository.deleteById(10013 L);

        logger.info("All users -> {}", employeeRepository.findAll());
    }

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

Output

Comments