动态 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 <= #{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 >= 100
</otherwise>
</choose>
</select>
但是我们可能会遇到过,如果没有查询条件即上述choose中没有 1=1这个条件,那么当查询条件为null时就会出现 一下的一种情况
select * from bookstore where and BookPrice >= 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 >= 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 <= #{arg1}
</select>
方式二—
用param来传递参数,注意从1开始,1表示第一个
<select id="findPartBook" resultType="book">
select * from bookstore where bookkind=#{param1} and bookprice <= #{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 <= #{arg1}-->
<!-- select * from bookstore where bookkind=#{param1} and bookprice <= #{param2}-->
select * from bookstore where bookkind=#{bookkind} and bookprice <= #{bookprice}
</select>
方式三----
通过集合的形式传入,
接口方法–
List<Book> findPartBook(Map<String,Object> book1);
<select id="findPartBook" resultType="book" parameterType="map">
<!-- select * from bookstore where bookkind=#{arg0} and bookprice <= #{arg1}-->
<!-- select * from bookstore where bookkind=#{param1} and bookprice <= #{param2}-->
select * from bookstore where bookkind=#{bookkind} and bookprice <= #{bookprice}
</select>
用集合时情况也可以起别名;
<select id="findPartBook" resultType="book" parameterType="map">
<!-- select * from bookstore where bookkind=#{arg0} and bookprice <= #{arg1}-->
<!-- select * from bookstore where bookkind=#{param1} and bookprice <= #{param2}-->
select * from bookstore where bookkind=#{B.bookkind} and bookprice <= #{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 <= #{arg1}-->
<!-- select * from bookstore where bookkind=#{param1} and bookprice <= #{param2}-->
<!-- select * from bookstore where bookkind=#{B.bookkind} and bookprice <= #{C.bookprice}-->
select * from bookstore where bookkind=#{B.bookkind} and bookprice <= #{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 <= #{arg1.bookprice}
select * from bookstore where bookkind=#{param1.bookkind} and bookprice <= #{param2.bookprice}