业务要求查询有分页功能和关键字查询
1、创建实体 TymOrg
2、创建TymOrgDto继承TymOrg,添加pageNum,pageSize,keyWord三个字段。
3、controller(对参数进行校验,防止sql异常)
@PostMapping(value = "/queryOfPage")
public R queryOfPage(@RequestBody() TymOrgDto tymOrgDto) throws Exception {
if (tymOrgDto.getPageNum() == null) { //检验页码
return R.error("页码为必传");
} else if (tymOrgDto.getPageSize() == null) { //检验页码
return R.error("每页条数为必传");
} else if (tymOrgDto.getPageNum() < 1) { //检验每页参数
return R.error("页码不能小于1");
}
return R.ok(tymOrgService.queryOfPage(tymOrgDto));
}
4、service(分页参数计算,limit start pageSize; start=(currentPage-1)*pageSize
@Override public List<TymOrg> queryOfPage(TymOrgDto tymOrgDto) { //分页参数设置 Integer pageNum = (tymOrgDto.getPageNum() - 1)*(tymOrgDto.getPageSize()); tymOrgDto.setPageNum(pageNum); return tymOrgMapper.queryOfPage(tymOrgDto); }
5、mapper.xml(sql关键字,用需要匹配的字段like #{keyWord} 然后用依次or进行连接 )
注意limit pageNum pageSize 后面两个参数需要用&{pageNum },&{pageSize }否则有错
<select id="queryOfPage" resultType="com.yizhangtu.tym_manage.entity.TymOrg"> select * from tym_org <where> <if test="keyWord != null and keyWord != '' "> org_name like concat('%',#{keyWord},'%') or administrative_division like concat('%',#{keyWord},'%') or contacts_name like concat('%',#{keyWord},'%') or contacts_name_telephone like concat('%',#{keyWord},'%') or contact_address like concat('%',#{keyWord},'%') </if> <if test="id != null and id != ''">id = #{id}</if> <if test="orgName != null and orgName != ''">org_name = #{orgName}</if> <if test="administrativeDivision != null and administrativeDivision != ''">administrative_division = #{administrativeDivision} </if> <if test="administrativeDivisionCode != null and administrativeDivisionCode != ''"> administrative_division_code = #{administrativeDivisionCode} </if> <if test="contactsName != null and contactsName != ''">contacts_name = #{contactsName}</if> <if test="contactsNameTelephone != null and contactsNameTelephone != ''">contacts_name_telephone = #{contactsNameTelephone} </if> <if test="contactAddress != null and contactAddress != ''">contact_address = #{contactAddress}</if> <if test="establishedTime != null and establishedTime != ''">established_time = #{establishedTime}</if> </where> limit ${pageNum},${pageSize}; </select>
成功: