hello,大家好,咱们又见面了,如约而至,相信观看前面的两篇之后对MyBatis有了一个比较清除的认识,并且大家应该能够基本使用的MyBatis框架解决一些实际问题!但是如果大家经过认真思考之后,一定会发现很多问题: 例如JDBC中的一些查询条件可以根据业务需求而改变,使得一个查询语句完成多种查询,而在MyBatis中,似乎一个查询语句只能完成一个功能;例如
多个条件的模糊查询
,根据不同查询条件查询对应的i结果
等等! 咱们之前所有的查询语句全部都是单表查询,而且细心的小伙伴可能已经发现了,咱们所建的实体类全部都是和表中字段名字一样的,难道只能这样嘛?MyBatis能够做多表连接查询嘛?下面咱们就带着这些疑问往下继续学习吧!
建立数据表book
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(7) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`createdate` datetime NULL DEFAULT NULL,
`status` int(7) NULL DEFAULT NULL,
`clazzid` int(7) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '鲁宾孙漂流记', '丹尼尔.迪福', '2019-02-24 14:38:58', 0, 1);
INSERT INTO `book` VALUES (2, '红楼梦', '曹雪芹', '2019-02-12 14:39:27', 0, 2);
INSERT INTO `book` VALUES (3, '水浒传', '施耐庵', '2019-02-15 14:39:44', 0, 2);
INSERT INTO `book` VALUES (4, '三国演义', '罗贯中', '2019-02-24 14:40:13', 0, 2);
INSERT INTO `book` VALUES (5, '西游记', '吴承恩', '2019-01-30 14:40:39', 0, 2);
INSERT INTO `book` VALUES (6, '聊斋志异', '蒲松龄', '2019-01-29 14:40:56', 0, 2);
INSERT INTO `book` VALUES (7, '简爱', '夏洛蒂.勃朗特', '2019-02-14 14:41:11', 0, 1);
INSERT INTO `book` VALUES (8, 'MyBatis入门篇章', '皇甫嗷嗷叫', '2019-02-24 14:54:59', 1, 3);
INSERT INTO `book` VALUES (9, 'MyBatis中级篇章', '皇甫嗷嗷叫', '2019-02-24 14:55:34', 1, 3);
INSERT INTO `book` VALUES (10, 'MyBatis高级篇章', '皇甫嗷嗷叫', '2019-02-24 14:56:01', 1, 3);
SET FOREIGN_KEY_CHECKS = 1;
需求:根据文章姓名和作者姓名查询具体数据,当用户只输入作者姓名时,只按照作者姓名查询;当用户只输入文章名称时只按照文章姓名查找;当作者数据文章标题和作者名称时,按照两个文章进行查询!
在看到这个需求时,如果你不了解MyBatis的动态sql的话,恐怕第一想到的就是,后台进行一系列的判断,判断执行那个方法和对应的具体sql吧!但是MyBatis为我们提供了强大的动态SQL后,一切都变的简单起来。
MyBatis提供了很强大的动态sql功能,他可以在sql语句内部进行判断,从而来完成一个动态sql的操作,具体的操作如下!
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.senior.dao.BookDao">
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book where
<if test="author!=null">
author = #{author}
</if>
<if test="name!=null">
and name = #{name}
</if>
</select>
</mapper>
具体代码 1.创建实体类
package com.senior.entity;
import java.util.Date;
/**
* @author 皇甫
*/
public class Book {
private Integer id;
private String name;
private String author;
private Date createDate;
private Integer status;
private Integer clazzId;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", author='" + author + '\'' +
", createDate=" + createDate +
", status=" + status +
", clazzId=" + clazzId +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Integer getClazzId() {
return clazzId;
}
public void setClazzId(Integer clazzId) {
this.clazzId = clazzId;
}
public Book() {
}
public Book(Integer id, String name, String author, Date createDate, Integer status, Integer clazzId) {
this.id = id;
this.name = name;
this.author = author;
this.createDate = createDate;
this.status = status;
this.clazzId = clazzId;
}
}
2.创建Dao接口
package com.senior.dao;
import com.senior.entity.Book;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author 皇甫
*/
public interface BookDao {
/**
* 需求功能
* 根据作者或者书籍名字查询
* @param author
* @param name
* @return
*/
public List<Book> queryBookByAuthorOrName(@Param("author") String author, @Param("name") String name);
}
Mapper文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.senior.dao.BookDao">
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book where
<if test="author!=null">
author = #{author}
</if>
<if test="name!=null">
and name = #{name}
</if>
</select>
</mapper>
注册进MyBatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="properties/db.properties">
<!--开启配置文件配置默认值-->
<property name="org.apache.ibatis.parsing.PropertyParser.enable-default-value" value="true"/>
</properties>
<!--配置默认使用那个环境变量 以id作为标志-->
<environments default="test">
<!--配置连接环境-->
<environment id="test">
<!--事务管理器使用JDBC-->
<transactionManager type="JDBC"/>
<!--数据源 连接池使用该POOLED-->
<dataSource type="POOLED">
<property name="driver" value="${mysql.driver:com.mysql.jdbc.Driver}"/>
<property name="url" value="${mysql.url:jdbc:mysql://127.0.0.1:3306/testmybatis}"/>
<property name="username" value="${mysql.username:root}"/>
<property name="password" value="${mysql.password:hr}"/>
</dataSource>
</environment>
</environments>
<!--将Mapper文件在这里注册-->
<mappers>
<mapper resource="mapper/BookMapper.xml"/>
</mappers>
</configuration>
运行测试 1.只存在作者姓名时
package com.senior.test;
import com.complete.util.MyBatisUtil;
import com.senior.dao.BookDao;
import com.senior.entity.Book;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @author 皇甫
*/
public class MyBatisTest {
private SqlSession sqlSession = null;
public MyBatisTest() {
sqlSession = MyBatisUtil.openSqlSession();
}
@Test
public void testQueryBookByAuthorOrName(){
BookDao bd = sqlSession.getMapper(BookDao.class);
List<Book> books = bd.queryBookByAuthorOrName("皇甫嗷嗷叫", null);
for (Book book : books) {
System.out.println(book);
}
}
}
日志如下
两个条件都存在时:
package com.senior.test;
import com.complete.util.MyBatisUtil;
import com.senior.dao.BookDao;
import com.senior.entity.Book;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @author 皇甫
*/
public class MyBatisTest {
private SqlSession sqlSession = null;
public MyBatisTest() {
sqlSession = MyBatisUtil.openSqlSession();
}
@Test
public void testQueryBookByAuthorOrName(){
BookDao bd = sqlSession.getMapper(BookDao.class);
List<Book> books = bd.queryBookByAuthorOrName("皇甫嗷嗷叫", "MyBatis入门篇章");
for (Book book : books) {
System.out.println(book);
}
}
}
日志如下:
以上就是动态sql的常规用法,下面我们将介绍几个几个常用的动态SQL语句
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book where
<if test="author!=null">
author = #{author}
</if>
<if test="name!=null">
and name = #{name}
</if>
</select>
相当于java语言里面的
if..else if ...else
,一旦有前面的when
符合条件,则后面的不会再执行,如果前面的when 都没有匹配条件,则执行otherwise
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.senior.dao.BookDao">
<!--1=1的作用是 当第一个条件不成立,第二个条件执行时,前面会多个and 导致报错,故加上一个无关紧要的条件来取消掉这个错误-->
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book where 1=1
<choose>
<when test="author!=null">
and author = #{author}
</when>
<when test="name!=null">
and name = #{name}
</when>
<otherwise>
author = "皇甫嗷嗷叫"
</otherwise>
</choose>
</select>
</mapper>
这里没有加1=1的原因是自动过滤了 最前面的 and
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.senior.dao.BookDao">
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book
<where>
<if test="author!=null">
author = #{author}
</if>
<if test="name!=null">
and name = #{name}
</if>
</where>
</select>
</mapper>
书写DAO接口方法
/**
* 修改书籍信息
* @param book
*/
public void updateBook(Book book);
书写Mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.senior.dao.BookDao">
<select id="queryBookByAuthorOrName" resultType="com.senior.entity.Book">
select * from book
<where>
<if test="author!=null">
author = #{author}
</if>
<if test="name!=null">
and name = #{name}
</if>
</where>
</select>
<!--只修改要修改的值,不必去构建一个完整的Book-->
<update id="updateBook" parameterType="com.senior.entity.Book">
update book
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="author!=null">
author = #{author},
</if>
<if test="createDate!=null">
createdate = #{createDate},
</if>
<if test="status !=null">
status = #{status},
</if>
<if test="clazzId !=null">
clazzid = #{clazzId}
</if>
</set>
where id=#{id}
</update>
</mapper>
测试
@Test
public void testUpDateBook(){
Book book = new Book();
book.setId(10);
book.setStatus(0);
BookDao bd = sqlSession.getMapper(BookDao.class);
bd.updateBook(book);
sqlSession.commit();
}
用于解决循环数组的问题
/**
* 根据给定id集合 查询所有符合条件的数据
* @param ids
* @return
*/
public List<Book> findBookByIds(@Param("ids") Integer[] ids);
Mapper
<select id="findBookByIds" resultType="com.senior.entity.Book">
select * from book where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
因为参数比较多,重点解释一下
----正常这个SQL语句为:
select * from book where id in(1,2,3,4,5);
1.collection:
没有@Param(xxx)
指定的时候,默认为数组的类型
,即-数组为array
集合为list
,@Param(xxx)指定的时候为@Param的值。
2.item:
为遍历的单个名字,命名随意,使用时直接使用即可,详情见Mapper的编写内容#{id}
3.open:
in后面以(
开头
4.close:
顾名思义,以)
结尾
5.separator:
以,
作为分割
本来想一下传完的,但是正文部分不能超过50000字,所以,今天先上传一部分,我们下期再见