多表联查
一对一查询
需求: 订单和用户是一对一,查询订单信息以及关联的用户信息.
数据准备:
CREATE TABLE `user` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
CREATE TABLE `tb_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`detail` varchar(255) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
实体类:
User类
package com.lyc.model;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private String phone;
//getter setter
...
}
Order类
package com.lyc.model;
import java.util.Date;
public class Order {
private int id;
private String detail;
private Date createTime;
private int uid; //用户Id
//一对一查询:一个订单对应一个用户,添加User类属性,用于封装查询出的User数据
private User user;
// getter和setter方法
}
UserMapper接口
package com.lyc.mapper;
import com.lyc.model.Order;
import com.lyc.model.User;
import java.util.List;
public interface UserMapper {
/**
* 一对一查询订单
*/
List<Order> queryOrderWithUser();
}
UserMapper.xml映射文件:
<!-- 一对一查询 -->
<resultMap id="OrderWithUser" type="Order">
<!-- 对单个属性进行封装-->
<id column="id" property="id" />
<result column="detail" property="detail"></result>
<result column="createTime" property="createTime"></result>
<result column="uid" property="uid"></result>
<!--对对象属性进行封装-->
<association property="user">
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="phone" property="phone"></result>
</association>
</resultMap>
<select id="queryOrderWithUser" resultMap="OrderWithUser">
select o.*, u.username,u.`password`,u.phone from user u,tb_order o where u.id = o.uid
</select>
对该映射文件的解析:
在标签内,一对一的封装
,直接使用resultType无法封装成功,因此需要使用resultMap
单独对其中的user属性封装。resultMap标签中的id,是当前标签的唯一标识,resultMap标签中的type,是最终封装的实体类。图解如下:
测试类
//一对一查询订单和用户信息
@Test
public void orderWithUser() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Order> orders = mapper.queryOrderWithUser();
for (Order order : orders) {
System.out.println(order);
}
}
一对多查询
需求:用户和订单是一对多关系(一个用户可以有多个订单),查询一个用户信息以及关联的所有订单信息。
多于一对多的关系,我们一般将多的一方写在一的一方
。
User实体类:
package com.lyc.model;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private String phone;
//一对多查询:一个用户拥有多个订单
private List<Order> orders;
...
}
UserMapper接口
/**
* 一对多查询
*/
List<User> queryUserWithOrder(int id);
UserMapper.xml映射文件:
<!-- 一对多查询:一个用户拥有多个订单 -->
<resultMap id="UserWithOrder" type="User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="phone" property="phone"></result>
<collection property="orders" ofType="Order">
<id column="oid" property="id"></id>
<result column="detail" property="detail"></result>
<result column="createTime" property="createTime"></result>
</collection>
</resultMap>
<select id="queryUserWithOrder" resultMap="UserWithOrder">
select U.*,o.id oid,o.detail,o.createTime from user u,tb_order o where u.id = o.uid and u.id=#{id}
</select>
一对多的语句和一对一的语句格式差不多,这里我们只做图解: