mybatis标签使用报错解决

问题描述:

今天在页面测试接口时候发现一个接口的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 id="queryXfDataList" resultType="com.fang.newhouse.server.model.dto.input.xinfangtong.XinFangQueryListInDTO">
        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
        <where>
            <if test="City != null and City !=''  and City !='-1'">
                a.City=#{City}
            </if>
            <if test="ProjName != null and ProjName !=''">
                a.ProjName=#{ProjName}
            </if>
            <if test="Aid != null and Aid !=''">
                a.Aid=#{Aid}
            </if>
            <if test="Phone != null and Phone !=''">
                a.Phone=#{Phone}
            </if>
        </where>
        order by a.Createtime desc
    </select>

原因:

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

<select id="queryXfDataList" resultType="com.fang.newhouse.server.model.dto.input.xinfangtong.XinFangQueryListInDTO">
        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
        <where>
            <if test="City != null and City !=''  and City !='-1'">
               AND a.City=#{City}
            </if>
            <if test="ProjName != null and ProjName !=''">
                AND a.ProjName=#{ProjName}
            </if>
            <if test="Aid != null and Aid !=''">
                AND a.Aid=#{Aid}
            </if>
            <if test="Phone != null and Phone !=''">
                AND a.Phone=#{Phone}
            </if>
        </where>
        order by a.Createtime desc
    </select>

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

总结：

第二个拼接的sql 前忘了加AND连接符导致的
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。