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:

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:

img

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
img
  • Right click mybatis directory: New > File
  • Fill in: mybatis-config.xml
  • Click “OK” button
img

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
img
  • Right click co.dongchen.shop.common.model directory: New > Java Class
  • Fill in: Product
  • Click “OK” button
img

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
img
  • Right click co.dongchen.shop.mapper directory: New > Java Class
  • Fill in: ProductMapper
  • Change Kind to: Interface
  • Click “OK” button
img

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
img
  • Right click mapper directory: New > File
  • Fill in: product.xml
  • Click “OK” button
img

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
img
  • Right click co.dongchen.shop.service directory: New > Java Class
  • Fill in: ProductService
  • Click “OK” button
img

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
img
  • Right click co.dongchen.shop.controller directory: New > Java Class
  • Fill in: ProductController
  • Click “OK” button
img

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
img

Choose the correspondent option and press enter.

View Products in the Browser 

1

http://localhost:8080/products
img

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

Buy me a coffeeBuy me a coffee