目录
动态sql
如果之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本
需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类
现在要学习的元素种类比原来的一半还要少。
主要的标签:
if
choose (when, otherwise)
trim (where, set)
上面几种都是常用的
foreach
搭建环境
1、创建一个基础工程
2、导包
3、编写配置文件
4、编写实体类
package com.q.pojo;
import lombok.Data;
import java.util.Date;
//实体类
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date create_time;
private long views;
}
4、编写实体类对应的Mapper接口和Mapper.xml文件
package com.q.dao;
import com.q.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
/**
* 添加的书
*
* @param blog 博客
* @return int
*///插入数据
int addBook(Blog blog);
}
mapper.xml
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定dao接口,会自动的找到sql语句-->
<mapper namespace="com.q.dao.BlogMapper">
<!--添加书-->
<insert id="addBook" parameterType="blog">
insert into mybatis.blog(id, title, author, create_time, views)
values (#{id}, #{title}, #{author}, #{create_time}, #{views});
</insert>
测试:
@Test
public void test() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IdUtlis.getId());
blog.setTitle("Mybatis");
blog.setAuthor("leslie");
blog.setCreate_time(new Date());
blog.setViews(9999);
mapper.addBook(blog);
blog.setId(IdUtlis.getId());
blog.setTitle("Java");
mapper.addBook(blog);
blog.setId(IdUtlis.getId());
blog.setTitle("Spring");
mapper.addBook(blog);
blog.setId(IdUtlis.getId());
blog.setTitle("微服务");
mapper.addBook(blog);
sqlSession.close();
}
添加成功,环境搭建成功
动态sql语句之if语句
where 1=1是为了两个if都不满足时输出blog中所有的数据,主要目的是不让where后面直接接and
1、写mapper接口
//查询博客
List<Blog> QueryBlog(Map map);
2、写mapper.xml
<select id="QueryBlog" parameterType="map" resultType="com.q.pojo.Blog">
select *
from mybatis.blog
where 1 = 1
<if test="title !=null">
and title=#{title}
</if>
<if test="author !=null">
and author=#{author}
</if>
</select>
3、测试
@Test
public void testSelect() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "Java");
map.put("author", "leslie");
List<Blog> blogs = mapper.QueryBlog(map);
System.out.println(blogs);
}
4、输出结果
==> Preparing: select * from mybatis.blog where 1=1 and title=? and author=?
==> Parameters: Java(String), leslie(String)
<== Columns: id, title, author, create_time, views
<== Row: 2, Java, leslie, 2021-03-14 12:02:52.0, 9999
<== Row: 15db7ba07f2146d9aeb997dd1c02677b, Java, leslie, 2021-06-21 17:44:59.0, 9999
<== Total: 2
[Blog(id=2, title=Java, author=leslie, create_time=null, views=9999), Blog(id=15db7ba07f2146d9aeb997dd1c02677b, title=Java, author=leslie, create_time=null, views=9999)]
choose (when, otherwise)
动态sql拼接
<select id="QueryBlogChoose" resultType="com.q.pojo.Blog" parameterType="map">
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>
上面的代码可以封装成sql片段
<!--sql片段,相当于封装-->
<sql id="choose-when">
<choose>
<when test="title!=null">
title=#{title}
</when>
<when test="author!=null">
and author=#{author}
</when>
<otherwise>
and views=#{views}
</otherwise>
</choose>
</sql>
trim (where, set)
这个是修改数据需要用的
根据不同条件生成不同的sql语句
所谓的动态sql,本质还是sql语句,只是可以在sql层面,去执行逻辑代码
1、接口
int upDateTest(Map map);
2、mapper.xml
<update id="upDateTest">
update mybatis.blog
<set>
<if test="title !=null">
title =#{title},
</if>
</set>
where id =#{id}
</update>
3、测试
@Test
public void test3() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "Java123434324");
map.put("author", "leslie2");
map.put("id", "1");
mapper.upDateTest(map);
sqlSession.close();
}
SQL片段
有的时候,我们使用的sql语句有些相同部分,可以提取出来,就是sql片段
(相当于封装,提高代码复用性)
使用sql标签抽取公共部分
<sql id="choose-when">
<choose>
<when test="title!=null">
title=#{title}
</when>
<when test="author!=null">
and author=#{author}
</when>
<otherwise>
and views=#{views}
</otherwise>
</choose>
</sql>
在需要使用的地方使用include标签应用
<select id="QueryBlogChoose" resultType="com.q.pojo.Blog" parameterType="map">
select * from mybatis.blog
<where>
<include refid="choose-when"/>
</where>
</select>
注意:
- 最好基于单表来定义sql
- 不要在标签里面存在where标签
foreach
根据条件循环遍历
1、接口
// 查询id 1 2 3的博客
List<Blog> selectBlog(Map map);
2、mapper.xml
<!--选择博客-->
<select id="selectBlog" resultType="com.q.pojo.Blog" parameterType="map">
select *
from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id =#{id}
</foreach>
</where>
</select>
3、测试
@Test
public void test4() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids", ids);
List<Blog> blogs = mapper.selectBlog(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
4、结果
==> Preparing: select * from mybatis.blog WHERE ( id =? or id =? or id =? )
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<== Columns: id, title, author, create_time, views
<== Row: 1, Java123434324, leslie, 2021-03-14 12:02:52.0, 9999
<== Row: 2, Java, leslie, 2021-03-14 12:02:52.0, 9999
<== Row: 3, Spring, leslie, 2021-03-14 12:02:52.0, 9999
<== Total: 3
Blog(id=1, title=Java123434324, author=leslie, create_time=null, views=9999)
Blog(id=2, title=Java, author=leslie, create_time=null, views=9999)
Blog(id=3, title=Spring, author=leslie, create_time=null, views=9999)
bind标签
1、Mapper接口
List<Blog> selectBlogByName(String string);
2、对应的sql语句实现
<select id="selectBlogByName" resultType="com.q.pojo.Blog">
<bind name="blogName" value="'%'+title+'%'"/>
select * from mybatis.blog where title like #{blogName};
</select>
3、测试
3.//测试<bind>标签
@Test
public void test011() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
List<Blog> selectBlogByName = mapper.selectBlogByName("a");
for (Blog blog : selectBlogByName) {
System.out.println(blog);
}
sqlSession.close();
}
4、输出结果
==> Preparing: select * from mybatis.blog where title like ?;
==> Parameters: %a%(String)
<== Columns: id, title, author, create_time, views
<== Row: 1, Java123434324, leslie, 2021-03-14 12:02:52.0, 9999
<== Row: 2, Java, leslie, 2021-03-14 12:02:52.0, 9999
<== Row: 79274adc884542a7b9461b0fb821b2c9, Mybatis, leslie, 2021-06-21 17:44:59.0, 9999
<== Row: 15db7ba07f2146d9aeb997dd1c02677b, Java, leslie, 2021-06-21 17:44:59.0, 9999
<== Total: 4
Blog(id=1, title=Java123434324, author=leslie, create_time=null, views=9999)
Blog(id=2, title=Java, author=leslie, create_time=null, views=9999)
Blog(id=79274adc884542a7b9461b0fb821b2c9, title=Mybatis, author=leslie, create_time=null, views=9999)
Blog(id=15db7ba07f2146d9aeb997dd1c02677b, title=Java, author=leslie, create_time=null, views=9999)
使用动态sql实现批量插入数据
1、接口
//批量添加
int batchAdd(List<Student1> list);
2、Mapper.xml
<!-- 批量添加-->
<insert id="batchAdd" parameterType="Student1">
insert into student(
id, name, tid, age
)values
<foreach collection="list" index="index" item="Student1" separator=",">
(#{Student1.id},#{Student1.name},#{Student1.tid},#{Student1.age})
</foreach>
</insert>
由于插入操作参数类型是一个对象,但是返回主键是一个值,就会造成参数类型不匹配。
修改后利用对象调用其属性。最终传入的参数类型还是一个对象。
3、测试
public void testBatchAdd() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper1 mapper = sqlSession.getMapper(StudentMapper1.class);
ArrayList<Student1> list = new ArrayList<>();
for (int i = 6; i < 9; i++) {
Student1 student1 = new Student1();
student1.setId(i);
student1.setName("ljq" + i);
student1.setTid(1);
student1.setAge(23);
list.add(student1);
}
System.out.println("批量增加:");
int i = mapper.batchAdd(list);
for (Student1 student1 : list) {
System.out.println(student1.getId());
}
sqlSession.close();
}
4、输出结果
DEBUG [main] - ==> Preparing: insert into student( id, name, tid, age )values (?,?,?,?) , (?,?,?,?) , (?,?,?,?)
DEBUG [main] - ==> Parameters: 1(Integer), ljq1(String), 1(Integer), 23(Integer), 2(Integer), ljq2(String), 1(Integer), 23(Integer), 3(Integer), ljq3(String), 1(Integer), 23(Integer)
DEBUG [main] - <== Updates: 3
1
2
3
遇到问题:就是循环的数据很容易出错,不能找到循环的字段,要通过item来配置当前循环的元素
批量查询
1、接口
// 批量查找数据
List<Student1> bulkQuery(List<Integer> list);
2、mapper.xml
<!-- 批量查询-->
<select id="bulkQuery" resultType="com.q.pojo.Student1" parameterType="list">
select *
from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
3、测试
public void test1() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper1 mapper = sqlSession.getMapper(StudentMapper1.class);
ArrayList<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
System.out.println("批量查询:");
List<Student1> student1s = mapper.bulkQuery(list);
for (Student1 student1 : student1s) {
System.out.println(student1);
}
sqlSession.close();
}
4、结果
DEBUG [main] - ==> Preparing: select * from student where id in ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
DEBUG [main] - <== Total: 3
Student1(id=1, name=ljq1, tid=1, age=23)
Student1(id=2, name=ljq2, tid=1, age=23)
Student1(id=3, name=ljq3, tid=1, age=23)
批量删除
1、接口
//批量删除
int batchDeletion(List<Integer> list);
2、mapper.xml
<!-- 批量删除-->
<delete id="batchDeletion" parameterType="list">
delete from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
3、测试
public void testBatchDeletion() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper1 mapper = sqlSession.getMapper(StudentMapper1.class);
System.out.println("批量删除数据");
ArrayList<Integer> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
list.add(i);
}
int i = mapper.batchDeletion(list);
System.out.println(i);
sqlSession.close();
}
4、结果
DEBUG [main] - ==> Preparing: delete from student where id in ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 0(Integer), 1(Integer), 2(Integer)
DEBUG [main] - <== Updates: 2
2
先写出sql语句,再去修改我们的动态sql语句
完整的接口1:
package com.q.dao;
import com.q.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
/**
* 添加的书
*
* @param blog 博客
* @return int
*///插入数据
int addBook(Blog blog);
/**
* 查询博客
*
* @param map 地图
* @return {@link List<Blog>}
*///查询博客
List<Blog> QueryBlog(Map<String, Object> map);
/**
* 查询博客选择
*
* @param map 地图
* @return {@link List<Blog>}
*/
List<Blog> QueryBlogChoose(Map<Object, Object> map);
/**
* 日期测试
*
* @param map 地图
* @return int
*/
int upDateTest(Map<Object, Object> map);
/**
* 选择博客
*
* @param map 地图
* @return {@link List<Blog>}
*/// 查询id 1 2 3的博客
List<Blog> selectBlog(Map<Object, Object> map);
/**
* 选择博客的名字
*
* @param string 字符串
* @return {@link List<Blog>}
*/
List<Blog> selectBlogByName(String string);
}
完整的动态sql语句1:
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定dao接口,会自动的找到sql语句-->
<mapper namespace="com.q.dao.BlogMapper">
<!--添加书-->
<insert id="addBook" parameterType="blog">
insert into mybatis.blog(id, title, author, create_time, views)
values (#{id}, #{title}, #{author}, #{create_time}, #{views});
</insert>
<!-- 这个方法是update-->
<update id="upDateTest">
update mybatis.blog
<set>
<if test="title !=null">
title =#{title},
</if>
</set>
where id =#{id}
</update>
<!--查询博客-->
<select id="QueryBlog" parameterType="map" resultType="com.q.pojo.Blog">
select *
from mybatis.blog where 1=1
<if test="title !=null">
and title=#{title}
</if>
<if test="author !=null">
and author=#{author}
</if>
</select>
<!--sql片段,相当于封装-->
<sql id="choose-when">
<choose>
<when test="title!=null">
title=#{title}
</when>
<when test="author!=null">
and author=#{author}
</when>
<otherwise>
and views=#{views}
</otherwise>
</choose>
</sql>
<!-- 查询博客选择-->
<select id="QueryBlogChoose" resultType="com.q.pojo.Blog" parameterType="map">
select * from mybatis.blog
<where>
<include refid="choose-when"/>
</where>
</select>
<!--选择博客-->
<select id="selectBlog" resultType="com.q.pojo.Blog" parameterType="map">
select *
from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id =#{id}
</foreach>
</where>
</select>
<!-- 选择博客的名字-->
<select id="selectBlogByName" resultType="com.q.pojo.Blog">
<bind name="blogName" value="'%'+title+'%'"/>
select * from mybatis.blog where title like #{blogName};
</select>
</mapper>
完整接口2:
package com.q.dao;
import com.q.pojo.Student1;
import java.util.List;
public interface StudentMapper1 {
// 如果年龄小于20,则查询姓名tom的学生,如果年龄大于20小于30,则查询jim的学生,大于30,查询jack的信息
Student1 getStudent(int age);
// 查询的接口,传递一个对象过去,通过set的方法注入值
List<Student1> getInformation(Student1 student1);
// 更新数据
int updateInformation(Student1 student1);
// 插入数据
int insertData(Student1 student1);
// 批量查找数据
List<Student1> bulkQuery(List<Integer> list);
//批量添加
int batchAdd(List<Student1> list);
//批量删除
int batchDeletion(List<Integer> list);
}
完整的动态sql语句2:
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.q.dao.StudentMapper1">
<!-- 插入数据-->
<insert id="insertData" parameterType="Student1">
insert into student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id !=null and id !=''">
id,
</if>
<if test="name !=null and name !=''">
name,
</if>
<if test="tid !=null and tid !=''">
tid,
</if>
<if test="age !=null and age !=''">
age,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id !=null and id !=''">
#{id},
</if>
<if test="name !=null and name !=''">
#{name},
</if>
<if test="tid !=null and tid !=''">
#{tid},
</if>
<if test="age !=null and age !=''">
#{age},
</if>
</trim>
</insert>
<!-- 批量添加-->
<insert id="batchAdd" parameterType="Student1">
insert into student(
id, name, tid, age
)values
<foreach collection="list" index="index" item="Student1" separator=",">
(#{Student1.id},#{Student1.name},#{Student1.tid},#{Student1.age})
</foreach>
</insert>
<!-- 更新数据-->
<update id="updateInformation" parameterType="Student1">
update student
<set>
<if test="name !=null and name !=''">
name =#{name},
</if>
<if test="age !=null and age !=''">
age=#{age},
</if>
<if test="tid !=null and tid !=''">
tid=#{tid},
</if>
</set>
where id=#{id};
</update>
<!-- 批量删除-->
<delete id="batchDeletion" parameterType="list">
delete from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<select id="getStudent" resultType="com.q.pojo.Student1">
select * from mybatis.student
<where>
<if test="age != null">
age=#{age}
</if>
</where>
</select>
<select id="getInformation" resultType="com.q.pojo.Student1" parameterType="Student1">
select *
from student
<where>
<bind name="name1" value="'%'+name+'%'"/>
<if test="name !=null and name !=''">
and name like #{name1}
</if>
<if test="age !=null and age !=''">
and age=#{age}
</if>
<if test="id !=null and id !=''">
and id=#{id}
</if>
</where>
</select>
<!-- 批量查询-->
<select id="bulkQuery" resultType="com.q.pojo.Student1" parameterType="list">
select *
from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
</mapper>