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. 视频笔记
本篇笔记也在小破站通过视频形式分享,欢迎访问: 小破站视频笔记。