一步一步搞定——动态SQL

最新推荐文章于 2023-08-04 10:05:29 发布

I'm sure ok!

最新推荐文章于 2023-08-04 10:05:29 发布

阅读量641

点赞数 1

分类专栏： MyBatis

本文链接：https://blog.csdn.net/lirui1212/article/details/108582292

版权

MyBatis 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

前言

大家都知道在mybatis中编写接口的时候传入参数的方式大概是四种方式
不知道的小伙伴可以点下面链接查看
多个参数传递

建议大家使用传对象的方式,具体为什么？
就是对象里面包含了用户传入参数的所有属性

 List<Books> queryById(Books books);

如果使用传统方式和@Param方式虽然你可以写多个参数但是你并不知道用户传入的几个参数
如果使用map方式,也不友好因为你的接口xml的#{}要与map.put的key相对应，不方便
通过上面对传入参数的了解接口一方已经不存在什么问题了，那么接口对应的xml如何编写
相信没有了解动态SQL之前，同学们应该是编写多个SQL,根据书名查写一条，根据书id查写一条，这样如果是单条字段少还可以做到
如果是一张表是十几二十个字段呢,这些字段组合起来的情况又会是多少种查询的SQL语句哇！好像很多勒,不知道同学们的概率怎么样？

什么是动态SQL？

简单来说:动态sql就是程序在执行的过程中动态生成sql
详细来说不管你有表中有多少字段,你传入的是什么我就执行具体的sql
比如说传入的是name我就执行select * from table where name=?你传入的是id我就执行select * from table where id=?
听起来好像不错，那就看下面的例子嘛

准备数据

create table `books` (
	`bookid` int (11),
	`bookname` varchar (30),
	`bookcount` int (20),
	`booktype` varchar (30),
	`bookprice` float 
); 
insert into `books` (`bookid`, `bookname`, `bookcount`, `booktype`, `bookprice`) values('1','红楼梦','22','古典类型','45');
insert into `books` (`bookid`, `bookname`, `bookcount`, `booktype`, `bookprice`) values('2','西游记','45','神魔类型','24');
insert into `books` (`bookid`, `bookname`, `bookcount`, `booktype`, `bookprice`) values('3','三国演义','60','历史类型','30');
insert into `books` (`bookid`, `bookname`, `bookcount`, `booktype`, `bookprice`) values('4','水浒传','10','历史类型','34');
insert into `books` (`bookid`, `bookname`, `bookcount`, `booktype`, `bookprice`) values('5','鬼吹灯','36','冒险类型','26');

动态SQL演化过程

sql标签

需求:通过查询所有书的信息引出sql标签

接口：

    //查询所有书籍信息
    List<Books> queryAll();

接口xml

    <select id="queryAll" resultType="Books">
        select
         bookid, bookname, bookcount,booktype,bookprice
        from
         books
    </select>

测试:

Preparing: select bookid, bookname, bookcount,booktype,bookprice from books 
==> Parameters: 
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 1, 红楼梦, 22, 古典类型, 45
<==        Row: 2, 西游记, 45, 神魔类型, 24
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==      Total: 5

通过id查询书的信息

接口

 Books queryById(Books books);

接口xml

   <select id="queryById" resultType="Books">
        select
         bookid, bookname, bookcount,booktype,bookprice
        from
         books
        where
         bookid=#{bookid}
    </select>

