Spring Boot + MyBatis + MySQL Example

Author:
In this tutorial, we will learn how to create a Spring boot application that connects to a MySQL database using MyBatis. You’ll build an application using myBatis to access data stored in a MySQL database.
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 MySQL?

Tools and Technologies used

1. Spring boot 2+
2. MyBatis
3. Maven 3+
4. JDK 1.8
5. IDE - Eclipse or STS
6. MySQL connector and 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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3. Database Setup

Let's create a MySQL database using the following command:
create database demo;
Execute the following DDL script of employees table in the above-created database:
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

Add the following configuration to application.properties file:
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/demo?useSSL=false
spring.datasource.username = root
spring.datasource.password = root


## Hibernate Properties
#The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

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

Related Spring and Spring Boot Tutorials/Guides:

Spring Boot Tutorials [500+] Spring Boot Testing Tutorial Spring Boot Microservice Tutorial Spring Boot Kafka Microservices Spring Boot + Apache Kafka Tutorial Spring Core Tutorial Spring MVC Tutorial Spring Data JPA Tutorial Spring Framework for Beginners Spring AOP Tutorial Spring Security Tutorial Spring Exceptions Tutorial Spring Boot Interview Questions Spring Boot Microservices Interview Questions Apache Kafka Tutorials Docker Tutorials and Guides Spring Boot RabbitMQ Tutorials Angular CRUD Example with Spring Boot Spring Boot + Angular 12 CRUD Full Stack Spring Boot + Angular 8 CRUD Full Stack Spring Boot + Angular 10 CRUD Full Stack Spring Boot + React JS CRUD Full Stack React JS ( React Hooks) + Spring Boot Spring Boot Thymeleaf CRUD Full Stack Spring Boot User Registration and Login Node Js + Express + MongoDB CRUD Vue JS + Spring Boot REST API Tutorial

Comments

  1. Unknown21 April 2020 at 23:33

    @Mapper annotation is not supporting in latest release of mybatis..
    please update the tutuorial

    ReplyDelete
  2. Nano-Tech8 July 2020 at 00:24

    @Mapper is not map to Employee object unless define this in application.properties

    mybatis.configuration.map-underscore-to-camel-case=true

    ReplyDelete

Post a Comment

Leave Comment