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

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>

        <!--          等同于上面，where内的条件成立则拼接sql，反之不执行-->
        <!--        select * from sys_role-->
        <!--        <where>-->
        <!--            <if test=" roleName != null and roleName != ''" >-->
        <!--                and role_name like concat('%',#{roleName},'%')-->
        <!--            </if>-->
        <!--        </where>-->

    </select>

    <!-- foreach 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>

    <!--        bind模糊查询，兼容不同的数据库-->
    <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