【AgileTC】_Mybatis配置文件

发表于 | 分类于

【AgileTC】_Mybatis

常用配置

  • 映射，将数据库查询结果字段与javabean对象进行映射；

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    <resultMap id="RoleMap" type="com.xiaoju.framework.shiro.entity.pojo.Role">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="role_name" jdbcType="VARCHAR" property="roleName" />
        <result column="is_delete" jdbcType="INTEGER" property="isDelete" />
        <result column="is_block" jdbcType="INTEGER" property="isBlock" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="line_id" jdbcType="BIGINT" property="lineId" />
        <result column="channel" jdbcType="INTEGER" property="channel" />
        <result column="type" jdbcType="INTEGER" property="type"/>
        <result column="creator" jdbcType="VARCHAR" property="creator"/>
        <result column="creator_cn" jdbcType="VARCHAR" property="creatorCn"/>
        <result column="modifier" jdbcType="VARCHAR" property="modifier"/>
        <result column="modifier_cn" jdbcType="VARCHAR" property="modifierCn"/>
    </resultMap>

  • 扩展映射与列表映射；

    1
    2
    3
    4
    5
    6
    7
    <resultMap id="RoleUserMap" extends="RoleMap" type="com.xiaoju.framework.shiro.entity.pojo.Role">
        <collection property="users" ofType="com.xiaoju.framework.shiro.entity.pojo.User">
            <id column="user_id" jdbcType="BIGINT" property="id" />
            <result column="username" jdbcType="VARCHAR" property="username" />
            <result column="username_cn" jdbcType="VARCHAR" property="usernameCn" />
        </collection>
    </resultMap>

  • sql语句，此处的查询结果会被mybatis直接赋值映射结果的对象字段中，包括列表。（这里的外连接查询与列表映射的结合太巧妙了，一定要学会）

    1
    2
    3
    4
    5
    6
    7
    8
    <select id="getRoleList" resultMap="RoleUserMap">
        select r.*, u.id as user_id, u.username, u.username_cn from roles r
            left join user_role_rel urr on r.id = urr.role_id and urr.is_delete = 0
            left join user_info u on u.id = urr.user_id and u.is_delete = 0
            and u.channel = #{channel} and u.line_id = #{lineId}
        where ((r.channel = #{channel} and r.line_id = #{lineId}) or (r.channel = -1 and r.line_id = 0))
          and r.is_delete = 0;
    </select>

MySQL中列表格式字符串字段的条件筛选查询

  • 场景描述，比如数据库中一个字段数据为”1,3,5,7”，现在我们输入查询条件数据为”1,2,4”，也就是说我们希望把数据库中该字段只有包含1或者2或者4的数据行都搜索出来。

  • 思路一：在dao层编写相关筛选逻辑，缺点是应该尽量将逻辑操作放到service层中。

    • 1.先将输入的查询条件分解成数组形式，作为mapper方法的输入

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      //分解为数组
      String[] requirementIds = StringUtils.isEmpty(req.getRequirementId()) ? null : req.getRequirementId().split(",");

      // 分页
      PageHelper.startPage(req.getPageNum(), req.getPageSize());
      List<TestCase> testCaseList = caseMapper.listTestCaseByIds(
              req.getLineId(), req.getCaseType(), req.getId(),
              req.getTitle(), req.getCreator(), requirementIds,
              beginTime, endTime, req.getChannel()
      );

    • 2.在xml文件中编写筛选查询逻辑，此处使用了find_in_set()函数，此函数常用于加在where后作为查询条件，find_in_set(str,strlist)中第二个参数表示被查询字段，该字段数据必须是以英文逗号为分隔符的字符串格式。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      <select id="listTestCaseByIds" parameterType="java.lang.Long" resultMap="ResultMapWithBLOBs">
        select
        <include refid="Base_Column_List" />
        from test_case
        <where>
          <if test="requirementId == null">
            product_line_id = #{productLineId,jdbcType=BIGINT} and is_delete = 0 and case_type = #{caseType,jdbcType=INTEGER }
            <if test="id != null">
              and id = #{id,jdbcType=BIGINT}
            </if>
            <if test="creator != null and creator != ''">
              and creator = #{creator,jdbcType=VARCHAR}
            </if>
            <if test="beginTime != null">
              and gmt_created &gt;= #{beginTime,jdbcType=TIMESTAMP}
            </if>
            <if test="endTime != null">
              and gmt_created &lt;= #{endTime,jdbcType=TIMESTAMP}
            </if>
            <if test="title != null and title != ''">
              and title like CONCAT('%',#{title,jdbcType=VARCHAR},'%')
            </if>
            <if test="channel!= null ">
              and channel = #{channel,jdbcType=INTEGER}
            </if>
          </if>
          <if test="requirementId != null">
            <foreach collection="requirementId" item="item" separator="or">
              product_line_id = #{productLineId,jdbcType=BIGINT} and is_delete = 0 and case_type = #{caseType,jdbcType=INTEGER }
              <if test="id != null">
                and id = #{id,jdbcType=BIGINT}
              </if>
              <if test="creator != null and creator != ''">
                and creator = #{creator,jdbcType=VARCHAR}
              </if>
              <if test="requirementId != null">
                and FIND_IN_SET(#{item,jdbcType=VARCHAR},requirement_id)
              </if>
              <if test="beginTime != null">
                and gmt_created &gt;= #{beginTime,jdbcType=TIMESTAMP}
              </if>
              <if test="endTime != null">
                and gmt_created &lt;= #{endTime,jdbcType=TIMESTAMP}
              </if>
              <if test="title != null and title != ''">
                and title like CONCAT('%',#{title,jdbcType=VARCHAR},'%')
              </if>
              <if test="channel!= null ">
                and channel = #{channel,jdbcType=INTEGER}
              </if>
            </foreach>
          </if>
        </where>
        order by id desc
      </select>

  • 思路二：先在dao层忽略这个字段的查询条件，然后service层添加逻辑来对dao层查询结果进行筛选，这样做的缺点是无法使用pagehelper分页助手。

    • 1.在service层遍历dao层的查询结果，不符合要求的则不放入最终返回结果列表中。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      //判断测试任务负责人字段是否包含查询负责人其中之一，不包含则舍弃
      if (!StringUtils.isEmpty(req.getOwner())) {
          Set<String> owners = Stream.of(req.getOwner().split(SystemConstant.COMMA)).collect(Collectors.toSet());
          Set<String> dbOwners = Stream.of(execRecord.getOwner().split(SystemConstant.COMMA)).collect(Collectors.toSet());
          boolean contain = false;
          for (String owner : owners) {
              if (dbOwners.contains(owner)) {
                  contain = true;
                  break;
              }
          }
          if (!contain) continue;
      }

    • 2.编写分页逻辑，手动计算出目标页码的第一行数据和最后一行数据在结果集中的索引。

      1
      2
      3
      4
      5
      6
      //获取筛选后的分页结果
      int fromIndex = req.getPageSize() * (req.getPageNum() - 1);
      int toIndex = Math.min(req.getPageSize() * req.getPageNum(), list.size());

      res = PageResult.buildPage(list.subList(fromIndex,toIndex), list.size());
      return res;