mapper中传参limit 取出的数据是?:
SSM 架构 通过 mapper 传参 map,写分页语句异常
问题描述
通过 map 传参, limit 分页,显示sql 语句中 数字作为字符串
@RequestMapping("/workbench/activity/queryActivityConditionForPage.do")
public @ResponseBody List<Activity> queryActivityConditionForPage(String pageNo,String pageSize){
Map<String,Object> map = new HashMap<>();
map.put("pageNo",pageNo); // 这里应该放 startIndex么?
map.put("pageSize",pageSize);
List<Activity> activities = activityService.selectActivityByConditionForPage(map);
return activities;
}
<select id="selectActivityByConditionForPage" resultMap="BaseResultMap" >
select u.name owner,a.name,a.start_date,a.end_date
from tbl_activity a
join tbl_user u
on a.owner= u.id
limit #{pageNo},#{pageSize}
</select>
SQL: select u.name owner,a.name,a.start_date,a.end_date from tbl_activity a join tbl_user u on a.owner= u.id limit ?,?
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘1’,‘10’’ at line 5
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘1’,‘10’’ at line 5
原因分析:
可以看到参数传过来了,但是向map中放的是 String 类型的,所以取出来的时候sql语句中 limit ‘1’,‘10’
虽然 map 是 <String,Object> ,但是放入数据的时候,放的是 String ,mapper.xml 拼接 sql 的时候会将其作为字符串
解决方案:
在向 map 中放数据的时候应该放 Integer
@RequestMapping("/workbench/activity/queryActivityConditionForPage.do")
public @ResponseBody List<Activity> queryActivityConditionForPage(Integer pageNo,Integer pageSize){
Map<String,Object> map = new HashMap<>();
map.put("pageNo",pageNo); // 这里应该放 startIndex么?
map.put("pageSize",pageSize);
List<Activity> activities = activityService.selectActivityByConditionForPage(map);
return activities;
}