Mybatis多表查询
1. 一对一查询
模型
用户表和订单表的关系为:一个用户可以有多个订单,一个订单只属于一个用户
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
sql语句
SELECT *, o.id oid
FROM orders o,
USER u
WHERE o.uid = u.id
查询结果
接口:
List<Order> findAll();
mapper文件
<?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">
<!--namespace:dao接口中的全限定名称-->
<mapper namespace="pdsu.edu.mybatis.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column:数据表的字段名称
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>-->
<!--第二种方式-->
<!--
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>
<!--使用insert,update,delete,select标签写sql-->
<select id="findAll" resultMap="orderMap">
SELECT *, o.id oid
FROM orders o,
USER u
WHERE o.uid = u.id
</select>
</mapper>
Order
public class Order {
private int id;
private Date ordertime;
private double total;
private User user;
生成对应的get,set方法
}
测试方法:
@Test
public void testSelectById() {
//1.获取SqlSession对象
SqlSession session = MybatisUtil.getSqlSession();
//2.获取dao代理
OrderMapper mapper = session.getMapper(OrderMapper.class);
List<Order> orderList = mapper.findAll();
orderList.forEach(order -> System.out.println(order));
//3.关闭SqlSession对象
session.close();
}
工具类:
/*
* 工具类:创建SqlSession对象
* */
public class MybatisUtil {
private static SqlSessionFactory factory = null;
static {
String config = "mybatis.xml";
try{
InputStream inputStream = Resources.getResourceAsStream(config);
factory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (IOException e){
e.printStackTrace();
}
}
//创建方法:SqlSession对象
public static SqlSession getSqlSession(){
SqlSession session = null;
if (factory!=null){
session = factory.openSession();//
}
return session;
}
}
2. 一对多查询
模型
用户表和订单表的关系为,一个用户可以有多个订单,一个订单只属于一个用户
一对多查询的需求:查询一个用户,于此同时查出该用户拥有的订单
sql语句
SELECT * ,o.id oid FROM user u,orders o WHERE u.id = o.uid
查询结果
接口
List<User> findAll();
mapper
<?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">
<!--namespace:dao接口中的全限定名称-->
<mapper namespace="pdsu.edu.mybatis.mapper.UserMapper">
<resultMap id="userMapper" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--配置集合信息-->
<!--
property:集合名称
ofType:当前集合中的数据类型
-->
<collection property="orderList" ofType="order">
<!--封装order的数据-->
<id column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
</collection>
</resultMap>
<!--使用insert,update,delete,select标签写sql-->
<select id="findAll" resultMap="userMapper">
SELECT * ,o.id oid FROM user u,orders o WHERE u.id = o.uid
</select>
</mapper>
User
public class User {
private int id;
private String username;
private String password;
private String birthday;
//描述的是当前用户存在那些订单
private List<Order> orderList;
生成对应的get和set方法
}
测试
@Test
public void test2() {
//1.获取SqlSession对象
SqlSession session = MybatisUtil.getSqlSession();
//2.获取dao代理
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
userList.forEach(user -> System.out.println(user));
//3.关闭SqlSession对象
session.close();
}
3. 多对多查询
模型
用户表和角色表的关系为,一个用户可以有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户的同时查询出该用户的所有角色
sql语句
SELECT * FROM user u,role r,user_role ur WHERE u.id = ur.userId AND ur.roleId = r.id
查询结果
接口
List<User> findUserAndRole();
mapper
<resultMap id="userRole" type="user">
<!--user的信息-->
<id column="userid" property="id"></id>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--user内部的信息-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"/>
<result column="roleDesc" property="roleDesc"/>
</collection>
</resultMap>
<select id="findUserAndRole" resultMap="userRole">
SELECT *
FROM user u,
role r,
user_role ur
WHERE u.id = ur.userId
AND ur.roleId = r.id
</select>
User
public class User {
private int id;
private String username;
private String password;
private String birthday;
//描述的是当前用户存在那些订单
private List<Order> orderList;
//描述的是当前用户具备那些角色
private List<Role> roleList;
生成对应的get和set方法
}
测试
@Test
public void test3() {
//1.获取SqlSession对象
SqlSession session = MybatisUtil.getSqlSession();
//2.获取dao代理
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> userList = mapper.findUserAndRole();
userList.forEach(user -> System.out.println(user));
//3.关闭SqlSession对象
session.close();
}
4. 知识小结
一对一配置:使用< resultMap >做配置
一对多配置:使用< resultMap >+< collection >做配置
多对多配置:使用< resultMap >+< collection >做配置