SpringBoot+Mybatis多条件筛选

  在实际的业务开发系统中，做的最多的工作就是增、删、改、查操作，而这部分增、删、改、查的操作中又有80%的都是查询操作。本文记录的主要内容是，基于SpringBoot和Mybatis来实现条件查询的功能。

1. 使用Map作为筛选条件

<select id="selectByCondition" resultMap="BaseResultMap" parameterType="Map">
    select
    <include refid="Base_Column_List"/>
    from t_user
    where 1=1
    <if test="map != null">
      <foreach collection="map" item="value" index="key">
        <if test="value != null and value != ''">
          AND ${key} = #{value}
        </if>
      </foreach>
    </if>
    <if test="offset != null and pageSize != null">
      LIMIT #{offset}, #{pageSize}
    </if>
  </select>

  Service中具体的调用逻辑如下：

public List<User> getByParam(UserListParam param) {
        int offset = 0;
        if (!ObjectUtils.isEmpty(param.getPageIndex()) && !ObjectUtils.isEmpty(param.getPageSize())) {
            offset = (param.getPageIndex() - 1) * param.getPageSize();
        }
        param.setOffset(offset);
        return userMapper.selectByParam(param);
    }

2. 使用对象作为筛选条件

<select id="selectByParam" resultMap="BaseResultMap" parameterType="com.pkh.bean.param.UserListParam" >
    select
    <include refid="Base_Column_List" />
    from t_user
    <where>
      <include refid="join_list_where" />
    </where>
    <if test="sortBy != null and sortBy != ''">
      ORDER BY ${sortBy}
      <if test="sortOrder != null and sortOrder != ''">
        ${sortOrder}
      </if>
    </if>
    <if test="offset != null and pageSize != null">
      LIMIT #{offset}, #{pageSize}
    </if>
  </select>

<sql id="Base_Column_List">
    id, user_id, user_name, `password`, deal_password, sex, `type`, phone, mobie, email, 
    we_chat, telegram, address, zip_code, create_time, update_time
  </sql>

<resultMap id="BaseResultMap" type="com.pkh.dao.po.User">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="user_id" jdbcType="VARCHAR" property="userId" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="deal_password" jdbcType="VARCHAR" property="dealPassword" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="type" jdbcType="VARCHAR" property="type" />
    <result column="phone" jdbcType="VARCHAR" property="phone" />
    <result column="mobie" jdbcType="VARCHAR" property="mobie" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="we_chat" jdbcType="VARCHAR" property="weChat" />
    <result column="telegram" jdbcType="VARCHAR" property="telegram" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="zip_code" jdbcType="VARCHAR" property="zipCode" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>

  筛选条件定义在id为join_list_where的sql标签中。

<sql id="join_list_where">
    <if test="userId != null and userId != '' ">
      AND user_id = #{userId}
    </if>
    <if test="sex != null and sex != '' ">
      AND sex = #{sex}
    </if>
    <if test="createTime != null and createTime.size() > 0">
      AND create_time <![CDATA[>= ]]> #{createTime[0]} AND create_time <![CDATA[<= ]]> #{createTime[1]}
    </if>
  </sql>

  Service中具体的调用逻辑如下：

public List<User> getByCondition(UserListParam param) {
        int offset = 0;
        if (!ObjectUtils.isEmpty(param.getPageIndex()) && !ObjectUtils.isEmpty(param.getPageSize())) {
            offset = (param.getPageIndex() - 1) * param.getPageSize();
        }
        Map<String, Object> map = new HashMap<>();
        map.put("user_id", param.getUserId());
        map.put("sex", param.getSex());
        return userMapper.selectByCondition(map, offset, param.getPageSize());
    }

3. 视频笔记

本篇笔记也在小破站通过视频形式分享，欢迎访问： 小破站视频笔记。