[大数据]动态sql以及Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

1.数据库表：

CREATE TABLE blog(
id VARCHAR(50) NOT NULL COMMENT '博客id',
title VARCHAR(100) NOT NULL COMMENT '博客标题',
author VARCHAR(30) NOT NULL COMMENT '博客作者',
create_time DATETIME NOT NULL COMMENT '创建时间',
views  INT(30) NOT NULL COMMENT '浏览量'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

BlogMapper接口代码

 //插入数据
    int addBlog(Blog blog);
//查询博客
    List<Blog> queryBlogIF(Map map);

BlogMapper.xml 代码

<!--插入-->
<insert id="addBlog" parameterType="blog">
    insert into mybatis.blog(id,title,author,create_time,views)
    values (#{id},#{title},#{author},#{createTime},#{views});
</insert>

测试类：

@Test
    public void addInitBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog=new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis 如此简单");
        blog.setAuthor("浮川@");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("java 如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("String 如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务 如此简单");
        mapper.addBlog(blog);

        sqlSession.close();
    }

IF语句的使用
当我们的查询语句为

<!--查询-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
       select * from mybatis.blog where

           <if test="title !=null">
               title=#{title}
           </if>
           <if test="author !=null">
              and author=#{author}
           </if>

   </select>

 @Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title", "java如此简单");
        //hashMap.put("author", "浮川@");

        List<Blog> blogs = mapper.queryBlogIF(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

输出的语句是正确的，where后面加条件

==> Preparing: select * from mybatis.blog where title=?

当我只查author，这么写时就会报错

==>  Preparing: select * from mybatis.blog where and author=?
==> Parameters: 浮川@(String)

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 'and author='浮川@'' at line 5
### The error may exist in com/sophy/dao/BlogMapper.xml
### The error may involve com.sophy.dao.BlogMapper.queryBlogIF-Inline
### The error occurred while setting parameters
### SQL: select * from mybatis.blog where                                             and author=?
### 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 'and author='浮川@'' at line 5

相当于 select * from mybatis.blog where and and author=#{author} 肯定要报错

@Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
//    hashMap.put("title", "java如此简单");
        hashMap.put("author", "浮川@");

        List<Blog> blogs = mapper.queryBlogIF(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

结果：

==> Preparing: select * from mybatis.blog where and author=?
==> Parameters: 浮川@(String)

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 ‘and author=‘浮川@’’ at line 5

The error may exist in com/sophy/dao/BlogMapper.xml

The error may involve com.sophy.dao.BlogMapper.queryBlogIF-Inline

The error occurred while setting parameters

SQL: select * from mybatis.blog where and author=?

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 ‘and author=‘浮川@’’ at line 5

当将where作为标签使用之后：
BlogMapper.xml

 <!--查询-->
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog

           <where>
               <if test="title !=null">
                   title=#{title}
               </if>
               <if test="author !=null">
                   and author=#{author}
               </if>
           </where>


    </select>

测试类：

    @Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        //hashMap.put("title", "java 如此简单");
        hashMap.put("author", "浮川@");

        List<Blog> blogs = mapper.queryBlogIF(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

结果
Preparing: select * from mybatis.blog WHERE author=?
==> Parameters: 浮川@(String)

 <if test="author !=null">
                   and author=#{author}
               </if>

<where></where>
<!--标签可以自动检查语句，如果条件是第一个，会自定将and去掉-->

当需要时and又会自动加上：

  @Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title", "java 如此简单");
        hashMap.put("author", "浮川@");

        List<Blog> blogs = mapper.queryBlogIF(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

结果：

Preparing: select * from mybatis.blog WHERE title=? and author=?
> Parameters: java 如此简单(String), 浮川@(String)
< Columns: id, title, author, create_time, views
<== Row: 06bfde70d1ca4875b309ba02b656139f, java 如此简单, 浮川@, 2022-02-03 23:59:24.0, 9999
<== Row: 6839c883e7084a169bbecbe1e67dee37, java 如此简单, 浮川@, 2022-02-04 10:39:54.0, 9999
<== Row: ec35cd5812e348d2a7edeca1d660a5de, java 如此简单, 浮川@, 2022-02-04 10:40:41.0, 9999
<== Row: 58514c0dd3f74a75ad0b704c46bbfcf1, java 如此简单, 浮川@, 2022-02-04 10:41:58.0, 9999
<== Total: 4

最后，当什么都不传的时候,会将where自动去掉：

 @Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
       // hashMap.put("title", "java 如此简单");
      //  hashMap.put("author", "浮川@");

        List<Blog> blogs = mapper.queryBlogIF(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

空的hashMap，看sql语句:select * from mybatis.blog
where 已经自动去掉了

choose,when,otherwise的使用
接口：

List<Blog> queryBlogChoose(Map map);

BlogMapper.xml

 <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title !=null">
                    title=#{title}
                </when>
                <when test="author !=null">
                   and author=#{author}
                </when>
                <otherwise>
                    and views=#{views}
                </otherwise>
            </choose>
        </where>
    </select>

测试类

    @Test
    public void queryBlogChoose() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        /*hashMap.put("title", "java 如此简单");
        hashMap.put("author", "浮川@");
        hashMap.put("views", "9999");
*/
        List<Blog> blogs = mapper.queryBlogChoose(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

什么都不传： Preparing: select * from mybatis.blog WHERE views=?
==> Parameters: null

当传递views的参数时：

    @Test
    public void queryBlogChoose() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title", "java 如此简单");
        hashMap.put("author", "浮川@");
        hashMap.put("views", "9999");

        List<Blog> blogs = mapper.queryBlogChoose(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

Preparing: select * from mybatis.blog WHERE views=?
==> Parameters: 9999(String)
会自动去掉and

查出了所有views为9999的blog

当传递的参数为：

 public void queryBlogChoose() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title", "java 如此简单");
       // hashMap.put("author", "浮川@");
        hashMap.put("views", "9999");

        List<Blog> blogs = mapper.queryBlogChoose(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

满足第一个条件了，查出了四条，因为是choose,只能选择其中一个，由于第一个已经满足了，不会再选择其它的
Preparing: select * from mybatis.blog WHERE title=?
==> Parameters: java 如此简单(String)

@Test
    public void queryBlogChoose() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        //hashMap.put("title", "java 如此简单");
         hashMap.put("author", "浮川@");
         hashMap.put("views", "9999");

        List<Blog> blogs = mapper.queryBlogChoose(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

当第一个条件不满足，或者没有写第一个条件满足，第二个条件满足，会自动去掉and

Preparing: select * from mybatis.blog WHERE author=?
==> Parameters: 浮川@(String)

set 的使用：
接口

  //更新博客
    int updateBlog(Map map);

BlogMapper.xml的查询语句

<update id="updateBlog" parameterType="map">
    update mybatis.blog
<set>
    <if test="title !=null">
        title=#{title},
    </if>
        <if test="author !=null">
            author=#{author}
        </if>
</set>
where id=#{id}
</update>

测试：

@Test
    public void updateBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title", "java 如此简单2");
       // hashMap.put("author", "浮川@@");
     //  hashMap.put("views", "9999");
        hashMap.put("id", "c3a0c6098b0940c48cf46d58b20019d5");
        mapper.updateBlog(hashMap);


        sqlSession.close();
    }

更新成功：
当为第二个条件满足时，会自动去掉BlogMapper.xml里面的更新语句里面的" , "

@Test
    public void updateBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        //hashMap.put("title", "java 如此简单2");
        hashMap.put("author", "浮川@@");
     //  hashMap.put("views", "9999");
        hashMap.put("id", "c3a0c6098b0940c48cf46d58b20019d5");
        mapper.updateBlog(hashMap);


        sqlSession.close();
    }

当只写id时：

@Test
    public void updateBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        //hashMap.put("title", "java 如此简单2");
        //hashMap.put("author", "浮川@@");
     //  hashMap.put("views", "9999");
        hashMap.put("id", "c3a0c6098b0940c48cf46d58b20019d5");
        mapper.updateBlog(hashMap);


        sqlSession.close();
    }

报错 语句不完整 set条件也不会有
SQL: update mybatis.blog where id=? 这个语句不完整