Mybatis之动态sql
Mybatis之动态sql
数据表
<!--创建表 -->
CREATE TABLE `books` (`bid` INT AUTO_INCREMENT NOT NULL COMMENT '编号',
`title` VARCHAR (50) NOT NULL COMMENT '书名',
`author` VARCHAR (30) NOT NULL COMMENT '作者',
`language` VARCHAR (20) NOT NULL COMMENT '语言',
`dateOfPublish` DATE NOT NULL COMMENT '出版日期',
`publisher` VARCHAR (50) NOT NULL COMMENT '出版社',
`type` VARCHAR (30) NOT NULL COMMENT '类型',
PRIMARY KEY (`bid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
<!--插入数据 -->
INSERT INTO `books` (`title`,`author`,`language`,`dateOfPublish`,`publisher`,`type`) VALUES
("人间失格","太宰治","日文",DATE"1948-4-8","浙江文艺出版社","小说"),
("罗生门","芥川龙之介","中文",DATE"1997-10-1","湖南文艺出版社","小说"),
("茶花女","小仲马","中文",DATE"1997-3-1","外国文学出版社","小说"),
("霍乱时期的爱情","加西亚·马尔克斯","中文",DATE"1985-10-1","外国文学出版社","小说");
pojo类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Books {
private int bid;
private String title;
private String author;
private String language;
private Date dateOfPublish;
private String publisher;
private String type;
}
1：if
标签：满足if条件，则拼接if标签中的语句
<!--BooksDao接口的方法-->
public ArrayList<Books> getBooks(HashMap<String,String> map);
<!--BooksDaoMapper.xml-->
<select id="getBooks" parameterType="map" resultType="book">
select * from books
where 1 = 1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="language != null">
and language = #{language}
</if>
<if test="dateOfPath != null">
and dateOfPath = #{dateOfPath}
</if>
<if test="publisher != null">
and publisher = #{publisher}
</if>
<if test="type != null">
and type = #{type}
</if>
</select>
测试：
@Test
public void query() {
SqlSession sqlSession = DbUtils.getSqlSession();
BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
HashMap<String, String> hashMap = new HashMap<String, String>();
hashMap.put("language","中文");
hashMap.put("author","芥川龙之介");
ArrayList<Books> list = booksDao.getBooks(hashMap);
for (Books book : list) {
System.out.println(book);
}
结果：
[com.cugb.dao.BooksDao.getBooks]-==> Preparing: select * from books where 1 = 1 and author = ? and language = ?
[com.cugb.dao.BooksDao.getBooks]-==> Parameters: 芥川龙之介(String), 中文(String)
[com.cugb.dao.BooksDao.getBooks]-<== Total: 1
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=dang小说)
当if标签中的test属性为true时,sql拼接标签内的语句，否则不拼接
2：where、 choose、 when、otherwise
这是一对组合的用法，相当于java中的switch case
<!--BooksDao接口的方法-->
public ArrayList<Books> getBooks2(HashMap<String,String> map);
<select id="getBooks2" parameterType="map" resultType="book">
select * from books
<where>
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<when test="language != null">
and language = #{language}
</when>
<when test="dateOfPublish != null">
and dateOfPublish = #{dateOfPublish}
</when>
<when test="publisher != null">
and publisher = #{publisher}
</when>
<when test="type != null">
and type = #{type}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
当when的test属性为true时，跳出choose，若都为false则执行otherwise，where则是去除拼接语句第一个and
测试
@Test
public void query() {
SqlSession sqlSession = DbUtils.getSqlSession();
BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
HashMap<String, String> hashMap = new HashMap<String, String>();
hashMap.put("language","中文");
hashMap.put("author","芥川龙之介");
ArrayList<Books> list = booksDao.getBooks2(hashMap);
for (Books book : list) {
System.out.println(book);
}
结果：
[com.cugb.dao.BooksDao.getBooks2]-==> Preparing: select * from books WHERE author = ?
[com.cugb.dao.BooksDao.getBooks2]-==> Parameters: 芥川龙之介(String)
[com.cugb.dao.BooksDao.getBooks2]-<== Total: 1
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
这时候你发现，sql语句跟之前的边了，它只有一个条件，是因为when中的test只要有一个是true，就会跳出true。and也被where标签给去除了。
** 3：set**
if标签中的test属性为true时，拼接标签中的sql语句，并去除最后的，
<update id="upateBook" parameterType="map" >
update books
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
<if test="language != null">
language = #{language},
</if>
<if test="dateOfPublish">
dateOfPublish = #{dateOfPublish},
</if>
<if test="type">
type = #{type},
</if>
</set>
where
bid = 3
</update>
测试：
@Test
public void update(){
SqlSession sqlSession = DbUtils.getSqlSession();
BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
HashMap<String, String> hashMap = new HashMap<String, String>();
//第一次查询
ArrayList<Books> list = booksDao.getBooks2(hashMap);
for (Books book : list) {
System.out.println(book);
}
hashMap.put("language","英文");
booksDao.upateBook(hashMap);
//清楚map的数据，并进行二次查询
hashMap.clear();
list = booksDao.getBooks2(hashMap);
for (Books book : list) {
System.out.println(book);
}
}
结果:
[com.cugb.dao.BooksDao.getBooks2]-==> Preparing: select * from books
[com.cugb.dao.BooksDao.getBooks2]-==> Parameters:
[com.cugb.dao.BooksDao.getBooks2]-<== Total: 4
Books(bid=1, title=人间失格, author=太宰治, language=日文, dateOfPublish=Thu Apr 08 00:00:00 CST 1948, publisher=浙江文艺出版社, type=小说)
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
Books(bid=3, title=茶花女, author=小仲马, language=中文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
Books(bid=4, title=霍乱时期的爱情, author=加西亚·马尔克斯, language=中文, dateOfPublish=Tue Oct 01 00:00:00 CST 1985, publisher=外国文学出版社, type=小说)
[com.cugb.dao.BooksDao.upateBook]-==> Preparing: update books SET language = ? where bid = 3;
[com.cugb.dao.BooksDao.upateBook]-==> Parameters: 英文(String)
[com.cugb.dao.BooksDao.upateBook]-<== Updates: 1
[com.cugb.dao.BooksDao.getBooks2]-==> Preparing: select * from books
[com.cugb.dao.BooksDao.getBooks2]-==> Parameters:
[com.cugb.dao.BooksDao.getBooks2]-<== Total: 4
Books(bid=1, title=人间失格, author=太宰治, language=日文, dateOfPublish=Thu Apr 08 00:00:00 CST 1948, publisher=浙江文艺出版社, type=小说)
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
Books(bid=3, title=茶花女, author=小仲马, language=英文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
Books(bid=4, title=霍乱时期的爱情, author=加西亚·马尔克斯, language=中文, dateOfPublish=Tue Oct 01 00:00:00 CST 1985, publisher=外国文学出版社, type=小说)
两次查询结果，发现《茶花女》的language变成了英文,set语句起了作用。
4：trim
trim是用来where 和set 去除拼接语句指定位置的字符,trim中有4个标签
prefix:给sql拼接语句添加前缀
suffix:给sql拼接语句添加后缀
prefixOverrides :去除sql拼接语句前的指定的关键字
suffixOverrides :去除sql拼接语句后的指定的关键字
用trim代替where、when、choose
/*
查询书籍
*/
public ArrayList<Books> getBooks3(HashMap<String,String> map);
<select id="getBooks3" parameterType="map" resultType="book">
select * from books
<trim prefix="where" prefixOverrides="and|or">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="language != null">
and language = #{language}
</if>
<if test="dateOfPath != null">
and dateOfPath = #{dateOfPath}
</if>
<if test="publisher != null">
and publisher = #{publisher}
</if>
<if test="type != null">
and type = #{type}
</if>
</trim>
</select>
测试
@Test
public void query() {
SqlSession sqlSession = DbUtils.getSqlSession();
BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
HashMap<String, String> hashMap = new HashMap<String, String>();
hashMap.put("language","中文");
hashMap.put("author","芥川龙之介");
ArrayList<Books> list = booksDao.getBooks3(hashMap);
for (Books book : list) {
System.out.println(book);
}
}
测试结果
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC Connection
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 1800659519.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6b53e23f]
[com.cugb.dao.BooksDao.getBooks3]-==> Preparing: select * from books where author = ? and language = ?
[com.cugb.dao.BooksDao.getBooks3]-==> Parameters: 芥川龙之介(String), 中文(String)
[com.cugb.dao.BooksDao.getBooks3]-<== Total: 1
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
执行的sql语句
reparing: select * from books where author = ? and language = ?
[com.cugb.dao.BooksDao.getBooks3]-==> Parameters: 芥川龙之介(String), 中文(String)
[com.cugb.dao.BooksDao.getBooks3]-<== Total: 1
从结果来看，trim实现了where、when、choose组合的效果
用trim代替set
/*
修改书籍信息
*/
public int upateBook2(HashMap<String,String> map);
<update id="upateBook2" parameterType="map" >
update books
<trim prefix="set" suffixOverrides=",">
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
<if test="language != null">
language = #{language},
</if>
<if test="dateOfPublish">
dateOfPublish = #{dateOfPublish},
</if>
<if test="type">
type = #{type},
</if>
</trim>
where
bid = 3;
</update>
测试
@Test
public void update(){
SqlSession sqlSession = DbUtils.getSqlSession();
BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
HashMap<String, String> hashMap = new HashMap<String, String>();
//第一次查询
ArrayList<Books> list = booksDao.getBooks2(hashMap);
for (Books book : list) {
System.out.println(book);
}
hashMap.put("language","英文");
//修改bid=3的语言
booksDao.upateBook2(hashMap);
//清除map的数据，并进行二次查询
hashMap.clear();
list = booksDao.getBooks2(hashMap);
for (Books book : list) {
System.out.println(book);
}
}
结果
[com.cugb.dao.BooksDao.getBooks2]-==> Preparing: select * from books
[com.cugb.dao.BooksDao.getBooks2]-==> Parameters:
[com.cugb.dao.BooksDao.getBooks2]-<== Total: 4
Books(bid=1, title=人间失格, author=太宰治, language=日文, dateOfPublish=Thu Apr 08 00:00:00 CST 1948, publisher=浙江文艺出版社, type=小说)
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
Books(bid=3, title=茶花女, author=小仲马, language=中文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
Books(bid=4, title=霍乱时期的爱情, author=加西亚·马尔克斯, language=中文, dateOfPublish=Tue Oct 01 00:00:00 CST 1985, publisher=外国文学出版社, type=小说)
[com.cugb.dao.BooksDao.upateBook2]-==> Preparing: update books set language = ? where bid = 3;
[com.cugb.dao.BooksDao.upateBook2]-==> Parameters: 英文(String)
[com.cugb.dao.BooksDao.upateBook2]-<== Updates: 1
[com.cugb.dao.BooksDao.getBooks2]-==> Preparing: select * from books
[com.cugb.dao.BooksDao.getBooks2]-==> Parameters:
[com.cugb.dao.BooksDao.getBooks2]-<== Total: 4
Books(bid=1, title=人间失格, author=太宰治, language=日文, dateOfPublish=Thu Apr 08 00:00:00 CST 1948, publisher=浙江文艺出版社, type=小说)
Books(bid=2, title=罗生门, author=芥川龙之介, language=中文, dateOfPublish=Wed Oct 01 00:00:00 CST 1997, publisher=湖南文艺出版社, type=小说)
Books(bid=3, title=茶花女, author=小仲马, language=英文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
Books(bid=4, title=霍乱时期的爱情, author=加西亚·马尔克斯, language=中文, dateOfPublish=Tue Oct 01 00:00:00 CST 1985, publisher=外国文学出版社, type=小说)
第一次查询
Books(bid=3, title=茶花女, author=小仲马, language=中文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
第二次查询
Books(bid=3, title=茶花女, author=小仲马, language=英文, dateOfPublish=Sat Mar 01 00:00:00 CST 1997, publisher=外国文学出版社, type=小说)
结果确实改变了language.
所以trim可以实现where when choose 和set 的功能。
