13、动态SQL语句
动态的sql语句,就是指,Mybatis是运行时,根据传的参数值的内容决定sql语句的改变 。
- 准备工作:
public class User {
private int id;
private String lastName;
private int sex;
}
13-1.if 语句
说明: if语句,可以动态的根据你的值来决定,是否需要动态的添加查询条件。
- Mapper接口:
public interface UserMapper {
/**
* 根据给定的User对象的LastName属性和sex属性来进行查询用户信息<br/>
* 只要lastName匹配的用户都查询,或匹配sex的用户都查询<br/>
* 如果lastName属性值不合法,或sex属性值不合法,不加入到查询条件中
* @return
*/
public List<User> queryUsersByUser(User user);
}
- Mapper.xml配置文件:
<!-- /**
* 根据给定的User对象的LastName属性和sex属性来进行查询用户信息<br/>
* 只要lastName匹配的用户都查询,或匹配sex的用户都查询<br/>
* 如果lastName属性值不合法,或sex属性值不合法,不加入到查询条件中
* @return
*/
public List<User> queryUsersByUser(User user);-->
<select id="queryUsersByUser" parameterType="com.atguigu.pojo.User"
resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
where
<!--
if 是if语句,它可以做if判断
test是判断条件
-->
<if test="lastName != null">
last_name = #{lastName}
</if>
<if test="sex == 0 || sex == 1">
or sex = #{sex}
</if>
</select>
- 测试代码:
@Test
public void queryUsersByUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
for (User user : mapper.queryUsersByUser(new User(null, "aaa186", 10))) {
System.out.println(user);
}
} finally {
session.close();
}
}
13-2.where 语句
说明: where语句,可以帮我们在多个动态语句中,有效的去掉前面的多余的and 或 or 之类的多余关键字。
- Mapper接口
public interface UserMapper {
/**
* 根据给定的User对象的LastName属性和sex属性来进行查询用户信息<br/>
* 只要lastName匹配的用户都查询,或匹配sex的用户都查询<br/>
* 如果lastName属性值不合法,或sex属性值不合法,不加入到查询条件中
* @return
*/
public List<User> queryUsersByUser(User user);
}
- Mapper.xml配置文件:
<!-- /**
* 根据给定的User对象的LastName属性和sex属性来进行查询用户信息<br/>
* 只要lastName匹配的用户都查询,或匹配sex的用户都查询<br/>
* 如果lastName属性值不合法,或sex属性值不合法,不加入到查询条件中
* @return
*/
public List<User> queryUsersByUser(User user);-->
<select id="queryUsersByUser" parameterType="com.atguigu.pojo.User"
resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
<!--
where它可以判断查询条件前面是否有多余的and或or,它会帮我们去掉,
如果where里有内容,还会帮我们添加Where关键字,如果没有内容。where就不存在
-->
<where>
<!--
if 是if语句,它可以做if判断
test是判断条件
-->
<if test="lastName != null">
last_name = #{lastName}
</if>
<if test="sex == 0 || sex == 1">
or sex = #{sex}
</if>
</where>
</select>
- 测试代码:
@Test
public void queryUsersByUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
for (User user : mapper.queryUsersByUser(new User(null, null, 10))) {
System.out.println(user);
}
} finally {
session.close();
}
}
13-3.trim语句
说明: trim 可以动态在包含的语句前面和后面添加内容。也可以去掉前面或者后面给定的内容:
- prefix 前面添加内容
- suffix 后面添加内容
- suffixOverrides 去掉的后面内容
- prefixOverrides 去掉的前面内容
Mapper.xml配置文件:
<select id="queryUsersByUser" parameterType="com.atguigu.pojo.User"
resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
<!--
说明: trim 可以动态在包含的语句前面和后面添加内容。也可以去掉前面或者后面给定的内容
prefix 前面添加内容
suffix 后面添加内容
suffixOverrides 去掉的后面内容
prefixOverrides 去掉的前面内容
-->
<trim suffixOverrides="or" prefix="where" prefixOverrides="or">
<!--
if 是if语句,它可以做if判断
test是判断条件
-->
<if test="lastName != null">
last_name = #{lastName}
</if>
<if test="sex == 0 || sex == 1">
or sex = #{sex}
</if>
</trim>
</select>
- 测试代码:
@Test
public void queryUsersByUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
for (User user : mapper.queryUsersByUser(new User(null, null, 1))) {
System.out.println(user);
}
} finally {
session.close();
}
}
13-4.choose( when , otherwise )语句
说明:choose when otherwise 可以执行多路选择判断,但是只会有一个分支会被执行。类似switch case default语句
- Mapper接口:
/*
根据给定的user的属性来进行查询<br/>
1、如果lastName有效,仅使用lastName做查询条件
2、如果sex属性有效,仅使用sex做查询条件
3、如果lastName和sex都无效,可以自定义一个默认的查询条件或没有
**/
public List<User> queryUsersByUserChoose(User user);
- Mapper.xml配置文件:
<select id="queryUsersByUserChoose" parameterType="com.atguigu.pojo.User"
resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
<where>
<choose>
<!-- when表示一种判断的情况 -->
<when test="lastName != null">
last_name = #{lastName}
</when>
<when test="sex == 0 or sex == 1">
sex = #{sex}
</when>
<!-- 表示以上条件都不成立。就会执行的内容 -->
<otherwise>
id = #{id}
</otherwise>
</choose>
</where>
</select>
- 测试代码:
@Test
public void queryUsersByUserChoose(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
for (User user : mapper.queryUsersByUserChoose(new User(1, null, 10))) {
System.out.println(user);
}
} finally {
session.close();
}
}
13-5.set语句
删除更新条件(列内容)后的逗号。
- Mapper接口:
/**
* 更新用户
*/
public int updateUser(User user);
- Mapper.xml配置文件:
<update id="updateUser" parameterType="com.atguigu.pojo.User">
update
t_user
<!-- set标签可以去掉更新列,后面多余的逗号
还会添加set关键字
-->
<set>
<if test="lastName != null">
`last_name` = #{lastName} ,
</if>
<if test="sex == 0 || sex == 1">
`sex` = #{sex}
</if>
</set>
where
id = #{id}
</update>
- 测试代码:
@Test
public void updateUser(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.updateUser(new User(13, "bbbbbbbbbbb", 10));
session.commit();
} finally {
session.close();
}
}
13-6.foreach语句
1、foreach经常用于做循环遍历输出。
2、insert into 表名(列
,列
) values(值,值) , (值,值)。
- Mapper接口:
/*批量的插入*/
public int insertUsers(List<User> users);
- Mapper.xml配置文件:
<insert id="insertUsers">
insert into
t_user(`last_name`,`sex`)
values
<!-- foreach做遍历使用
collection表示遍历的集合
item 表示当前遍历到的数据
separator 遍历的每个元素中间的分隔符
-->
<foreach collection="list" item="user" separator=",">
(#{user.lastName},#{user.sex})
</foreach>
</insert>
- 测试代码:
@Test
public void insertUsers(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = new ArrayList<>();
users.add(new User(null,"aaaaa",1));
users.add(new User(null,"bbbbb",1));
users.add(new User(null,"ccccc",1));
mapper.insertUsers(users);
session.commit();
} finally {
session.close();
}
}
select * from 表名 where id in (xx,xx,xx);
- Mapper接口:
public List<User> queryUsers(List<Integer> ids);
- Mapper.xml配置文件:
<select id="queryUsers" resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
<where>
id in
<!--
collection 属性表示遍历的集合
item 表示当前遍历到的数据
separator 遍历每个元素中间的间隔符
open 表示遍历前输出的内容
close 表示遍历后输出的内容
-->
<foreach open="(" close=")" collection="list" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
- 测试代码:
@Test
public void queryUsersByIds(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(4);
for (User user : mapper.queryUsers(ids)) {
System.out.println(user);
}
} finally {
session.close();
}
}
13-7.sql片段
14、MyBatis缓存
1、什么是缓存?
- 按照动词解释: 缓存是指把经常访问的数据提前保存到高速缓存区中,叫缓存.
- 按照名词解释: 缓存是指保存到了高速缓冲区中的数据叫缓存
2、缓存的作用就是提高访问速度。
3、一级缓存: 保存数据到SQLSession范围。
4、二级缓存: 保存数据到SqlSessionFactory范围。
14-1.MyBatis的一级缓存的示例
示意图:
代码:
@Test
public void firstLevelCache() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
User user1 = mapper.queryUserById(1);
System.out.println(user1);
} finally {
session.close();
}
}
14-2.一级缓存的管理
缓存失效的四种情况:
1.不在同一个SqlSession对象中
public void queryOne(){
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
} finally {
session.close();
}
}
// 缓存失效的四种情况:
// 1.不在同一个SqlSession对象中
@Test
public void firstLevelCacheFail1() {
queryOne();
queryOne();
}
2.执行语句的参数不同。缓存中也不存在数据。
@Test
public void firstLevelCacheFail2() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
User user1 = mapper.queryUserById(8);
System.out.println(user1);
} finally {
session.close();
}
}
3.执行增,删,改,语句,会清空掉缓存
@Test
public void firstLevelCacheFail3() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
mapper.updateUser(new User(1, "asdf", 1));
User user1 = mapper.queryUserById(1);
System.out.println(user1);
session.commit();
} finally {
session.close();
}
}
4.手动清空缓存数据
@Test
public void firstLevelCacheFail4() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
// 清除缓存
session.clearCache();
User user1 = mapper.queryUserById(1);
System.out.println(user1);
} finally {
session.close();
}
}
14-3.MyBatis的二级缓存
二级缓存的图解示意:
二级缓存的使用
MyBatis的二级缓存默认是开启的。
1、我们需要在mybatis的核心配置文件中配置setting选项 :
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
2、在Mapper的配置文件中加入cache标签启用缓存。
3、需要被二级缓存的对象必须要实现java的序列化接口。
二级缓存的演示
public void queryOne() {
SqlSession session = sqlSessionFactory.openSession();
try {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
} finally {
session.close();
}
}
@Test
public void secondLevelCache() {
queryOne();
queryOne();
}
useCache="false"的演示和说明
userCache属性是select标签中的属性。表示在查询的时候,使用二级缓存。默认值是true。
<!--
userCache属性是select标签中的属性。表示在查询的时候,使用二级缓存。默认值是true.
-->
<select id="queryUserById" useCache="true" resultType="com.atguigu.pojo.User">
select
`id`,`last_name` lastName,`sex`
from
t_user
where
id = #{id}
</select>
flushCache="false"的演示和说明
flushCache属性是设置是否清空缓存。它是insert\update\delete标签的属性。默认值是true表示清空。fasle表示不清空。
<!--
flushCache属性是设置是否清空缓存。它是insert , update ,delete标签的属性。默认值是true表示清空。fasle表示不清空。
-->
<update id="updateUser" flushCache="true" parameterType="com.atguigu.pojo.User">
update
t_user
<set>
<if test="lastName != null">
`last_name` = #{lastName}
</if>
<if test="sex == 0 || sex == 1">
, `sex` = #{sex}
</if>
</set>
where
id = #{id}
</update>
15、缓存的使用顺序说明
1、当我们执行一个查询语句的时候。mybatis会先去二级缓存中查询数据。
2、如果二级缓存中没有。就到一级缓存中查找。
3、如果二级缓存和一级缓存都没有。就发sql语句到数据库中去查询。
4、查询出来之后马上把数据保存到一级缓存中。
5、当SqlSession关闭的时候,会把一级缓存中的数据保存到二级缓存中。
16、MyBatis逆向工程
MyBatis逆向工程,简称MBG。是一个专门为MyBatis框架使用者定制的代码生成器。可以快速的根据数据库表生成对应的映射文件,接口,以及Bean类对象。在Mybatis中,有一个可以自动对单表生成的增,删,改,查代码的插件。叫 mybatis-generator-core-1.3.2。它可以帮我们对比数据库表之后,生成大量的这个基础代码。
这些基础代码有:
1、数据库表对应的javaBean对象。
2、这些javaBean对象对应的Mapper接口。
3、这些Mapper接口对应的配置文件。
<!-- 去掉全部的注释 -->
<commentGenerator>
<property name="suppressAllComments" value="true" />
</commentGenerator>
- 准备数据库表:
create database mbg;
use mbg;
create table t_user(
`id` int primary key auto_increment,
`username` varchar(30) not null unique,
`password` varchar(40) not null,
`email` varchar(50)
);
insert into t_user(`username`,`password`,`email`) values('admin','admin','admin@atguigu.com');
insert into t_user(`username`,`password`,`email`) values('wzg168','123456','admin@atguigu.com');
insert into t_user(`username`,`password`,`email`) values('admin168','123456','admin@atguigu.com');
insert into t_user(`username`,`password`,`email`) values('lisi','123456','admin@atguigu.com');
insert into t_user(`username`,`password`,`email`) values('wangwu','123456','admin@atguigu.com');
create table t_book(
`id` int primary key auto_increment,
`name` varchar(50),
`author` varchar(50),
`price` decimal(11,2),
`sales` int,
`stock` int
);
## 插入初始化测试数据
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , 'java从入门到放弃' , '国哥' , 80 , 9999 , 9);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , '数据结构与算法' , '严敏君' , 78.5 , 6 , 13);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , '怎样拐跑别人的媳妇' , '龙伍' , 68, 99999 , 52);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , '木虚肉盖饭' , '小胖' , 16, 1000 , 50);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , 'C++编程思想' , '刚哥' , 45.5 , 14 , 95);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , '蛋炒饭' , '周星星' , 9.9, 12 , 53);
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock` )
values(null , '赌神' , '龙伍' , 66.5, 125 , 535);
select * from t_user;
select * from t_book;
mbg.xml配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--
targetRuntime 可以设置生成的代码功能级别
MyBatis3Simple 标配版 CRUD
MyBatis3 豪华版
-->
<context id="DB2Tables" targetRuntime="MyBatis3">
<!-- 去掉全部的注释 -->
<commentGenerator>
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!--修改数据库的连接属性-->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mbg"
userId="root"
password="root">
</jdbcConnection>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!--生成JavaBean的
targetPackage="com.atguigu.pojo" 生成的JavaBean的包名
targetProject=".\mbg\src" 生成的java源代码存放的位置
-->
<javaModelGenerator targetPackage="com.atguigu.pojo" targetProject=".\mbg\src">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--
生成mapper.xml配置文件
-->
<sqlMapGenerator targetPackage="com.atguigu.dao" targetProject=".\mbg\src">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!--
生成Mapper接口
-->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.atguigu.dao"
targetProject=".\mbg\src">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!--
一个table标签,表示数据库中的一张表
tableName 数据库表名
domainObjectName 生成的类名
-->
<table schema="DB2ADMIN" tableName="t_user" domainObjectName="User" ></table>
<table schema="DB2ADMIN" tableName="t_book" domainObjectName="Book" ></table>
</context>
</generatorConfiguration>
- 运行mbg逆向工程的代码:
public class MBGRunner {
public static void main(String[] args) throws Exception {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
File configFile = new File("mbg/mbg.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
}
- 测试的代码:
public class BookMapperTest {
SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
}
@Test
public void countByExample() {
SqlSession session = sqlSessionFactory.openSession();
try {
// countByExample() 查询统计个数 count()
BookMapper bookMapper = session.getMapper(BookMapper.class);
BookExample bookExample = new BookExample();
// 创建查询条件
BookExample.Criteria criteria = bookExample.createCriteria();
// criteria.andPriceGreaterThan(new BigDecimal(50));
// criteria.andNameLike("__v%");
criteria.andPriceBetween(new BigDecimal(50),new BigDecimal(100));
// 此方法用于查询统计个数
// 参数就是查询的条件
System.out.println(bookMapper.countByExample(bookExample));
} finally {
session.close();
}
}
@Test
public void deleteByExample() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
BookExample bookExample = new BookExample();
// bookExample.createCriteria().andIdEqualTo(5);
// 删除 价格区间50 到 100
// bookExample.createCriteria().andPriceBetween(new BigDecimal(50),new BigDecimal(100));
bookExample.createCriteria().andAuthorEqualTo("刚哥").andSalesGreaterThan(100);
// 删除 全部
System.out.println(bookMapper.deleteByExample(bookExample));
session.commit();
} finally {
session.close();
}
}
@Test
public void deleteByPrimaryKey() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
bookMapper.deleteByPrimaryKey(1);
session.commit();
} finally {
session.close();
}
}
@Test
public void insert() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
bookMapper.insert(new Book(null,"xxx", "1125", new BigDecimal(100), 100, 100));
session.commit();
} finally {
session.close();
}
}
@Test
public void insertSelective() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
// 只要方法里带有Selective,就会忽略掉,字段为null的列操作
bookMapper.insertSelective(new Book(null,null, null, new BigDecimal(123),1,1));
bookMapper.insert(new Book(null,null, null, new BigDecimal(123),1,1));
session.commit();
} finally {
session.close();
}
}
@Test
public void selectByExample() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
// 查询价格在50 到 100之间,并按价格排序
BookExample bookExample = new BookExample();
BookExample.Criteria criteria = bookExample.createCriteria().andPriceBetween(new BigDecimal(50), new BigDecimal(100));
// 设置排序
bookExample.setOrderByClause(" price ");
for (Book book : bookMapper.selectByExample(bookExample)) {
System.out.println(book);
}
} finally {
session.close();
}
}
@Test
public void selectByPrimaryKey() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
System.out.println(bookMapper.selectByPrimaryKey(2));
} finally {
session.close();
}
}
@Test
public void updateByExampleSelective() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
Book book = new Book(null,null,null, new BigDecimal(1234), 1,1);
BookExample bookExample = new BookExample();
bookExample.createCriteria().andSalesEqualTo(1);
// 不更新值为null的列
bookMapper.updateByExampleSelective(book, bookExample);
session.commit();
} finally {
session.close();
}
}
@Test
public void updateByExample() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
Book book = new Book(10,null,null, new BigDecimal(1234), 1,1);
BookExample bookExample = new BookExample();
bookExample.createCriteria().andIdEqualTo(10);
// 更新值为null的列
bookMapper.updateByExample(book, bookExample);
session.commit();
} finally {
session.close();
}
}
@Test
public void updateByPrimaryKeySelective() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
Book book = new Book(9,null,null, new BigDecimal(1234), 1,1);
BookExample bookExample = new BookExample();
bookExample.createCriteria().andSalesEqualTo(1);
// 不更新值为null的列
bookMapper.updateByPrimaryKeySelective(book);
session.commit();
} finally {
session.close();
}
}
@Test
public void updateByPrimaryKey() {
SqlSession session = sqlSessionFactory.openSession();
try {
BookMapper bookMapper = session.getMapper(BookMapper.class);
Book book = new Book(9,null,null, new BigDecimal(1234), 1,1);
BookExample bookExample = new BookExample();
bookExample.createCriteria().andSalesEqualTo(1);
// 也会更新null值
bookMapper.updateByPrimaryKey(book);
session.commit();
} finally {
session.close();
}
}
}