测试结果:

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books where bookid=? 
==> Parameters: 2(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 2, 西游记, 45, 神魔类型, 24
<==      Total: 1

通过以上两个查询我们发现下面代码是重复使用,mybatis中我们可以使用sql标签把相同的部分抽取出来，然后查询语句引用。

bookid, bookname, bookcount,booktype,bookprice

//抽取出来的相同部分 id与include标签refid保持一致
<sql id="baseColumn">
bookid, bookname, bookcount,booktype,bookprice
</sql>
//include标签就是放在查询语句中的标签  然后引用相同的部分
<include refid="baseColumn"></include>

所以以上代码可以修改为

<sql id="baseColumn">
        bookid, bookname, bookcount,booktype,bookprice
</sql>
<select id="queryAll" resultType="Books">
        select
         <include refid="baseColumn"></include>
        from
         books
</select>

if标签和 where标签

需求:现在有个通过书名的id和书名或者书的类型查询的功能
现在要求如果输入书的id按照书的id查询，如果输入书的名字就按照书名查询，如果输入书的类型就按照书类型查询,如果没有输入应该是查询全部
通常我们是编写多个sql

	 //根据id查询
	 <select id="queryById" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
         books
        where
         bookid=#{bookid}
    </select>
    //根据书名查询
 	<select id="queryById" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
         books
        where
         bookname=#{bookname}
    </select>
    //根据书类型查询
    <select id="queryById" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
         books
        where
        booktype=#{booktype}
    </select>

通过上面的代码已经认识到sql是不灵活的并且代码量大
首先通过传统和if标签相结合的方式去实现根据不同的输入参数,查询出不同的结果
if标签：相当于我们java中if判断语句条件为真，就执行,如果条件都为假,就应该不执行

这里面的where 1=1就避免了如果不传入参数也执行查询所有书籍信息
这种方式存在sql注入的问题
select bookid, bookname, bookcount,booktype,bookprice from books where 1=1

接口

List<Books> queryById2(Books books);

接口xml

 <select id="queryById2" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        where 1=1
        <if test="bookid!=null">
            and bookid=#{bookid}
        </if>
        <if test="bookname!=null">
            and bookname=#{bookname}
        </if>
        <if test="bookcount!=null">
            and bookcount=#{bookcount}
        </if>
        <if test="booktype!=null">
            and booktype=#{booktype}
        </if>
        <if test="bookprice!=null">
            and bookprice=#{bookprice}
        </if>
    </select>

测试代码:

 @Test
    public void queryById2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookprice(30f);
        List<Books> booksList = mapper.queryById2(books);
        System.out.println(JSON.toJSONString(booksList,true));
    }

测试结果:

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books where 1=1 and bookprice=? 
==> Parameters: 30.0(Float)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==      Total: 1

重点：select bookid, bookname, bookcount,booktype,bookprice from books where 1=1 and bookprice=?
我们可以通过比较发现执行的sql是根据你传入的参数而定的这就是if标签的作用

其次通过if+where标签的方式根据不同的输入参数,查询出不同的结果
where标签在下面的作用是：自动根据判断的条件选择是否生成 where 条件
如果条件都为假不生成where添加语句
where标签还可以自动去除第一个and或者or

 <select id="queryById3" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="bookid!=null">
                and bookid=#{bookid}
            </if>
            <if test="bookname!=null">
                and bookname=#{bookname}
            </if>
            <if test="bookcount!=null">
                and bookcount=#{bookcount}
            </if>
            <if test="booktype!=null">
                and booktype=#{booktype}
            </if>
            <if test="bookprice!=null">
                and bookprice=#{bookprice}
            </if>
        </where>
    </select>

测试结果

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books WHERE bookname=? 
==> Parameters: 水浒传(String)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==      Total: 1

同样的sql还是根据传入参数属性去执行的

set标签

需求：更新某个id中的书名或者其他列名的信息

//更新数据
    void update(Books books);

 //不使用if标签判断
    <update id="update">
        UPDATE
         `books`
        SET
          bookname=#{bookname},bookcount=#{bookcount},booktype=#{booktype},bookprice=#{bookprice}
        WHERE
          bookid=#{bookid}
    </update>

测试代码:

 @Test
    public void update() throws IllegalAccessException {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("口才与训练");
        books.setBookid(1);
        mapper.update(books);
        sqlSession.commit();
    }

==>  Preparing: UPDATE `books` SET bookname=?,bookcount=?,booktype=?,bookprice=? WHERE bookid=? 
==> Parameters: 口才与训练(String), null, null, null, 1(Integer)
<==    Updates: 1

这里我们发现如果只修改书名数据库中的其他列的值被通通赋值为null
在这里插入图片描述
上面的结果显然不是我们想要看到的,我们想要的结果是：修改的书名生效其他列的值应该不变
为了防止这种把null值传入数据库其实在测试的时候判断一下即可

@Test
    public void update() throws IllegalAccessException {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("努力一点");
        books.setBookid(1);
        if (books.getBookcount()!= null && books.getBookprice() != null && books.getBooktype() != null) {
            mapper.update(books);
            sqlSession.commit();
        } else {
            throw new IllegalAccessException("数量、价格、类型为null");
        }
    }

测试结果:应该是抛出异常不提交给数据库

java.lang.IllegalAccessException: 数量、价格、类型为null

使用if标签

接口

    void update2(Books books);

接口xml

  <update id="update2">
        UPDATE
         `books`
        SET
        <if test="bookname!=null">
            bookname=#{bookname},
        </if>
        <if test="bookcount!=null">
            bookcount=#{bookcount},
        </if>
        <if test="booktype!=null">
            booktype=#{booktype},
        </if>
        <if test="bookprice!=null">
            bookprice=#{bookprice},
        </if>
        WHERE
         bookid=#{bookid}
    </update>

测试代码

 @Test
    public void update2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("三国演义");
        books.setBookid(1);
        mapper.update2(books);
        sqlSession.commit();

    }

测试结果:

//根据书名查询 很显然sql语法不对 多了个逗号
UPDATE `books`  SET  bookname=?,    WHERE  bookid=?

问题：修改书名生效，其他列的值应该不变
使用if标签始终都有逗号存在

使用set标签解决上面的两个问题

接口

 void update3(Books books);

接口xml

<update id="update3">
        UPDATE
        `books`
        <set>
            <if test="bookname!=null">
                 bookname=#{bookname},
            </if>
            <if test="bookcount!=null">
                 bookcount=#{bookcount},
            </if>
            <if test="booktype!=null">
                 booktype=#{booktype},
            </if>
            <if test="bookprice!=null">
                 bookprice=#{bookprice},
            </if>
        </set>
        WHERE
        bookid=#{bookid}
    </update>

测试代码

@Test
    public void update3(){
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        //这里有个问题就是如果不传入其他参数 结果应该是更新的数据为null 但是实际上SQL报错
        books.setBookname("大学英语四级考试题型");
        books.setBookid(2);
        mapper.update3(books);
        sqlSession.commit();
    }

测试结果

==>  Preparing: UPDATE `books` SET bookname=? WHERE bookid=? 
==>  Parameters: 大学英语四级考试题型(String), 2(Integer)
<==    Updates: 1

总结：
set标签可以动态生成set条件语句
set标签可以帮我们去除逗号

trim标签

上述中我们使用了where标签和set标签
但是强大的是trim标签，它可以替换if和where标签

trim标签的参数值:
prefix 添加前缀
prefixOverrides 去除前缀
suffixOverrides 添加后缀
suffix 去除后缀

代替where标签
接口

//查询所有书的信息
List<Books> selectByBooks(Books books);

接口xml

<select id="selectByBooks" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <trim prefix="where" prefixOverrides="and|or" >
            <if test="bookid!=null">
                or bookid=#{bookid}
            </if>
            <if test="bookname!=null">
                or bookname=#{bookname}
            </if>
            <if test="bookcount!=null">
                and bookcount=#{bookcount}
            </if>
            <if test="booktype!=null">
                and booktype=#{booktype}
            </if>
            <if test="bookprice!=null">
                and bookprice=#{bookprice}
            </if>
        </trim>
    </select>

测试代码

  @Test
    public void selectByBooks() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        //要么查询出来书名是红楼梦的信息 要么查询出来id 1 的信息，
        Books books = new Books();
        books.setBookname("红楼梦");
        books.setBookid(1);
        List<Books> booksList = mapper.selectByBooks(books);
        System.out.println(JSON.toJSONString(booksList,true));
    }

测试结果

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@15bfd87]
==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books where bookid=? or bookname=? 
==> Parameters: 1(Integer), 红楼梦(String)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 1, 口才与训练, null, null, null
<==      Total: 1

代替set标签
接口

