1 引言
本章实现一对多的查询，还是继续一对一的上一章基础上加上一对多。
2、一对多实现
mapper.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">
<!--命名空间：分类管理sql隔离，方便管理-->
<mapper namespace="com.ycy.mybatis.dao.OrdersCustomMapper">
<resultMap id="orderResultMap" type="orders">
<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="user" javaType="user">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
</association>
</resultMap>
<resultMap id="oderAndDetailMap" type="orders" extends="orderResultMap">
<!--继承订单信息与用户信息-->
<!--订单明细
ofType:集合中po类型
-->
<collection property="orderdetails" ofType="Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
<!--resultType进行查询-->
<select id="findOrderCustomer" resultType="OrdersCustom">
SELECT
o.*,
u.username,
u.address
FROM
orders o,
USER u
WHERE o.user_id = u.id
</select>
<!--使用resultmap进行查询-->
<select id="findOrderResultMap" resultMap="orderResultMap">
SELECT
o.*,
u.username,
u.address
FROM
orders o,
USER u
WHERE o.user_id = u.id
</select>
<!--根据订单联合查询用户与订单详情 一对多关联（订单与订单详情）-->
<select id="findOrderAndDetail" resultMap="oderAndDetailMap" >
SELECT
o.*,
u.username,
u.address ,
d.id orderdetail_id,
d.items_id,
d.items_num
FROM
orders o,
USER u ,
orderdetail d
WHERE o.user_id = u.id
AND d.orders_id=o.id
</select>
</mapper>
</pre><pre name="code" class="html">
Orders.java实体类
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//用户信息
private User user;
//getset 省略 篇幅问题
//订单明细
private List<Orderdetail> orderdetails;
}
mapper.java
package com.ycy.mybatis.dao;
import com.ycy.mybatis.module.Orders;
import com.ycy.mybatis.module.OrdersCustom;
import java.util.List;
/**
* Created by Administrator on 2015/9/9 0009.
*/
public interface OrdersCustomMapper {
//一对一ResultType
public List<OrdersCustom> findOrderCustomer() throws Exception;
//一对一ResultMap
public List<Orders> findOrderResultMap() throws Exception;
//一对多
public List<Orders> findOrderAndDetail() throws Exception;
}
一对多测试
package com.ycy.mybatis.test;
import com.ycy.mybatis.dao.OrdersCustomMapper;
import com.ycy.mybatis.module.Orderdetail;
import com.ycy.mybatis.module.Orders;
import com.ycy.mybatis.module.OrdersCustom;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* Created by Administrator on 2015/8/31 0031.
*/
public class MybatisTest7 {
private SqlSessionFactory sqlSessionFactory = null;
@Before
public void before() throws IOException {
String resource="SqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
sqlSessionFactory= new SqlSessionFactoryBuilder().build(in);
}
//一对多 订单-订单详情
@Test
public void findOrderAndDetail() throws Exception {
SqlSession sqlSession=sqlSessionFactory.openSession();
OrdersCustomMapper ordersCustomMapper= sqlSession.getMapper(OrdersCustomMapper.class);
List<Orders> ordersList= ordersCustomMapper.findOrderAndDetail();
sqlSession.close();
for (Orders ordersCustom : ordersList) {
System.out.println(ordersCustom.getUser().getUsername());
List<Orderdetail> orderdetailList= ordersCustom.getOrderdetails();
System.out.println(ordersCustom.getOrderdetails());
for (Orderdetail orderdetail : orderdetailList) {
System.err.println(orderdetail.getItemsNum());
}
}
}
}
2、一对多实现（稍微复杂）
之前我们看看我们查询结果，是根据order表来的查询结果现在我们根据User表
在user.java中创建映射的属性：集合 List<Orders> orderlist
在Orders中创建映射的属性：集合List<Orderdetail> orderdetails
在Orderdetail中创建商品属性：pojo Items items
具体的java实体类我就不写出了了，我只写一个mapper.xml 文件你看懂就ok了。。。主要是requestMmap的编写 <!--userAndDetailMap 根据用户查询订单，订单详细，商品-->
<resultMap id="userAndDetailMap" type="com.ycy.mybatis.module.User">
<!--用户信息-->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<!--订单信息-->
<collection property="orderlist" javaType="com.ycy.mybatis.module.Orders">
<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"/>
<!--订单明细-->
<collection property="orderdetails" javaType="com.ycy.mybatis.module.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!--商品信息-->
<association property="items" javaType="com.ycy.mybatis.module.Items">
<id column="item_id" property="id"/>
<result column="item_name" property="name"/>
<result column="item_detail" property="detail"/>
</association>
</collection>
</collection>
</resultMap>sql语句依然可以使用这样
<!--根据用户单联合查询用户与订单详情 一对多关联（订单与订单详情）-->
<select id="findUserAndDetail" resultMap="userAndDetailMap" >
SELECT
o.*,
u.username,
u.address ,
d.id orderdetail_id,
d.items_id,
d.items_num
FROM
orders o,
USER u ,
orderdetail d
WHERE o.user_id = u.id
AND d.orders_id=o.id
</select>
2、一对多实现总结（暂时）
1、collection ：当我们的java类利用有list的就用Collection，因为这个单词本身就是集合的意思
2、association ：当我们的java类利用有联合其他类的就用association ，因为这个单词本身就是联合的意思
3、当我是实现子类查询的时候，javaType尽量用全称（经验），property必须与java类里面的名称一样
