今天把原来的一个查询接口的业务代码进行了优化,减少了十几行冗余的代码。
原来的代码
ChongwuServiceImpl.java
/*** @author heyunlin* @version 1.0*/
@Slf4j
@Service
public class ChongwuServiceImpl implements ChongwuService {@Overridepublic JsonResult<JsonPage<Chongwu>> selectByPage(ChongwuPager pager) {List<Integer> skillIds = pager.getSkillIds();if (CollectionUtils.isNotEmpty(skillIds)) {int size = skillIds.size();// 得到order by语句String statement = Pager.getOrderByStatement(pager);List<Chongwu> rows = chongwuMapper.selectBySkills(pager, skillIds, size, statement);long total = chongwuMapper.selectCountBySkills(pager, skillIds, size);return JsonResult.restPage(total, rows);} else {Page<Chongwu> page = new Page<>(pager.getPage(), pager.getRows());QueryWrapper<Chongwu> wrapper = new QueryWrapper<>();wrapper.eq(pager.getCategoryId() != null,"category_id", pager.getCategoryId());wrapper.eq(StringUtils.isNotEmpty(pager.getRoleId()),"role_id", pager.getRoleId());wrapper.eq(StringUtils.isNotEmpty(pager.getZuoqiId()),"zuoqi_id", pager.getZuoqiId());// 得到order by语句String statement = Pager.getOrderByStatement(pager);wrapper.last(statement);Page<Chongwu> result = chongwuMapper.selectPage(page, wrapper);return JsonResult.restPage(result);}}}
ChongwuMapper.java
@Repository
public interface ChongwuMapper extends BaseMapper<Chongwu> {/*** 查询已学习指定技能的宠物数量* @param pager 分页参数* @param skillIds 宠物技能类型id列表* @param total 总技能数* @return int*/long selectCountBySkills(@Param("pager") ChongwuPager pager,@Param("skillIds") List<Integer> skillIds,@Param("total") int total);/*** 查询已学习指定技能的宠物* @param pager 分页参数* @param skillIds 宠物技能类型id列表* @param total 总技能数* @param statement order by后面的语句* @return List<Chongwu>*/List<Chongwu> selectBySkills(@Param("pager") ChongwuPager pager,@Param("skillIds") List<Integer> skillIds,@Param("total") int total,@Param("statement") String statement);
}
ChongwuMapper.xml
<?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.edu.sgu.www.mhxysy.mapper.chongwu.ChongwuMapper"><resultMap id="resultMap" type="cn.edu.sgu.www.mhxysy.entity.chongwu.Chongwu"><result column = "id" property = "id" /><result column = "name" property = "name" /><result column = "type" property = "type" /><result column = "grade" property = "grade" /><result column = "score" property = "score" /><result column = "role_id" property = "roleId" /><result column = "zuoqi_id" property = "zuoqiId" /><result column = "zizhi_id" property = "zizhiId" /><result column = "lifespan" property = "lifespan" /><result column = "ty_status" property = "tyStatus" /><result column = "category_id" property = "categoryId" /><result column = "attribute_id" property = "attributeId" /></resultMap><select id="selectCountBySkills" resultType="long">select count(*) from chongwu where id in (select cs.chongwu_id from (select chongwu_id from chongwu_skill where skill_id in (<foreach item='skillId' collection='skillIds' separator=','>#{skillId}</foreach>)) as csgroup by cs.chongwu_idhaving count(cs.chongwu_id) >= #{total})<if test='pager.zuoqiId != null and pager.zuoqiId != ""'>and zuoqi_id = #{pager.zuoqiId}</if><if test='pager.roleId != null and pager.roleId != ""'>and role_id = #{pager.roleId}</if><if test='pager.categoryId != null'>and category_id = #{pager.categoryId}</if></select><select id="selectBySkills" resultMap="resultMap">select * from chongwu where id in (select cs.chongwu_id from (select chongwu_id from chongwu_skill where skill_id in (<foreach item='skillId' collection='skillIds' separator=','>#{skillId}</foreach>)) as csgroup by cs.chongwu_idhaving count(cs.chongwu_id) >= #{total})<if test='pager.zuoqiId != null and pager.zuoqiId != ""'>and zuoqi_id = #{pager.zuoqiId}</if><if test='pager.roleId != null and pager.roleId != ""'>and role_id = #{pager.roleId}</if><if test='pager.categoryId != null'>and category_id = #{pager.categoryId}</if>order by role_id, #{statement}</select>
</mapper>
重构后的代码
ChongwuServiceImpl.java
/*** @author heyunlin* @version 1.0*/
@Slf4j
@Service
public class ChongwuServiceImpl implements ChongwuService {@Overridepublic Page<Chongwu> selectByPage(ChongwuPager pager) {List<Integer> skillIds = pager.getSkillIds();pager.setTotal(skillIds != null ? skillIds.size() : 0);pager.setStatement(Pager.getStatement(pager));Page<Chongwu> page = Pager.ofPage(pager);return chongwuMapper.selectPage(page, pager);}}
ChongwuMapper.java
@Repository
public interface ChongwuMapper extends BaseMapper<Chongwu> {/*** 分页查询宠物列表* @param page 分页参数* @param pager 查询条件* @return List<Chongwu>*/Page<Chongwu> selectPage(Page<Chongwu> page, ChongwuPager pager);
}
ChongwuMapper.xml
<?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.edu.sgu.www.mhxysy.mapper.chongwu.ChongwuMapper"><resultMap id="resultMap" type="cn.edu.sgu.www.mhxysy.entity.chongwu.Chongwu"><result column = "role_id" property = "roleId" /><result column = "zuoqi_id" property = "zuoqiId" /><result column = "zizhi_id" property = "zizhiId" /><result column = "ty_status" property = "tyStatus" /><result column = "category_id" property = "categoryId" /><result column = "attribute_id" property = "attributeId" /></resultMap><select id="selectPage" resultMap="resultMap">select * from chongwu<where>1 = 1<if test='pager.total > 0'>and id in (select cwjnb.chongwu_id from (select chongwu_id from chongwu_skill where skill_id in (<foreach item="skillId" collection="pager.skillIds" separator=",">#{skillId}</foreach>)) as cwjnbgroup by cwjnb.chongwu_idhaving count(cwjnb.chongwu_id) >= #{pager.total})</if><if test="pager.zuoqiId != null and pager.zuoqiId != ''">and zuoqi_id = #{pager.zuoqiId}</if><if test="pager.roleId != null and pager.roleId != ''">and role_id = #{pager.roleId}</if><if test='pager.categoryId != null'>and category_id = #{pager.categoryId}</if></where><if test="pager.statement != null and pager.statement != ''">order by #{pager.statement}</if></select>
</mapper>
排序失效问题
修改后引发了前端排序失效问题,点击排序图标触发了重新渲染表格数据的ajax请求,而且控制台打印的SQL语句也没有问题,直接复制到数据库中执行,能查出排序后的数据。
前端排序失效截图
控制台打印的SQL查询语句
直接复制到Navicat中执行,查询到了正确的结果(score列按升序排序)
问题原因分析
遇到这个问题其实仔细一想非常简单,就是${}和#{}的区别,把#{}改为${}即可。
这是把#{}改成${}之后,在控制台的SQL语句,两者有一定的区别:
- 上图的SQL语句:select * from chongwu WHERE 1 = 1 order by role_id , "score desc" LIMIT 10
- 下图的SQL语句:select * from chongwu WHERE 1 = 1 order by role_id , score desc LIMIT 10
纠正后的代码
ChongwuMapper.xml
<?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.edu.sgu.www.mhxysy.mapper.chongwu.ChongwuMapper"><resultMap id="resultMap" type="cn.edu.sgu.www.mhxysy.entity.chongwu.Chongwu"><result column = "id" property = "id" /><result column = "name" property = "name" /><result column = "type" property = "type" /><result column = "grade" property = "grade" /><result column = "score" property = "score" /><result column = "role_id" property = "roleId" /><result column = "zuoqi_id" property = "zuoqiId" /><result column = "zizhi_id" property = "zizhiId" /><result column = "lifespan" property = "lifespan" /><result column = "ty_status" property = "tyStatus" /><result column = "category_id" property = "categoryId" /><result column = "attribute_id" property = "attributeId" /></resultMap><select id="selectPage" resultMap="resultMap">select * from chongwu<where>1 = 1<if test='pager.total > 0'>and id in (select cwjnb.chongwu_id from (select chongwu_id from chongwu_skill where skill_id in (<foreach item="skillId" collection="pager.skillIds" separator=",">#{skillId}</foreach>)) as cwjnbgroup by cwjnb.chongwu_idhaving count(cwjnb.chongwu_id) >= #{pager.total})</if><if test="pager.zuoqiId != null and pager.zuoqiId != ''">and zuoqi_id = #{pager.zuoqiId}</if><if test="pager.roleId != null and pager.roleId != ''">and role_id = #{pager.roleId}</if><if test='pager.categoryId != null'>and category_id = #{pager.categoryId}</if></where><if test="pager.statement != null and pager.statement != ''">order by ${pager.statement}</if></select>
</mapper>
重新启动项目之后,问题得以解决,不知道有没有大佬遇到类似的问题,欢迎评论区留言分享~
好了,文章就分享到这里了,看完不要忘了点赞+收藏哦~