美文网首页
mybatis标签使用报错解决

mybatis标签使用报错解决

作者: 月中眠_d56d | 来源:发表于2019-07-25 16:08 被阅读0次

    问题描述:

    今天在页面测试接口时候发现一个接口的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 元素也会将它们去除。

    相关文章

      网友评论

          本文标题:mybatis标签使用报错解决

          本文链接:https://www.haomeiwen.com/subject/rhbhrctx.html