【SpringMVC】MyBatis多表操作与注解开发
一、 多表操作
1. 一对一查询
想象一下你在淘宝，在购买东西后会有个订单支付的页面，下单后用户和订单就关联起来了，同时订单有一个唯一的编号叫订单号。用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
即 order对user是一对一的，反过来，user对order是一对多的
1.1 建一张order表
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` bigint(20) 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;
1.2 user和order的一对一查询
select * ,o.id oid from orders o,user u where o.uid=u.id
这个查询结果看起来很怪，有很多个id，不着急，后面我们在进行配置文件的配置的时候可以将id忽略。
1.3 创建Order和User实体类
User和以前一样
public class Order {
private int id;
private Date ordertime;
private double total;
//代表当前订单从属于哪一个客户
private User user;
1.4 创建OrderMapper接口
public interface OrderMapper {
public List<Order> findAll();
}
1.5 配置OrderMapper.xml
*********** OrderMapper.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="org.example.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column: 数据表ode字段名称
property: 实体的属性名称
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * ,o.id oid from orders o,user u where o.uid=u.id
</select>
<insert id="insert" parameterType="order">
<!-- insert into user (username,password) values(#{username},#{password})-->
insert into orders values(0,#{ordertime},#{total},#{user.id})
</insert>
</mapper>
*********** sqlMapperConfig.xml *************
<!-- 自定义别名-->
<typeAliases>
<typeAlias type="org.example.domain.User" alias="user"></typeAlias>
<typeAlias type="org.example.domain.Order" alias="order"></typeAlias>
</typeAliases>
<!-- 加载映射文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
<mapper resource="mapper/OrderMapper.xml"/>
</mappers>
测试
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orderList = mapper.findAll();
System.out.println(orderList);
或者通过
<association>标签配置user
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column: 数据表ode字段名称
property: 实体的属性名称
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--
property: 当前实体(Order)的属性名称（private User user）
javaType: 当前实体(Order)中的属性的类型(User)
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
2. 一对多查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
2.1 user和orders的一对多查询
select *,o.id oid from user u,orders o where u.id = o.id
image.png
2.2 修改User实体
public class User {
private int id;
private String username;
private String password;
private Date birthday;
private List<Order> orderList;
}
2.3 创建UserMapper
public interface UserMapper {�
List<User> findAll();�
}
2.4 配置UserMapper.xml
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!-- 配置集合信息：-->
<!-- property: 集合名称-->
<!-- ofType: 当前集合中的数据类型-->
<collection property="OrderList" ofType="order">
<!-- 封装order的数据-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<!-- userMapper.findAll-->
<select id="findAll" resultMap="userMap">
select *,o.id oid from user u,orders o where u.id = o.id
</select>
2.5 测试
//一对多测试
@Test
public void test7() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
System.out.println("################################################");
for (User user : userList) {
System.out.println(user);
}
System.out.println("################################################");
sqlSession.commit();
sqlSession.close();
}
打印结果
################################################
User{id=8, username='tom', password='abcd', birthday=Thu Jan 01 08:00:00 CST 1970, orderList=[Order{id=4, ordertime=Thu Nov 05 23:29:26 CST 2020, total=4000.0, user=null}]}
User{id=10, username='lucy', password='hisudhaius', birthday=Thu Jan 01 08:00:00 CST 1970, orderList=[Order{id=5, ordertime=Thu Nov 05 23:29:26 CST 2020, total=4000.0, user=null}, Order{id=8, ordertime=Thu Nov 05 23:32:44 CST 2020, total=3000.0, user=null}]}
User{id=13, username='libai', password='dasnk', birthday=Wed Nov 04 00:18:37 CST 2020, orderList=[Order{id=7, ordertime=Thu Nov 05 23:29:26 CST 2020, total=5000.0, user=null}]}
################################################
多对多查询
再来假设一个场景，小明是一个学生，他同时是班干部和学生会的干部，而学生会干部可以有多个学生，这样学生和学生会干部表就构成了多对多的关系
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
2.1 添加role 和 user_role表
要实现这种多对多的关系，我们不仅需要role角色表，也需要一个中间表，用户和角色表之间的桥梁——用户id和角色id对应的表。
create table role(
id int AUTO_INCREMENT,
rolename varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table user_role(
uid int NOT NULL,
rid int NOT NULL,
PRIMARY KEY (uid , rid),
KEY(rid),
CONSTRAINT user_role_ibfk_1 FOREIGN KEY (uid) REFERENCES user (id),
CONSTRAINT user_role_ibfk_2 FOREIGN KEY (rid) REFERENCES role (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.2 user和role的多对多查询
select u.*,r.*,r.id rid,u.id uid from user u left join user_role ur on u.id=ur.uid inner join role r on ur.rid=r.id
或
select * from user u,user_role ur,role r where u.id = ur.uid and ur.rid =r.id
3.3 创建 role 实体类
public class Role {
private int id;
private String rolename;
}
User类添加
private List<Role>roleList;
UserMapper接口添加
public List<User> findUserAndRoleAll();
3.4 配置UserMapper.xml
<resultMap id="userRoleMap" type="user">
<!-- user信息-->
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!-- user内部的roleList信息-->
<collection property="roleList" ofType="role">
<!-- 封装order的数据-->
<id column="rid" property="id"></id>
<result column="rolename" property="rolename"></result>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
select * from user u,user_role ur,role r where u.id = ur.uid and ur.rid =r.id
</select>
配置sqlMapperConfig.xml，给Role增加别名
<typeAlias type="org.example.domain.Role" alias="role"></typeAlias>
3.5 测试
//多对多测试
@Test
public void test8() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findUserAndRoleAll();
System.out.println("################################################");
for (User user : userList) {
System.out.println(user);
}
System.out.println("################################################");
sqlSession.commit();
sqlSession.close();
}
User{id=14, username='libai', password='dasnk', birthday=Wed Nov 04 01:09:39 CST 2020, orderList=null, roleList=[Role{id=1, rolename='学生'}, Role{id=4, rolename='学生会干事'}]}
User{id=9, username='libai', password='dasnk', birthday=Wed Nov 04 00:46:39 CST 2020, orderList=null, roleList=[Role{id=3, rolename='班主任'}]}
User{id=13, username='libai', password='dasnk', birthday=Wed Nov 04 01:01:32 CST 2020, orderList=null, roleList=[Role{id=3, rolename='班主任'}, Role{id=5, rolename='辅导员'}]}
User{id=7, username='libai', password='dasnk', birthday=Wed Nov 04 00:18:37 CST 2020, orderList=null, roleList=[Role{id=4, rolename='学生会干事'}]}
小结
MyBatis多表配置方式：
- 一对一配置：使用<resultMap>做配置
- 一对多配置：使用<resultMap>+<collection>做配置
- 多对多配置：使用<resultMap>+<collection>做配置
二、 注解开发
这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件了。
我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。
1. 常见的注解
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装
1.2 增删改查
修改MyBatisTest.java，将公共操作抽取出来
private UserMapper userMapper;
private UserMapper userMapper;
private SqlSession sqlSession;
private SqlSessionFactory sqlSessionFactory;
private InputStream resourceAsStream;
@Before
public void before() throws IOException {
resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession();
userMapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void after() {
sqlSession.commit();
sqlSession.close();
}
@Test
public void saveTest() {
User user = new User();
user.setUsername("djjjj");
user.setPassword("asssdddd");
user.setBirthday(new Date());
userMapper.save(user);
}
@Test
public void updateTest() {
User user = new User();
user.setId(16);
user.setUsername("tom");
user.setPassword("dasdasdsadas");
user.setBirthday(new Date());
userMapper.update(user);
}
@Test
public void deleteTest() {
userMapper.delete(12);
}
@Test
public void findByIdTest() {
User user =userMapper.findById(11);
System.out.println(user);
}
@Test
public void findAllTest() {
List<User> userList =userMapper.findAll();
System.out.println("################################################");
for (User user : userList) {
System.out.println(user);
}
System.out.println("################################################");
}
1.3 修改UserMapper.java
@Insert("insert into user values (0,#{username},#{password},#{birthday})")
public void save(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public User findById(int id);
@Select("select * from user ")
public List<User> findAll();
1.4 删除UserMapper.xml
删除了之后，我们没有了配置文件了呀，那怎么做呢。
我们之前在sqlMapperConfig.xml里面配置了
<!-- 加载映射文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
<mapper resource="mapper/OrderMapper.xml"/>
</mappers>
删除了之后我们就得加载映射关系，指定接口所在的包
<!-- 加载映射关系 TODO-->
<mappers>
<!-- 指定接口所在的包-->
<package name="org.example.mapper"/>
</mappers>
1.5 测试原来的增删改查操作
没有问题！说明可以运行。
2. 使用注解实现更复杂的查询
实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用
@Results注解，@Result注解，@One注解，@Many注解
组合完成复杂关系的配置
前面我们用配置文件的方式实现了order和user的一对多查询,同样的我们也可以使用注解实现。
2. 1 一对一查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
对应的sql语句：
select * from orders;
select * from user where id=查询出订单的uid;
1. 修改OrderMapper
public interface OrderMapper {
@Select("select * ,o.id oid from orders o,user u where o.uid=u.id")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "user.id"),
@Result(column = "username",property = "user.username"),
@Result(column = "password",property = "user.password"),
@Result(column = "birthday",property = "user.birthday")
})
public List<Order> findAll();
@Insert("insert into orders values(0,#{ordertime},#{total},#{user.id})")
public void insert(Order order);
}
测试
@Test
public void orderFindAllTest() {
List<Order> orderList = orderMapper.findAll();
System.out.println(orderList);
}
@Test
public void orderInsertTest(){
User user = userMapper.findById(10);
Order order = new Order();
order.setTotal(3000);
order.setUser(user);
order.setOrdertime(new Date());
orderMapper.insert(order);
}
两个都可以正常运行。
我们上面的查询方式是一次查两张表，但是我们也可以通过查order表获取uid，再通过uid去查user表，这种方法怎么实现呢？
不知道你还记不记得，前面我们使用了<association>进行order和user表的一对一查询的封装，前面图中提到了@One属性，就是它的一种替代
2 修改OrderMapper.java
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
@Select("select * ,o.id oid from orders o,user u where o.uid=u.id")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
property = "user",//代表要封装的属性名称
column = "uid",//根据哪个字段去查询user表
javaType = User.class,//要封装的实体类型
//select属性，代表查询哪个接口的方法获得数据
one = @One(select = "org.example.mapper.UserMapper.findById")
)
})
public List<Order> findAll();
可能这种方式用的比较多
2.2 一对多查询
对应的sql语句：
select * from user;
select * from orders where uid=查询出用户的id;
给OrderMapper.java增加一个查询方法
@Select("select * from orders where uid=#{uid}")
public List<Order> findByUid(int uid);
修改UserMapper.java
@Select("select * from user")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
@Result(
property = "orderList",
column = "id",
javaType = List.class,
many = @Many(select = "org.example.mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();
但是这种方法，其实是查到了所有的user，其中有些有orderList，原因在于这其实是一种按顺序的查询方式，先查了user表再查了order表
和
select *,o.id oid from user u,orders o where u.id = o.id是不一样的
2.3 多对多查询
再来回顾一下多对多的查询
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
对应的sql语句：
select * from user;
select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户的id
1. 新建一个RoleMapper接口
public interface RoleMapper {
@Select("select * from user_role ur,role r where ur.rid = r.id and ur.uid = #{uid}")
public List<Role> findByUid(int id);
}
2. 修改UserMapper
@Select("select * from user")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
@Result(
property = "roleList",
column = "id",
javaType = List.class,
many = @Many(select = "org.example.mapper.RoleMapper.findByUid")
)
})
public List<User> findUserAndRoleAll();
这样子，就有个很怪的问题，注解查询每次都返回了全部的user，我不需要这么多的user啊，我只想要和role关联的那几个user，这会是一个好的方法吗。
