解决MySQL报错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.

1.问题产生

项目测试环节，jdbc连接过程where子句出现报错问题

org.apache.ibatis.exceptions.PersistenceException: 
### 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 'title = 'xxx'' at line 3
### The error may exist in com/lanyy/dao/BlogMapper.xml
### The error may involve com.lanyy.dao.BlogMapper.queryBlogIF-Inline
### The error occurred while setting parameters
### SQL: select * from blog where 1=1                       title = ?
### 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 'title = 'xxxx'' at line 3

2.问题排查

where之后需接条件语句，需写where 1=1，如果引入标签则不要再写，标签会自动添加执行

3.问题解决

引入<where>标签子句

where元素只会在至少有一个子元素的条件满足情况下才去插入 \ 查询，

这个“where”标签会知道如果它输入的信息中包含的标签有返回值（参数）的话，它就插入一个‘where’条件。

此外，如果标签返回的内容是以AND 或 OR 开头的，则它会剔除掉AND或OR。

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
            and author = #{author}
            </if>
        </where>
    </select>

不引入标签，只使用sql中where语句，需要在where后加入条件判断

<select id="queryBlogIF" parameterType="map" resultType="Blog">
 <!-- 1=1恒等 条件永久成立 直接遍历blog表中所有信息 选择查询title或者author-->
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
</select>

调试后运行成功

了解更多知识请戳下：

@Author:懒羊羊