MyBatis 高级查询
ResultMap 属性resultType - 如果实体的属性名与表中字段名一致,将查询结果自动封装到实体类中
resutlMap - 如果实体的属性名与表中字段名不一致,可以使用 resutlMap 实现手动封装到实体类中
编写 UserMapper 接口
List findAllResultMap();
编写 UserMapper.xml
select * from `user`
代码测试
@Before
public void init() throws IOException {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapConfig.xml"));
sqlSession = sqlSessionFactory.openSession();
mapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void close() {
sqlSession.close();
}
@Test
public void testFindAllResultMap() {
List allResultMap = mapper.findAllResultMap();
for (User user : allResultMap) {
System.out.println(user);
}
}
多条件查询
方式一
使用 #{arg0}-#{argn} 或者 #{param1}-#{paramn} 获取参数
UserMapper 接口
List findByIdAndUsername1(int id, String username);
UserMapper.xml
select * from `user` where id = #{param1} and username = #{param2}
测试
@Test
public void test3() throws IOException {
mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.findByIdAndUsername1(3, "Renda");
for (User user : users) {
System.out.println(user);
}
}
方式二
使用注解,引入 @Param() 注解获取参数
UserMapper 接口
public List findByIdAndUsername2(@Param("id") int id, @Param("username") String username);
UserMapper.xml
select * from `user` where id = #{id} and username = #{username}
测试
@Test
public void test4() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.findByIdAndUsername2(4, "Renda");
for (User user : users) {
System.out.println(user);
}
}
方式三(推荐)
使用 POJO 对象传递参数
UserMapper 接口
List findByIdAndUsername3(User user);
UserMapper.xml
select * from `user` where id = #{id} and username = #{username}
测试
@Test
public void test5() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = new User();
user1.setId(1);
user1.setUsername("Renda");
List users = mapper.findByIdAndUsername3(user1);
for (User user : users) {
System.out.println(user);
}
}
模糊查询
方式一
UserMapper 接口
public List findByUsername1(String username);
UserMapper.xml
select * from `user` where username like #{username}
测试
@Test
public void test6() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.findByUsername1("%Renda%");
for (User user : users) {
System.out.println(user);
}
}
方式二
UserMapper 接口
List findByUsername2(String username);
UserMapper.xml
select * from `user` where username like '${value}'
测试
@Test
public void test7() {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.findByUsername2("%Renda%");
for (User user : users) {
System.out.println(user);
}
}
${} 与 #{} 区别
#{} 表示一个占位符号通过 #{} 可以实现 preparedStatement 向占位符中设置值,自动进行 Java 类型和 JDBC 类型转换,#{} 可以有效防止 SQL 注入。
#{} 可以接收简单类型值或 POJO 属性值。
如果 parameterType 传输单个简单类型值,#{} 括号中名称随便写。
${} 表示拼接 SQL 串通过 ${} 可以将 parameterType 传入的内容拼接在 SQL 中且不进行 JDBC 类型转换,会出现 SQL 注入问题。
${} 可以接收简单类型值或 POJO 属性值。
如果 parameterType 传输单个简单类型值, ${} 括号中只能是 value。
MyBatis 映射文件深入
返回主键
应用场景:向数据库插入一条记录后,希望能立即拿到这条记录在数据库中的主键值。
useGeneratedKeys
注意:只适用于主键自增的数据库,MySQL 和 SQL Server 支持,Oracle 不行。
UserMapper 接口
void saveUser1(User user);
UserMapper.xml
insert into `user` (username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
测试
@Test
public void test8() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("某冰冰");
user.setBirthday(new Date());
user.setAddress("北京昌平");
user.setSex("女");
System.out.println(user);
mapper.saveUser1(user);
System.out.println(user);
sqlSession.commit();
}
selectKey
UserMapper 接口
public void saveUser2(User user);
UserMapper.xml
SELECT LAST_INSERT_ID();
insert into `user` (username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
测试
@Test
public void test9() {
User user = new User();
user.setUsername("汤唯");
user.setBirthday(new Date());
user.setAddress("北京昌平");
user.setSex("女");
System.out.println(user);
mapper.saveUser2(user);
System.out.println(user);
sqlSession.commit();
}
动态 SQL
应用场景:根据不同的条件,来执行不同的 SQL 语句的时候,需要用到动态 SQL。
动态 SQL 之
根据 id 和 username 查询,但是不确定两个都有值。
UserMapper 接口
public List findByIdAndUsernameIf(User user);
UserMapper.xml
select * from `user`
and id = #{id}
and username = #{username}
测试
@Test
public void test10() throws IOException {
User user = new User();
user.setId(7);
user.setUsername("汤唯");
List users = mapper.findByIdAndUsernameIf(user);
for (User user1 : users) {
System.out.println(user1);
}
}
动态 SQL 之
动态更新 user 表数据,如果该属性有值就更新,没有值不做处理。
UserMapper 接口
int updateIf(User user);
UserMapper.xml
update `user`
username = #{username},
birthday = #{birthday},
sex = #{sex},
address = #{address},
where id = #{id}
测试
@Test
public void test11() throws IOException {
User user = new User();
user.setId(1);
user.setUsername("张人大");
user.setAddress("广东广州");
user.setBirthday(new Date());
if (mapper.updateIf(user) > 0) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
sqlSession.commit();
}
动态 SQL 之
标签用于遍历集合,它的属性:collection - 代表要遍历的集合元素
open - 代表语句的开始部分
close - 代表结束部分
item - 代表遍历集合的每个元素,生成的变量名
sperator - 代表分隔符
集合
UserMapper 接口
List findByList(List ids);
UserMapper.xml
select * from `user`
#{id}
测试
@Test
public void test12() {
List ids = new ArrayList<>();
ids.add(1);
ids.add(6);
ids.add(2);
List users = mapper.findByList(ids);
for (User user : users) {
System.out.println(user);
}
}
数组
UserMapper 接口
List findByArray(Integer[] ids);
UserMapper.xml
#{id}
测试
@Test
public void test13() {
Integer[] ids = {2,6,7};
List byArray = mapper.findByArray(ids);
for (User user : byArray) {
System.out.println(user);
}
}
SQL 片段
应用场景:映射文件中可将重复的 SQL 提取出来,使用时用 include 引用即可,最终达到 SQL 重用的目的
select * from user
小结
MyBatis 映射文件配置:
: 查询
: 插入
: 修改
: 删除
: 返回主键
: where条件
: if判断
: for循环
: set 设置
: sql 片段抽取
MyBatis 核心配置文件深入
plugins 标签
MyBatis 可以使用第三方的插件来对功能进行扩展,分页助手 PageHelper 是将分页的复杂操作进行封装,使用简单的方式即可获得分页的相关数据。
开发步骤:导入通用 PageHelper 的坐标
在 MyBatis 核心配置文件中配置 Page Helper 插件
测试分页数据获取
导入通用 PageHelper 坐标
com.github.pagehelper
pagehelper
3.7.5
com.github.jsqlparser
jsqlparser
0.9.1
在 MyBatis 核心配置文件中配置 PageHelper 插件
测试分页代码实现
@Test
public void test14() throws IOException {
// 设置分页参数 // 参数 1: 当前页 // 参数 2: 每页显示的条数 PageHelper.startPage(1, 2);
List users = mapper.findAllResultMap();
for (User user : users) {
System.out.println(user);
}
// 获取分页相关的其他参数 PageInfo pageInfo = new PageInfo(users);
System.out.println("总条数:"+ pageInfo.getTotal());
System.out.println("总页数:"+ pageInfo.getPages());
System.out.println("是否是第一页:"+ pageInfo.isIsFirstPage());
}
小结
MyBatis核心配置文件常用标签:properties标签:该标签可以加载外部的 properties 文件
typeAliases 标签:设置类型别名
environments 标签:数据源环境配置标签
plugins 标签:配置 MyBatis 的插件
MyBatis 多表查询
数据库表关系介绍
关系型数据库表关系一对一
一对多
多对多
举例人和身份证号就是一对一:一个人只能有一个身份证号,一个身份证号只能属于一个人
用户和订单就是一对多,订单和用户就是多对一:一个用户可以下多个订单,多个订单属于同一个用户
学生和课程就是多对多:一个学生可以选修多门课程,一个课程可以被多个学生选修
特例:一个订单只从属于一个用户,所以 MyBatis 将多对一看成了一对一
案例环境准备
USE `mybatis_db`;
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'
);
一对一(多对一)
介绍
一对一查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户。
一对一查询的需求:查询所有订单,与此同时查询出每个订单所属的用户。
从订单角度来看:一个订单 orders 对一个用户 user
一对一查询语句
SELECT * FROM orders o LEFT JOIN `user` u ON o.`uid`=u.`id`
代码实现
Orders 实体
public class Orders {
private Integer id;
private String ordertime;
private Double total;
private Integer uid;
/*** 表示当前订单属于那个用户 association*/
private User user;
// getter and setter ... // toString}
OrderMapper 接口
public interface OrderMapper {
List findAllWithUser();
}
OrderMapper.xml 映射
SELECT * FROM orders o LEFT JOIN `user` u ON o.uid = u.id
测试代码
public class MyBatisTest {
SqlSession sqlSession;
OrderMapper mapper;
@Before
public void init() throws IOException {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapConfig.xml"));
sqlSession = sqlSessionFactory.openSession();
mapper = sqlSession.getMapper(OrderMapper.class);
}
@After
public void close() {
sqlSession.close();
}
@Test
public void test1() {
List orders = mapper.findAllWithUser();
for (Orders order : orders) {
System.out.println(order);
}
}
}
一对多
介绍
一对多查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户。
一对多查询的需求:查询所有用户,与此同时查询出该用户具有的订单。
从用户的角度看:一个用户 user 对多个订单 orders。
一对多查询语句
SELECT u.*, o.id oid, o.ordertime, o.total, o.uid FROM orders o RIGHT JOIN `user` u ON o.uid = u.id
代码实现
User 实体
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
/*** 表示多方关系: 集合 - 代表了当前用户所具有的订单列表 collection*/
private List ordersList;
// getter and setter ... // toString}
UserMapper 接口
public interface UserMapper {
public List findAllWithOrder();
}
UserMapper.xml 映射
-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
SELECT u.*, o.id oid, o.ordertime, o.total, o.uid FROM orders o RIGHT JOIN `user` u ON o.uid = u.id
测试代码
@Test
public void test2() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List userList = userMapper.findAllWithOrder();
for (User user : userList) {
System.out.println(user);
}
}
多对多
介绍
多对多查询的模型
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用。
多对多查询的需求:查询所有用户同时查询出该用户的所有角色。
用户表 - user,中间表 - user_role,角色表 - role。
多对多查询语句
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`;
代码实现
User 和 Role 实体
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List roleList;
...
}
public class Role {
private Integer id;
private String rolename;
private String roleDesc;
...
}
UserMapper 接口
List findAllWithRole();
UserMapper.xml 映射
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`
测试代码
@Test
public void test3() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List allWithRole = userMapper.findAllWithRole();
for (User user : allWithRole) {
System.out.println(user);
}
}
小结
MyBatis 多表配置方式多对一(一对一)配置:使用 + 做配置
一对多配置:使用 + 做配置
多对多配置:使用 + 做配置;多对多的配置跟一对多很相似,难度在于 SQL 语句的编写。
MyBatis 嵌套查询
什么是嵌套查询
嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询,再使用 MyBatis 的语法嵌套在一起。
联合查询
SELECT * FROM orders o LEFT JOIN `user` u ON o.uid=u.id
嵌套查询 - 使用 MyBatis,将以下二步嵌套起来:
# 先查询订单SELECT * FROM orders
# 再根据订单 uid 外键,查询用户SELECT * FROM user WHERE id = # { 根据订单查询的 uid }
一对一嵌套查询
介绍
需求:查询一个订单,与此同时查询出该订单所属的用户
一对一查询语句
-- 先查询订单SELECT * FROM orders;
-- 再根据订单 uid 外键,查询用户SELECT * FROM `user` WHERE id = #{ 订单的 uid };
代码实现
OrderMapper 接口
List findAllWithUser2();
OrderMapper.xml 映射
select="com.renda.mapper.UserMapper.findById" column="uid" fetchType="eager"/>
SELECT * FROM orders
UserMapper 接口
User findById(Integer id);
UserMapper.xml 映射
SELECT * FROM user WHERE id = #{id}
测试代码
@Test
public void test4() {
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List allWithUser2 = orderMapper.findAllWithUser2();
for (Orders orders : allWithUser2) {
System.out.println(orders);
}
}
一对多嵌套查询
介绍
需求:查询所有用户,与此同时查询出该用户具有的订单。
一对多查询语句
-- 先查询用户SELECT * FROM `user`;
-- 再根据用户 id 主键,查询订单列表SELECT * FROM orders where uid = #{ 用户 id };
代码实现
UserMapper 接口
List findAllWithOrder2();
UserMapper.xml 映射
select="com.renda.mapper.OrderMapper.findByUid" >
SELECT * FROM `user`
OrderMapper 接口
List findByUid(Integer uid);
OrderMapper.xml 映射
SELECT * FROM orders WHERE uid = #{uid}
测试代码
@Test
public void test5() throws IOException {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List allWithOrder2 = userMapper.findAllWithOrder2();
for (User user : allWithOrder2) {
System.out.println(user);
}
}
多对多嵌套查询
介绍
需求:查询用户,同时查询出该用户的所有角色
多对多查询语句
-- 先查询用户SELECT * FROM `user`;
-- 再根据用户 id 主键,查询角色列表SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON r.`id` = ur.`rid`
WHERE ur.`uid` = #{ 用户 id };
代码实现
UserMapper 接口
List findAllWithRole2();
UserMapper.xml 映射
select="com.renda.mapper.RoleMapper.findByUid">
SELECT * FROM USER
RoleMapper 接口
public interface RoleMapper {
List findByUid(Integer uid);
}
RoleMapper.xml 映射
-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid = r.id
WHERE ur.userid = #{uid}
测试代码
@Test
public void test6() throws IOException {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List allWithRole2 = mapper.findAllWithRole2();
for (User user : allWithRole2) {
System.out.println(user);
}
}
小结一对一配置:使用 + 做配置,通过 column 条件,执行 select 查询
一对多配置:使用 + 做配置,通过 column 条件,执行 select 查询
多对多配置:使用 + 做配置,通过 column 条件,执行 select 查询
优点:简化多表查询操作。
缺点:执行多次 SQL 语句,浪费数据库性能。想了解更多,欢迎关注我的微信公众号:Renda_Zhang