- 搭建
- 配置
- Mybatis获取参数的两种方式 $ #
- 查询结果不为同一个对象,单值
- 查询结果不为同一个对象,多值-list<map<xx,xx>>,@Mapkey
- 模糊查询、批量删除、设置表名
- 添加,获取自增的id
- sql字段名与属性不一致
- 处理多对一的映射关系(学生查询班级)
- 延迟加载(优化查询速度)
- 处理一对多映射(班级查询学生)
- 动态sql
- 缓存
- 分页
mysql的搭建
搭建
1.pom文件中导入 <dependencies>
<!-- Mybatis核心 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- junit测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!--日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
2.Mybatis核心配置文件-连接数据库
mybatis-config.xml文件 整合spring后,文件可以省略
作用:连接数据库,配置数据库的信息
<configuration>
<settings>
<!-- 输出日志-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--可选的值有:SLF4J、LOG4J、LOG4J2、JDK_LOGGING、COMMONS_LOGGING、STDOUT_LOGGING、NO_LOGGING -->
</settings>
<!--设置连接数据库的环境-->
<environments default="development">
<environment id="development">
<!-- 事物管理器-->
<transactionManager type="JDBC"/>
<!-- 管理数据库的连接-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm1?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="12345"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<package name="com.qwq.Mapper"/>
</mappers>
</configuration>
3.mapper
public interface UserMapper {
// 添加
int insertUser(User user);
// 修改
int updateUser(User user);
// 删除
int DeleteUser(User user);
}
4.mapper.xml
<?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的全限定类名-->
<mapper namespace="com.qwq.Mapper.UserMapper">
<!-- 与方法重名-->
<insert id = "insertUser">
Insert into user value (#{id},#{name},#{password},#{age},#{gender},#{email})
</insert>
<!-- 与方法重名-->
<update id="updateUser">
update user set id=#{id}, name =#{name},password=#{password},age=#{age},gender=#{gender},email=#{email} where id = #{id}
</update>
<update id="DeleteUser">
delete from user where id = #{id};
</update>
</mapper>
5.test测试获取sqlsession对象
@Test
public void test04() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setId(1);
int i = userMapper.DeleteUser(user);
System.out.println(i);
}
查询
// 查询
List<User> SelectUser();
<!-- 一对多的关系用resultMap,自定义映射 一对一用resultType,设置结果类型,查询的数据要转换的java类型-->
<select id="SelectUser" resultType="com.qwq.Pojo.User">
select * from user;
</select>
## mybatis核心配置文件
<?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文件的位置-->
<properties resource="jdbc.properties" />
<!--设置类型别名-->
<typeAliases>
<!--
typeAlias:设置某个类型的别名
属性:
type:设置需要设置别名的类型
alias:设置某个类型的别名,若不设置该属性,那么该类型拥有默认的别名,即类名
且不区分大小写
-->
<!--以包为单位,将包下所有的类型设置默认的类型别名,即类名且不区分大小写-->
<package name="com.qwq.Pojo"/>
</typeAliases>
<!--设置连接数据库的环境-->
<environments default="development">
<!-- 数据库的唯一标识-->
<environment id="development">
<!-- 事物管理器 type:设置事物的管理方式
jdbc:使用jdbc原生的事物管理方式
MANAGED:被管理 例如spring-->
<transactionManager type="JDBC"/>
<!-- 管理数据库的连接 dataSource设置数据源
type:设置数据源的类型
type="POOLED|UNPOOLED|JNDI"
POOLED:表示使用数据库连接池缓存数据库连接
UNPOOLED:表示不使用数据库连接池
JNDI:表示使用上下文中的数据源-->
<dataSource type="POOLED">
<!-- 数据库驱动类名-->
<property name="driver" value="${jdbc.driver}"/>
<!-- 连接数据库的url字符串-->
<property name="url" value="${jdbc.url}"/>
<!-- 访问数据库的用户名-->
<property name="username" value="${jdbc.user}"/>
<!-- 访问数据库的密码-->
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<package name="com.qwq.Mapper"/>
</mappers>
</configuration>
## mybatis获取参数的两种方式 1.$ #
<select id="GetUserName" resultType="user">
select * from user where name = '${name}'
</select>
// select * from user where name = 'www'
<select id="GetUserName" resultType="user">
select * from user where name = #{name}
</select>
Preparing: select * from user where name = ?
Parameters: www(String)
2.当存在多个参数的时候,#{} ${} 需要使用
arg:arg0 、arg1…
param:param1、param2
select * from user where name= ? and password = ?
<select id="getUser" resultType="User">
select * from user where name= #{arg0} and password = #{arg1}
</select>
select * from user where name= '${param1}' and password = '${param2}'
<select id="getUser" resultType="User">
select * from user where name= '${param1}' and password = '${param2}'
</select>
3.多个参数自定义 使用map
//方法
User getUserMap(Map<String,Object> map);
//测试类
@Test
public void test08() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map =new HashMap<String,Object>();
//键为变量名
map.put("name","www");
map.put("password","11");
User users = userMapper.getUserMap(map);
System.out.println(users);
}
//xml
<select id="getUserMap" resultType="User">
select * from user where name= #{name} and password = #{password}
</select>
4.方法参数为实体类 参数与sql中的参数名一一对应
注解 @Param
给参数设置别名,以两种形式进行存储1.以@Param的属性值为键,以参数为值进行存储
2.以param1,param2为键,以参数为值进行存储
User getUsername(@Param("username") String name ,@Param("userpassword") String password);
<select id="getUsername" resultType="com.qwq.Pojo.User">
select * from user where name= #{username} and password = #{userpassword}
</select>
查询结果不为同一个对象时,使用map,键为key,值为value,一条数据
<select id="getMap" resultType="map">
select * from user where id= #{id}
</select>
@Test
public void test11() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = userMapper.getMap(2);
System.out.println(map);
// {password=11, gender=z, name=ww, id=2, age=12, email=12@qq.com}
}
查询结果不为同一个对象,存在多个值
1.使用list嵌套map
List<Map<String,Object>> getListMap(String name);
@Test
public void test12() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Map<String, Object>> listMap = userMapper.getListMap("ww");
System.out.println(listMap);
// [{password=11, gender=z, name=ww, id=2, age=12, email=12@qq.com}, {password=11, gender=z, name=ww, id=3, age=12, email=12@qq.com}, {password=11, gender=z, name=ww, id=5, age=12, email=12@qq.com}, {password=11, gender=z, name=ww, id=6, age=12, email=12@qq.com}, {password=11, gender=z, name=ww, id=7, age=12, email=12@qq.com}, {password=11, gender=z, name=ww, id=8, age=12, email=12@qq.com}]
}
2.多个值使用map,使用注解@Mapkey,指明键
@MapKey("id")
Map<String,Object> getMaps(String name);
@Test
public void test13() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> ww = userMapper.getMaps("ww");
System.out.println(ww);
// {2={password=11, gender=z, name=ww, id=2, age=12, email=12@qq.com},
// 3={password=11, gender=z, name=ww, id=3, age=12, email=12@qq.com}, 5={password=11, gender=z, name=ww, id=5, age=12, email=12@qq.com}, 6={password=11, gender=z, name=ww, id=6, age=12, email=12@qq.com}, 7={password=11, gender=z, name=ww, id=7, age=12, email=12@qq.com}, 8={password=11, gender=z, name=ww, id=8, age=12, email=12@qq.com}}
}
模糊查询
<select id="SelectUser" resultType="user">
select * from user where name like "%" #{mohu} "%"
</select>
Preparing: select * from user where name like "%" ? "%"
<!--批量删除 delete from user where id in (2,3) -->
<delete id="deleteUser">
delete from user where id in (${id})
</delete>
<!-- 设置查询表名 select * from user-->
<select id="users" resultType="user">
select * from ${tbName}
</select>
获取添加后,自增的主键
<!-- useGeneratedKeys=true :表示当前添加使用的是自增主键
keyProperty="id" 将主键的值赋值为实体类的参数-->
<insert id="InsertUsers" useGeneratedKeys="true" keyProperty="id">
INSERT into user value (#{id} ,#{name},#{password},#{age},#{gender},#{email})
</insert>
sql字段名与属性名不一致
1.xml设置驼峰命名法
<!-- 将下划线映射为驼峰命名法 emp_id === empId emp_name ====== empName-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
2,使用resultMap
<resultMap id="empMap" type="emp">
<!-- 自增字段用id 普通字段用result sql查询出的字段用column 映射关系中的属性名,必须为实体类的属性 property -->
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
</resultMap>
<select id="empList" resultMap="empMap">
select * from emp where emp_id = #{id}
</select>
## 处理多对一的映射关系
<!-- 1. 多对一的映射关系 EmpIdMap -->
<resultMap id="EmpIdMapOne" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!-- 使用dept_id 和 dept.DeptId 映射
Emp{empId=1, empName='1', age=1, gender='1', deptId=null, dept=Dept{DeptId=1, DeptName='1'}} -->
<result column="dept_id" property="dept.DeptId"></result>
<result column="dept_id" property="dept.DeptName"></result>
</resultMap>
<!-- 2. 多对一的映射关系 association(要有一个多表中的实体类) -->
<resultMap id="EmpIdMap" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!-- property表中的实体类 javaType实体类的类型 -->
<association property="dept" javaType="Dept">
<id column="dept_id" property="DeptId"></id>
<result column="dept_name" property="DeptName"></result>
</association>
</resultMap>
<!-- Emp{empId=1, empName='1', age=1, gender='1', deptId=1, dept=null} -->
<select id="getEmoId" resultMap="EmpIdMap">
select * from emp left join dept on emp.emp_id=dept.dept_id where emp_id = #{empId};
</select>
<!--3.分布查询-->
<resultMap id="EmpIdMapone" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!-- property需要映射的多表属性名
select分布查询sql
column:分布查询sql的条件、第二个方法的参数-->
<association property="dept"
select="com.qwq.Mapper.DeptMapper.selectDept
" column="DeptId">
</association>
</resultMap>
<select id="getEmoIdone" resultMap="EmpIdMapone">
select * from emp where emp_id = #{empId}
</select>
<select id="selectDept" resultType="com.qwq.Pojo.Dept">
select * from dept where dept_id=#{DeptId}
</select>
延迟加载
全局配置:<settrings>
<!-- 延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 按需加载 true都会执行 false延迟执行-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
指定是否延迟加载 Mapper
<!--分布查询-->
<resultMap id="EmpIdMapone" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<!-- property需要映射的多表属性名
select分布查询sql
column:分布查询sql的条件、第二个方法的参数的字段
fetchType="eager"(立即加载) lazy(延迟加载) -->
<association property="dept"
fetchType="lazy"
select="com.qwq.Mapper.DeptMapper.selectDept" column="dept_id">
</association>
</resultMap>
结果:
延迟加载
==> Preparing: select * from emp where emp_id = ?
==> Parameters: 1(Integer)
<== Columns: emp_id, emp_name, age, gender, dept_id
<== Row: 1, 1, 1, 1, 1
<== Total: 1
立即加载
==> Preparing: select * from emp where emp_id = ?
==> Parameters: 1(Integer)
<== Columns: emp_id, emp_name, age, gender, dept_id
<== Row: 1, 1, 1, 1, 1
====> Preparing: select * from dept where dept_id = ?
====> Parameters: 1(Integer)
<==== Columns: dept_id, dept_name
<==== Row: 1, 1
<==== Total: 1
<== Total: 1
## 处理一对多的映射关系
1.collection
<!-- 多对一的映射关系
ofType 集合熟悉中存储的数据类型
-->
<resultMap id="DeptEmpMap" type="Dept">
<id column="dept_id" property="DeptId"></id>
<result column="dept_name" property="DeptName"></id>
<collection property="emps" ofType="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="age" property="age"></result>
<result column="gender" property="gender"></result>
<result column="dept_id" property="deptId"></result>
</collection>
</resultMap>
<select id="deptEmp" resultMap="DeptEmpMap">
select * from dept left join emp on dept.dept_id = emp.dept_id where dept.dept_id = #{DeptId}
</select>
==> Preparing: select * from dept left join emp on dept.dept_id = emp.dept_id where dept.dept_id = ?
==> Parameters: 1(Integer)
<== Columns: dept_id, dept_name, emp_id, emp_name, age, gender, dept_id
<== Row: 1, 1, 1, 王1, 1, 1, 1
<== Row: 1, 1, 2, 王2, 21, 1, 1
<== Total: 2
Dept{DeptId=1, DeptName='1', emps=[Emp{empId=1, empName='王1', age=1, gender='1', deptId=1, dept=null}, Emp{empId=2, empName='王2', age=21, gender='1', deptId=1, dept=null}]}
2.分布查询
<!-- 分布查询
1:先查出部门id
2.再根据唯一标识找到对应的方法,将查询出的条件交给下一个sql,
-->
<resultMap id="DeptOneEmp" type="Dept">
<id column="dept_id" property="DeptId"></id>
<result column="dept_name" property="DeptName"></result>
<collection property="emps"
select="com.qwq.Mapper.empMapper.empTow"
column="dept_id"></collection>
</resultMap>
<select id="empTow" resultType="Emp">
select * from emp where dept_id = #{deptId}
</select>
<select id="deptOne" resultMap="DeptOneEmp">
select * from dept where dept_id = #{DeptId}
</select>
==> Preparing: select * from dept where dept_id = ?
==> Parameters: 1(Integer)
<== Columns: dept_id, dept_name
<== Row: 1, 1
<== Total: 1
==> Preparing: select * from emp where dept_id = ?
==> Parameters: 1(Integer)
<== Columns: emp_id, emp_name, age, gender, dept_id
<== Row: 1, 王1, 1, 1, 1
<== Row: 2, 王2, 21, 1, 1
<== Total: 2
Dept{DeptId=1, DeptName='1', emps=[Emp{empId=1, empName='王1', age=1, gender='1', deptId=1, dept=null}, Emp{empId=2, empName='王2', age=21, gender='1', deptId=1, dept=null}]}
动态sql
- if
<!-- if test 判断内容是否有效 不符合条件不拼接入sql-->
<select id="getEmpByCondition" resultType="Emp">
select * from emp where
<if test="empName != null and empName != '' ">
emp_name =#{empName}
</if>
<if test="age != null and age != '' ">
and age =#{age}
</if>
<if test="gender != null and gender != '' ">
and gender = #{gender}
</if>
</select>
select * from emp where emp_name =? and age =?
- where
<!-- where标签 1.查询条件成立,自动生成where标签
2.where标签可以将sql前的and去掉
3.where标签全不成立,则没有where标签-->
<select id="getEmpByCondition" resultType="Emp">
select * from emp
<where>
<if test="empName != null and empName != '' ">
emp_name =#{empName}
</if>
<if test="age != null and age != '' ">
and age =#{age}
</if>
<if test="gender != null and gender != '' ">
and gender = #{gender}
</if>
</where>
</select>
//全不成立 select * from emp
//查询成立 select * from emp WHERE emp_name =?
//查询去掉and select * from emp WHERE age =?
3.trim
<!-- prefix :在标签中内容前面添加xx内容
suffix :在标签中内容后面添加xx内容
prefixOverrides、suffixOverrides :标签中内容前后面去掉xx内容-->
<select id="getEmpByCondition" resultType="Emp">
select * from emp
<trim prefix="where" suffixOverrides="and" >
<if test="empName != null and empName != '' ">
emp_name =#{empName}and
</if>
<if test="age != null and age != '' ">
age =#{age} and
</if>
<if test="gender != null and gender != '' ">
gender = #{gender}
</if>
</trim>
</select>
- 1=1
<select id="getEmpByCondition2" resultType="Emp">
select * from emp
<where>
<if test="empName != null and empName != '' ">
emp_name =#{empName}
</if>
<if test="age != null and age != '' ">
and age =#{age}
</if>
<if test="gender != null and gender != '' ">
and gender = #{gender}
</if>
</where>
</select>
5.choose 父标签 when (if) otherwise (else)
<!-- choose :父标签
when: if
otherwise else-->
<select id="getEmpByCondition" resultType="Emp">
select * from emp
<where>
<choose>
<when test="empName != null and empName != '' ">
emp_name =#{empName}
</when>
<when test="age != null and age != '' ">
age =#{age}
</when>
<otherwise >
gender = #{gender}
</otherwise>
</choose>
</where>
</select>
6.foreach
<!-- foreach :
collection : 要循环的数组或集合
item: 用一个字符串表示数组或集合中的每一个数据(xx) for(String xx : xxx)
separator:每一次循环后,使用什么作为分隔符
open:循环的内容以什么开始
close:循环的所以内容以什么结束 -->
<insert id="insertEmps">
insert into emp values
<foreach collection="emps" item="emp" separator="," >
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
<!-- insert into emp values (null,?,?,?,null) , (null,?,?,?,null) , (null,?,?,?,null) -->
<delete id="deleteEmps">
delete from emp where emp_id in
(
<foreach collection="empids" item="empid" separator=",">
#{empid}
</foreach>
)
</delete>
<!-- delete from emp where emp_id in ( ? , ? ) -->
<delete id="deleteEmps2">
delete from emp where
(
<foreach collection="empids" item="empid" separator="or" >
emp_id = #{empid}
</foreach>
)
</delete>
<!-- delete from emp where ( emp_id = ? or emp_id = ? )-->
7.sql
<sql id="sql1">
emp_id,emp_name,age,gender,dept_id
</sql>
<select id="getEmoIdone" resultMap="EmpIdMapone">
select <include refid="sql1"></include> from emp where emp_id = #{empId}
</select>
<!--select emp_id,emp_name,age,gender,dept_id from emp where emp_id = ? -->
缓存
缓存顺序
1.先查询二级缓存,在查询一级缓存
2.二级缓存没有,再查询一级缓存
3.均没有查询数据库
/**
* Mybatis 的一级缓存 是SqlSession级别的 通过同一个SqlSession查询同一条数据,会从缓存种获取
* SqlSession失效的四个原因
* 1.不同SqlSession对应不同的缓存
* 2.同一个SqlSession查询对象不同
* 3.同一个SqlSession两次查询之间执行了一次增删改
* 4.同一个SqlSession在两次查询中间手动清空了缓存
*/
// 一级缓存的效果
@Test
public void test06() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
empMapper mapper = sqlSession.getMapper(empMapper.class);
Emp empId1 = mapper.getEmpId(6);
Emp empId2 = mapper.getEmpId(6);
System.out.println("执行一条sql");
System.out.println(empId1);
System.out.println(empId2);
SqlSession sqlSession1 = sqlSessionFactory.openSession(true);
empMapper mapper1 = sqlSession1.getMapper(empMapper.class);
System.out.println("不同SqlSession对象查询同一条数据,不在缓存查询");
Emp empId = mapper1.getEmpId(6);
System.out.println(empId);
}
/**
Created connection 1877453512.
==> Preparing: select * from emp where emp_id = ?
==> Parameters: 6(Integer)
<== Columns: emp_id, emp_name, age, gender, dept_id
<== Row: 6, 1, 3, , 1
<== Total: 1
Cache Hit Ratio [com.qwq.Mapper.empMapper]: 0.0
执行一条sql
Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
不同SqlSession对象查询同一条数据,不在缓存查询
Cache Hit Ratio [com.qwq.Mapper.empMapper]: 0.0
Opening JDBC Connection
Created connection 551479935.
==> Preparing: select * from emp where emp_id = ?
==> Parameters: 6(Integer)
<== Columns: emp_id, emp_name, age, gender, dept_id
<== Row: 6, 1, 3, , 1
<== Total: 1
Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
**/
// 在两组查询之间,使用新增方法,不走缓存
@Test
public void test07() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
empMapper mapper = sqlSession.getMapper(empMapper.class);
Emp empId1 = mapper.getEmpId(6);
Emp emp = new Emp(null, "ww", 12, "1", 1);
// 两个查询方法,在使用新增方法后,不在缓存中查询
mapper.InsertEmp(emp);
Emp empId2 = mapper.getEmpId(6);
System.out.println("执行一条sql");
System.out.println(empId1);
System.out.println(empId2);
SqlSession sqlSession1 = sqlSessionFactory.openSession(true);
empMapper mapper1 = sqlSession1.getMapper(empMapper.class);
System.out.println("不同SqlSession对象查询同一条数据,不在缓存查询");
Emp empId = mapper1.getEmpId(6);
System.out.println(empId);
/**
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* ==> Preparing: insert into emp values (null,?,?,?,?);
* ==> Parameters: ww(String), 12(Integer), 1(String), 1(Integer)
* <== Updates: 1
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* 执行一条sql
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
* 不同SqlSession对象查询同一条数据,不在缓存查询
* Opening JDBC Connection
* Created connection 1637290981.
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
*
* Process finished with exit code 0
*/
}
// 两个查询方法之间 关闭SqlSession,不走缓存
@Test
public void test08() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
empMapper mapper = sqlSession.getMapper(empMapper.class);
Emp empId1 = mapper.getEmpId(6);
// 手动清空缓存
sqlSession.clearCache();
Emp empId2 = mapper.getEmpId(6);
System.out.println("执行一条sql");
System.out.println(empId1);
System.out.println(empId2);
SqlSession sqlSession1 = sqlSessionFactory.openSession(true);
empMapper mapper1 = sqlSession1.getMapper(empMapper.class);
System.out.println("不同SqlSession对象查询同一条数据,不在缓存查询");
Emp empId = mapper1.getEmpId(6);
System.out.println(empId);
/**
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* 执行一条sql
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
* 不同SqlSession对象查询同一条数据,不在缓存查询
* Opening JDBC Connection
* Created connection 1010856212.
* ==> Preparing: select * from emp where emp_id = ?
* ==> Parameters: 6(Integer)
* <== Columns: emp_id, emp_name, age, gender, dept_id
* <== Row: 6, 1, 3, , 1
* <== Total: 1
* Emp{empId=6, empName='1', age=3, gender='', deptId=1, dept=null}
*/
}
二级缓存
/**
* 二级缓存
* SqlSessionFactory级别
* 通过同一个SqlSessionFactory获取到的SqlSession对象被缓存,在同一个SqlSessionFactory获取到的SqlSession对象可以从缓存中获取
* 1. 核心配置文件cacheEnabled="true” 默认为True 不需要设置
* 2. 在映射文件中加入<cache /> 标签
* 3. 二级缓存需要在SqlSession对象关闭或提交之后有效果
* 4. 查询接口的实体类需要序列化
* 查询缓存失败原因
* 1.两次查询之间使用增删改,会使一级二级缓存失效
*/
@Test
public void test10() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
empMapper mapper = sqlSession.getMapper(empMapper.class);
Emp empId = mapper.getEmpId(6);
sqlSession.close();
System.out.println("SqpSession" + empId);
SqlSession sqlSession2 = sqlSessionFactory.openSession(true);
empMapper mapper2 = sqlSession2.getMapper(empMapper.class);
Emp empId2 = mapper2.getEmpId(6);
System.out.println("SqlSession2" + empId2);
sqlSession.close();
}
分页
1.加入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
2.在mybatis核心配置文件中配置分页插件 typeAliases后面
<plugins>
<!--设置分页插件-->
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
3.查询前加入
Page<Object> Page = PageHelper.startPage(5, 4);
查询后使用
// 查询功能结束后,获取分页的想过信息
// navigatePages :5 显示当前导航页的页码条数为 5个 当前页码数为5 [3, 4, 5, 6, 7]
PageInfo<Emp> empPageInfo = new PageInfo<Emp>(emps,5);
@Test
public void Test01() throws IOException {
//1.读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3.通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory,生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4.创建SqlSession对象,此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//5.通过代理模式创建UserMapper接口的代理实现类对象
empMapper mapper = sqlSession.getMapper(empMapper.class);
// 查询条件前使用分页条数
Page<Object> Page = PageHelper.startPage(5, 4);
List<Emp> emps = mapper.PageMapper();
// 查询功能结束后,获取分页的想过信息
// navigatePages :5 显示当前导航页的页码条数为 5个 当前页码数为5 [3, 4, 5, 6, 7]
PageInfo<Emp> empPageInfo = new PageInfo<Emp>(emps,5);
for (Emp emp:emps){
System.out.println("这个对象是"+emp);
}
System.out.println(empPageInfo);
}