问题描述:

今天在页面测试接口时候发现一个接口的bug:

image.png

部分报错信息:

Caused by: java.sql.SQLSyntaxErrorException: 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 'a.Aid='22'

order by a.Createtime desc) tmp_count' at line 6

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.15.jar:8.0.15]

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.15.jar:8.0.15]

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.15.jar:8.0.15]

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970) ~[mysql-connector-java-8.0.15.jar:8.0.15]

at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:387) ~[mysql-connector-java-8.0.15.jar:8.0.15]

at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.10.jar:1.1.10]

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.6.jar:3.4.6]

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.6.jar:3.4.6]

mapper里的sql:

SELECT a.RoleId,a.RoleName,a.ID,a.City,a.ManagerName,a.Phone,a.ProjName,a.Aid,a.DeptName,a.JobTitle,a.Createtime,a.AddUser from 表名a

a.City=#{City}

a.ProjName=#{ProjName}

a.Aid=#{Aid}

a.Phone=#{Phone}

order by a.Createtime desc

原因:

在使用 mybatis 动态sql标签的时候,里面sql拼接有错误,应该改为:

SELECT a.RoleId,a.RoleName,a.ID,a.City,a.ManagerName,a.Phone,a.ProjName,a.Aid,a.DeptName,a.JobTitle,a.Createtime,a.AddUser from 表名a

AND a.City=#{City}

AND a.ProjName=#{ProjName}

AND a.Aid=#{Aid}

AND a.Phone=#{Phone}

order by a.Createtime desc

再运行,请求接口就好了 ~

总结：

第二个拼接的sql 前忘了加AND连接符导致的

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。