前言
这篇文章将mybatis框架操作多表的几种情况进行梳理。包括一对一、一对多、多对多三种情况。
1.在数据库中,多表之间的关系,我们用外键来表示;数据库中的表,映射到Java中就是实体类,实体与实体之间的关系,在Java中,我们用引用对象来表示。
2.在多表操作时,mybatis无法将查询到的结果自动封装为对应的实体,所以我们要手动建立数据库与实体之间的映射关系,这也是mybatis多表操作的关键。
一、一对一关系
user表中的一个对象对应多个order对象,
一个order对象对应一个user对象。
1.建立数据表
主表user:
从表orders:
2.建立实体类
user类:
package com.itheima.domain;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
//省略get、set方法
Order类:
package com.itheima.domain;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
private User user;
其中private User user 为orders的外键uid对应的实体对象。
3、配置映射文件
查询谁就配置谁的映射文件,这里要查询order,一个order对应一个user,所以要配置order的映射文件。
<?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="com.itheima.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!--手动指定数据库字段与Java定义的实体间的映射关系
colunm:数据表的字段名称
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>-->
<!--方法2
利用association属性直接将主键user配置好
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>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT * ,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>
</mapper>
4.编写测试方法
@Test
//一对一查询
public void test1() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
//mapper类调用方法实现查询
List<Order> orderList = mapper.findAll();
for (Order order : orderList) {
System.out.println(order);
}
}
二、一对多关系
查询一个user对象,对应多个order对象
编写user的映射文件
<?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="com.itheima.mapper.UserMapper">
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<!--配置集合信息
property:集合名称
JavaType:集合中的数据类型
-->
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT * ,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>
</mapper>
编写测试函数
@Test
//一对多查询
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//mapper类调用方法实现查询
List<User> list = mapper.findAll();
for (User user : list) {
System.out.println(user);
}
}
二、多对多关系
1.建立数据表
数据表之间的多对多关系,需要借助中间表来实现。
已经有两个表,user和sys_role之间存在多对多关系,建立中间表sys_user_role,表中有两个外键:userId和orderId分别指向表user和sys_role.
表:sys_role
中间表:sys_user_role
2.建立实体类
Role类:
public class Role {
private int id;
private String roleName;
private String roleDesc;
User类:
public class User {
private int id;
private String username;
private String password;
private List<Role> roleList;
3.编写user的映射文件
<?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="com.itheima.mapper.UserMapper">
<resultMap id="userRoleMap" type="user">
<id column="userId" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>
</mapper>
4.编写测试方法
@Test
//多对多查询
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置分页参数
//PageHelper.startPage(1,3);
//mapper类调用方法实现查询
List<User> userAndRoleAll = mapper.findUserAndRoleAll();
for (User user : userAndRoleAll) {
System.out.println(user);
}
}