//更新数据
 void updateByTrim(Books books);

接口xml

<update id="updateByTrim">
        UPDATE
        `books`
        <trim prefix="set" suffixOverrides=",">

            <if test="bookname!=null">
                bookname=#{bookname},
            </if>
            <if test="bookcount!=null">
                bookcount=#{bookcount},
            </if>
            <if test="booktype!=null">
                booktype=#{booktype},
            </if>
            <if test="bookprice!=null">
                bookprice=#{bookprice},
            </if>
        </trim>
        WHERE
        bookid=#{bookid}
    </update>

测试代码

    @Test
    public void updateByTrim() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("快乐");
        books.setBookid(2);
        mapper.updateByTrim(books);
        sqlSession.commit();
    }

测试结果

==>  Preparing: UPDATE `books` set bookname=? WHERE bookid=? 
==> Parameters: 快乐(String), 2(Integer)
<==    Updates: 1

trim标签插入数据的运用
接口

//插入数据
  void insert2(Books books);

接口xml

  <insert id="insert2">
       INSERT
       INTO
        `books`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bookname!=null">
               bookname,
            </if>
            <if test="bookcount!=null">
                bookcount,
            </if>
            <if test="booktype!=null">
                booktype,
            </if>
            <if test="bookprice!=null">
                bookprice,
            </if>
        </trim>
       VALUES
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bookname!=null">
                #{bookname},
            </if>
            <if test="bookcount!=null">
               #{bookcount},
            </if>
            <if test="booktype!=null">
               #{booktype},
            </if>
            <if test="bookprice!=null">
               #{bookprice},
            </if>
        </trim>
    </insert>

测试代码

 @Test
    public void insert2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("别怕C语言");
        books.setBookcount(20);
        books.setBookprice(36f);
        books.setBooktype("教材");
        mapper.insert2(books);
        sqlSession.commit();
    }

测试结果

==>  Preparing: INSERT INTO `books` ( bookname, bookcount, booktype, bookprice ) VALUES ( ?, ?, ?, ? ) 
==> Parameters: 别怕C语言(String), 20(Integer), 教材(String), 36.0(Float)
<==    Updates: 1

注意：insert和update情况不一样
insert理所当然插入什么就有什么其他值默认为null(前提是数据库没有not null约束)
update更新数据你更新什么就改变什么

choose标签

   //choose标签相当于switch case 语句 如果传入的是某一个参数就执行
   //如果都没有传入就执行默认的
    <choose>
    <when test=""></when>
    <when test=""></when>
     ......
    <otherwise></otherwise>
    </choose>

接口

    //动态SQL 使用choose
    List<Books> byChoose(Books books);

接口xml

<select id="byChoose" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <trim prefix="where" prefixOverrides="and">
            <choose>
                <when test="bookid!=null">
                    and bookid=#{bookid}
                </when>
                <when test="bookname!=null">
                    and bookname=#{bookname}
                </when>
                <when test="booktype!=null">
                    and booktype=#{booktype}
                </when>
                <when test="bookid!=null">
                    and bookid=#{bookid}
                </when>
                <when test="bookprice!=null">
                    and bookprice=#{bookprice}
                </when>
                <otherwise>
                    and bookcount=#{bookcount}
                </otherwise>
            </choose>
        </trim>
    </select>

测试代码

@Test
    public void byChoose() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Books books = new Books();
        books.setBookname("大学英语");
        List<Books> booksList = mapper.byChoose(books);
        System.out.println(JSON.toJSONString(booksList,true));
    }

测试结果

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@15bfd87]
==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books where bookname=? 
==> Parameters: 大学英语(String)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 2, 大学英语, 45, 神魔类型, 24
<==      Total: 1

以上就是动态sql中的标签,如果那块有问题欢迎及时交流。

foreach标签

需求：查询书的id分别为1,9，10的信息

//通过以下两种方式可以实现
SELECT * FROM books WHERE bookid=1 OR bookid=9 OR bookid=10;
SELECT * FROM books WHERE  bookid IN(1,9,10);

