Technology Sharing

Detailed explanation of four ways MyBatis interacts with the database

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Table of Contents

Preface

In Java development, MyBatis, as an excellent persistence layer framework, is widely used in various projects with its simple configuration and powerful functions. This article will introduce in detail the four methods of writing SQL in the mapper layer of MyBatis, and explain it in detail through specific implementation codes, taking the operation of the user table as an example.

1. Introduction to MyBatis

1. Introduction to MyBatis

MyBatis is a semi-ORM framework that maps SQL into XML files or annotations to simplify database operations. It supports custom SQL, stored procedures, and advanced mapping.

2. Directory Structure

The following is an example of a simple project structure based on Spring Boot and MyBatis:

springboot-template-mybatis
│  pom.xml
│
├─src
│  └─main
│      ├─java
│      │  └─com
│      │      └─zcs
│      │          │  Application.java
│      │          │
│      │          ├─common
│      │          │      Result.java
│      │          │
│      │          ├─controller
│      │          │      UserControllerByAnnotation.java
│      │          │      UserControllerByMap.java
│      │          │      UserControllerByProvider.java
│      │          │      UserControllerByXml.java
│      │          │
│      │          ├─entity
│      │          │      UserEntity.java
│      │          │
│      │          ├─mapper
│      │          │  │  UserMapperByAnnotation.java
│      │          │  │  UserMapperByMap.java
│      │          │  │  UserMapperByProvider.java
│      │          │  │  UserMapperByXml.java
│      │          │  │
│      │          │  └─provider
│      │          │          UserProvider.java
│      │          │
│      │          └─service
│      │                  UserService.java
│      │                  UserServiceByMap.java
│      │
│      └─resources
│          │  application.yml
│          │  log4j.properties
│          │
│          └─com
│              └─zcs
│                  └─mapper
│                          UserMapperByXml.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
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

3. Create the user table and corresponding entity class

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
import lombok.Data;

@Data
public class UserEntity {
    private Integer id;
    private String name;
    private Integer age;
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

2. Write SQL using XML file

1. UserMapperByXml.java

import com.zcs.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapperByXml {
    List<UserEntity> selectAllUsersByXml();

    boolean insertUserByXml(UserEntity user);

    boolean updateUserByXml(UserEntity user);

    boolean deleteUserByXml(Integer id);
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

2. UserMapperByXml.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="com.zcs.mapper.UserMapperByXml">
    <!-- 查询所有用户 -->
    <select id="selectAllUsersByXml">
        SELECT *
        FROM user
    </select>

    <!-- 插入用户 -->
    <insert id="insertUserByXml">
        INSERT INTO user (id, name, age)
        VALUES (#{id}, #{name}, #{age})
    </insert>

    <!-- 更新用户 -->
    <update id="updateUserByXml">
        UPDATE user
        SET name = #{name},
            age  = #{age}
        WHERE id = #{id}
    </update>

    <!-- 删除用户 -->
    <delete id="deleteUserByXml">
        DELETE
        FROM user
        WHERE id = #{id}
    </delete>
</mapper>

  • 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

3. UserControllerByXml.java

import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByXml;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@RestController
@RequestMapping("/usersByXml")
public class UserControllerByXml {

    @Resource
    private UserMapperByXml userMapperByXml;
    
    @GetMapping
    public Result<List<UserEntity>> selectAllUsersByAnnotation() {
        List<UserEntity> userEntities = userMapperByXml.selectAllUsersByXml();
        return Result.success("successfully", userEntities);
    }

    @PostMapping
    public Result<String> insertUserByAnnotation(@RequestBody UserEntity user) {
        boolean b = userMapperByXml.insertUserByXml(user);
        if (b) {
            return Result.success("successfully", "数据插入成功");
        }
        return Result.error(500, "数据插入失败");
    }

    @PutMapping
    public Result<String> updateUserByAnnotation(@RequestBody UserEntity user) {
        boolean b = userMapperByXml.updateUserByXml(user);
        if (b) {
            return Result.success("successfully", "数据更新成功");
        }
        return Result.error(500, "数据更新失败");
    }

    @DeleteMapping
    public Result<String> deleteUserByAnnotation(@RequestParam Integer id) {
        boolean b = userMapperByXml.deleteUserByXml(id);
        if (b) {
            return Result.success("successfully", "数据删除成功");
        }
        return Result.error(500, "数据删除失败");
    }

}

  • 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

3. Use annotations to write SQL

1. UserMapperByAnnotation.java

import com.zcs.entity.UserEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapperByAnnotation {
    @Select("SELECT * FROM user")
    List<UserEntity> getAllUser();

    @Insert("INSERT INTO user (id, name, age) VALUES (#{id}, #{name}, #{age})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    boolean insertUser(UserEntity user);

    @Update("UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}")
    boolean updateUser(UserEntity user);

    @Delete("DELETE FROM user WHERE id = #{id}")
    boolean deleteUserById(Integer id);
}

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

2. UserServiceByAnnotation.java

import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByAnnotation;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceByAnnotation {
    @Resource
    private UserMapperByAnnotation userMapperByAnnotation;

    public List<UserEntity> getAllUser() {
        return userMapperByAnnotation.getAllUser();
    }

    public boolean insertUser(UserEntity user) {
        return userMapperByAnnotation.insertUser(user);
    }

    public boolean updateUser(UserEntity user) {
        return userMapperByAnnotation.updateUser(user);
    }

    public boolean deleteUserById(Integer id) {
        return userMapperByAnnotation.deleteUserById(id);
    }
}

  • 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

3. UserControllerByAnnotation.java

import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.service.UserServiceByAnnotation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@RestController
@RequestMapping("/usersByAnnotation")
public class UserControllerByAnnotation {

    @Resource
    private UserServiceByAnnotation userServiceByAnnotation;

    @GetMapping
    public Result<List<UserEntity>> getAllUser() {
        List<UserEntity> allUserM = userServiceByAnnotation.getAllUser();
        return Result.success("success", allUserM);
    }

    @PostMapping
    public Result<String> insertUser(@RequestBody UserEntity user) {
        boolean b = userServiceByAnnotation.insertUser(user);
        if (b) {
            return Result.success("success", "数据插入成功");
        }
        return Result.error(500, "数据插入失败");
    }

    @PutMapping
    public Result<String> updateUser(@RequestBody UserEntity user) {
        boolean b = userServiceByAnnotation.updateUser(user);
        if (b) {
            return Result.success("success", "数据更新成功");
        }
        return Result.error(500, "数据更新失败");
    }

    @DeleteMapping
    public Result<String> deleteUserById(@RequestParam Integer id) {
        boolean b = userServiceByAnnotation.deleteUserById(id);
        if (b) {
            return Result.success("success", "数据删除成功");
        }
        return Result.error(500, "数据删除失败");
    }

}

  • 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

4. Write SQL using provided classes

1. UserProvider.java

import com.zcs.entity.UserEntity;
import org.apache.ibatis.jdbc.SQL;

public class UserProvider {
    public String selectAllUser() {
        SQL sql = new SQL();
        sql.SELECT("*");
        sql.FROM("user");

        return sql.toString();
    }

    public String insertUser(UserEntity userEntity) {
        SQL sql = new SQL();
        sql.INSERT_INTO("user");

        if (userEntity.getId() != null) {
            sql.VALUES("id", "#{id}");
        }
        if (userEntity.getName() != null) {
            sql.VALUES("name", "#{name}");
        }
        if (userEntity.getAge() != null) {
            sql.VALUES("age", "#{age}");
        }

        return sql.toString();
    }

    public String updateUser(UserEntity userEntity) {
        SQL sql = new SQL();
        sql.UPDATE("user");

        if (userEntity.getName() != null) {
            sql.SET("name = #{name}");
        }
        if (userEntity.getAge() != null) {
            sql.SET("age = #{age}");
        }

        sql.WHERE("id = #{id}");

        return sql.toString();
    }

    public String deleteUserById(Integer id) {
        SQL sql = new SQL();
        sql.DELETE_FROM("user");
        sql.WHERE("id = #{id}");

        return sql.toString();
    }
}

  • 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

2. UserMapperByProvider.java

import com.zcs.entity.UserEntity;
import com.zcs.mapper.provider.UserProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapperByProvider {
    @SelectProvider(type = UserProvider.class, method = "selectAllUser")
    List<UserEntity> selectAllUser();

    @InsertProvider(type = UserProvider.class, method = "insertUser")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    boolean insertUser(UserEntity userEntity);

    @UpdateProvider(type = UserProvider.class, method = "updateUser")
    boolean updateUser(UserEntity userEntity);

    @DeleteProvider(type = UserProvider.class, method = "deleteUserById")
    boolean deleteUserById(Integer id);
}

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

3. UserControllerByProvider.java

import com.zcs.common.Result;
import com.zcs.entity.UserEntity;
import com.zcs.mapper.UserMapperByProvider;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@RestController
@RequestMapping("/usersByProvider")
public class UserControllerByProvider {

    @Resource
    private UserMapperByProvider userMapper;

    @GetMapping
    public Result<List<UserEntity>> selectAllUsersByAnnotation() {
        List<UserEntity> userEntities = userMapper.selectAllUser();
        return Result.success("successfully", userEntities);
    }

    @PostMapping
    public Result<String> insertUserByAnnotation(@RequestBody UserEntity user) {
        boolean b = userMapper.insertUser(user);
        if (b) {
            return Result.success("success", "数据插入成功");
        }
        return Result.error(500, "数据插入失败");
    }

    @PutMapping
    public Result<String> updateUserByAnnotation(@RequestBody UserEntity user) {
        boolean b = userMapper.updateUser(user);
        if (b) {
            return Result.success("success", "数据更新成功");
        }
        return Result.error(500, "数据更新失败");
    }

    @DeleteMapping
    public Result<String> deleteUserByAnnotation(@RequestParam Integer id) {
        boolean b = userMapper.deleteUserById(id);
        if (b) {
            return Result.success("success", "数据删除成功");
        }
        return Result.error(500, "数据删除失败");
    }

}

  • 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

5. How to pass SQL as a parameter

1. UserMapperByMap.java

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapperByMap {
    @Select("${sql}")
    List<Map<String, Object>> getAllUser(Map<String, Object> map);

    @Insert("${sql}")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    boolean insertUser(Map<String, Object> map);

    @Update("${sql}")
    boolean updateUser(Map<String, Object> map);

    @Delete("${sql}")
    boolean deleteUserById(Map<String, Object> map);
}

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

2. UserServiceByMap.java

import com.zcs.mapper.UserMapperByMap;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Service
public class UserServiceByMap {
    @Resource
    private UserMapperByMap userMapperByMap;

    public List<Map<String, Object>> getAllUser(Map<String, Object> map) {
        String sql = "SELECT * FROM user";
        map.put("sql", sql);
        return userMapperByMap.getAllUser(map);
    }

    public boolean insertUser(Map<String, Object> map) {
        String sql = "INSERT INTO user (id, name, age) VALUES (#{id}, #{name}, #{age})";
        map.put("sql", sql);
        return userMapperByMap.insertUser(map);
    }

    public boolean updateUser(Map<String, Object> map) {
        String sql = "UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}";
        map.put("sql", sql);
        return userMapperByMap.updateUser(map);
    }

    public boolean deleteUserById(Map<String, Object> map) {
        String sql = "DELETE FROM user WHERE id = #{id}";
        map.put("sql", sql);
        return userMapperByMap.deleteUserById(map);
    }
}

  • 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

3. UserControllerByXml.java

import com.zcs.common.Result;
import com.zcs.service.UserServiceByMap;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@RequestMapping("/usersByMap")
public class UserControllerByMap {

    @Resource
    private UserServiceByMap userServiceByMap;

    @GetMapping
    public Result<List<Map<String, Object>>> getAllUser() {
        HashMap<String, Object> map = new HashMap<>();
        List<Map<String, Object>> allUser = userServiceByMap.getAllUser(map);
        return Result.success("success", allUser);
    }

    @PostMapping
    public Result<String> insertUser(@RequestBody Map<String, Object> map) {
        boolean b = userServiceByMap.insertUser(map);
        if (b) {
            return Result.success("success", "数据插入成功");
        }
        return Result.error(500, "数据插入失败");
    }

    @PutMapping
    public Result<String> updateUserM(@RequestBody Map<String, Object> map) {
        boolean b = userServiceByMap.updateUser(map);
        if (b) {
            return Result.success("success", "数据更新成功");
        }
        return Result.error(500, "数据更新失败");
    }

    @DeleteMapping
    public Result<String> deleteUserByIdM(@RequestParam Map<String, Object> map) {
        boolean b = userServiceByMap.deleteUserById(map);
        if (b) {
            return Result.success("success", "数据删除成功");
        }
        return Result.error(500, "数据删除失败");
    }

}

  • 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

Summarize

This article introduces four methods of writing SQL in the mapper layer of MyBatis, including using XML files, annotations, providing classes, and passing SQL as parameters. Through the specific implementation code, readers can better understand the advantages and disadvantages of each method and choose the appropriate method according to actual needs.

Personal profile

I have devoted myself to the research of technology for more than 30 years. I am proficient in various languages ​​such as Java, Linux, JavaScript, PHP, CSS, etc. I have made many contributions in the field of open source. I have established a developer documentation site to share some problems in technology development for everyone to read.

my contact information

Mail