Berbagi teknologi

Penjelasan rinci tentang empat metode MyBatis untuk berinteraksi dengan database

2024-07-12

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

Direktori artikel

Kata pengantar

Dalam pengembangan Java, MyBatis, sebagai kerangka lapisan persistensi yang sangat baik, banyak digunakan di berbagai proyek dengan konfigurasi sederhana dan fungsi yang kuat. Artikel ini akan memperkenalkan secara rinci empat metode penulisan SQL di lapisan mapper di MyBatis, dan menjelaskan secara rinci melalui kode implementasi tertentu, dengan mengambil tabel pengguna sebagai contoh.

1. Pengenalan MyBatis

1. Pengenalan MyBatis

MyBatis adalah kerangka kerja semi-ORM yang memetakan SQL ke dalam file XML atau anotasi, menyederhanakan operasi database. Ini mendukung SQL khusus, prosedur tersimpan, dan pemetaan tingkat lanjut.

2. Struktur direktori

Berikut ini contoh struktur proyek sederhana berdasarkan Spring Boot dan 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. Buat tabel pengguna dan kelas entitas terkait

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. Tulis SQL menggunakan file XML

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. Pemeta PenggunaByXml.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. KontrolerPenggunaByXml.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. Tulis SQL menggunakan anotasi

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. LayananPenggunaOlehAnnotation.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. KontrolerPenggunaBerdasarkanAnnotation.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. Tulis SQL menggunakan kelas yang disediakan

1. Penyedia Pengguna.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. KontrolerPenggunaOlehPenyedia.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. Berikan SQL sebagai 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. Layanan PenggunaByMap.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. KontrolerPenggunaByXml.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

Meringkaskan

Artikel ini memperkenalkan secara rinci empat metode penulisan SQL di lapisan mapper MyBatis, termasuk menggunakan file XML, anotasi, menyediakan kelas, dan meneruskan SQL sebagai parameter. Melalui kode implementasi yang spesifik, pembaca dapat lebih memahami kelebihan dan kekurangan masing-masing metode dan memilih metode yang sesuai dengan kebutuhan sebenarnya.

Profil pribadi

Ia telah mengabdikan dirinya untuk meneliti teknologi selama lebih dari 30 tahun, dan mahir dalam berbagai bahasa seperti java, linux, javascript, php, css, dll. "Saya telah memberikan banyak kontribusi di bidang dokumentasi pengembang stasiun sumber terbuka untuk berbagi beberapa masalah dalam pengembangan teknologi untuk referensi di masa mendatang. Semua orang memeriksanya

informasi kontak saya

Surat