MyBatis “minimalka”

MyBatis “minimalka”

Hello, Habre! My name is Petro Gusarov, I am a Java programmer at CDEK. In this article, I will talk about the not very common MyBatis framework.

Why MyBatis? Because we at CDEK use it in most projects, and it has performed very well. A little complicated and unusual at the entry stage, but these disadvantages are covered by its flexibility. “But there is Hibernate, Jooq, JDBC and something else,” experienced people will say. There is, but this article will be about MyBatis.

The article will be useful for beginners who wanted to try this framework or tried but something did not work out.

Content

What will we do?

  • Let’s look at the pros and cons of this framework

  • Let’s deploy MyBatis based on Spring Boot (so faster)

  • Let’s write and run a couple of requests

  • Let’s see in which cases it is better to use MyBatis than other frameworks.

Why MyBatis

I have two news: good and bad. Where to start? Okay, let’s start with the bad, or rather, with the disadvantages:

  • all queries will have to be written in native SQL in XML files. Stand, do not go! Not everything is as scary as it sounds 🙂

Now listen, bird: you will write SQL in XML (frame from the film “Wings, Legs and Tails”)

Now to the benefits:

  • full control over requests to the database;

  • it is much easier to impose logic on legacy DB schemes (just map queries to entities, the rest is done by “bird”);

  • during the development and complexity of the product, you will spend less time on query optimization than in other frameworks;

  • data processing speed is higher. But there are nuances here: the developer is responsible for generating requests, and it depends only on him how quickly the data exchange between the application and the database will work;

  • no knowledge of additional states of your object is required, as in Hibernate.

So let’s start. What do many people usually do when they start learning a new framework? Open official documentation they connect it and start poking from different sides. As a last resort, they try to find a ready-made example on the network. A framework is considered successful if it can be lifted, adding a dependency and a couple of annotations, and everything works. With MyBatis it is a little different – a phrase from the cartoon is suitable here: “it’s better to lose a day, then fly in 5 minutes”.

*in the framework of this article, we will touch only on mappers

Let’s launch the bird

For the most impatient, the link to the example lies here.

Let’s create a project based on Spring Boot. I will not describe the details, you already know how it is done. Who is not aware of you here. Add such dependencies as mybatis, h2, lombok. Or just grab them from this pom file:

pom.xml
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.3</version>
        <relativePath/>
    </parent>
    <groupId>ru.gpm.example</groupId>
    <artifactId>mybatis-minimum</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis-minimum</name>
    <description>Demo MyBatis Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter-test</artifactId>
            <version>3.0.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

We can already launch the project. It will start but not do anything. This is the best program: it doesn’t do anything, it doesn’t break anything. But we were not taught that way: we need movement, liver and salary 🙂

We start at low speeds

We will not write “hello world”, but we will write a project closer to reality, because this is the approach, in my opinion, that will show this tool in practice. We will provide a service for managing the warehouse and the remaining goods in it.

Let’s configure MyBatis (there will be some XML here). We will put the file next to application.yml mybatis‑config.xml of the following content:

<?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>
    <typeAliases>
        <package name="ru.gpm.example.mybatis.min.domain"/>
    </typeAliases>
</configuration>

Here we have shown the “bird” where we will have objects displaying data from the database.

Let’s create a file schema.sql and put it in the root of the program’s resources. This will be the skeleton of our highly loaded DB, as we like.

schema.sql
set mode MySQL;

