高级映射主要还是映射，只是映射中的数据关系复杂了，其中就包括一对一、一对多、多对多的关系。
说到映射就想到mapper.xml文件
说到关系想到一对一、一对多、多对多
一对一、一对多、多对多就是查询结果，这样的话mapper.xml中对应查询结果的就是resultType和resultMap。
一对一、一对多、多对多会有什么样的结果？
一对一：一张表一条数据对应关联表一条数据
一对多：一张表一条数据对应关联表多条数据
多对多：一张表多条数据对应关联表多条数据
下面看看resulType、resultMap分别如何实现上面一对一、一对多、多对多结果集
一、resulType
Ⅰ、一对一
1、Mapper.xml
<select id="findOrdersUser" resultType=""> SELECT orders.*, t_user.* FROM orders, t_user WHERE orders.user_id = t_user.id </select>
resultType的值要包含orders、t_user的内容的返回类型，需要在Orders类型的基础上扩展一个User结构的扩展类
2、设置Orders属性
public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note;
3、设置OrdersExtend属性
public class OrdersExtend extends Orders { private Integer id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址
所以resultType配置
<select id="findOrdersUser" resultType="com.xxx.mybatis.po.OrdersExtend"> SELECT orders.*, t_user.* FROM orders, t_user WHERE orders.user_id = t_user.id </select>
4、OrdersMapper.java
public interface OrdersMapper { public OrdersExtend findOrdersUser(); }
5、编写测试
SqlSessionFactory sqlSessionFactory; @BeforeEach public void setUp() throws Exception { // mybatis核心配置文件 String resource = "SqlMapConfig.xml"; // 核心配置文件流 InputStream inputStream = Resources.getResourceAsStream(resource); // 根据核心配置文件，创建SqlSessionFactory对象 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void testFindOrdersUser() { SqlSession sqlSession=sqlSessionFactory.openSession(); OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class); List<OrdersExtend> list=ordersMapper.findOrdersUser(); System.out.println(list); }
6、测试结果：发现和sql数据库查出的数据不一致（id为null）
[ Orders [id=null, user_Id=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null] OrdersExtend [id=1, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] , Orders [id=null, user_Id=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null] OrdersExtend [id=2, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] ]
使用resultType需要定义符合查询结果的类型，如果查询结果中有字段名一样时，会出现有一个字段为null的结果，当然可以使用别名解决，
例如上例中的Orders中的id
1、将配置文件中的sql语句中select *换成 select 字段名
<select id="findOrdersUser" resultType="com.xxx.mybatis.po.OrdersExtend"> SELECT orders.id AS order_id ,orders.user_id ,orders.number ,orders.createtime ,orders.note ,t_user.* FROM orders, t_user WHERE orders.user_id = t_user.id </select>
2、修改Orders类型中id字段名
public class Orders { private Integer id order_id;
Ⅱ、一对多
1、配置Mapper.xml
<select id="findOrdersAndOrderDetail" resultType="com.xxx.mybatis.po.OrdersExtend"> SELECT orders.id AS order_id, orders.user_id, orders.number, orders.createtime, orders.note, t_user.* orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id FROM orders, USER, orderdetail WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id </select>
2、Mapper.java
public interface OrdersMapper { public List<OrdersExtend> findOrdersUser(); public List<OrdersExtend> findOrdersAndOrderDetail(); }
3、增加OrdersExtend属性
public class OrdersExtend extends Orders { private String username;// 用户姓名 private String address;// 地址 private Integer id orderdetail_id; private Integer items_id; private Integer items_num; private Integer orders_id;
4、测试代码
public void testFindOrdersAndOrderDetail() { SqlSession sqlSession=sqlSessionFactory.openSession(); OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class); List<OrdersExtend> list=ordersMapper.findOrdersAndOrderDetail(); System.out.println(list); }
5、测试结果（Orders的数据有重复）
[Orders [id=1, user_Id=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null] OrdersExtend [username=zhangsan, address=tianji, orderdetail_id=1, items_id=1, items_num=1, orders_id=1], Orders [id=1, user_Id=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null] OrdersExtend [username=zhangsan, address=tianji, orderdetail_id=2, items_id=2, items_num=3, orders_id=1], Orders [id=2, user_Id=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null] OrdersExtend [username=zhangsan, address=tianji, orderdetail_id=3, items_id=3, items_num=4, orders_id=2], Orders [id=2, user_Id=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null] OrdersExtend [username=zhangsan, address=tianji, orderdetail_id=4, items_id=2, items_num=3, orders_id=2]]
Ⅲ、多对多
要以用户为主查询，用户和商品是多对多的关系，一个用户可以购买多种商品，一种商品可以被多个用户购买
1、配置Mapper.xml
<select id="findManytoMany" resultType="com.xxx.mybatis.po.UserExtend"> SELECT t_user.*, orders.number, orders.createtime ordercreatetime, orderdetail.items_num itemsid, orderdetail.orders_id ordersId, items.name items_name, items.detail items_detail, items.price items_price FROM orders, t_user, orderdetail, items WHERE orders.user_id = t_user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id </select>
2、Mapper.java
public List<UserExtend> findManytoMany();
3、创建UserExtend
public class UserExtend extends User { //订单属性 private String number; private Date ordercreatetime; //订单明细 private Integer ordersId; private Integer itemsId; //商品 private String item_name; private Double item_price; private String item_detail;
4、测试代码
@Test public void testFindManytoMany() { SqlSession sqlSession=sqlSessionFactory.openSession(); UserMapper mapper=sqlSession.getMapper(UserMapper.class); List<UserExtend> list=mapper.findManytoMany(); System.out.println(list); }
5、测试结果
[User [id=1, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] UserExtend [number=1000010, ordercreatetime=Wed Feb 04 13:22:35 CST 2015, ordersId=1, itemsId=1, item_name=null, item_price=null, item_detail=null], User [id=1, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] UserExtend [number=1000010, ordercreatetime=Wed Feb 04 13:22:35 CST 2015, ordersId=1, itemsId=3, item_name=null, item_price=null, item_detail=null], User [id=1, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] UserExtend [number=1000011, ordercreatetime=Tue Feb 03 13:22:41 CST 2015, ordersId=2, itemsId=4, item_name=null, item_price=null, item_detail=null], User [id=1, username=zhangsan, sex=2, birthday=Thu Jul 10 00:00:00 CST 2014, address=tianji] UserExtend [number=1000011, ordercreatetime=Tue Feb 03 13:22:41 CST 2015, ordersId=2, itemsId=3, item_name=null, item_price=null, item_detail=null]]
总结：
1、resultType的映射类型（xxxExtend）的属性会随着查询的项目的变化而变化
2、查询字段名和参数类型的字段名要一致
3、多表查询字段名不能相同，相同的话要用别名区分
4、resultType比较适用于单表查询，或不需要新增太多属性的查询
二、resulMap
Ⅰ、一对一
1、配置Mapper.xml
<select id="findOrdersUserResultMap" resultMap=""> SELECT orders.*, t_user.* FROM orders, t_user WHERE orders.user_id = t_user.id </select>
resultMap需要指定Map，那么就需要创建一个Map给它
2、创建Map，指定映射关系
2.1、创建返回参数类型
public class OrdersExtendResultMap extends Orders { private User user;
2.2、创建resultMap
<!-- type:查询结果映射类型 id：resultMap区分，指定查询结果要使用哪个映射Map --> <resultMap type="com.xxx.mybatis.po.OrdersExtendResultMap" id="OrdersUserResultMap"> <!-- id：指定查询列中的唯一标识，如果有多个列组成唯一标识那就配置多个id column：查询字段名(select id,user_id,number,createtime,note) property：映射类型（OrdersExtendResultMap）的属性名 --> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- association：用于映射关联查询单个对象的信息(一对一映射) property：映射类型（OrdersExtendResultMap）的属性名 javaType：映射类型（OrdersExtendResultMap）的属性名的类型 --> <association property="user" javaType="com.xxx.mybatis.po.User"> <!-- id：指定查询列中的唯一标识，如果有多个列组成唯一标识那就配置多个id column：查询字段名(select user_id,username,sex,address) property：映射类型（OrdersExtendResultMap）的属性名 --> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap>
3、将创建的resultMap的id赋值给statement的resultMap
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap"> SELECT orders.*, t_user.* FROM orders, t_user WHERE orders.user_id = t_user.id </select>
4、Mapper.java
public List<OrdersExtendResultMap> findOrdersUserResultMap();
5、测试代码
@Test public void testFindOrdersUserResultMap() { SqlSession sqlSession=sqlSessionFactory.openSession(); OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class); List<OrdersExtendResultMap> list=ordersMapper.findOrdersUserResultMap(); System.out.println(list); }
6、测试结果（birthday=null是因为没有做映射）
[Orders [id=1, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null] OrdersExtendResultMap [user=User [id=1, username=zhangsan, sex=2, birthday=null, address=tianji]], Orders [id=2, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null] OrdersExtendResultMap [user=User [id=1, username=zhangsan, sex=2, birthday=null, address=tianji]]]
Ⅱ、一对多
1、OrderDetail
public class Orderdetail { private Integer id; private Integer ordersId; private Integer itemsId;
2、Mapper.xml
<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 --> <resultMap type="com.xxx.mybatis.po.OrdersExtendResultMap" id="OntoManyResultMap" extends="OrdersUserResultMap"> <collection property="orderdetails" ofType="com.xxx.mybatis.po.Orderdetail"> <!-- id：订单明细唯 一标识 property:要将订单明细的唯 一标识 映射到cn.itcast.mybatis.po.Orderdetail的哪个属性 --> <id column="orderdetail_id" property="id" /> <result column="items_id" property="itemsId" /> <result column="items_num" property="itemsNum" /> <result column="orders_id" property="ordersId" /> </collection> </resultMap>
3、OrdersExtendResultMap添加属性
public class OrdersExtendResultMap extends Orders { private User user; private List<Orderdetail> orderdetails;
4、配置statement
<select id="findOnetoMany" resultMap="OntoManyResultMap"> SELECT orders.*, t_user.username, t_user.sex, t_user.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id FROM orders, t_user, orderdetail WHERE orders.user_id = t_user.id AND orderdetail.orders_id=orders.id </select>
5、接口方法
public List<OrdersExtendResultMap> findOnetoMany();
6、测试代码
@Test public void testFindOnetoMany() { SqlSession sqlSession=sqlSessionFactory.openSession(); OrdersMapper ordersMapper=sqlSession.getMapper(OrdersMapper.class); List<OrdersExtendResultMap> list=ordersMapper.findOnetoMany(); System.out.println(list); }
7、测试结果
[Orders [id=1, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null] OrdersExtendResultMap [ user=User [id=1, username=zhangsan, sex=2, birthday=null, address=tianji], orderdetails= [ Orderdetail [id=1, ordersId=1, itemsId=1, itemsNum=1], Orderdetail [id=2, ordersId=1, itemsId=2, itemsNum=3]]], Orders [id=2, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null] OrdersExtendResultMap [ user=User [id=1, username=zhangsan, sex=2, birthday=null, address=tianji], orderdetails= [ Orderdetail [id=3, ordersId=2, itemsId=3, itemsNum=4], Orderdetail [id=4, ordersId=2, itemsId=2, itemsNum=3]]]]
Ⅲ、多对多
1、一个用户有多个订单
public class UserExtendResultMap extends User { private List<OrdersExtendResultMap> orders;
2、一个订单有多个订单明细
public class OrdersExtendResultMap extends Orders { private List<OrderdetailExtendResultMap> orderdetails;
3、一个明细有多种商品
public class OrderdetailExtendResultMap extends Orderdetail { private List<Items> items;
4、商品
public class Items { private Integer id; private String name; private Double price; private String detail; private String pic; private Date createtime;
5、配置statement（注意唯一标识--id），如果有唯一标识相同的话会出现数据列表只读一条记录
<select id="findManytoManyResultMap" resultMap="ManytoManyresultMap"> SELECT t_user.*, orders.id oid, orders.user_id, orders.number, orders.createtime, orders.note, orderdetail.id odid, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id, items.id iid, items.name items_name, items.detail items_detail, items.price items_price FROM orders, t_user, orderdetail, items WHERE orders.user_id = t_user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id </select>
6、配置resultMap
<resultMap type="com.xxx.mybatis.po.UserExtendResultMap" id="ManytoManyresultMap"> <id column="id" property="id" /> <result column="username" property="username" /> <result column="sex" property="sex" /> <result column="address" property="address" /> <!-- 一个用户有多个订单 --> <collection property="orders" ofType="com.xxx.mybatis.po.OrdersExtendResultMap"> <id column="oid" property="id" /> <result column="user_id" property="userId" /> <result column="number" property="number" /> <result column="createtime" property="createtime" /> <result column="note" property="note" /> <!-- 一个订单有多个订单明细 --> <collection property="orderdetails" ofType="com.xxx.mybatis.po.OrderdetailExtendResultMap"> <id column="odid" property="id" /> <result column="items_id" property="itemsId" /> <result column="items_num" property="itemsNum" /> <result column="orders_id" property="ordersId" /> <!-- 一个订单明细有一个商品 --> <association property="items" javaType="com.xxx.mybatis.po.Items"> <id column="iid" property="id" /> <result column="name" property="name" /> <result column="detail" property="detail" /> <result column="price" property="price" /> </association> </collection> </collection> </resultMap>
7、接口方法
public List<UserExtendResultMap> findManytoManyResultMap();
8、测试代码
@Test public void testFindManytoManyResultMap() { SqlSession sqlSession=sqlSessionFactory.openSession(); UserMapper mapper=sqlSession.getMapper(UserMapper.class); List<UserExtendResultMap> list=mapper.findManytoManyResultMap(); System.out.println(list); }
9、测试结果
[UserExtendResultMap [ orders=[ OrdersExtendResultMap [ orderdetails=[ OrderdetailExtend [ items=Items [id=1, name=null, price=null, detail=null, pic=null, createtime=null]], OrderdetailExtend [ items=Items [id=2, name=null, price=null, detail=null, pic=null, createtime=null]]]], OrdersExtendResultMap [ orderdetails=[ OrderdetailExtend [ items=Items [id=3, name=null, price=null, detail=null, pic=null, createtime=null]], OrderdetailExtend [ items=Items [id=2, name=null, price=null, detail=null, pic=null, createtime=null]]]]]]]
总结：
resultType：将查询结果按照sql列名pojo属性名一致性映射到pojo中
resultMap：使用association和collection完成高级映射（对结果有特殊的映射要求）
association：一对一关系映射
将关联查询信息映射到一个pojo对象中
collection：将关联查询信息映射到一个list集合中