文章目录
Mybatis
ctrl + alt + shift + j
:对相同部分内容选中
动态SQL
动态SQL的作用:
- 通过在映射文件中使用一些不同的标签,会让一个方法对应的SQL语句(主要是insert、delete、update、select标签内的sql语句)产生变化
- 是为了让你这个标签内的SQL更强大,更通用
业务场景举例:
- 条件查询
- 举例
-- 查询所有的手机的记录
select * from phone_t
-- 如果增加机身存储空间的条件
select * from phone_t where storage = '512GB'
-- 继续增加条件CPU为天玑7200
select * from phone_t where storage = '512GB' and cpu = '天玑7200'
-- 继续增加内存
select * from phone_t where storage = '512GB' and cpu = '天玑7200' and memory = '12GB'
- Mapper接口中的方法可以如何定义
List<Phone> selectAll();
List<Phone> selectByStorage(String storage);
List<Phone> selectByStorageAndCPU(String storage,String cpu);
List<Phone> selectByStorageAndCPUAndMemory(String storage,String cpu,String memory);
- 如果有4个条件选择其中的两个查询,变成6种情况
- 如果使用条件查询 → 只用Mapper接口中的一个方法就能满足我们的需求
List<User> selectByCondition(String storage,String cpu,String memory,String size)
where标签
- where这个标签可以帮助我们在最终执行的SQL中自动生成where关键字
- 可以自动拼接where关键字 (一般和if配合使用)
- 去除相邻的
and
或者是or
关键字 - 如果where标签中没有条件满足的时候(如果SQL片段需要拼接),那么where标签不会给我们拼接where关键字
eg:
<select id="selectByPrimaryKey" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
select id, phoneName, phoneStorage, phoneMemory from test_phone
/*如果where标签后面添加了and或者or,那么在预编译过程被自动省略掉
如果where标签里没有内容,不会拼接where*/
*/
<where>
and id = #{id}
</where>
</select>
==> Preparing: select id, phoneName, phoneStorage, phoneMemory from test_phone WHERE id = ?
// where标签
PhoneUser[] selectByPrimaryKey(@Param("id") Integer id);
if标签
映射文件中使用if
标签,test属性它的值为true
,则会拼接if标签内的SQL语句;如果test属性值为false
则不会拼接
eg:
<!-- 如果全部参数都为空以及and,这是使用where标签的好处-->
<select id="selectByCondition" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
select id, phoneName, phoneStorage, phoneMemory from test_phone
<where>
<if test="phoneName != null and phoneName != ''">
phoneName = #{phoneName}
</if>
<if test="phoneStorage != null and phoneStorage != ''">
and phoneStorage = #{phoneStorage}
</if>
<if test="phoneMemory != null and phoneMemory != ''">
and phoneMemory = #{phoneMemory}
</if>
</where>
</select>
// if标签 ---> 完成条件查询
List<PhoneUser> selectByCondition(@Param("phoneName") String phoneName
, @Param("phoneStorage") String phoneStorage, @Param("phoneMemory") String phoneMemory);
List<PhoneUser> list = userMapper.selectByCondition("apple", null, null);
==>
Preparing: select id, phoneName, phoneStorage, phoneMemory from test_phone WHERE phoneName = ?
if标签可以帮助我们去做判断是否满足某个条件:
- 在
<if test="">
引号中,我们可以不使用转义字符,可以使用OGNL表达式的写法
转义字符(推荐) OGNL表达式
// > > gt
// < < lt
// >= >= gte
// <= <= lte
// !=
// ==
// and
// or
注意:
- test属性中合法的写法:
- 输入映射可以写的值,那么在test属性中可以直接使用
- 如果有多个条件,使用 and或or来连接
choose when otherwise标签
-
choose when otherwise就相当于Java中的
if .... else....
-
语法构成:
<choose>
<when test="它的用法和if标签中的test一样">
if拼接的sql语句
</when>
<otherwise>
else拼接的sql语句
</otherwise>
</choose>
eg:
</select>
<select id="selectByMemory" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
select id, phoneName, phoneStorage, phoneMemory from test_phone
<where>
<choose>
<when test="phoneMemory == 8">
phoneMemory >= #{phoneMemory}
</when>
<otherwise>
phoneMemory = #{phoneMemory}
</otherwise>
</choose>
</where>
</select>
// choose when otherwise 标签
// 如果传入的memory为8,则执行的条件为 where memory >= #{memory}
// 如果传入的memory不为8,则执行的条件为 where memory = #{memory}
List<PhoneUser> selectByMemory(@Param("phoneMemory") String phoneMemory);
/**
* ==> Preparing: select id, phoneName, phoneStorage, phoneMemory from test_phone WHERE phoneMemory >= ?
* ==> Parameters: 8(String)
*/
List<PhoneUser> phoneUserList1 = userMapper.selectByMemory("8");
/**
* ==> Preparing: select id, phoneName, phoneStorage, phoneMemory from test_phone WHERE phoneMemory = ?
* ==> Parameters: 16(String)
*/
List<PhoneUser> phoneUserList2 = userMapper.selectByMemory("16");
sql-include标签
- 映射文件中出现的相同的SQL可以做一个提取
- 把相同的部分放在SQL标签中,并且对外提供一个id,这段SQL也可以称之为SQL片段
- 使用
include
标签进行引用,refid
属性值为sql标签的id属性值
eg:
<sql id="Base_Phone_Select">
select id, phoneName, phoneStorage, phoneMemory from test_phone
</sql>
使用:
<select id="selectByMemory" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
<include refid="Base_Phone_Select"></include>
<where>
<choose>
<when test="phoneMemory == 8">
phoneMemory >= #{phoneMemory}
</when>
<otherwise>
phoneMemory = #{phoneMemory}
</otherwise>
</choose>
</where>
</select>
优点:
- 避免写
select *
- 避免浪费网络资源
- 不破坏SQL语句的可读性
业务场景举例:
- 条件更新(插入)
- 更新对象中成员变量不为空的列
- in语句
- in语句举例:
select * from User where id in (1, 2, 3);
- 这样的参数怎么通过输入映射传进来
- in语句举例:
trim标签
- trim标签:里面写的是SQL语句(包含标签),在其内容的最前面和最后面可以增加指定字符,在其内容最前面和最后面也可以去除指定字符
- trim标签可以帮助我们动态的去增加指定的字符,或者是删除指定的字符
- 拼接属性:
prefix
:增加指定的前缀suffix
:增加指定的后缀
- 省略、去除属性:
prefixOverrides
:删除指定的前缀suffixOverrides
:删除指定的后缀
- 拼接属性:
eg:
<update id="updateByPrimaryKey">
update test_phone
set
phoneName = #{phoneName}, phoneStorage = #{phoneStorage},
phoneMemory = #{phoneMemory}
where id = #{id}
</update>
<update id="updateSelectiveByPrimaryKey">
update test_phone
set
<trim suffixOverrides=",">
<if test="phoneName != null">
phoneName = #{phoneName},
</if>
<if test="phoneStorage != null">
phoneStorage = #{phoneStorage},
</if>
<if test="phoneMemory != null">
phoneMemory = #{phoneMemory}
</if>
</trim>
where id = #{id}
</update>
!!!
<update id="updateSelectiveByPrimaryKey">
/*也可以将set写入到trim标签中*/
update test_phone
<trim prefix="set" suffixOverrides=",">
/**
* 这里会将其他的列(没有update的列)更新为null
*/
@Test
public void testUpdateByPrimaryKey(){
PhoneUser phoneUser = new PhoneUser();
phoneUser.setPhoneName("xiaomi");
phoneUser.setPhoneStorage("1TB");
phoneUser.setId(1);
SqlSession sqlSession = MybatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int i = userMapper.updateByPrimaryKey(phoneUser);
System.out.println("i = " + i);
}
/**
* PhoneUser中的成员变量为null的项,不更新它所对应的列
*/
/**
* 修改前:
* update test_phone set phoneName = ?, phoneStorage = ?,where id = ?
* where前面多了一个逗号
* trim标签修改后:
* update test_phone set phoneName = ?, phoneStorage = ? where id = ?
*
*/
@Test
public void testUpdateByPrimaryKey1(){
PhoneUser phoneUser = new PhoneUser();
phoneUser.setPhoneName("xiaomi");
phoneUser.setPhoneStorage("512GB");
phoneUser.setId(1);
SqlSession sqlSession = MybatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int i = userMapper.updateSelectiveByPrimaryKey(phoneUser);
System.out.println("i = " + i);
}
set标签
<set>
就相当于 <trim prefix="SET" suffixOverrides=",">
这个配置,它俩是等价的
eg:
<update id="updateSelectiveByPrimaryKey1">
update test_phone
<set>
<if test="phoneName != null">
phoneName = #{phoneName},
</if>
<if test="phoneStorage != null">
phoneStorage = #{phoneStorage},
</if>
<if test="phoneMemory != null">
phoneMemory = #{phoneMemory},
</if>
</set>
where id = #{id}
</update>
==> Preparing: update test_phone SET phoneName = ?, phoneStorage = ? where id = ?
foreach标签
- 作用:做的是遍历,在遍历的过程中,拼接foreach标签中的内容
- 做的是数组和集合类的遍历
- foreach标签以及里面的属性:
collection
属性:- 如果Mapper接口中的方法的形参使用了
@Param
,用的就是@Param注解的属性值 - 如果没有使用注解:如果是数组,则写array;如果是list就写list
- 如果Mapper接口中的方法的形参使用了
item
属性:自己定义,在foreach标签内可以使用#{}
方式使用该值open
、close
属性:最前和最后去拼接字符,拼接一次separator
属性:每两个遍历的内容之间的补充字符;拼接次数为n-1次
eg:
<foreach collection="需要遍历的内容,比如数组或集合类list等"
item="遍历过程中的单项值"
open="在foreach标签最左侧拼接字符"
close="foreach标签最右侧拼接值"
separator="每两个遍历的内容之间的补充字符"
index="下标,没啥用">
会被遍历拼接的内容
</foreach>
使用in查询
eg:
// 以下几种写法,collection属性值应该如何写
// 无注解数组 -> collection = "array"
List<PhoneUser> selectByIds1(Integer[] ids);
// 有注解数组 -> collection = "ids"
List<PhoneUser> selectByIds2(@Param("ids") Integer[] ids);
// 无注解list -> collection = "list"
List<PhoneUser> selectByIds3(List<Integer> ids);
// 有注解list -> collection = "ids"
List<PhoneUser> selectByIds4(@Param("ids") List<Integer> ids);
<sql id="Base_User_Select">
select id, phonename, phonestorage, phonememory from test_phone
</sql>
<select id="selectByIds1" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
<include refid="Base_User_Select"/>
<where>
/* id in (?, ?, ?)
? 出现n次 --> foreach标签中的内容
, 出现n-1次 --> separator中的内容
() 都出现1次 --> open、close
*/
id in
<foreach collection="array" item="singleId" separator="," open="(" close=")">
#{singleId}
</foreach>
</where>
</select>
<select id="selectByIds2" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
<include refid="Base_User_Select"/>
<where>
/* id in (?, ?, ?)
? 出现n次 --> foreach标签中的内容
, 出现n-1次 --> separator中的内容
() 都出现1次 --> open、close
*/
id in
<foreach collection="ids" item="singleId" separator="," open="(" close=")">
#{singleId}
</foreach>
</where>
</select>
<select id="selectByIds3" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
<include refid="Base_User_Select"/>
<where>
/* id in (?, ?, ?)
? 出现n次 --> foreach标签中的内容
, 出现n-1次 --> separator中的内容
() 都出现1次 --> open、close
*/
id in
<foreach collection="list" item="singleId" separator="," open="(" close=")">
#{singleId}
</foreach>
</where>
</select>
<select id="selectByIds4" resultType="com.coo1heisenberg.demo3.bean.PhoneUser">
<include refid="Base_User_Select"/>
<where>
/* id in (?, ?, ?)
? 出现n次 --> foreach标签中的内容
, 出现n-1次 --> separator中的内容
() 都出现1次 --> open、close
*/
id in
<foreach collection="ids" item="singleId" separator="," open="(" close=")">
#{singleId}
</foreach>
</where>
</select>
@Test
public void testForeachIn(){
SqlSession sqlSession = MybatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Integer> ids = Arrays.asList(1, 2, 3);
List<PhoneUser> phoneUsers = userMapper.selectByIds3(ids);
// ==> Preparing: select id, phonename, phonestorage, phonememory from test_phone WHERE id in ( ? , ? , ? )
System.out.println("phoneUsers = " + phoneUsers);
Integer[] ids1 = new Integer[3];
ids1[0] = 1;
ids1[1] = 2;
ids1[2] = 3;
List<PhoneUser> phoneUsers1 = userMapper.selectByIds1(ids1);
System.out.println("phoneUsers1 = " + phoneUsers1);
}
}
-
业务场景举例:
- 批量插入
- 传入PhoneUser的多条记录(eg:
PhoneUser[]
、List<PhoneUser>
、Set<PhoneUser>
…) - 想要通过一条SQL语句一起插入到数据库
- 传入PhoneUser的多条记录(eg:
- 批量插入
批量插入
举例:
-- 哪些内容出现了n次 → foreach标签中 → (?,?,?,?,?,?)
-- 哪些内容出现了n-1次 → separator属性中 → ,
-- 哪些内容出现了1次 → open、close属性中 → 无
insert into test_Phone (phoneName, phoneStorage, phoneMemory) values (?, ?, ?),(?, ?, ?),
(?, ?, ?),(?, ?, ?)
eg:
// 批量插入
// 返回值为插入到表中对象的数量
int insertPhoneUser(@Param("phoneUsersList") List<PhoneUser> phoneUsersList);
<insert id="insertPhoneUser">
insert into test_phone (phoneName, phoneStorage, phoneMemory)
values
<!--(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?)-->
<foreach collection="phoneUsersList" item="phoneUser" separator=",">
(#{phoneUser.phoneName}, #{phoneUser.phoneStorage}, #{phoneUser.phoneMemory})
</foreach>
</insert>
selectKey标签
- 额外执行一个查询,并且将查询的结果给到输入映射传入的参数
- 这个标签可以帮助我们在执行目标SQL语句之前或者是之后执行一条额外的SQL语句
- selectKey标签中的属性:
keyColumn
:查询结果集中的列名keyProperty
:封装给谁 → 和输入映射可写的值有关系 → user 、user.xxxresultType
:查询结果的类型标签中
:额外执行的sql语句order
:顺序,相较于当前的sql语句,额外的查询的相对顺序- 写
before
或者after
- 写
<selectKey keyColumn="" keyProperty="" resultType="" order="">
</selectKey>
eg:
// 插入一条数据,并且获得它的自增的主键值
// 要用到select LAST_INSERT_ID();
int insertOnePhoneUser(@Param("phoneUser") PhoneUser phoneUser);
<insert id="insertOnePhoneUser">
<selectKey keyColumn="lastid" keyProperty="phoneUser.id" resultType="integer" order="AFTER">
select LAST_INSERT_ID() as lastid;
</selectKey>
insert into test_phone (phoneName, phoneStorage, phoneMemory)
values (#{phoneUser.phoneName}, #{phoneUser.phoneStorage}, #{phoneUser.phoneMemory})
<!--在执行完insert之后,执行select LAST_INSERT_ID(); 这个查询是有结果的,这个结果要给到userid-->
</insert>
useGeneratedKeys标签
- useGeneratedKeys: 获取insert/update操作数据的主键
- 需要使用到两个属性:
useGeneratedKeys
、keyProperty
eg:
<insert id="insertOnePhoneUser1" useGeneratedKeys="true" keyProperty="phoneUser.id">
insert into test_phone (phoneName, phoneStorage, phoneMemory)
values (#{phoneUser.phoneName}, #{phoneUser.phoneStorage}, #{phoneUser.phoneMemory})
</insert>
多表查询
一对一结构
方式一:分次查询
- 就是分别查询两个表的信息再做拼接
- eg:
select * from user where username = 'xxx';
select * from user_detail where user_id = X;
- eg:
- 构建bean软件包下的class类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String phoneName;
private String phoneStorage;
private String phoneMemory;
private UserDetail userDetail;
}
@Data
public class UserDetail {
Integer id;
String price;
String color;
Integer phone_id;
}
Usermapper
接口
public interface UserMapper {
User selectByPrimaryKey(Integer id);
// 查询到User的信息,也能查询到userDetail的信息
User selectUserByUserName(@Param("phoneName") String phoneName);
User[] selectUsersByInIdArray(@Param("ids") Integer[] ids);
}
public interface UserDetailMapper {
UserDetail selectByUserId(Integer phone_id);
}
- 写SQL语句
<mapper namespace="com.coo1heisenberg.demo1.mapper.UserDetailMapper">
<select id="selectByUserId" resultType="com.coo1heisenberg.demo1.bean.UserDetail">
select id, price, color, phone_id from test_phone_detail where phone_id = #{phone_id}
</select>
<mapper namespace="com.coo1heisenberg.demo1.mapper.UserMapper">
<resultMap id="userMap" type="com.coo1heisenberg.demo1.bean.User">
<id column="id" property="id"/>
<result column="phoneName" property="phoneName"/>
<result column="phoneStorage" property="phoneStorage"/>
<result column="phoneMemory" property="phoneMemory"/>
<!--
select属性:第二次查询的坐标(命名空间 + id)
column属性:给select属性对应的查询所提供的参数
property属性:封装给哪一个成员变量
-->
<association property="userDetail" column="id"
select="com.coo1heisenberg.demo1.mapper.UserDetailMapper.selectByUserId"/>
</resultMap>
<select id="selectByPrimaryKey" resultMap="userMap">
select id, phoneName, phoneStorage, phoneMemory from test_phone where id = #{id}
</select>
<select id="selectUserByUserName" resultMap="userMap">
select id, phoneName, phoneStorage, phoneMemory from test_phone
where phoneName = #{phoneName}
</select>
<select id="selectUsersByInIdArray" resultMap="userMap">
select id, phoneName, phoneStorage, phoneMemory
from test_phone
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
- 测试文件
@Test
public void testSelectUser() {
SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SqlSession sqlSession1 = MyBatisUtil.getSqlSessionFactory().openSession(true);
UserDetailMapper userDetailMapper = sqlSession1.getMapper(UserDetailMapper.class);
User user = userMapper.selectByPrimaryKey(2);
/**
* 1. 根据userDetailMapper.selectByUserId这样的坐标在映射文件中执行SQL语句
* 2. 依据的参数 -> 上一次查询它的查询结果集中id这一列的值
* 3. 查询结果封装为userDetail的实例
* 4. 通过set方法(成员变量)来进行封装
* -->
* 提取几个关键词:坐标(命名空间 + id)、结果集中的列名、set方法(成员变量)
*/
/**
* ==> Preparing: select id, phoneName, phoneStorage, phoneMemory from test_phone where id = ?
* ==> Parameters: 2(Integer)
*
* ====> Preparing: select id, price, color, phone_id from test_phone_detail where phone_id = ?
* ====> Parameters: 2(Integer)
*/
// UserDetail userDetail = userDetailMapper.selectByUserId(user.getId());
//
// user.setUserDetail(userDetail);
System.out.println(user);
}
@Test
public void testSelectByUserName() {
SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserByUserName("xiaomi");
System.out.println(user);
}
@Test
public void testSelectByIdArray() {
SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User[] users = userMapper.selectUsersByInIdArray(new Integer[]{1, 2, 3});
System.out.println(Arrays.asList(users));
}
}
方式二:连接查询
- 合并在一起查询
- eg:
select * from user u left join user_detail ud on u.id = ud.user_id where u.username = 'xxx;'
- eg:
eg:
// 提供左连接查询的方式来查询、封装
User selectUserByUserNameLeftJoin(@Param("phoneName") String phoneName);
<resultMap id="LeftUserMap" type="com.coo1heisenberg.demo1.bean.User">
<id column="id" property="id"/>
<result column="phoneName" property="phoneName"/>
<result column="phoneStorage" property="phoneStorage"/>
<result column="phoneMemory" property="phoneMemory"/>
<!--javaType是一对一的时候使用的,进一步建立映射关系-->
<association property="userDetail" javaType="com.coo1heisenberg.demo1.bean.UserDetail">
<id column="tpdid" property="id"/>
<result column="price" property="price"/>
<result column="color" property="color"/>
<result column="phone_id" property="phone_id"/>
</association>
</resultMap>
<select id="selectUserByUserNameLeftJoin" resultMap="LeftUserMap">
select tp.id, tp.phoneName, tp.phoneStorage, tp.phoneMemory,
tpd.id as tpdid, tpd.price, tpd.color, tpd.phone_id
from test_phone tp
LEFT JOIN test_phone_detail tpd on tp.id = tpd.phone_id
where phoneName = #{phoneName}
</select>
@Test
public void testSelectByUsernameLeftJoin() {
SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(true);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserByUserNameLeftJoin("apple");
System.out.println(user);
}
一对多结构
方式一:分次查询
User
类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String phoneName;
private String phoneStorage;
private String phoneMemory;
private List<UserDetail> userDetails;
}
UserMapper
接口
User selectByPrimaryKeyMany(@Param("id") Integer id);
User selectUserByUserNameMany(@Param("phoneName") String phoneName);
UserMapper.xml
<sql id="Base_User_Select">
select id, phoneName, phoneStorage, phoneMemory from test_phone
</sql>
<resultMap id="UserMap" type="com.coo1heisenberg.demo1.bean.User">
<id column="id" property="id"/>
<result column="phoneName" property="phoneName"/>
<result column="phoneStorage" property="phoneStorage"/>
<result column="phoneMemory" property="phoneMemory"/>
<!--一对多要用collection标签-->
<collection property="userDetails" column="id"
select="com.coo1heisenberg.demo1.mapper.UserDetailMapper.selectByUserIdMany"/>
</resultMap>
<select id="selectByPrimaryKeyMany" resultMap="UserMap">
<include refid="Base_User_Select"/>
where id = #{id}
</select>
<select id="selectUserByUserNameMany" resultMap="UserMap">
<include refid="Base_User_Select"/>
<where>
phoneName = #{phoneName}
</where>
</select>
方式二:连接查询
UserMapper
接口
// 使用连接查询来做
User[] selectUserByIdArrayLeftJoin(@Param("ids") Integer[] ids);
UserMapper.xml
<resultMap id="UserMap2" type="com.coo1heisenberg.demo1.bean.User">
<id column="id" property="id"/>
<result column="phoneName" property="phoneName"/>
<result column="phoneStorage" property="phoneStorage"/>
<result column="phoneMemory" property="phoneMemory"/>
<!--ofType是一对多的时候使用的,进一步建立映射关系-->
<collection property="userDetails" ofType="com.coo1heisenberg.demo1.bean.UserDetail">
<id column="id" property="id"/>
<result column="price" property="price"/>
<result column="color" property="color"/>
<result column="phone_id" property="phone_id"/>
</collection>
</resultMap>
<select id="selectUserByIdArrayLeftJoin" resultMap="UserMap2">
select tp.id,
tp.phoneName,
tp.phoneStorage,
tp.phoneMemory,
tpd.id,
tpd.price,
tpd.color,
tpd.phone_id
from test_phone tp
LEFT JOIN test_phone_detail tpd on tp.id = tpd.phone_id
where tp.id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
多对多结构
- 建表
@Data
public class Course {
Integer id;
String name;
List<Student> students;
}
@Data
public class Student {
Integer id;
String name;
List<Course> courses;
}
UserMapper
接口
public interface CourseMapper {
List<Course> selectByStudentId(@Param("stuId") Integer stuId);
Course selectByName(@Param("name") String name);
}
public interface StudentMapper {
Student selectByName(@Param("name") String name);
}
UserMapper.xml
<mapper namespace="com.coo1heisenberg.demo2.mapper.CourseMapper">
<select id="selectByStudentId" resultType="com.coo1heisenberg.demo2.bean.Course">
select tc.id,tc.name from test_course tc
LEFT JOIN test_relation tr on tr.c_id = tc.id
where tr.stu_id = #{stuId}
</select>
<resultMap id="courseMap" type="com.coo1heisenberg.demo2.bean.Course">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="students" ofType="com.coo1heisenberg.demo2.bean.Student">
<id column="tid" property="id"/>
<result column="tname" property="name"/>
</collection>
</resultMap>
<select id="selectByName" resultMap="courseMap">
select tc.id, tc.name,
tt.id as tid, tt.name as tname
from test_course tc
LEFT JOIN test_relation tr on tc.id = tr.c_id
LEFT JOIN test_student tt on tt.id = tr.stu_id
where tc.name = #{name}
</select>
</mapper>
<mapper namespace="com.coo1heisenberg.demo2.mapper.StudentMapper">
<resultMap id="studentMap" type="com.coo1heisenberg.demo2.bean.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="courses" column="id" select="com.coo1heisenberg.demo2.mapper.CourseMapper.selectByStudentId">
</collection>
</resultMap>
<select id="selectByName" resultMap="studentMap">
select id,name from test_student
where name = #{name}
</select>
</mapper>
懒加载
- 针对于:多表查询
- 是指在Mybatis进行分次查询的时候,假如第二次查询的内容没有被使用到的话,那么就不去执行第二次查询的SQL语句,等到用到第二次查询的内容的时候再去执行第二条SQL语句
- 总开关配置:mybatis的主配置文件里面的settings里面
<settings>
<!-- 懒加载 true: 表示开启 false:默认值,表示关闭 -->
<setting name="xxxxx" value="true"/>
</settings>
- 注意:
-
- 当局部开关配置的时候,以局部开关的配置为准
-
- 当局部开关没有配置的时候,以总开关的配置为准
-
- 当总开关也没有配置的时候,以默认配置为准(默认配置是关闭懒加载)
-
eg:
<!--这里fetchType有eager(立即加载)和lazy(懒加载)两个选项,就是局部开关-->
<collection column="id" property="xxx" fetchType="eager"
select="com.coo1heisenberg.mapper.UserMapper.selectOrdersByUserId"/>
缓存
- 缓存是指:在Mybatis中,单独开辟一块内存空间(map),来存储查询的信息。后续假如再次调用了到了同样的查询,那么就直接查询缓存。
- MyBatis默认开启了缓存
- MyBatis是怎么存储缓存的: 在MyBatis中缓存是以Map(集合类容器)接口存储的
- key:SQL语句和查询的条件(注意: SQL语句是依赖于坐标的)
- value:查询的结果
一级缓存
- 一级缓存是一个以SqlSession管理的Mapper级别的缓存,缓存的内容存储在sqlSession中管理
- 配置:一级缓存默认是开启的,并且没有提供开关给用户关闭(不可以关闭)
- 一级缓存失效:
SqlSession
关闭的时候SqlSession
调用增删改的时候SqlSession
调用commit方法
- 情况:
- 同一个SqlSession 获取的同一个Mapper:走缓存
- 同一个SqlSession获取不同的Mapper:走缓存
- 不同SqlSession获取不同的Mapper:不走缓存
eg:
@Test
public void mytest1() {
//使用同一个sqlSession的同一个Mapper可以使用到一级缓存
User user1 = userMapper.selectByPrimaryKey(1);//生成缓存
User user2 = userMapper.selectByPrimaryKey(1);//使用缓存
User user3 = userMapper.selectByPrimaryKey(1);//使用缓存
User user4 = userMapper.selectByPrimaryKey(1);//使用缓存
User user5 = userMapper.selectByPrimaryKey(1);//使用缓存
}
@Test
public void mytest2() {
//使用同一个sqlSession的不同Mapper可以使用到一级缓存
UserMapper userMapper1 = sqlSession.getMapper(UserMapper.class);
UserMapper userMapper2 = sqlSession.getMapper(UserMapper.class);
User user1 = userMapper1.selectByPrimaryKey(1);//生成缓存
User user2 = userMapper1.selectByPrimaryKey(1);//使用缓存
User user3 = userMapper2.selectByPrimaryKey(1);//使用缓存
User user4 = userMapper2.selectByPrimaryKey(1);//使用缓存
}
@Test
public void mytest3() {
//如果sqlSession发生变化,使用不到一级缓存
SqlSession sqlSession1 = sqlSessionFactory.openSession();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
User user1 = userMapper1.selectByPrimaryKey(1);//生成缓存
User user2 = userMapper1.selectByPrimaryKey(1);//使用缓存
User user3 = userMapper2.selectByPrimaryKey(1);//生成缓存
User user4 = userMapper2.selectByPrimaryKey(1);//使用缓存
}
@Test
public void mytest4() {
//使用同一个sqlSession的同一个Mapper可以使用到一级缓存
User user1 = userMapper.selectByPrimaryKey(1);//生成缓存
User user2 = userMapper.selectByPrimaryKey(1);//使用缓存
User user3 = userMapper.selectByPrimaryKey(1);//使用缓存
sqlSession.commit(); //缓存失效
User user4 = userMapper.selectByPrimaryKey(1);//生成缓存
User user5 = userMapper.selectByPrimaryKey(1);//使用缓存
}
二级缓存
- 二级缓存是一个NameSpace级别(
mapper.xml
)的缓存,每一个NameSpace都有自己的单独的缓存空间。(要通过两级配置开启) - 配置1:总开关
<settings>
<!-- 二级缓存开关配置 -->
<setting name="cacheEnabled" value="true"/>
<settings/>
- 配置2: 局部开关
- 注意:
-
- 二级缓存是 namespace级别/Mapper级别 的缓存
-
- 多个SqlSession可以共用二级缓存(同一个Mapper)
-
- 在关闭sqlsession后(close); 才会把该sqlsession一级缓存中的数据添加到对应namespace的二级缓存中。
-
- 当Mybatis默认先查询二级缓存,二级缓存中无对应数据,再去查询一级缓存,一级缓存中也没有,最后去数据库查找。
-
- 二级缓存的用处
- 其实有一定的作用,但是也有一定的缺陷
- 确实能够提高Mybatis的性能
- 不能完美的解决脏数据的问题
- 二级缓存空间对于用户来说是完全透明的,我们用户不能够直接的去操作它,也不能够让用户指定去查询数据库还是查询缓存,所以其实使用起来不太方便
- 使用缓存的场景:使用我们的NoSQL数据库(Redis)
- 其实有一定的作用,但是也有一定的缺陷