那么在mybatis中如何实现SELECT * FROM books WHERE bookid IN(1,9,10)
这就需要用到foreach标签了，foreach标签见名知意就是循环的意思,

 foreach的属性取值有三种：
        1.array数组
        2.list集合
        3.map集合

首先了解array数组
接口

//根据id查询多本书的信息
 List<Books> selectByForeach(Integer[] bookid);

接口xml

<!--  
		 通过trim标签与foreach标签方式实现
		 item 的属性值是循环的每一项值 必须和#{}保持一致 名字任意但是为了见名知意我们这里写bookid
		 使用trim标签时候我们要去除后缀的最后一个逗号
 -->
 <select id="selectByForeach" resultType="Books">
       SELECT
        *
       FROM
        books
       WHERE
        bookid IN(
       <trim suffixOverrides=",">
           <foreach collection="array" item="bookid">
               #{bookid},
           </foreach>
       </trim>
        )
   </select>

测试代码

  @Test
    public void selectForeach() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Integer[] bookid={2,3,10};
        List<Books> books = mapper.selectByForeach(bookid);
        System.out.println(JSON.toJSONString(books,true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN( ?, ?, ? ) 
==> Parameters: 2(Integer), 3(Integer), 10(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 2, 高数, 45, 神魔类型, 24
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 3

通过trim标签和foreach标签实现了根据bookid查询多个书的信息
注意:使用trim标签我们需要注意必须使用后缀去除最后一个逗号
对于foreach标签它里面的属性可以完全规避逗号的问题

下面只使用foreach标签
接口

	//根据id查询多本书的信息
    List<Books> selectByForeach2(Integer[] bookid);

接口xml

<!--
		separator:分隔符可以实现两个值之间自动生成逗号 避免最后一位多逗号的问题
-->
<select id="selectByForeach2" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN(
            <foreach collection="array" item="bookid" separator=",">
                #{bookid}
            </foreach>
        )
    </select>

测试代码

    @Test
    public void selectForeach2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Integer[] bookid = {4, 5, 9};
        List<Books> books = mapper.selectByForeach2(bookid);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN( ? , ? , ? ) 
==> Parameters: 4(Integer), 5(Integer), 9(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==        Row: 9, 别怕C语言, 20, 教材, 36
<==      Total: 3

通过foreach标签的属性值还可以实现in后面的小括号
接口

	//根据id查询多本书的信息
    List<Books> selectByForeach3(Integer[] bookid);

接口xml

<!--
    open:  以什么开头
    close: 以什么结尾
-->
 <select id="selectByForeach3" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN
        <foreach collection="array" item="bookid" separator="," open="(" close=")">
            #{bookid}
        </foreach>
    </select>

测试代码

 @Test
    public void selectForeach3() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Integer[] bookid = {5, 9};
        List<Books> books = mapper.selectByForeach3(bookid);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN ( ? , ? ) 
==> Parameters: 5(Integer), 9(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==        Row: 9, 别怕C语言, 20, 教材, 36
<==      Total: 2

其次了解list集合
对于collection的属性值是list集合
泛型有几种情况：
list集合的泛型是Integer、String
list集合的泛型是对象
接口

//根据id查询多本书的信息
 List<Books> selectByForeach4(List<Integer> list);

接口xml

<!--
	collection的属性值是list item的意思还是循环中的每一项值
-->
<select id="selectByForeach4" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN
        <foreach collection="list" item="bookid" separator="," open="(" close=")">
            #{bookid}
        </foreach>
    </select>

测试代码

  @Test
    public void selectForeach4() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        List<Integer> list = new ArrayList<>();
        list.add(2);
        list.add(10);
        List<Books> books = mapper.selectByForeach4(list);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN ( ? , ? ) 
==> Parameters: 2(Integer), 10(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 2, 高数, 45, 神魔类型, 24
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 2

注意:对于collection参数值是list集合加不加泛型 integer或者string都没有问题

接口

 List<Books> selectByForeach5(List<Books> list);

接口xml

  <!--
        如果参数类型是集合且泛型是对象 #{books.bookid}里面的值必须是对象.属性值
        其中item是集合中的对象
    -->
    <select id="selectByForeach5" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN
        <foreach collection="list" item="books" separator="," open="(" close=")">
            #{books.bookid}
        </foreach>
    </select>

测试代码

 @Test
    public void selectForeach5() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        List<Books> list = new ArrayList<>();
        //这里添加多个对象的时候使用代码块相当于每次创建了不同的对象
        {
            Books b = new Books();
            b.setBookid(3);
            list.add(b);
        }
        {
            Books b = new Books();
            b.setBookid(10);
            list.add(b);
        }
        List<Books> books = mapper.selectByForeach5(list);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN ( ? , ? ) 
==> Parameters: 3(Integer), 10(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 2

最后了解map集合

特别注意:map必须配合@Param使用否则会报以下错误
The expression 'map' evaluated to a null value.

接口

    List<Books>  selectByForeach7(@Param("map") Map<String, Object> map);

接口xml

<!--
        collection参数是map
        item对应的是map中的value
        item的值与#{}里面的值保持一致
    -->
    <select id="selectByForeach7" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN
        <foreach collection="map" item="bookid" separator="," open="(" close=")">
            #{bookid}
        </foreach>
    </select>

测试代码

   @Test
    public void selectForeach7() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Map<String,Object> map = new HashMap<>();
        //map的key任意
        map.put("m1", 2);
        map.put("m2",10);

        List<Books> books = mapper.selectByForeach7(map);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN ( ? , ? ) 
==> Parameters: 2(Integer), 10(Integer)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 2, 高数, 45, 神魔类型, 24
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 2

foreach标签里面还有个index的属性通过以下实例测试
接口

    //测试collection参数index
    List<Books> selectByForeach8(@Param("map") Map<String, Object> map);

接口xml

  <select id="selectByForeach8" resultType="Books">
        SELECT
        *
        FROM
        books
        WHERE
        bookid IN
        <foreach collection="map" item="bookid" index="key" separator="," open="(" close=")">
            #{bookid},#{key}
        </foreach>
    </select>

测试代码

   @Test
    public void selectForeach8() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Map<String, Object> map = new HashMap<>();
        //map的key任意
        map.put("m1", 3);
        map.put("m2", 4);
        map.put("m3", 5);
        List<Books> books = mapper.selectByForeach8(map);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: SELECT * FROM books WHERE bookid IN ( ?,? , ?,? , ?,? ) 
==> Parameters: 3(Integer), m1(String), 4(Integer), m2(String), 5(Integer), m3(String)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==      Total: 3

SELECT * FROM books WHERE bookid IN ( ?,? , ?,? , ?,? )
Parameters: 3(Integer), m1(String), 4(Integer), m2(String), 5(Integer), m3(String)

结论:通过参数和sql语句来看index属性的值和map.put()方式中的key是保持一致的

特殊情况

在前言中我讲到传入参数建议使用对象，下面说一说如果传入的是Sting字符串呢
会有什么问题?

出现的错误演示
错误:
There is no getter for property named 'bookname' in 'class java.lang.String'
接口

 List<Books> solveString(String bookname);

接口xml


    <!-- if标签在对String 引用类型进行判断是回去找String类里面的bookname的get方法
         找不到就报错
         There is no getter for property named 'bookname' in 'class java.lang.String'
     -->
    <select id="solveString" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="bookname!=null and bookname!=''">
                and bookname=#{bookname}
            </if>
        </where>
    </select>

测试代码


    @Test
    public void solveString() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        List<Books> books = mapper.solveString("");
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

There is no getter for property named 'bookname' in 'class java.lang.String'

对于传入参数是String字符串,接口的xml使用了if标签就会上面的错误。
意思是在String类中找不到有关的bookname属性的getter方法
其实报这个错误是因为不符合OGNL表达式语言，具体是为什么不符合，还不清楚只知道解决方法，有兴趣可以百度一下查询

解决如上问题:

接口

 //使用value值
    List<Books> solveString2(String bookname);

接口xml

<!--
	这里在if标签中对参数值作了非空和不等于null判断
	使用value值去替换 可以看做在使用${}对于传入参数是Integer类型 所表达的意思一样
	
-->
<select id="solveString2" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="value!=null and value!=''">
                and bookname=#{bookname}
            </if>
        </where>
    </select>

测试代码

 @Test
    public void solveString2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        //使用value值作为if判断条件 对于非空判断不起作用  传入"" 还是会执行条件语句
        List<Books> books = mapper.solveString2("");
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books WHERE bookname=? 
==> Parameters: (String)
<==      Total: 0
[]

在接口xml中if标签作了判断但是测试的时候传入空值执行了where后面的条件语句
这个问题还是不符合OGNL表达式

解决方式二:
使用_parameter替换
接口

  //使用_parameter
    List<Books> solveString3(String bookname);

接口xml

 <!--使用_parameter 参数进行if判断 不会出现非空不起作用 -->
    <select id="solveString3" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="_parameter!=null and _parameter!=''">
                and bookname=#{bookname}
            </if>
        </where>
    </select>

测试代码

    @Test
    public void solveString3() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        List<Books> books = mapper.solveString3("");
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books 
==> Parameters: 
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 1, 努力一点, null, null, null
<==        Row: 2, 高数, 45, 神魔类型, 24
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==        Row: 9, 别怕C语言, 20, 教材, 36
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 7

解决方式三:
使用注解@Param
接口

  //使用@Param("bookname")
    List<Books> solveString4(@Param("bookname") String bookname);

接口xml

<!--这里test的参数还是写bookname-->
 <select id="solveString4" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="bookname!=null and bookname!=''">
                and bookname=#{bookname}
            </if>
        </where>
    </select>

测试代码

  @Test
    public void solveString4() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        List<Books> books = mapper.solveString4("水浒传");
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books WHERE bookname=? 
==> Parameters: 水浒传(String)
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==      Total: 1

解决方式四:
使用map方式

接口

//使用map方式
    List<Books> solveString5(Map map);

接口xml


    <!--使用map方式 map的方法put的key 要对应test属性的值-->
    <select id="solveString5" resultType="Books">
        select
        <include refid="baseColumn"></include>
        from
        books
        <where>
            <if test="bookname!=null and bookname!=''">
                and bookname=#{bookname}
            </if>
        </where>
    </select>

测试代码

 @Test
    public void solveString5() {
        SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
        BooksMapper mapper = sqlSession.getMapper(BooksMapper.class);
        Map map = new HashMap();
        map.put("bookname","");
        List<Books> books = mapper.solveString5(map);
        System.out.println(JSON.toJSONString(books, true));
    }

测试结果

==>  Preparing: select bookid, bookname, bookcount,booktype,bookprice from books 
==> Parameters: 
<==    Columns: bookid, bookname, bookcount, booktype, bookprice
<==        Row: 1, 努力一点, null, null, null
<==        Row: 2, 高数, 45, 神魔类型, 24
<==        Row: 3, 三国演义, 60, 历史类型, 30
<==        Row: 4, 水浒传, 10, 历史类型, 34
<==        Row: 5, 鬼吹灯, 36, 冒险类型, 26
<==        Row: 9, 别怕C语言, 20, 教材, 36
<==        Row: 10, 别怕C语言, 20, 教材, 36
<==      Total: 7

总结：对于传入参数是String字符串使用@Param、map、_parameter都可以解决
不管传入参数是哪种引用类型建议使用对象，不存在以上的问题

至此动态sql的所有标签就分享到这!

I'm sure ok!

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
一步一步搞定——动态SQL

文章目录前言什么是动态SQL？动态SQL演化过程sql标签if标签和 where标签set标签trim标签choose标签前言大家都知道在mybatis中编写接口的时候传入参数的方式大概是四种方式不知道的小伙伴可以点下面链接查看多个参数传递建议大家使用传对象的方式,具体为什么？就是对象里面包含了用户传入参数的所有属性 List<Books> queryById(Books books);如果使用传统方式和@Param方式虽然你可以写多个参数但是你并不知道用户传入的几个参
复制链接

扫一扫