Mybatis动态SQL含运算语句

凉月

发布于：2019年7月25日

最近在一个小项目中用到分页，网上找到一条MySQL分页的公式

int page;	//page为当前页
int pageSize;	//pageSize为每页数量

//limit m, n 表示从第几条开始检索，查询多少条记录
select * from table limit (page - 1)*page , pageSize;

看起来好像很简单，稍微改下mapper文件就行

1 2	//当参数超过一个时，需使用 @Param 标注 ArrayList<animaInfo> selectAnima(@Param("page") int page, @Param("pageSize") int pageSize);

<select id="selectAnima" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from anima_info
  limit (#{page} - 1) * #{page}, #{pageSize}
</select>

保存、部署、运行一气呵成

2019-08-01 11:35:23.260 ERROR 7152 --- [p-nio-80-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 

### Error querying database.  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 - 1) * 1, 10' at line 6

### The error may exist in file [F:\Test\anima\target\classes\mapper\animaInfoMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select           id, anima_name, season, chapter, anima_link, watch_time, watch_status         from anima_info     limit (? - 1) * ?, ?
### 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 - 1) * 1, 10' at line 6
; 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 - 1) * 1, 10' at line 6] with root cause

あれ，居然出问题了

解决方案

赶紧查找大佬博客资料，好像Mybatis不能执行这种含有运算符的语句，有点坑。

知道问题就好办了，继续查找。

发现了一个标签<bind>,可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中。

继续改改，试试

<select id="selectAnima" resultMap="BaseResultMap">

    <bind name="num" value="(page - 1) * limit" />

    select
    <include refid="Base_Column_List" />
    from anima_info
    limit #{num} , #{pageSize}
  </select>