MyBatis Plus基本用法-SpringBoot框架

喜欢ヅ旅行 2023-10-12 09:58 27阅读 0赞

#### 依赖 ####

使用 Mybatis Plus 框架时，需要添加以下依赖：

<dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>latest-version</version>
    </dependency>

其中，`latest-version` 为最新版本号，可以在官网或 Maven 仓库中查看。

此外，还需要添加与所使用的数据库连接相关的依赖。例如，如果使用 MySQL 数据库，需要添加以下依赖：

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.26</version>
    </dependency>

如果使用 Oracle 数据库，需要添加以下依赖：

<dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>19.3.0.0</version>
    </dependency>

如果使用 PostgreSQL 数据库，需要添加以下依赖：

<dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.23</version>
    </dependency>

如果使用 SQL Server 数据库，需要添加以下依赖：

<dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>9.4.0.jre11</version>
    </dependency>

这些依赖可以根据实际需要进行选择和配置。

### 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文件进行增删改查操作的示例代码：

<mapper namespace="com.example.mapper.UserMapper">
    
        
        <insert id="insertUser" parameterType="com.example.entity.User">
            INSERT INTO user(name, age) VALUES (#{name}, #{age})
        </insert>
    
        
        <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>
    
        
        <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);
        }
    
    }

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