mybatis、mybatis-plus、ibatis注解使用对比

偏执的太偏执、 2023-06-19 11:07 20阅读 0赞

mybatis操作数据的实现方式主要分为三种

1、通过原生的方式配置xml操作数据

2、通过继承mapper接口操作数据，mapper接口操作数据有多种方式，如 tk.mybatis、mybatis-plus,本文使用mybatis-plus

3、通过注解方式操作数据，例如使用 @SelectProvider注解动态拼装sql查询数据，适用于复杂的sql查询

以下示例三种实现方式，以springboot项目为例

**目录**

一、xml实现数据操作

1、引入相关的pom文件

2、配置yml

3、建立实体类，mapper接口，service类，控制类，mybatis xml文件

二、mapper接口操作数据

1、引入相关pom文件

2、配置yml：

3、建立实体类，mapper接口，控制类等

三、ibatis注解操作数据

建立实体类，mapper接口，provider类，控制类

--------------------

## 一、xml实现数据操作 ##

### 1、引入相关的pom文件 ###

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.1</version>
            </dependency>

### 2、配置yml ###

mybatis:
      mapper-locations: classpath:mybatis/*.xml
      #配置打印sql
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

### 3、建立实体类，mapper接口，service类，控制类，mybatis xml文件 ###

实体类：

@Data
    public class SysRole {
        private Integer roleId;
    
        private String roleName;
    
        private String roleKey;
    
        private String roleSort;
    
        private String dataScope;
    
        private String status;
    
        private String delFlag;
    
        private String createBy;
    
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
        private Date createTime;
    
        private String updateBy;
    
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
        private Date updateTime;
    
        private String remark;
    }

mapper接口：

public interface SysRoleMapper  {
    
        public List<SysRole> list();
    
        public List<SysRole> getByRoleName(SysRole sysRole);
    
        /**
         * @description: 单参数数组
         * @param: [ids]
         * @return: java.util.List<com.cp.entity.SysRole>
         * @author: chenping
         * @date: 2019/10/31
         */
        public List<SysRole> getByArrayIds(String[] ids);
    
        public List<SysRole> selectLikeWithBind(String roleName);
    
    
        int insert(SysRole sysRole);
    }

