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"?>
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <description>Demo project for Spring Boot</description>
        <relativePath />
        <!-- lookup parent from repository -->

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

# Show all queries

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;

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;

public class Application implements CommandLineRunner {

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

    private EmployeeMyBatisRepository employeeRepository;

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

        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10011 L, "Ramesh", "Fadatare", "[email protected]")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10012 L, "John", "Cena", "[email protected]")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10013 L, "tony", "stark", "[email protected]")));

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

        logger.info("Update 10003 -> {}", employeeRepository.update(new Employee(10011 L, "ram", "Stark", "[email protected]")));

        employeeRepository.deleteById(10013 L);

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

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