一、MyBatis高级查询
1.resultMap属性
前言:在建立对象关系映射时,如果实体类中的属性名和数据库表中的字段名一致,使用resultType属性可以将查询结果自动封装到实体类中。
那如果实体类中的属性名和数据库表中的字段名不一致,就需要使用restltMap实现将查询结果手动封装到实体类中。
(1)编写UserMapper接口
package cn.xuguowen.mapper;
import cn.xuguowen.pojo.User;
import java.util.List;
/**
* @author 徐国文
* @create 2021-09-26 14:48
*
*/
public interface UserMapper {
/**
* 查询所有用户
* @return
*/
List<User> findAll();
}
(2)编写UserMapper.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 namespace="cn.xuguowen.mapper.UserMapper">
<!--
1.由于现在实体类中的属性名和表中的字段名不一致,所以不可以使用resultType了
2.resultMap:当实体类中的属性名和表中的字段名不一致时,
mybatis不会自动帮助我们将查询结果封装到实体类中了,
所以需要我们手动将查询结果封装到实体类对象中,一次配置可以多次使用
-->
<!--
1.id:resultMap标签的唯一标识
2.type:要封装的实体类型,因为在核心配置文件中起了别名,所以写的是user
如果没有设置别名的话,就得写实体类得全限定类名了
-->
<resultMap id="userResultMap" type="user">
<!--手动配置映射关系
其中property属性的值是:实体类中的属性名
column属性的值是:表中的字段名
-->
<!--id标签:用来配置主键的-->
<id property="id" column="id"></id>
<!--配置普通属性和字段的-->
<result property="usernameabc" column="username"></result>
<result property="birthdayabc" column="birthday"></result>
<result property="sexabc" column="sex"></result>
<result property="addressabc" column="address"></result>
</resultMap>
<select id="findAll" resultMap="userResultMap">
select * from user
</select>
</mapper>
(3)测试
@Test
public void testFindAll() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
sqlSession.close();
}
2.多条件查询(三种)
需求:根据id和username查询user表
(1)方式一:使用 #{arg0}-#{argn} 或者 #{param1}-#{paramn} 获取参数
①:编写UserMapper接口
/**
* 根据id和用户名查询
* @param id
* @param username
* @return
*/
User findByIdAndUserName1(int id, String username);
②:UserMapper.xml
<!-- 多条件查询方式一 -->
<select id="findByIdAndUserName1" resultMap="userResultMap">
<!--select * from user where id = #{arg0} and username = #{arg1}-->
select * from user where id = #{param1} and username = #{param2}
</select>
③:测试
@Test
public void testFindByIdAndUserName1() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findByIdAndUserName1(1, "王莹");
System.out.println(user);
sqlSession.close();
}
(2)方式二:使用注解,引入 @Param() 注解获取参数
①:编写UserMapper接口
/**
* 根据id和用户名查询方式二
* @param id
* @param username
* @return
*/
User findByIdAndUserName2(@Param("id") int id, @Param("username") String username)
②:UserMapper.xml
<!-- 多条件查询方式二 -->
<select id="findByIdAndUserName2" resultMap="userResultMap">
select * from user where id = #{id} and username = #{username}
</select>
③:测试
@Test
public void testFindByIdAndUserName2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findByIdAndUserName1(1, "王莹");
System.out.println(user);
sqlSession.close();
}
(3)方式三:使用pojo对象传递参数
①:编写UserMapper接口
/**
* 根据id和用户名查询方式三
* @param user
* @return
*/
User findByIdAndUserName3(User user);
②:UserMapper.xml
<!-- 多条件查询方式三 -->
<select id="findByIdAndUserName3" resultMap="userResultMap" parameterType="user">
<!-- #{}里的参数要和实体类中getXxx()方法中的Xxx名字相同,首字母小写 -->
select * from user where id = #{id} and username = #{usernameabc}
</select>
③:测试
@Test
public void testFindByIdAndUserName3() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = new User();
user1.setId(1);
user1.setUsernameabc("王莹");
User user = mapper.findByIdAndUserName3(user1);
System.out.println(user);
sqlSession.close();
}
3.模糊查询(两种)
需求:根据username模糊查询user表
(1)方式一:#{}
①:编写UserMapper接口
/**
* 根据username查询用户(模糊查询)
* @param username
* @return
*/
List<User> findByUserName1(String username);
②:UserMapper.xml
<!-- 模糊查询方式一 -->
<select id="findByUserName1" resultMap="userResultMap" parameterType="string">
<!-- SELECT * FROM USER WHERE username LIKE '%王%'; -->
<!-- 当参数类型是基本数据类型或者String类型时,并且只有一个参数时,#{}里面的值可以随便写-->
<!-- #{username}==#{'%王%'} 底层会自动帮助我们加上单引号或者双引号
SELECT * FROM USER WHERE username LIKE '%王%'
-->
select * from user where username like #{username}
</select>
③:测试
@Test
public void testFindByUserName1() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findByUserName1("%王%");
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
(2)方式二:${}
①:编写UserMapper接口
/**
* 根据username查询用户(模糊查询)
* @param username
* @return
*/
List<User> findByUserName2(String username);
②:UserMapper.xml
<select id="findByUserName2" resultMap="userResultMap" parameterType="string">
<!-- ${username}==${%王%} 底层不会帮助我们加单引号或者双引号
select * from user where username like %王%
-->
select * from user where username like '${value}'
</select>
③:测试
@Test
public void testFindByUserName2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findByUserName2("%王%");
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
#{}和${}区别
-
#{}:表示一个占位符
- 通过#{}可以实现preparedStatement向占位符中设置值,自动进行java类型和jdbc类型转换(实体类中属性的类型和表中字段类型的转换),还可以有效防止sql注入问题。
- #{}可以接收简单数据类型的值和pojo属性值
- 如果parameterType是基本数据类型或者String类型的,并且只有一个参数时,#{}括号中的名称随便写
-
${}:表示拼接sql串
- 通过${}可以将parameterType传入的内容拼接在sql语句上,不进行jdbc类型转换,会出现sql注入问题。
- ${}可以接收简单数据类型的值和pojo属性值
- 如果parameterType中的值是基本数据类型或者是String类型的,并且只有一个参数时,${}括号里面只能是value(老版本中)
二、MyBatis映射文件深入
1.返回主键
应用场景:我们很多时候有这种需求,向数据库插入一条记录后,希望能立即拿到这条记录在数据库中的主键值。
(1)方式一:useGeneratedKeys属性
①:编写UserMapper接口
/**
* 新增用户(插入数据),并且获取主键值 方式一
* 在实际开发中,id字段是自增长的,在插入数据之后,我还想要获取到这个用户的id是多少
* @param user
*/
void saveUser1(User user);
②:UserMapper.xml
<!--新增用户,并且获取到主键的值 方式一
useGeneratedKeys="true" 声明返回主键
keyProperty="id" 把返回来的主键值,封装到实体对象的id属性中
-->
<insert id="saveUser1" parameterType="user" useGeneratedKeys="true" keyProperty="id">
<!-- 局限性:只适用于主键自增的数据库 如mysql/sqlserver-->
insert into user (username,birthday,sex,address)
values (#{usernameabc},#{birthdayabc},#{sexabc},#{addressabc})
</insert>
③:测试
@Test
public void testSaveUser1() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsernameabc("尼古拉斯·赵四");
user.setBirthdayabc(new Date());
user.setSexabc("男");
user.setAddressabc("象牙山庄");
System.out.println(user); // id null
mapper.saveUser1(user);
System.out.println(user); // id 7
// 记住手动提交事务
sqlSession.commit();
sqlSession.close();
注意 只适用于主键自增的数据库,mysql和sqlserver支持,oracle不行。
(2)方式二:selectKey标签
①:编写UserMapper接口
/**
* 新增用户(插入数据),并且获取主键值 方式二
* 在实际开发中,id字段是自增长的,在插入数据之后,我还想要获取到这个用户的id是多少
* @param user
*/
void saveUser2(User user);
②:UserMapper.xml
<!-- 新增用户,并且获取到主键的值 方式二-->
<insert id="saveUser2" parameterType="user">
<!--
特点:适用范围广,支持所有类型的数据库
1.order="AFTER":表示执行完sql语句之后再执行
2.keyColumn="id":指定表中主键对应的字段名称
3.keyProperty="id":把返回主键的值,封装到实体类中的id属性上
4.resultType="int":指定表中主键的类型
-->
<selectKey order="AFTER" keyColumn="id" keyProperty="id" resultType="int">
select last_insert_ID()
</selectKey>
insert into user (username,birthday,sex,address)
values (#{usernameabc},#{birthdayabc},#{sexabc},#{addressabc})
</insert>
③:测试
@Test
public void testSaveUser2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsernameabc("詹姆斯·马双");
user.setBirthdayabc(new Date());
user.setSexabc("男");
user.setAddressabc("向阳山庄");
System.out.println(user); // id null
mapper.saveUser2(user);
System.out.println(user); // id 8
// 记住手动提交事务
sqlSession.commit();
sqlSession.close();
}
2.动态SQL
应用场景:当我们要根据不同的条件,来执行不同的sql语句的时候,需要用到动态sql。
(1)动态SQL之 if
需求:根据id和username查询,但是不确定两个都有值
①:编写UserMapper接口
/**
* 动态sql之if
* @param user
* @return
*/
List<User> findByIdAndUsername(User user);
②:UserMapper.xml
<!-- 动态sql if-->
<select id="findByIdAndUsername" parameterType="user" resultMap="userResultMap">
select * from user <!-- where 1 = 1 -->
<!-- 也可以省略where 1 = 1,使用where标签表示恒等,或者也可以理解为:把第一个and关键字去掉 -->
<where>
<if test="id != null">
and id = #{id}
</if>
<!-- 由于我这个User类中的属性名和表中的字段名不一致,
我使用resultMap标签使得实体类属性名和表中字段名对应了起来 -->
<if test="usernameabc != null">
and username = #{usernameabc}
</if>
</where>
</select>
③:测试
/**
* 测试动态sql if 方式一
*/
@Test
public void testFindByIdUsername1() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsernameabc("王莹");
List<User> users = mapper.findByIdAndUsername(user);
for (User user1 : users) {
System.out.println(user1);
}
}
(2)动态SQL之 set
需求:动态更行user表中的数据,如果该属性有值就更新,没有值就不处理。
①:编写UserMapper接口
/**
* 动态更新user表数据,如果该属性有值就更新,没有值不处理
* @param user
*/
void updateSet(User user);
②:UserMapper.xml
<update id="updateSet" parameterType="user">
update user <!--set -->
<!--
1.set标签在更新数据时候,自动加上set关键字,所以上面的sql语句中set关键字可以省略
2.set标签会在 if标签中的sql语句后面要加上 , 而且会去掉最后一个if标签中sql语句的 ,
这样sql语句才对
update user set username = ?,birthday = ?,sex = ?,address = ? where id = ?
-->
<set>
<if test="usernameabc != null">
username = #{usernameabc},
</if>
<if test="birthdayabc != null">
birthday = #{birthdayabc},
</if>
<if test="sexabc != null">
sex = #{sexabc},
</if>
<if test="addressabc != null">
address = #{addressabc},
</if>
</set>
where id = #{id}
</update>
③:测试
/**
* 测试动态更新数据set标签
* @throws IOException
*/
@Test
public void testUpdateSet() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(5);
user.setUsernameabc("刘诗诗");
user.setSexabc("女");
mapper.updateSet(user);
sqlSession.commit();
sqlSession.close();
}
(3)动态SQL之 foreach
foreach主要是用来做数据的循环遍历
例如:select * from user where id in (1,2,3)这样的sql语句中,传入的参数部分必须依靠foreach遍历才能实现。
<foreach>标签用于遍历集合,它的属性:
• collection:代表要遍历的集合
• open:代表语句的开始部分
• close:代表语句的结束部分
• item:代表遍历集合的每个元素,生成的变量名
• sperator:代表分隔符
传入的参数是集合
①:编写UserMapper接口
/**
* 动态sql之foreach标签的使用 多值查询
* @param ids 参数类型是集合
* @return
*/
List<User> findByList(List<Integer> ids);
②:UserMapper.xml
<!-- 动态sql 之foreach标签多值查询 参数类型是集合 -->
<select id="findByList" parameterType="list" resultMap="userResultMap"> <!-- 补充:mybatis给List类型参数起了别名 list -->
<!-- select * from user where id in (1,2,3)-->
select * from user
<where>
<!--
1.collection:代表要遍历的集合 通常写collection/list
2.item:代表要遍历集合中的每个元素的变量名
3.open 代表sql语句的开始部分
4.close 代表sql语句的结束部分
5.separator 表示分割符 1,2,3,要遍历的元素是以 , 分割的
-->
<foreach collection="list" open="id in (" close=")" separator="," item="id">
#{id} <!-- #{id} 循环一次拿到1 2 3 -->
</foreach>
</where>
</select>
③:测试
/**
* 测试动态sql foreach标签多值查询 参数类型是集合
*/
@Test
public void testFindByList() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(5);
ids.add(7);
List<User> users = mapper.findByList(ids);
for (User user : users) {
System.out.println(user);
}
}
传入的参数是数组
①:编写UserMapper接口
/**
* 动态sql之foreach标签的使用 多值查询
* @param ids 参数类型是数组
* @return
*/
List<User> findByArray(Integer[] ids);
②:UserMapper.xml
<!-- 动态sql 之foreach标签多值查询 参数类型是int类型数组-->
<select id="findByArray" parameterType="int" resultMap="userResultMap">
select * from user
<where>
<foreach collection="array" open="id in(" close=")" separator="," item="id">
#{id}
</foreach>
</where>
</select>
③:测试
/**
* 测试动态sql foreach标签多值查询 参数类型是数组
*/
@Test
public void testFindByArray() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer[] ids = new Integer[]{1,2,5};
List<User> users = mapper.findByArray(ids);
for (User user : users) {
System.out.println(user);
}
}
3.SQL片段
应用场景:在映射文件中,可以将重复的sql语句提取出来,使用时使用include标签引用即可,最终达到sql重用的目得。
<!--sql 片段抽取
1.针对于重复的sql语句,我们可以抽取出来,提高复用性
2.提高维护性,需要修改sql语句的话,这样就之修改sql片段中的语句就可以了
-->
<sql id="selectUser">
select * from user
</sql>
<!-- 动态sql 之foreach标签多值查询 参数类型是集合 -->
<select id="findByList" parameterType="list" resultMap="userResultMap"> <!-- 补充:mybatis给List类型参数起了别名 list -->
<!-- select * from user where id in (1,2,3)-->
<!-- select * from user -->
<!-- 使用include引用sql片段中的sql语句 -->
<include refid="selectUser"></include>
<where>
<!--
1.collection:代表要遍历的集合 通常写collection/list
2.item:代表要遍历集合中的每个元素的变量名
3.open 代表sql语句的开始部分
4.close 代表sql语句的结束部分
5.separator 表示分割符 1,2,3,要遍历的元素是以 , 分割的
-->
<foreach collection="list" open="id in (" close=")" separator="," item="id">
#{id} <!-- #{id} 循环一次拿到1 2 3 -->
</foreach>
</where>
</select>
<!-- 动态sql 之foreach标签多值查询 参数类型是int类型数组-->
<select id="findByArray" parameterType="int" resultMap="userResultMap">
<include refid="selectUser"></include>
<where>
<foreach collection="array" open="id in(" close=")" separator="," item="id">
#{id}
</foreach>
</where>
</select>
三、MyBatis核心配置文件深入
1.plugins标签
MyBatis可以使用第三方的插件来对功能进行扩展,分页助手PageHelper是将分页的复杂操作进行封装,使用简单的方式即可获得分页的相关数据。
开发步骤
①:导入通用PageHelper依赖坐标
<!-- 导入分页助手的依赖坐标-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
②:在mybatis核心配置文件中配置PageHelper插件
<!--plugin标签中可以配置自定义插件,也可以导入第三方插件
注意 plugins标签配置的顺序,可不能随便
-->
<plugins>
<!--1.interceptor:PageHelper全路径-->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!--
不同的数据库产品分页关键字是不同的
name:dialect 方言的意思
value:mysql 表示使用mysql下的分页关键字 limit
-->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
③:测试分页代码实现
/**
* 测试分页助手
* @throws IOException
*/
@Test
public void testFindAllPageHelper() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 在调用查询之前,一定要进行分页参数的设置
// 参数一pageNum:表示当前是第几页
// 参数二pageSize:表示每页显示的条数
PageHelper.startPage(1, 2);
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
// 获取其他的分页信息
PageInfo<User> pageInfo = new PageInfo<>(all); // 泛型和传入list集合的泛型一致
System.out.println("总条数:" + pageInfo.getTotal());
System.out.println("总页数:" + pageInfo.getPages());
System.out.println("当前页:" + pageInfo.getPageNum());
System.out.println("每页显示的长度:" + pageInfo.getPageSize());
System.out.println("是否是第一页:" + pageInfo.isIsFirstPage());
System.out.println("是否是最后一页:" + pageInfo.isIsLastPage());
}
四、MyBatis多表查询
1.数据库表关系介绍
关系型数据库表关系分为
- 一对一 人和身份证号就是一对一
- 一对多 用户和订单就是一对多(一个用户可以下多个订单),订单和用户就是多对一(多个订单属于同一个用户)
- 多对多 学生和课程就是多对多
- 特例:从数据层面看,一个订单只从属于一个用户,所以MyBatis将多对一看成了一对一
案例环境准备:
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ordertime` VARCHAR(255) DEFAULT NULL,
`total` DOUBLE DEFAULT NULL,
`uid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2020-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2020-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2020-12-12', '5000', '2');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`rolename` VARCHAR(255) DEFAULT NULL,
`roleDesc` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` INT(11) NOT NULL,
`roleid` INT(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role`
(`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user`
(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');
2.一对一(多对一)
①:介绍
一对一查询模型 用户表和订单表的关系为:一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求 查询所有订单,与此同时查询出每个订单所属的用户
一对一查询语句
SELECT * FROM orders o LEFT JOIN USER u ON o.uid = u.id
②:代码实现
a):Orders实体和User实体
public class Orders {
private Integer id;
private String ordertime;
private double total; // 订单价格
private Integer uid; // 关联user表
// 因为mybatis中把多对一的关系看做是一对一的关系
private User user; // 表示当前订单属于哪个用户
}
public class User {
private Integer id;
private String username;
private Date birthday;
private Character sex;
private String address;
}
b):OrdersMapper接口
public interface OrdersMapper {
/**
* 一对一关联查询:查询所有订单,与此同时还要查询出每个订单所属的用户信息
* @return
*/
List<Orders> findAllWithUser();
}
c):OrdersMapper.xml
<mapper namespace="cn.xuguowen.mapper.OrdersMapper">
<!-- 手动将查询结果封装到orders实体类中 -->
<resultMap id="ordersMap" type="orders">
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
<!--
1.association 在进行一对一关联查询配置时,使用association标签进行关联
property="user" 表示将查询结果封装到实体类中的属性名
javaType="user" 表示将查询结果封装到实体类中的属性的类型是什么类型
-->
<association property="user" javaType="user">
<!-- column可不能写成id,如果写成id,出现重名id-->
<id property="id" column="uid"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
</resultMap>
<select id="findAllWithUser" resultMap="ordersMap">
SELECT * FROM orders o LEFT JOIN USER u ON o.uid = u.id
</select>
</mapper>
d):测试
@Test
public void testFindAllWithUser() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> orders = mapper.findAllWithUser();
for (Orders order : orders) {
System.out.println(order);
}
sqlSession.close();
}
2.一对多
①:介绍
一对多查询模型 用户表和订单表的关系为:一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求 查询所有用户,与此同时查询出每个用户具有的订单
一对一查询语句
-- 查询所有用户,与此同时查询出该用户具有的订单
SELECT u.*,o.id oid,o.ordertime,o.total,o.uid FROM USER u LEFT JOIN orders o ON u.id = o.uid;
②:代码实现
a):Orders实体和User实体
public class User {
private Integer id;
private String username;
private Date birthday;
private Character sex;
private String address;
// 表示多的关系:当前用户具备的订单列表
private List<Orders> ordersList;
}
public class Orders {
private Integer id;
private String ordertime;
private double total; // 订单价格
private Integer uid; // 关联user表
// 因为mybatis中把多对一的关系看做是一对一的关系
private User user; // 表示当前订单属于哪个用户
}
b):UserMapper接口
public interface UserMapper {
/**
* 一对多关联查询:查询所有用户,与此同时查询出该用户具有的订单
* @return
*/
List<User> findAllWithOrders();
}
c):UserMapper.xml
<mapper namespace="cn.xuguowen.mapper.UserMapper">
<resultMap id="userMap" type="cn.xuguowen.pojo.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--
一对多使用collection标签关联
1.property="ordersList" 表示实体类中封装到集合的属性名
2.ofType="cn.xuguowen.pojo.Orders" 表示封装集合的泛型
-->
<collection property="ordersList" ofType="cn.xuguowen.pojo.Orders">
<id property="id" column="oid"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
</collection>
</resultMap>
<select id="findAllWithOrders" resultMap="userMap">
<!-- SELECT * FROM USER u LEFT JOIN orders o ON u.id = o.uid; -->
<!-- 避免主键重名-->
SELECT u.*,o.id oid,o.ordertime,o.total,o.uid FROM USER u LEFT JOIN
orders o ON u.id = o.uid;
</select>
</mapper>
d):测试
@Test
public void testFindAllWithOrders() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithOrders = mapper.findAllWithOrders();
for (User users : allWithOrders) {
System.out.println(users);
}
sqlSession.close();
}
3.多对多
①:介绍
多对多查询模型 用户表和订单表的关系为:一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求 查询所有用户,与此同时查询出每个用户的所有角色
多对一查询语句
-- 查询所有用户,同时查询出该用户的所有角色
SELECT u.*,r.id rid,r.rolename,r.roleDesc FROM USER u LEFT JOIN sys_user_role ur ON u.id = ur.userid
LEFT JOIN sys_role r ON ur.roleid = r.id
②:代码实现
a):User实体和Role实体
public class User {
private Integer id;
private String username;
private Date birthday;
private Character sex;
private String address;
// 表示多的关系:当前用户具备的订单列表
private List<Orders> ordersList;
// 表示多的关系:当前用户具备的角色列表
private List<Role> roleList;
}
public class Role {
private Integer id;
private String rolename;
private String roleDesc;
}
b):UserMapper接口
/**
* 一对多关联查询:查询所有用户,与此同时查询出该用户具有的角色
* @return
*/
List<User> findAllWithRole();
c):UserMapper.xml
<!-- 一对多关联查询:查询所有用户,与此同时查询出该用户具有的角色 -->
<resultMap id="userRoleMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<collection property="roleList" ofType="role">
<id property="id" column="rid"/>
<result property="rolename" column="rolename"/>
<result property="roleDesc" column="roleDesc"/>
</collection>
</resultMap>
<select id="findAllWithRole" resultMap="userRoleMap">
SELECT u.*,r.id rid,r.rolename,r.roleDesc FROM USER u
LEFT JOIN sys_user_role ur ON u.id = ur.userid
LEFT JOIN sys_role r ON ur.roleid = r.id
</select>
d):测试
/**.
* 多对多可以看作是 2 个一对多
* 一对多关联查询:查询所有用户,与此同时查询出该用户具有的角色
* @throws IOException
*/
@Test
public void testFindAllWithRole() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithRole = mapper.findAllWithRole();
for (User user : allWithRole) {
System.out.println(user);
}
sqlSession.close();
}
4.总结
* 多对一(一对一)配置:使用<resultMap>+<association>做配置
* 一对多配置:使用<resultMap>+<collection>做配置
* 多对多配置:使用<resultMap>+<collection>做配置
* 多对多的配置跟一对多很相似,难度在于SQL语句的编写。
五、MyBatis嵌套查询
1.什么是嵌套查询
嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询,再使mybatis的语法嵌套在一起。
例如:查询一个订单,与此同时查询出该订单所属的用户信息
2.一对一嵌套查询
①:介绍
需求:查询一个订单,与此同时查询出该订单所属的用户信息
一对一查询语句
-- 1.先查询订单信息
SELECT * FROM orders;
-- 2.再根据订单表中的uid查询用户信息
SELECT * FROM USER WHERE id = #{uid}
②:代码实现
a):OrdersMapper接口
/**
* 一对一嵌套查询:查询所有订单,与此同时还要查询出每个订单所属的用户信息
* @return
*/
List<Orders> findAllWithUser2();
b):OrdersMapper.xml
<!-- 一对一嵌套查询 查询所有订单,与此同时还要查询出每个订单所属的用户信息-->
<resultMap id="ordersMap2" type="orders">
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
<!--写到这里:就要考虑到第二次sql查询语句如何引入,
并且将第一条sql语句查询出来的uid作为参数传递到第二天sql语句中
使用select引入第二条sql语句:值为第二条sql语句的statementid
使用column标签将上面查询出来的uid作为参数传递到第二条sql语句中
-->
<association property="user" javaType="user"
select="cn.xuguowen.mapper.UserMapper.findById" column="uid"/>
</resultMap>
<select id="findAllWithUser2" resultMap="ordersMap2">
<!-- 注意返回结果类型不能使用resultType进行自动封装查询结果,
因为Orders实体类中还存在一个属性 user
-->
select * from orders
</select>
c):在编写OrdersMapper.xml时需要依赖UserMapper接口和UserMapper.xml文件,因为第二条sql语句单独操作user表
UserMapper接口
/**
* 一对一嵌套查询:查询所有订单,与此同时还要查询出每个订单所属的用户信息
* @param id
* @return
*/
User findById(Integer id);
UserMapper.xml
<!-- 一对一嵌套查询 查询所有订单,与此同时还要查询出每个订单所属的用户信息-->
<select id="findById" resultType="user" parameterType="int">
<!-- 参数id由第一条sql语句查询出来的uid进行传递 -->
select * from user where id = #{id}
</select>
d):测试
/**
* 一对一嵌套查询:查询所有订单,与此同时还要查询出每个订单所属的用户信息
* @throws IOException
*/
@Test
public void testFindAllWithUser2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> allWithUser2 = mapper.findAllWithUser2();
for (Orders orders : allWithUser2) {
System.out.println(orders);
}
sqlSession.close();
}
3.一对多嵌套查询
①:介绍
需求:查询所有用户,与此同时查询出该用户具有的订单
一对多查询语句
-- 1.先查询出所用用户
SELECT * FROM USER;
-- 2.根据id查询出每个用户所具有的订单信息
SELECT * FROM orders WHERE uid = #{id}
②:代码实现
a):UserMapper接口
/**
* 一对多嵌套查询:查询所有用户,与此同时查询出该用户具有的订单
* @return
*/
List<User> findAllWithOrders2();
b):UserMapper.xml
<!--一对多嵌套查询:查询所有用户,与此同时查询出该用户具有的订单-->
<resultMap id="userMap2" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<collection property="ordersList" ofType="orders"
select="cn.xuguowen.mapper.OrdersMapper.findById" column="id"/>
</resultMap>
<select id="findAllWithOrders2" resultMap="userMap2">
select * from user
</select>
c):在编写UserMapper.xml时需要依赖OrdersMapper接口和OrdersMapper.xml文件,因为第二条sql语句单独操作orders表
OrdersMapper接口
/**
* 一对多嵌套查询:查询所有用户,与此同时查询出该用户具有的订单
* 根据第一条查询结果中的用户id查询每个用户所具有的订单信息
* @param id
* @return
*/
List<Orders> findById(Integer id);
OrdersMapper.xml
<!--一对多嵌套查询:查询所有用户,与此同时查询出该用户具有的订单-->
<select id="findById" resultType="orders" parameterType="int">
select * from orders where uid = #{id}
</select>
d):测试
/**
* 一对多嵌套查询:查询所有用户,与此同时查询出该用户具有的订单
* @throws IOException
*/
@Test
public void testFindAllWithOrders2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithOrders2 = mapper.findAllWithOrders2();
for (User user : allWithOrders2) {
System.out.println(user);
}
sqlSession.close();
}
4.多对多嵌套查询
①:介绍
需求:查询所有用户,同时查询出该用户的所有角色
多对多查询语句
-- 1.先查询出所用用户
SELECT * FROM USER;
-- 2.根据id查询出每个用户所具有的订单信息
SELECT * FROM orders WHERE uid = #{id}
②:代码实现
a):UserMapper接口
/**
* 多对多嵌套查询:查询所有用户,同时查询出该用户的所有角色
* @return
*/
List<User> findAllWithRole2();
b):UserMapper.xml
<!--多对多嵌套查询:查询所有用户,同时查询出该用户的所有角色-->
<resultMap id="userRoleMap2" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<collection property="roleList" ofType="role"
select="cn.xuguowen.mapper.RoleMapper.findByUid" column="id" />
</resultMap>
<select id="findAllWithRole2" resultMap="userRoleMap2">
select * from user
</select>
c):在编写UserMapper.xml时需要依赖RoleMapper接口和RoleMapper.xml文件
/**
* 多对多嵌套查询:查询所有用户,同时查询出该用户的所有角色
* @param uid 根据第一条sql语句查询结果中的id字段(用户id)查询用户所具有的角色
* @return
*/
List<Role> findByUid(Integer uid);
<mapper namespace="cn.xuguowen.mapper.RoleMapper">
<select id="findByUid" parameterType="int" resultType="role">
SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON r.id = ur.roleid
WHERE ur.userid = #{id}
</select>
</mapper>
d):测试
/**
* 多对多嵌套查询:查询所有用户,同时查询出该用户的所有角色
* @throws IOException
*/
@Test
public void testFindAllWithRole2() throws IOException {
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allWithRole2 = mapper.findAllWithRole2();
for (User user : allWithRole2) {
System.out.println(user);
}
sqlSession.close();
}
③:总结
一对一配置:使用<resultMap>+<association>做配置,通过column条件,执行select查询
一对多配置:使用<resultMap>+<collection>做配置,通过column条件,执行select查询
多对多配置:使用<resultMap>+<collection>做配置,通过column条件,执行select查询
优点:简化多表查询操作
缺点:执行多次sql语句,浪费数据库性能