MyBatis Plus基本用法-SpringBoot框架

1. XML文件代码注释说明

数据库表生成

首先需要在数据库中创建一个user表，表结构如下：

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

Mybatis Plus的增删改查

下面是使用XML文件进行增删改查操作的示例代码：

<!-- UserMapper.xml -->

<mapper namespace="com.example.mapper.UserMapper">

    <!-- 新增数据 -->
    <insert id="insertUser" parameterType="com.example.entity.User">
        INSERT INTO user(name, age) VALUES (#{name}, #{age})
    </insert>

    <!-- 根据id查询数据 -->
    <select id="selectUserById" resultType="com.example.entity.User">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <!-- 更新数据 -->
    <update id="updateUser" parameterType="com.example.entity.User">
        UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}
    </update>

    <!-- 根据id删除数据 -->
    <delete id="deleteUserById">
        DELETE FROM user WHERE id = #{id}
    </delete>

    <!-- 多表关联分组查询 -->
    <select id="selectUserOrderCount" resultType="com.example.entity.UserOrderCount">
        SELECT u.name, u.age, COUNT(o.id) as order_count FROM user u
        LEFT JOIN `order` o ON u.id = o.user_id
        GROUP BY u.id
    </select>

    <!-- 视图查询 -->
    <select id="selectUserViewList" resultType="com.example.entity.UserView">
        SELECT * FROM user_view
    </select>

    <!-- 存储过程查询 -->
    <select id="callGetUserCount" statementType="CALLABLE" parameterType="java.util.Map">
        {CALL getUserCount(#{age, mode=IN, jdbcType=INTEGER}, #{count, mode=OUT, jdbcType=INTEGER})}
    </select>

</mapper>

2. Mapper文件代码注释

下面是使用Mapper文件调用上述方法的示例代码：

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

@Mapper
public interface UserMapper {

    // 新增数据
    void insertUser(User user);

    // 根据id查询数据
    User selectUserById(Long id);

    // 更新数据
    void updateUser(User user);

    // 根据id删除数据
    void deleteUserById(Long id);

    // 多表关联分组查询
    List<UserOrderCount> selectUserOrderCount();

    // 视图查询
    List<UserView> selectUserViewList();

    // 调用存储过程查询
    void callGetUserCount(Map<String, Object> paramMap);
}

3. SQL脚本

下面是提供相关建表、建视图、建存储过程和数据插入的SQL脚本：

-- 创建user表
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建order表
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建user_view视图
CREATE VIEW `user_view` AS 
SELECT u.name, u.age, COUNT(o.id) as order_count FROM user u 
LEFT JOIN `order` o ON u.id = o.user_id 
GROUP BY u.id;

-- 创建存储过程getUserCount
CREATE PROCEDURE `getUserCount`(IN age INT, OUT count INT)
BEGIN
    SELECT COUNT(*)
    INTO count
    FROM user
    WHERE age = age;
END;

-- 插入测试数据
INSERT INTO user(name, age) VALUES ('张三', 18);
INSERT INTO user(name, age) VALUES ('李四', 20);
INSERT INTO user(name, age) VALUES ('王五', 22);

INSERT INTO `order`(order_no, user_id) VALUES ('202201010001', 1);
INSERT INTO `order`(order_no, user_id) VALUES ('202201010002', 1);
INSERT INTO `order`(order_no, user_id) VALUES ('202201010003', 2);
INSERT INTO `order`(order_no, user_id) VALUES ('202201010004', 3);

4. YML文件代码注释说明

下面是使用YML文件进行Mybatis Plus的详细全面的配置的示例代码：

mybatis-plus:
  # mapper文件的路径
  mapper-locations: classpath:/mapper/*.xml
  # 实体类的包路径
  typeAliasesPackage: com.example.entity
  configuration:
    # 配置驼峰命名规则
    map-underscore-to-camel-case: true
    # 配置缓存
    cache-enabled: true
    # 配置多数据源
    # dataSource: com.baomidou.mybatisplus.spring.boot.starter.jdbc.MybatisPlusProperties$Druid
    global-config:
      # 配置逻辑删除字段
      db-config:
        logic-delete-field: deleted
        logic-delete-value: 1
        logic-not-delete-value: 0
      # 配置分页插件
      page-helper:
        auto-dialect: true
        reasonable: true
        support-methods-arguments: true

5. Java文件代码注释

下面是使用Java文件进行实体类的示例代码：

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("user")
public class User {

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @TableField("name")
    private String name;

    @TableField("age")
    private Integer age;

}

import lombok.Data;

@Data
public class UserOrderCount {

    private String name;

    private Integer age;

    private Integer orderCount;

}

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

@Data
public class UserView {

    @TableField("name")
    private String name;

    @TableField("age")
    private Integer age;

    @TableField("order_count")
    private Integer orderCount;

}

下面是使用Java文件进行Service和Controller的示例代码：

import com.baomidou.mybatisplus.extension.service.IService;

public interface UserService extends IService<User> {

    void addUser(User user);

    User getUserById(Long id);

    void updateUser(User user);

    void deleteUserById(Long id);

    List<UserOrderCount> getUserOrderCount();

    List<UserView> getUserViewList();

    Integer getUserCountByAge(Integer age);

}

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Override
    public void addUser(User user) {
        baseMapper.insertUser(user);
    }

    @Override
    public User getUserById(Long id) {
        return baseMapper.selectUserById(id);
    }

    @Override
    public void updateUser(User user) {
        baseMapper.updateUser(user);
    }

    @Override
    public void deleteUserById(Long id) {
        baseMapper.deleteUserById(id);
    }

    @Override
    public List<UserOrderCount> getUserOrderCount() {
        return baseMapper.selectUserOrderCount();
    }

    @Override
    public List<UserView> getUserViewList() {
        return baseMapper.selectUserViewList();
    }

    @Override
    public Integer getUserCountByAge(Integer age) {
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("age", age);
        paramMap.put("count", null);
        baseMapper.callGetUserCount(paramMap);
        return (Integer) paramMap.get("count");
    }

}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @PostMapping("/add")
    public String addUser(@RequestBody User user) {
        userService.addUser(user);
        return "添加成功";
    }

    @GetMapping("/{id}")
    public User getUserById(@PathVariable Long id) {
        return userService.getUserById(id);
    }

    @PutMapping("/update")
    public String updateUser(@RequestBody User user) {
        userService.updateUser(user);
        return "更新成功";
    }

    @DeleteMapping("/{id}")
    public String deleteUserById(@PathVariable Long id) {
        userService.deleteUserById(id);
        return "删除成功";
    }

    @GetMapping("/orderCount")
    public List<UserOrderCount> getUserOrderCount() {
        return userService.getUserOrderCount();
    }

    @GetMapping("/viewList")
    public List<UserView> getUserViewList() {
        return userService.getUserViewList();
    }

    @GetMapping("/countByAge")
    public Integer getUserCountByAge(@RequestParam Integer age) {
        return userService.getUserCountByAge(age);
    }

}

以上代码仅供参考，具体实现方式根据实际情况而定。