We first need to install a MariaDB 10.x database locally, and then access the database through MyBatis configuration.

Data Preparation

Install Database Locally

Since I also develop PHP projects on my Windows 10 desktop PC, I’ll use the MariaDB 10.x that comes with the WAMPSERVER for convenience:

Wampserver

Create Database and Table

Run following SQL script in MySQL or MariaDB database console:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


-- Create a database called shop
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Use the created database
USE shop;

-- Create a table called product, it contains three fields
CREATE TABLE product (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` INT(11) NOT NULL,
  `updated_at` INT(11) NOT NULL,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(50) NOT NULL,
  `stock` INT(11) NOT NULL,
  `price` INT(11) NOT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `images` VARCHAR(1024) NOT NULL DEFAULT '',
  `description` VARCHAR(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

-- Insert two test data into product table
INSERT INTO
product(`created_at`, `updated_at`, `sku`, `name`, `stock`, `price`, `description`)
VALUES
(UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), "SKU-001", "Green T-Shirt", 99, 999, "Green T-Shirt For Sale!"),
(UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), "SKU-002", "Red T-Shirt", 99, 999, "Red T-Shirt For Sale!");

-- Show all product data
SELECT * FROM product;

After executing the above SQL scripts, you may see the following results:

Dependencies and Configurations

Add MyBatis and MySQL Dependencies

Add MyBatis dependency into pom.xml:

1
2
3
4
5


<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
</dependency>

Add MySQL dependency into pom.xml:

1
2
3
4
5


<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

Add JDBC Data Source

Add JDBC Data Source into application.properties

1
2
3
4


spring.datasource.url=jdbc:mysql://localhost:3307/shop?characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Add MyBatis Configurations

Add MyBatis configuration path into application.properties:

1

mybatis.config-location=classpath:/mybatis/mybatis-config.xml

Right click resources directory: New > Directory
Fill in: mybatis
Click “OK” button

Right click mybatis directory: New > File
Fill in: mybatis-config.xml
Click “OK” button

Copy and paste the following content into mybatis-config.xml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!-- Disable Caches  -->
        <setting name="cacheEnabled" value="false"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="defaultExecutorType" value="REUSE"/>
        <!-- Transaction Timeout -->
        <setting name="defaultStatementTimeout" value="600"/>
    </settings>

    <typeAliases>
        <typeAlias type="co.dongchen.shop.common.model.Product" alias="product" />

    </typeAliases>

    <mappers>
        <mapper resource="mapper/product.xml" />
    </mappers>

</configuration>

Model View Controller (MVC)

Create Model

Right click java directory: New > Package
Fill in: co.dongchen.shop.common.model
Click “OK” button

Right click co.dongchen.shop.common.model directory: New > Java Class
Fill in: Product
Click “OK” button

Copy and paste the following content into Product.java:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137


package co.dongchen.shop.common.model;

import com.google.common.collect.Lists;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

public class Product {

    private Long id;
    private Integer createdAt;
    private Integer updatedAt;
    private String sku;
    private String name;
    private Integer stock;
    private Integer price;
    private Boolean isEnabled;
    private String images;
    private String description;

    private String mainImage;
    private List<String> imageList = Lists.newArrayList();
    private Boolean isFavourite;
    private List<MultipartFile> imageFiles;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Integer createdAt) {
        this.createdAt = createdAt;
    }

    public Integer getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(Integer updatedAt) {
        this.updatedAt = updatedAt;
    }

    public String getSku() {
        return sku;
    }

    public void setSku(String sku) {
        this.sku = sku;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getStock() {
        return stock;
    }

    public void setStock(Integer stock) {
        this.stock = stock;
    }

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }

    public Boolean getEnabled() {
        return isEnabled;
    }

    public void setEnabled(Boolean enabled) {
        isEnabled = enabled;
    }

    public String getImages() {
        return images;
    }

    public void setImages(String images) {
        this.images = images;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getMainImage() {
        return mainImage;
    }

    public void setMainImage(String mainImage) {
        this.mainImage = mainImage;
    }

    public List<String> getImageList() {
        return imageList;
    }

    public void setImageList(List<String> imageList) {
        this.imageList = imageList;
    }

    public Boolean getFavourite() {
        return isFavourite;
    }

    public void setFavourite(Boolean favourite) {
        isFavourite = favourite;
    }

    public List<MultipartFile> getImageFiles() {
        return imageFiles;
    }

    public void setImageFiles(List<MultipartFile> imageFiles) {
        this.imageFiles = imageFiles;
    }
}

Create Mapper Interface

Right click java directory: New > Package
Fill in: co.dongchen.shop.mapper
Click “OK” button

Right click co.dongchen.shop.mapper directory: New > Java Class
Fill in: ProductMapper
Change Kind to: Interface
Click “OK” button

Copy and paste the following content into ProductMapper.java:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


package co.dongchen.shop.mapper;

import co.dongchen.shop.common.model.Product;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ProductMapper {

    List<Product> queryProducts();
}

Create Mapper XML

Right click resources directory: New > Directory
Fill in: mapper
Click “OK” button

Right click mapper directory: New > File
Fill in: product.xml
Click “OK” button

Copy and paste the following content into product.xml:

1
2
3
4
5
6
7
8
9


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="co.dongchen.shop.mapper.ProductMapper">
    <select id="queryProducts" resultType="product">
        select id, created_at, updated_at, sku, name, stock, price, is_enabled, images, description from product
    </select>
</mapper>

Create Service

Right click java directory: New > Package
Fill in: co.dongchen.shop.service
Click “OK” button

Right click co.dongchen.shop.service directory: New > Java Class
Fill in: ProductService
Click “OK” button

Copy and paste the following content into ProductService.java:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


package co.dongchen.shop.service;

import co.dongchen.shop.common.model.Product;
import co.dongchen.shop.mapper.ProductMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ProductService {

    @Autowired
    private ProductMapper productMapper;

    public List<Product> getProducts() {
        return productMapper.queryProducts();
    }
}

Create Controller

Right click java directory: New > Package
Fill in: co.dongchen.shop.controller
Click “OK” button

Right click co.dongchen.shop.controller directory: New > Java Class
Fill in: ProductController
Click “OK” button

Copy and paste the following content into ProductController.java:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


package co.dongchen.shop.controller;

import co.dongchen.shop.common.model.Product;
import co.dongchen.shop.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class ProductController {

    @Autowired
    private ProductService productService;

    @RequestMapping("products")
    public List<Product> getProducts() {
        return productService.getProducts();
    }
}

Verify

Run the App

1
2


// Short Cut for "Run the Program"
Alt + Shift + F10

Choose the correspondent option and press enter.

View Products in the Browser

1

http://localhost:8080/products

References Wampserver, mybatis-spring-boot-autoconfigure, Maven Repository: MySQL Connector/J » 8.0.15

Buy me a coffee

2. Database Access - MyBatis and MariaDB 10.x

Contents