mybatis 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.cp.mapper.SysRoleMapper">
        <resultMap type="com.cp.entity.SysRole" id="sysRole">
            <id property="roleId" column="role_id"/>
            <result property="roleName" column="role_name"/>
            <result property="roleKey" column="role_key"/>
            <result property="roleSort" column="role_sort"/>
            <result property="dataScope" column="data_scope"/>
            <result property="status" column="status"/>
            <result property="delFlag" column="del_flag"/>
            <result property="createBy" column="create_by"/>
            <result property="createTime" column="create_time"/>
            <result property="updateTime" column="update_time"/>
            <result property="updateBy" column="update_by"/>
            <result property="remark" column="remark"/>
        </resultMap>
    
        <select id="list" resultMap="sysRole">
            select * from sys_role
        </select>
    
        <select id="getByRoleName" parameterType="com.cp.entity.SysRole" resultMap="sysRole">
            select * from sys_role where 1 = 1
            <if test=" roleName != null and roleName != ''">
                and role_name like concat('%',#{roleName},'%')
            </if>
    
            
            
            
            
            
            
            
    
        </select>
    
        
        <select id="getByArrayIds" parameterType="String" resultMap="sysRole">
            select * from sys_role where role_id in
            <foreach collection="array" item="ids" index="index" open="(" separator="," close=")">
                #{ids}
            </foreach>
        </select>
    
        
        <select id="selectLikeWithBind" parameterType="String" resultMap="sysRole">
            <bind name="pattern" value=" '%' + roleName + '%' "/>
            select * from sys_role where role_name like #{pattern}
        </select>
    
        <insert id="insert" parameterType="com.cp.entity.SysRole" >
            insert into sys_role (role_key,role_sort,data_scope)
            values ( #{roleKey},#{roleSort},#{dataScope} )
        </insert>
    </mapper>

控制类：

@RestController
    @Slf4j
    @RequestMapping("/xml")
    public class XmlMybatisSysRoleController {
        @Autowired
        SysRoleService sysRoleService;
    
        @Autowired
        SysRoleMapper sysRoleMapper;
    
        @RequestMapping("/sysrole")
        public List<SysRole> sysrole() {
            log.info("1111111111111111111111111");
            return sysRoleService.list();
        }
    
        @PostMapping("/selectByRoleName")
        public List<SysRole> selectByRoleName(@RequestBody SysRole sysRole) {
            return sysRoleMapper.getByRoleName(sysRole);
        }
    
    
        /**
         * @description: string 参数，批量查询
         * @param: [params]
         * @return: java.util.List<com.cp.entity.SysRole>
         * @author: chenping
         * @date: 2019/10/31
         */
        @GetMapping("/selectByIds")
        public List<SysRole> selectByIds(@RequestParam String params) {
            String[] str = params.split(",");
            return sysRoleMapper.getByArrayIds(str);
        }
    
        /**
         * @description: mybatis 使用bind模糊查询，兼容不同的数据库
         * @param: [roleName]
         * @return: java.util.List<com.cp.entity.SysRole>
         * @author: chenping
         * @date: 2019/11/1
         */
        @GetMapping("/selectLikeWithBind")
        public List<SysRole> selectLikeWithBind(@RequestParam String roleName) {
            return sysRoleMapper.selectLikeWithBind(roleName);
        }
    
        /** 
         * @Description:  插入
         * @param: sysRole 
         * @return: int 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @PostMapping("/insert")
        public int insert(@RequestBody SysRole sysRole) {
            return sysRoleMapper.insert(sysRole);
        }
    
    }

## 二、mapper接口操作数据 ##

### 1、引入相关pom文件 ###

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

### 2、配置yml： ###

mybatis-plus:
      mapper-locations: classpath*:mybatis/*.xml
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

### 3、建立实体类，mapper接口，控制类等 ###

实体类：

@Data
    @TableName(value = "user")//指定表名
    public class User {
        private Integer id;
    
        private String name;
    
        private Integer age;
    
        @TableField(value = "manager_id")
        private Integer managerId;
    
        /**
         * 数据库中不存在的字段
         */
        @Transient
        @TableField(exist = false)
        private String userName;
    }

mapper接口：

public interface UserMapper extends BaseMapper<User> {
    
    }

控制类：

@RestController
    @RequestMapping("/entity")
    public class EntityMybatisPlusUserController {
    
        @Autowired
        UserMapper userMapper;
    
        /** 
         * @Description:  通过QueryWrapper查询
         * @param:  
         * @return: java.util.List<com.cp.entity.User> 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/getUsers")
        public List<User> getAll() {
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("manager_id",102);
            return userMapper.selectList(queryWrapper);
    //        return userMapper.selectList(null);
        }
    
        /** 
         * @Description:  通过id查询
         * @param:  
         * @return: com.cp.entity.User 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/selectById")
        public User selectById() {
            return userMapper.selectById(101);
        }
    
        /** 
         * @Description:  通过实体插入
         * @param:  
         * @return: int 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/insertUsers")
        public int insert() {
            User user = new User();
            user.setId(204);
            user.setAge(66);
            user.setName("cart");
            user.setManagerId(102);
            return userMapper.insert(user);
    
        }
    
        /** 
         * @Description:  根据实体带id更新
         * @param:  
         * @return: int 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/updateUser")
        public int updateUser() {
            User user = new User();
            user.setId(204);
            user.setAge(55);
            return userMapper.updateById(user);//不传的字段值不更新
    
    
        }
    
        /** 
         * @Description:  通过map参数查询
         * @param:  
         * @return: java.util.List<com.cp.entity.User> 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/getByColumn")
        public List<User> getByColumn() {
            Map<String, Object> columnMap = new HashMap<>();
            columnMap.put("id", 204);//写表中的列名
            columnMap.put("age", 55);
            return userMapper.selectByMap(columnMap);
        }
    
        /** 
         * @Description:  根据多个id批量查询
         * @param:  
         * @return: java.util.List<com.cp.entity.User> 
         * @Author: chenping
         * @Date: 2019/12/5
         **/
        @RequestMapping("/batchSelect")
        public List<User> batchSelect() {
            List<Integer> listIds = new ArrayList<>();
            listIds.add(101);
            listIds.add(102);
            return userMapper.selectBatchIds(listIds);
        }
    }

## 三、ibatis注解操作数据 ##

### 建立实体类，mapper接口，provider类，控制类 ###

实体类：

@Data
    public class Score {
    
        private String subject;
    
        private Integer score;
    
        private Integer stuId;
    }

mapper接口：

public interface ScoreMapper {
    
        @SelectProvider(type = ScoreProvider.class, method = "getListSql")
        public List<ScoreResponse> list(Score score);
    }

provider类：

public class ScoreProvider {
    
        public String getListSql(Score score) {
            SQL sql = new SQL();
    
            StringBuffer selectColumns = new StringBuffer();
            selectColumns.append(" subject,score,stu_id as stuId");
    
            sql.SELECT(selectColumns.toString());
            sql.FROM(" score ");
            if (!StringUtils.isEmpty(score.getSubject())) {
                sql.WHERE(" subject = #{subject}");
            }
            if (null != score.getScore()) {
                sql.WHERE(" score = #{score}");
            }
            return sql.toString();
        }
    }

控制类：

@RestController
    @RequestMapping("/annotation")
    public class AnnotationMybatisScoreController {
    
        @Autowired
        ScoreMapper scoreMapper;
    
        @RequestMapping("/list")
        public List<ScoreResponse> getList() {
            return scoreMapper.list(null);
        }
    }

详细代码请参考本人GitHub项目 [https://github.com/chenping-1993/springmvc-mybatis][https_github.com_chenping-1993_springmvc-mybatis]

[https_github.com_chenping-1993_springmvc-mybatis]: https://github.com/chenping-1993/springmvc-mybatis