Mybatis最全指南

Mybatis

Base_Column

1
2
3

<sql id="Base_Column">
  ID,USER_NAME,PASSWORD,CREATE_TIME,UPDATE_TIME,HEAD_PORTRAIT,STATUS
</sql>

Base_Where

<sql id="Base_Where">
  <where>
      <if test="id != null and id != ''">
          AND ID=#{id}
      </if>
      <if test="userName != null and userName != ''">
          AND USER_NAME like concat('%',#{userName},'%')
      </if>
      <if test="password != null and password != ''">
          AND PASSWORD=md5(#{password})
      </if>
      <if test="status != null">
          AND STATUS=#{status}
      </if>
  </where>
</sql>

Update_Column

<sql id="Update_Column">
  <set>
      <if test="userName != null and userName!= ''">USER_NAME =#{userName},</if>
      <if test="password != null and password!= ''">PASSWORD=md5(#{password}),</if>
      <if test="headPortrait != null and headPortrait != ''">HEAD_PORTRAIT=#{headPortrait},</if>
      <if test="updateTime != null">UPDATE_TIME=#{updateTime},</if>
      <if test="status != null">STATUS=#{status},</if>
  </set>
</sql>

特殊查询

返回对象为HashMap

List<HashMap<String, Object>> getByIds(@Param("ids") List<Integer> ids);

<select id="getByIds" resultType="java.util.Map"
...
</select>

注意这种方式，相当于以单个 Map做对象，然后对象属性名作为key，值作为value,适合查询多条记录

HashMap 或者 JSONObject 作为入参，只要key值对应上就好，可以理解为同VO对象用法

Map<String, Object> param = new HashMap<>();
param.put("id", 1);
param.put("username", "lauy");

<select id="getByIds" resultType="java.util.Map"
	SELECT
    <include refid="Base_Column"/>
    FROM user_info
    WHERE id = #{id} AND username = #{username}
</select>

单表操作

新增

<insert id="insertOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo"
        useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    INSERT INTO
    user_info(ID,USER_NAME,PASSWORD,CREATE_TIME)
    VALUES(#{id},#{userName},md5(#{password}),now())
</insert>

查询: 根据封装进 userInfo 对象里的条件查询，结果可以是单个对象也可以是集合

<select id="findOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo"
    resultType="cn.blue.项目工程名.bean.UserInfo">
    SELECT
    <include refid="Base_Column"/>
    FROM user_info
    <include refid="Base_Where"/>
</select>

删除：

<delete id="deleteById" parameterType="string">
    DELETE FROM user_info
    <include refid="Base_Where"/>
</delete>

修改：

<update id="deleteById" parameterType="string">
    UPDATE user_info SET STATUS = 1
    WHERE
    id=#{id}
</update>

两表查询

resultMap：

<resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
    <id column="ID" property="id"/>
    <result column="USER_NAME" property="userName"/>
    <result column="PASSWORD" property="password"/>
    <result column="CREATE_TIME" property="createTime"/>
    <result column="UPDATE_TIME" property="updateTime"/>
    <result column="HEAD_PORTRAIT" property="headPortrait"/>
    <result column="STATUS" property="status"/>
    <collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
                select="cn.blue.项目工程名.mapper.RoleMapper.findRoleById"/>
</resultMap>

UserInfoMapper 中的查询语句：

<select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
        resultMap="userInfoMap">
    SELECT
    <include refid="Base_Column"/>
    FROM user_info
    <include refid="Base_Where"/>
</select>

RoleMapper 表中的查询

<select id="findById" parameterType="int"
    resultMap="roleMap">
    select
    <include refid="Base_Column"></include>
    from role
    where id=#{id}
</select>

利用中间表过渡的两表查询

resultMap：

<resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
    <id column="ID" property="id"/>
    <result column="USER_NAME" property="userName"/>
    <result column="PASSWORD" property="password"/>
    <result column="CREATE_TIME" property="createTime"/>
    <result column="UPDATE_TIME" property="updateTime"/>
    <result column="HEAD_PORTRAIT" property="headPortrait"/>
    <result column="STATUS" property="status"/>
    <collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
                select="cn.blue.项目工程名.mapper.RoleMapper.findRoleByUserId"/>
</resultMap>

UserInfoMapper 中的查询语句：

<select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
        resultMap="userInfoMap">
    SELECT
    <include refid="Base_Column"/>
    FROM user_info
    <include refid="Base_Where"/>
</select>

RoleMapper 中的查询语句

<select id="findRoleByUserId" parameterType="int" resultType="cn.blue.项目工程名.bean.Role">
    SELECT
    <include refid="Base_Column"/>
    FROM role r, user_role ur
    WHERE r.id=ur.role_id
    AND ur.user_id=#{userId}
</select>

resultMap使用Map返回数据

<?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="cn.lauy.customer.business.customer.dao.AskedQuestionsCategoryDao">

    <!--    TODO 不使用就删除-->
    <resultMap id="questionsMap" type="com.lauy.customer.business.customer.dto.AskedQuestionsCategoryDTO">
        <id column="ID" property="id"/>
        <result column="remark" property="remark"/>
        <!--【传多个参数值到下一个SQL查询】：对应 AskedQuestionsCategoryDTO 中数据：参数=列名       -->
        <collection column="{questionId=ID,questionName=REMARK}" property="AskedQuestionsList" ofType="cn.lauy.customer.business.customer.model.AskedQuestions"
                    select="getListQuestionsInfo"/>
    </resultMap>

    <select id="getListCategoryAndQuestions" resultMap="questionsMap">
        SELECT c.* FROM asked_questions_category c
        LEFT JOIN asked_questions q
              ON c.id = q.category_id
        ORDER BY c.weight
    </select>

    <select id="getListQuestionsInfo" parameterType="java.util.HashMap"
            resultType="cn.lauy.customer.business.customer.model.AskedQuestions">
        SELECT q.* FROM asked_questions q
        LEFT JOIN asked_questions_info i
        ON i.question_id = #{id}
        <if test="questionName != '' and questionName != null">
            AND i.question_name LIKE CONCAT(#{questionName}, '%')
        </if>
        ORDER BY  q.question_weight
    </select>
</mapper>

遍历操作

根据 ids 来删除:
注：数组或者集合都可，如下示例 ids 是个数组，故需要以此表达：String[] ids

/**
*  支持批量删除用户
* @param ids 多个用户id
* @return 受影响行数
* 数组 或者 集合都可
*/
int deleteByIds(@Param("ids")String... ids);

<delete id="deleteByIds" parameterType="string">
    DELETE FROM user_info
    WHERE
    <foreach collection="ids" open="id in (" close=")"
              separator="," item="id">
        #{id}
    </foreach>
</delete>

根据用户 id 来在中间表中新增与之对应的角色 ids：一对一或者一对多的关系

/**
  * 修改中间表user_role的id
  * @param userId 用户id
  * @param roleIds 角色id
  * @return 受影响行数
  */
int addRolesByUserId(@Param("userId") String userId, @Param("roleIds") String[] roleIds);

<insert id="addRolesByUserId">
    INSERT INTO user_role
    <foreach collection="roleIds" open="values" item="roleId" separator=",">
        (#{userId},#{roleId})
    </foreach>
</insert>

注解式查询

@Insert
@Update
@Delete
@Select
@Results，resultMap一组结果映射，指定了对某个特定结果列，映射到某个属性或字段的方式

示例1：

@Results(id = "userResult", value = {
  @Result(property = "id", column = "uid", id = true),
  @Result(property = "firstName", column = "first_name"),
  @Result(property = "lastName", column = "last_name")
})
@Select("select * from users where id = #{id}")
User getUserById(Integer id);

示例2：

@Select("<script>" +
    	"SELECT id,username,age from user WHERE id in " +
        "<foreach item = 'id' index = 'index' collection = 'ids' open='(' separator=',' close=')'>" +
                "#{id}" +
         "</foreach>"+
        "</script>")
List<User> getByIds(@Param("ids") List<Integer> ids);

等同于xml中

<select id="getByIds" resultType="cn.blue.项目工程名.bean.Role">
    SELECT id,username,age from user 
    WHERE id in 
    <foreach item = 'id' index = 'index' collection = 'ids' open='(' separator=',' close=')'>
        #{id}
    </foreach>
</select>

一步步放弃mybatis的xml

Java API

大于等于小于等于的写法

第一种写法（1）：

原符号       <        <=      >       >=       &        '        "
替换符号    &lt;    &lt;=   &gt;    &gt;=   &amp;   &apos;  &quot;
例如：sql如下：
create_date_time &gt;= #{startTime} and  create_date_time &lt;= #{endTime}

第二种写法（2）：
大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>
例如：sql如下：
create_date_time <![CDATA[ >= ]]> #{startTime} and  create_date_time <![CDATA[ <= ]]> #{endTime}

参考-mybatis中大于等于小于等于的写法

常见错误

1. Caused by: com.alibaba.druid.sql.parser.EOFParserException: EOF

可能是mybatis没有遍历的时候没有添加非空判断, 即purchaseIdList.size() > 0

<if test="purchaseIdList != null and purchaseIdList.size() > 0"> and purchase_id in
    <foreach item="purchaseId" collection="purchaseIdList" open="(" separator="," close=")" index="index">
        #{purchaseId}
    </foreach>
</if>

Mybatis Plus

官方文档

UpdateWrapper用法

记一次测试库事故，一个update直接导致全表更新，事后火葬场！！！

UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.set("username", "lauy);
// wrapper.eq("id", user.getId());
userService.update(user, wrapper);

// 同
userService.update(user, new QueryWrapper<User>().lambda()
    .eq(User::getId, user.getId())
    .eq(User::getUsername, user.getUsername()));

可以看出我们使用wrapper构造器时候，你传入的user对象它最后只是决定修改哪几列（user对象有值的字段对应的column），而不是修改这唯一一条。所以我们不加eq条件，那么就是走的全表！！！
修改后的sql为：

1	UPDATE acl_user SET username=?, password=?, nick_name=?, salt=?, token=?, is_deleted=?, gmt_modified=? WHERE username = ? and id = ?

详解

WorkOrderRetry orderRetry = new WorkOrderRetry();
orderRetry.setState(true);
orderRetry.setTimes(null);
UpdateWrapper<WorkOrderRetry> wrapper = new UpdateWrapper<>();
wrapper.lambda().eq(WorkOrderRetry::getId, id);
workOrderRetryService.update(orderRetry, wrapper);