文章目录

前言

在Java开发中，MyBatis作为一款优秀的持久层框架，以其简洁的配置和强大的功能，被广泛应用于各种项目中。本文将详细介绍MyBatis中mapper层编写SQL的四种方法，并通过具体的实现代码以操作user表为例，进行详细讲解。

一、MyBatis 简介

1. MyBatis 简介

MyBatis是一个半ORM框架，它将SQL映射成XML文件或注解，简化了数据库操作。它支持自定义SQL、存储过程以及高级映射。

2. 目录结构

以下是一个基于Spring Boot和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

3. 创建user表和对应实体类

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

import lombok.Data;

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

二、使用XML文件方式写SQL

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);
}

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>

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, "数据删除失败");
    }

}

三、使用注解方式写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);
}

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);
    }
}

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, "数据删除失败");
    }

}

四、使用提供类方式写SQL

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();
    }
}

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);
}

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, "数据删除失败");
    }

}

五、把SQL作为参数传入方式

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);
}

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);
    }
}

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, "数据删除失败");
    }

}

总结

本文详细介绍了MyBatis中mapper层编写SQL的四种方法，包括使用XML文件、注解、提供类以及将SQL作为参数传入的方式。通过具体的实现代码，读者可以更好地理解每种方法的优缺点，并根据实际需求选择合适的方法。

# mybatis # 数据库 # 交互
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

cover

计算机网络微课堂笔记

avatar 腾讯云开发者社区
cover

Rabbitmq在java中的使用

avatar 腾讯云开发者社区

java try catch 之后定位不到具体报错行_JAVA入门（三）上

点击蓝字｜关注我们一、异常与异常处理异常简介代码中：阻止当前方法或作用域继续实现的，称之为异常java中的所有异常类都继承Throwable类，Exception 的父类是 Throwable编码 环境 用户操作输入出现问题由java虚拟机自动抛出和自动捕获需要手动添加抛出和捕获语句文件找不到ThrowableErrorException虚拟机错误 VirtualMachineError...

avatar 腾讯云开发者社区