mybatis动态SQL与参数传递[笔记]

动态 SQL 是 MyBatis 的强大特性之一

在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3
替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。

主要的动态sql标签-----

:判断语句,用于单条件分支判断。 (、):用于多条件分支判断。 、、:辅助元素,用于处理一些SQL拼装、特殊字符问题。 :循环语句,常用于in语句等列举条件中。 :从OGNL表达式中创建一个变量,并将其绑定到上下文,常用于模糊查询的SQL中。

准备案例,
三步走----->>.
创建数据库,建立数据表,如果你之前就有了的话,可以直接拿来用;

建数据库,建立表,插入数据


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for bookstore
-- ----------------------------
DROP TABLE IF EXISTS `bookstore`;
CREATE TABLE `bookstore`  (
  `BookId` int(0) NOT NULL AUTO_INCREMENT,
  `BookName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `BookPublish` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `BookPrice` double(10, 2) NULL DEFAULT NULL,
  `BookKind` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `BookCount` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`BookId`) USING BTREE,
  INDEX `name_index`(`BookName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1112 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bookstore
-- ----------------------------
INSERT INTO `bookstore` VALUES (1001, 'java入门', '清华出版社', 56.80, '计算机', 90);
INSERT INTO `bookstore` VALUES (1002, 'python深入', '机械工业出版社', 78.60, '计算机', 130);
INSERT INTO `bookstore` VALUES (1003, '前端html', '清华出版社', 88.80, '计算机', 100);
INSERT INTO `bookstore` VALUES (1004, '废都', '张江出版社', 25.70, '文学名著', 100);
INSERT INTO `bookstore` VALUES (1005, '官场现形记', '青岛出版社', 23.80, '文学名著', 100);
INSERT INTO `bookstore` VALUES (1006, '红楼梦', '青岛出版社', 36.80, '文学名著', 100);
INSERT INTO `bookstore` VALUES (1007, '母猪的产后护理', '机械工业出版社', 27.80, '实用技术', 100);
INSERT INTO `bookstore` VALUES (1008, '车辆维修大全', '机械工业出版社', 26.80, '实用技术', 100);
INSERT INTO `bookstore` VALUES (1009, '如何讨取富婆欢心富婆', '开心出版社', 125.00, '生活技能', 100);
INSERT INTO `bookstore` VALUES (1010, 'JVM调优', '北京科技出版社', 68.90, '计算机', 120);
INSERT INTO `bookstore` VALUES (1011, 'GO', '南京邮电出版社', 78.90, '计算机', 120);
INSERT INTO `bookstore` VALUES (1012, 'Android入门', '电子科技出版社', 88.90, '计算机', 120);
INSERT INTO `bookstore` VALUES (1014, '果树种植学', '西北农林出版社', 128.90, '农业', 120);
INSERT INTO `bookstore` VALUES (1015, '笑话', '上海出版社', 48.90, '生活', 120);
INSERT INTO `bookstore` VALUES (1111, 'MQ消息队列', '北大出版社', 98.90, '计算机', 120);
INSERT INTO `bookstore` VALUES (1222, 'java大数据', '清华出版社', 124.70, '计算机', 160);
INSERT INTO `bookstore` VALUES (1233, 'java集合', '电子科技出版社', 120.80, '计算机', 80);

SET FOREIGN_KEY_CHECKS = 1;

通过逆向工程生成所需要的数据---->>>

在这里插入图片描述
参考逆行工程----->>
接下来根据生成的数据开始编写sql

if

在这里插入图片描述先说一下查询目标----->>找到小于指定数量的书,另一个不确定的条件是书的名字,有可能有,也有可能没有;
当传入 模糊查询条件时,会返回相应的结果集,否则会返回 所有小于指定数量的的结果;

设想如果换成java代码来实现判断的话,有一些繁琐;
运行后----->>>
在这里插入图片描述使用动态 SQL 中的if最常见情景是根据条件包含 where 子句的一部中;

来查看一下生成的sql语句长什么样子
在这里插入图片描述
可以看到,当传入的参数符合要求时才会执行if里面的sql条件,否则就不会;

if 代码片段

  <select id="selectBook" resultType="com.gavin.pojo.Book">
    select <include refid="Base_Column_List"></include>
    from bookstore where BookCount &lt;= #{bookcount}
    <if test="bookname !=null">
      and BookName like concat('%',#{bookname},'%')
    </if>
  </select>

有时候我们并不像所有的条件都是用,而是只要满足这里条件中的一个就可以了,或者是传入什么参数就用什么参数查找或者修改(就比如上条查询,如果传入的是出版社,那么就按出版社进行模糊查询若是种类,则按种类查询);
这个时候就要用到了choose来实现类似功能了;

choose (when, otherwise)

在这里插入图片描述

运行结果在这里插入图片描述
这就很类似于java中的switch和前端jstl中的choose

万一我们一个参数不传入的话?
我们还可以给他设置一个默认值------>>

在这里插入图片描述choose代码片段----->>

 <select id="selectBookBySth" resultType="book">
select <include refid="Base_Column_List"></include>

from bookstore where 1=1

<choose>
    <when test="bookname != null">
        and BookName like concat('%',#{bookname},'%')
    </when>
    <when test="bookpublish  != null">
        and BookPublish like concat('%',#{bookpublish},'%')
    </when>
    <when test="bookkind  != null" >
        and BookKind like concat('%',#{bookkind},'%')
    </when>
    <otherwise>
       and  BookPrice &gt;= 100
    </otherwise>
</choose>
    </select>

但是我们可能会遇到过,如果没有查询条件即上述choose中没有 1=1这个条件,那么当查询条件为null时就会出现 一下的一种情况

select * from bookstore where   and  BookPrice &gt;= 100

查询语句出错了,所以我在上面的choose案例中添加了 1=1这个恒成立的条件;

假设不加的话,那么当条件为null时该怎么处理呢?
mybatis提供了一个方法,就是在 查询条件时外部嵌套where标签

就像下面这个样子
在这里插入图片描述

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。

如果where元素还不能满足我们的而要求,那么还可通过自定义trim 元素来定制where标签实现我们想要的;

如上案例,和 where 元素等价的自定义 trim 元素

trim (where, set)

where
由于我们是对where进行自定义的,所以prefix处写where
在这里插入图片描述

trim—where代码片段

 <select id="selectBookBySth" resultType="book">
select <include refid="Base_Column_List"></include>
from bookstore
  <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <choose>
        <when test="bookname != null">
          BookName like concat('%',#{bookname},'%')
        </when>
        <when test="bookpublish  != null">
          and BookPublish like concat('%',#{bookpublish},'%')
        </when>
        <when test="bookkind  != null" >
          and BookKind like concat('%',#{bookkind},'%')
        </when>
        <otherwise>
          and  BookPrice &gt;= 100
        </otherwise>
      </choose>
  </trim>
    </select>

<trim元素的作用是去除一些特殊的字符串,它的prefix属性代表的是语句的前缀(这里使用where来连接后面的SOL片段),而prefixOverrides属性代表的是需要去除的那些特殊字符串(这里定义了要去除SQL中的and)

set标签用于动态更新

在这里插入图片描述

比如我们并不想要更新某条记录的全部数据,那么我们就可通过set来动态的更新
例如想要更新BookId为1111的数据,更改内容为书价格;

在这里插入图片描述
但是我们有时候会要更改的数据不是固定的,这时候用set来解决



  <update id="updateByPrimaryKeySelective" parameterType="com.gavin.pojo.Book">
    update bookstore
    <set>
      <if test="bookname != null">
        BookName = #{bookname,jdbcType=VARCHAR},
      </if>
      <if test="bookpublish != null">
        BookPublish = #{bookpublish,jdbcType=VARCHAR},
      </if>
      <if test="bookprice != null">
        BookPrice = #{bookprice,jdbcType=DOUBLE},
      </if>
      <if test="bookkind != null">
        BookKind = #{bookkind,jdbcType=VARCHAR},
      </if>
      <if test="bookcount != null">
        BookCount = #{bookcount,jdbcType=INTEGER},
      </if>
    </set>
    where BookId = #{bookid,jdbcType=INTEGER}
  </update>

与 set 元素等价的自定义 trim 元素:

  <update id="updateByPrimaryKeySelective" parameterType="com.gavin.pojo.Book">
    update bookstore

    <trim prefix="SET" suffixOverrides=",">

      <if test="bookname != null">
        BookName = #{bookname,jdbcType=VARCHAR},
      </if>
      <if test="bookpublish != null">
        BookPublish = #{bookpublish,jdbcType=VARCHAR},
      </if>
      <if test="bookprice != null">
        BookPrice = #{bookprice,jdbcType=DOUBLE},
      </if>
      <if test="bookkind != null">
        BookKind = #{bookkind,jdbcType=VARCHAR},
      </if>
      <if test="bookcount != null">
        BookCount = #{bookcount,jdbcType=INTEGER},
      </if>
  </trim>

  where BookId = #{bookid,jdbcType=INTEGER}
  </update>

set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候);

例如我们想要在计算机和文学名著中中查找书价格小于100的,

先看foreach用法
在这里插入图片描述可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。

foreach代码片段

<select id="findBook" resultType="book">
  select <include refid="Base_Column_List"></include>

  from bookstore where BookKind in
  <foreach collection="list" item="bookkind" index="index" open="(" separator="," close=")">
    #{bookkind}
  </foreach>

</select>

在使用时,最关键、最容易出错的就是collection属性,该属性是必须指定的,而且在不同情况下该属性的值是不一样的,主要有以下3种情况。

如果传入的是单参数且参数类型是一个数组或者List的时候,collection属性值分别为array、list(或collection)。

如果传入的参数有多个,就需要把它们封装成一个Map,当然单参数也可以封装成Map集合,这时collection属性值就为Map的键。

如果传入的参数是POJO包装类,collection属性值就为该包装类中需要进行遍历的数组或集合的属性名。

在带注解的接口类中使用动态sql

在这里插入图片描述
在这里插入图片描述

当查询语句比较复杂时像动态sql,看起来注解比较糟糕,需要处理单引号和双引号的问题,
而且代码也不美观;

如果像是 这样的sql语句 select * from bookstore ,这样看起来还行;

还有一点要注意的时,如果是传入的数组,那么在映射文件中就需要注意了,参数类型哪里写list,在遍历的时候collection处写array

在这里插入图片描述

刚刚想起,map是很重要的一个集合,不能落下,于是乎,将上面的查询改成map形式的

首先,在mapper层定义好查询方法---->>
查询某一种类中的书籍
在这里插入图片描述

再然后是mapper映射文件
在这里插入图片描述

测试代码
在这里插入图片描述

当多条件的复杂查询时可以用map来实现;

说一下bind标签
bind 元素允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文。

例如这样:-----

在这里插入图片描述
在这里插入图片描述

mybatis对多数据库的支持

如果配置了 databaseIdProvider,你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select * from bookstore
    </if>
    <if test="_databaseId == 'db2'">
       select * from bookstore
    </if>
  </selectKey>
  insert into bookstore values (#{bookname}, #{bookprice})
</insert>

小结----->>>

我们在实际操做数据库的时候经常会遇到多个参数的时候,

一个参数时很好解决,多个参数时,可以用集合list/map的形式去实现;

map key放字段, value 放条件

mybatis参数传递

基本数据
在这里插入图片描述

方式一----

用arg来传递参数,注意从0开始,0表示第一个

    <select id="findPartBook" resultType="book">
        select * from bookstore  where bookkind=#{arg0} and bookprice &lt;= #{arg1}
    </select>

方式二—

用param来传递参数,注意从1开始,1表示第一个

    <select id="findPartBook" resultType="book">
    select * from bookstore  where bookkind=#{param1} and bookprice &lt;= #{param2}
    </select>

还可以为参数设置别名
接口中方法名

 List<Book> findPartBook(@Param("bookkind") String BookKind ,@Param("bookprice") Double BookPrice);
    <select id="findPartBook" resultType="book">
<!--        select * from bookstore  where bookkind=#{arg0} and bookprice &lt;= #{arg1}-->
<!--        select * from bookstore  where bookkind=#{param1} and bookprice &lt;= #{param2}-->
        select * from bookstore  where bookkind=#{bookkind} and bookprice &lt;= #{bookprice}
    </select>

方式三----
通过集合的形式传入,
接口方法–

  List<Book> findPartBook(Map<String,Object> book1);
    <select id="findPartBook" resultType="book"  parameterType="map">
<!--        select * from bookstore  where bookkind=#{arg0} and bookprice &lt;= #{arg1}-->
<!--        select * from bookstore  where bookkind=#{param1} and bookprice &lt;= #{param2}-->
        select * from bookstore  where bookkind=#{bookkind} and bookprice &lt;= #{bookprice}
    </select>

用集合时情况也可以起别名;

 <select id="findPartBook" resultType="book"  parameterType="map">
<!--        select * from bookstore  where bookkind=#{arg0} and bookprice &lt;= #{arg1}-->
<!--        select * from bookstore  where bookkind=#{param1} and bookprice &lt;= #{param2}-->
        select * from bookstore  where bookkind=#{B.bookkind} and bookprice &lt;= #{B.bookprice}
    </select>

小结—>>

参数为基本类型------->>>
1,参数为基本参数类型,用arg 和param都可以;

2,如果给参数起了别名.那么arg的形式传入参数会失效;

参数为引用类型---->>
1参数为单个的引用类型,那么在传入参数时,要写对应的参数属性名;

2,参数为多个引用参数时也是;
例如---->>>

   List<Book> findPartBook(@Param("B") Map<String,Object> book1,@Param("C") Map<String,Double>book2);

    <select id="findPartBook" resultType="book"  parameterType="map">
<!--        select * from bookstore  where bookkind=#{arg0} and bookprice &lt;= #{arg1}-->
<!--        select * from bookstore  where bookkind=#{param1} and bookprice &lt;= #{param2}-->
<!--        select * from bookstore  where bookkind=#{B.bookkind} and bookprice &lt;= #{C.bookprice}-->
        select * from bookstore  where bookkind=#{B.bookkind} and bookprice &lt;= #{C.bookprice}
    </select>

Map<String,Object> map= new HashMap<>();
Map<String,Double> map1= new HashMap<>();
map.put("bookkind","计算机" );
map1.put("bookprice",100.0);
        List<Book> list = mapper.findPartBook(map,map1);
        for (Book book : list) {
            System.out.println(book);
        }
    }

在这里插入图片描述

在不起别名的情况下---->> arg 和param都可以;

  List<Book> findPartBook( Map<String,Object> book1, Map<String,Double>book2);

select * from bookstore  where bookkind=#{arg0.bookkind} and bookprice &lt;= #{arg1.bookprice}
select * from bookstore  where bookkind=#{param1.bookkind} and bookprice &lt;= #{param2.bookprice}
  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

CodeMartain

祝:生活蒸蒸日上!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值