mysql 执行动态语句 mysql的动态sql

文章目录

• 1. 简介
• 2. 搭建环境

• 2.1 在MySQL中创建blog表
• 2.2 编写实体类
• 2.3 编写实体类对应Mapper接口
• 2.4 编写Mapper接口对应的Mapper.xml文件
• 2.5 编写测试类

• 3. if
• 4. where
• 5. set
• 6. choose
• 7. foreach

1. 简介

动态SQL就是指根据不同的条件生成不同的SQL语句

传统的使用JDBC的方法，在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。

2. 搭建环境

2.1 在MySQL中创建blog表

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

2.2 编写实体类

package com.zz.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createDate;
    private int views;

}

可选择在idea中连接数据库

解决办法：在mybatis-config.xml中开启驼峰命名

同时也开启日志

<!--开启日志和驼峰命名-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

2.3 编写实体类对应Mapper接口

package com.zz.mapper;

import com.zz.pojo.Blog;

public interface BlogMapper {

    //新增一个博客
    int addBlog(Blog blog);
}

2.4 编写Mapper接口对应的Mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zz.mapper.BlogMapper">

<insert id="addBlog" parameterType="Blog">

    insert into mybatis.blog (id, title, author, create_time, views)
    values (#{id},#{title},#{author},#{createDate},#{views});
</insert>
    
</mapper>

2.5 编写测试类

可选择在util目录下创建IDUtils工具类，因为在真实开发中id一般是随机的数字，调用IDUtils类，保证生成的id都不相同。

package com.zz.utils;

import java.util.UUID;

public class IDUtils {
        public static String getId(){
            return UUID.randomUUID().toString().replaceAll("-","");
        }
}

编写测试类，往数据库中添加八篇博客

package com.zz.mapper;

import com.zz.pojo.Blog;
import com.zz.utils.IDUtils;
import com.zz.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;

public class BlogMapperTest {
    @Test
    public void testAddBlog(){
        SqlSession session = MyBatisUtils.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog01 = new Blog();
        //调用IDUtils包，保证id都不相同
        blog01.setId(IDUtils.getId());  //真实开发中id一般是随机的数字（uuid）
        blog01.setTitle("Hello MyBatis");
        blog01.setAuthor("大佬");
        blog01.setCreateDate(new Date());
        blog01.setViews(9999);
        mapper.addBlog(blog01);

        Blog blog02 = new Blog();
        //调用IDUtils包，保证id都不相同
        blog02.setId(IDUtils.getId());  //真实开发中id一般是随机的数字（uuid）
        blog02.setTitle("Hello JDBC");
        blog02.setAuthor("大佬");
        blog02.setCreateDate(new Date());
        blog02.setViews(9999);
        mapper.addBlog(blog02);

        Blog blog03 = new Blog();
        //调用IDUtils包，保证id都不相同
        blog03.setId(IDUtils.getId());  //真实开发中id一般是随机的数字（uuid）
        blog03.setTitle("Hello MySQL");
        blog03.setAuthor("大佬");
        blog03.setCreateDate(new Date());
        blog03.setViews(9999);
        mapper.addBlog(blog03);

        Blog blog04 = new Blog();
        //调用IDUtils类，保证id都不相同
        blog04.setId(IDUtils.getId());  //真实开发中id一般是随机的数字（uuid）
        blog04.setTitle("Hello Spring");
        blog04.setAuthor("大佬");
        blog04.setCreateDate(new Date());
        blog04.setViews(9999);
        mapper.addBlog(blog04);

        session.commit(); //提交事务

    }
}

运行结果：

至此，环境搭建完毕！

3. if

接口BlogMapper的代码：

package com.zz.mapper;

import com.zz.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {

    //新增一个博客
    int addBlog(Blog blog);
    
    //通过作者名和博客名来查询博客
    //如果作者名为空，则根据博客名来查询
    List<Blog> getBlogByIf(Map map);
}

接口的配置文件BlogMapper.xml 代码：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zz.mapper.BlogMapper">

<insert id="addBlog" parameterType="Blog">

    insert into mybatis.blog (id, title, author, create_time, views)
    values (#{id},#{title},#{author},#{createDate},#{views});
</insert>

   <select id="getBlogByIf" resultType="Blog" parameterType="map">
       select * from mybatis.blog where

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

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

   </select>
    
</mapper>

测试类BlogMapperTest 代码：

• 当在map中只添加title时

public class BlogMapperTest {
    //测试if
    @Test
    public void testGetBlogByIf(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String, String> map = new HashMap<String, String>();
        map.put("title","Hello MyBatis");
        //map.put("author","小白");

        List<Blog> blogByIf = mapper.getBlogByIf(map);
        for (Blog blog : blogByIf) {
            System.out.println(blog);
        }
    }
}

运行结果：

• 当在map中添加title和author时

public class BlogMapperTest {
    //测试if
    @Test
    public void testGetBlogByIf(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String, String> map = new HashMap<String, String>();
        map.put("title","Hello MyBatis");
        map.put("author","小白");

        List<Blog> blogByIf = mapper.getBlogByIf(map);
        for (Blog blog : blogByIf) {
            System.out.println(blog);
        }
    }
}

运行结果：

可以看出：根据不同的条件生成不同的SQL语句，即动态SQL

4. where

使用场景：如果我们需要拼接where条件，又不希望客户端传递错误信息，这时需要使用where 标签。
作用：如果后面有语句，就自动添加where；如果后面语句开头是and 或者or，它可以自动去掉。

接口的配置文件BlogMapper.xml 代码：
注意：在author 前添加了 and

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zz.mapper.BlogMapper">

<insert id="addBlog" parameterType="Blog">

    insert into mybatis.blog (id, title, author, create_time, views)
    values (#{id},#{title},#{author},#{createDate},#{views});
</insert>

   <select id="getBlogByIf" resultType="Blog" parameterType="map">
       select * from mybatis.blog
       <where>

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

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

       </where>

   </select>

</mapper>

测试类BlogMapperTest 的代码：

• 当在map中什么都不添加，为空时

public class BlogMapperTest {

    //测试where
    @Test
    public void testGetBlogByIf(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String, String> map = new HashMap<String, String>();
        //map.put("title","Hello MyBatis");
        //map.put("author","小白");

        List<Blog> blogByIf = mapper.getBlogByIf(map);
        for (Blog blog : blogByIf) {
            System.out.println(blog);
        }
    }
}

运行结果：

可以看出：查询出了所有的博客

• 当在map中只添加author时

public class BlogMapperTest {
//测试where
    @Test
    public void testGetBlogByIf(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String, String> map = new HashMap<String, String>();
        //map.put("title","Hello MyBatis");
        map.put("author","小白");

        List<Blog> blogByIf = mapper.getBlogByIf(map);
        for (Blog blog : blogByIf) {
            System.out.println(blog);
        }
    }
}

运行结果：

可以看出，把配置文件中的 and 去掉了

5. set

如果里面的条件满足，自动拼接set标签；后面如果有多余的逗号，可以自动去除

接口BlogMapper的代码：

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

接口的配置文件BlogMapper.xml 代码：

注意：在title 后添加了逗号

<insert 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}
    </insert>

测试类BlogMapperTest 的代码：

map中必须添加id，否则会报错

• 当在map中只添加id和author时

//测试set
    @Test
    public void testSet(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("id","74205db5524c4e92942241e8b6443032");
        map.put("author","大佬");
        //map.put("title","大佬");

        mapper.updateBlog(map);
        session.commit();
    }

运行结果：

• 当在map中添加id，author和title时

//测试set
    @Test
    public void testSet(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("id","74205db5524c4e92942241e8b6443032");
        map.put("author","大佬");
        map.put("title","大佬");

        mapper.updateBlog(map);
        session.commit();
    }

运行结果：

• 当在map中只添加id和title时

//测试set
    @Test
    public void testSet(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("id","74205db5524c4e92942241e8b6443032");
        //map.put("author","大佬");
        map.put("title","大佬");

        mapper.updateBlog(map);
        session.commit();
    }

运行结果：

可以发现：title 后面的逗号去掉了

where标签和set 标签的底层都是trim标签

6. choose

好比java中的switch

接口BlogMapper的代码：

//查询博客 但是只要有一个条件满足即可
List<Blog> queryBlogByChoose(Map map);

接口的配置文件BlogMapper.xml 代码：

<select id="queryBlogByChoose" 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>

测试类BlogMapperTest 的代码：

• 当在map中什么都不添加时

//测试choose
    @Test
    public void testChoose(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();

        mapper.queryBlogByChoose(map);

        session.commit();
    }

运行结果：

• 当在map中只添加title时

//测试choose
    @Test
    public void testChoose(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","Hello Spring");

        mapper.queryBlogByChoose(map);
        session.commit();
    }

运行结果：

• 当在map中只添加author时

//测试choose
    @Test
    public void testChoose(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        //map.put("title","Hello Spring");
        map.put("author","大佬");

        mapper.queryBlogByChoose(map);
        session.commit();
    }

运行结果：

• 当在map中添加title和author时

//测试choose
    @Test
    public void testChoose(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","Hello Spring");
        map.put("author","大佬");

        mapper.queryBlogByChoose(map);
        session.commit();
    }

运行结果：

可以看出：虽然传入了两个参数，但只显示了一个

7. foreach

相当于子查询 where in(1,2,3)

collection 表示输入的参数 map
item 表示遍历出来的每一项
open 表示打开
close 表示关闭
separator 表示分割符
通过item 遍历出来的标签可以在foreach中使用

接口BlogMapper的代码：

List<Blog> queryBlogByForeach(Map map);

接口的配置文件BlogMapper.xml 代码：

<!--子查询 where in(1,2,3)-->
    <select id="queryBlogByForeach" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>

    </select>

测试类BlogMapperTest 代码：

//测试foreach
    @Test
    public void testForeach(){
        SqlSession session = MyBatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String,List> map = new HashMap();
        List<String> ids = new ArrayList<String>();
        ids.add("bac368d88b4a4d8eb6bd1b539a3338a0");
        ids.add("321c98e9beea45d092c39b2cfe0fc370");
        ids.add("cfbc34f0a1dd40fdba91e99dfe4c365a");

        map.put("ids",ids);

        mapper.queryBlogByForeach(map);
        session.commit();
    }

运行结果：

可以看出：就相当于SQL中的 select * from mybatis.blog where and (id=xxx or id=xxx or id=xxx)