CREATE TABLE IF NOT EXISTS product (
    id integer NOT NULL auto_increment,
    name varchar,
    sku varchar,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS  warehouse (
    id integer NOT NULL auto_increment,
    name varchar,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS  stock (
    product_id integer,
    warehouse_id integer,
    qty integer,
    PRIMARY KEY (product_id, warehouse_id)
)

mode MySql required for the H2 database to support some convenient functions when updating upsert data.

There we will create a folder with the name mappers, in it we will put our files with requests to the database. Now let’s tell Spring where we have this XML file and where to take requests to the database. For this in application.yml let’s write the following:

mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath*:mappers/*.xml
spring.sql.init.mode: always
  • config-location – shows where MyBatis settings are;

  • mapper-locations – where the “bird” will take requests;

  • spring.sql.init.mode – tells Spring when to initiate the script schema.sql (In our case – every time at startup).

Finished with the settings, let’s start writing the code. Let’s create 3 classes in the domain package: product, warehouse, and leftovers.

@Data
@Accessors(chain = true)
public class Product {
    private Integer id;
    private String name;
    private String sku;
}

@Data
@Accessors(chain = true)
public class Stock {
    private Product product;
    private Warehouse warehouse;
    private int count;
}

@Data
@Accessors(chain = true)
public class Warehouse {
    private Integer id;
    private String name;
}

Next, we will write the requests, repositories and services themselves in the style we are used to. Let’s start with repositories. More precisely, here they are called “mappers”.

In a good way, it is better to use repositories with DI mappers in which, since this creates the right data layer, and will give the opportunity to control the format of the repository data. But we do not expect such difficulties within the framework.

@Mapper
public interface ProductRepository {

    void save(Product product);

    List<Product> findAll();
}

@Mapper
public interface WarehouseRepository {

    void save(Warehouse warehouse);

    Warehouse findOne(int id);
}

@Mapper
public interface StockRepository {

    List<Stock> findStockByWarehouse(Warehouse warehouse);

    void save(Stock stock);
}

A lot is ready, it remains to write SQL queries. Yes, yes, queries in good old SQL).

mappers/product-mapper.xml
<?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="ru.gpm.example.mybatis.min.repository.ProductRepository">

    <insert id="save" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO product (name, sku)
        VALUES (#{name}, #{sku});
    </insert>

    <select id="findAll" resultMap="ProductMap">
        SELECT id, name, sku
        FROM product;
    </select>

    <resultMap id="ProductMap" type="Product" autoMapping="true"/>
</mapper>
mappers/warehouse-mapper.xml
<?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="ru.gpm.example.mybatis.min.repository.WarehouseRepository">

    <insert id="save" useGeneratedKeys="true" keyProperty="id">
        <if test="id == null">
            INSERT INTO warehouse (name) values (#{name});
        </if>
        <if test="id != null">
            UPDATE warehouse SET name=#{name} where id=#{id};
        </if>
    </insert>

    <select id="findOne" resultMap="WarehouseMap">
        SELECT id, name FROM warehouse WHERE id = #{id};
    </select>

    <resultMap id="WarehouseMap" type="Warehouse" autoMapping="true"/>
</mapper>

Please note: block ids in the XML schema are completely identical to the method names in the repository interfaces. Thus, requests in XML are automatically linked with Java interfaces of repositories. There are other options, but we will not complicate things.

Here we used such elements as insert select and resultMap. Let’s dwell on them a little:

  • insert – Performs an insert in the database.
    id = “…” is the statement id for matching the repository that maps to this request;
    useGeneratedKeys=”true” indicates that the query generates a key value;
    keyProperty=”id” tells the “bird” which property in the model class is responsible for the ID and sets its value in the object after saving.

  • select – Reads data from the database.
    resultMap = “StockMap” used in the block ) to the domain object. There are many options. We will not consider everything within the framework of this article. Let’s focus on the main ones.
    autoMapping=”true” announces to the “birds”: “Do everything yourself.” But it works when the class and the alias in the response have the same field names, otherwise you have to describe the mapping rules.

On the next mapper (mappers/stock-mapper.xml) let’s stop in more detail.

<?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'>
<!--suppress ALL -->
<mapper namespace="ru.gpm.example.mybatis.min.repository.StockRepository">

    <insert id="save">
        INSERT INTO stock (product_id, warehouse_id, qty)
        VALUES (#{product.id}, #{warehouse.id}, #{count}) ON DUPLICATE KEY
        UPDATE qty = #{count}
    </insert>

    <sql id="stock-select-request">
        SELECT
            p.id AS p_id,
            p.name AS p_name,
            p.sku AS p_sku,
            w.id AS w_id,
            w.name AS w_name,
            s.qty
        FROM stock s
        LEFT JOIN product p ON s.product_id = p.id
        LEFT JOIN warehouse w ON s.warehouse_id = w.id
    </sql>

    <select id="findStockByWarehouse" resultMap="StockMap" parameterType="Warehouse">
        <include refid="stock-select-request"/>
        WHERE w.id = #{id}
    </select>

    <select id="findStockByWarehouseAndProduct" resultMap="StockMap">
        <include refid="stock-select-request"/>
        WHERE w.id = #{warehouseId} AND p.id = #{productId}
    </select>

    <resultMap id="StockMap" type="Stock">
        <result property="count" column="qty"/>
        <association property="product" columnPrefix="p_" javaType="Product">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sku" column="sku"/>
        </association>
        <association property="warehouse" columnPrefix="w_" javaType="Warehouse">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>
</mapper>

Let’s consider the new elements:

  • sql — a template query construct that can be used multiple times.
    id = “…” – This is the id of the template.

  • include refid = “…” – Actually, the very insertion of the template. It is used here in two queries with different conditions where.

  • parameterType=”Warehouse” tells the “bird” what class of object is passed in the request parameters.

  • resultMap – More detailed mapper. Here you can see how response aliases are superimposed on nested objects.
    result property = “count” column = “qty” – Adjusts the relationship between class properties and the names of response fields;
    association – Adjusts the relationship of nested objects to the class. Thus, we implement a one-to-one relationship. Where property indicates the class property of the nested object, a columnPrefix – This is a kind of alias filter in the response for this object.

Well here it is. Now we’re old school boys and girls again! (frame from m/f “Wings, Legs and Tails”)

And the very heart of our application is services. In order not to overload this example with logic, let’s write one service for obtaining the remaining goods in warehouses. We will save in the tests directly through the repositories.

@Service
@RequiredArgsConstructor
public class StockService {

    private final StockRepository repository;

    public Stock save(Stock stock) {
        repository.save(stock);
        return stock;
    }

    public List<Stock> getAllByWarehouse(Warehouse warehouse) {
        return repository.findStockByWarehouse(warehouse);
    }

    public Stock getBy(Warehouse warehouse, Product product) {
        return repository.findStockByWarehouseAndProduct(warehouse.getId(), product.getId());
    }
}

That’s all – we can launch our project and catch bugs enjoy life. Let’s not pull all the splendor of it with REST controllers, but just write a test that will show how it all works.

Let’s see how it all works

frame from the “Wings, Legs and Tails” m/f

@Slf4j
@SpringBootTest
class MyBatisApplicationTest {

    @Autowired
    private StockService stockService;
    @Autowired
    private ProductRepository productRepository;
    @Autowired
    private WarehouseRepository warehouseRepository;

    @Test
    void init() {
        // Добавим товары в БД
        productRepository.save(new Product().setName("name-1").setSku("sku-1"));
        productRepository.save(new Product().setName("name-2").setSku("sku-2"));
        final List<Product> all = productRepository.findAll();
        Assertions.assertEquals(2, all.size());

        // Добавим склад.
        final Warehouse warehouse = new Warehouse().setName("склад-1");
        warehouseRepository.save(warehouse);
        Assertions.assertNotNull(warehouseRepository.findOne(warehouse.getId()));

        // Сохраним остатки по товарам на складе
        final Stock stock1 = new Stock().setProduct(all.get(0)).setWarehouse(warehouse).setCount(10);
        final Stock stock2 = new Stock().setProduct(all.get(1)).setWarehouse(warehouse).setCount(50);
        stockService.save(stock1);
        stockService.save(stock2);

        // Получим текущие остатки на складе
        List<Stock> allByWarehouse = stockService.getAllByWarehouse(warehouse);
        Assertions.assertEquals(2, allByWarehouse.size());
        log.info("{}", allByWarehouse);

        // Поменяем остаток товара
        stockService.save(stock1.setCount(20));
        allByWarehouse = stockService.getAllByWarehouse(warehouse);
        Assertions.assertEquals(2, allByWarehouse.size());
        final Stock stockEdit =  stockService.getBy(warehouse, stock1.getProduct());
        Assertions.assertNotNull(stockEdit);
        Assertions.assertEquals(20, stockEdit.getCount());
        log.info("{}", allByWarehouse);
    }
}

Curtain

I think it is enough for the first acquaintance with the “bird”. I suspect that many will say that it is inconvenient to write all this with pens, if there are frameworks that do everything for you. In part, you will be right: they are very convenient to quickly write a standard program. But when you have to connect these “super machines” to legacy databases or you need to optimize queries due to increased data size, MyBatis really saves.
Maybe this article will help someone in choosing a technology, and for someone I just stole time.

Always ready for constructive criticism, because it raises the knowledge and experience of the criticized 🙂

Link: GitHub Example Cartoon: “Wings, Legs and Tails” (Screenshots and quotes).

Related posts