一对多
实体关系
~~~
public class Order {
private Integer orderid;
private Timestamp ordertime;
private Userinfo userinfo;
private Double totalprice;
private String status;
}
~~~
~~~
public class Userinfo {
private Integer userid;
private String username;
private String sex;
private double sal;
private Date birthday;
private Timestamp stime;
List<Order> orders;
}
~~~
1. 一对多(查询某个用户，并且得到用户下的所有订单)
~~~
<select id="getUserById" parameterType="int" resultMap="_userorder">
select u.*, o.*
from userinfo u, sorder o
where u.id = o.userid
and u.id = #{id}
</select>
<resultMap type="Userinfo" id="_userorder">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="sal" property="sal"/>
<!-- column通常为 order表中的外键列 -->
<collection property="orders" ofType="Sorder" column="userid">
<id column="oid" property="oid"/>
<result column="odate" property="odate"/>
<result column="ostatus" property="ostatus"/>
<result column="oaddress" property="oaddress"/>
<result column="totalprice" property="totalprice"/>
</collection>
</resultMap>
~~~
测试类
~~~
Userinfo u = userinfoMapper.getUserinfoOrders(2);
System.out.println(u.getUsername());
List<Order> orders = u.getOrders();
for(Order o: orders)
{
System.out.println(o.getOrderid()+"\t"+o.getTotalprice());
}
~~~
2. 一对多(查询所有用户，并且得到用户下的所有订单)
~~~
<select id="getAllUsers" resultMap="_userorder">
select u.*, o.*
from userinfo u, sorder o
where u.id = o.userid
</select>
<resultMap type="Userinfo" id="_userorder">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="sal" property="sal"/>
<!-- column通常为 order表中的外键列 -->
<collection property="orders" ofType="Sorder" column="userid">
<id column="oid" property="oid"/>
<result column="odate" property="odate"/>
<result column="ostatus" property="ostatus"/>
<result column="oaddress" property="oaddress"/>
<result column="totalprice" property="totalprice"/>
</collection>
</resultMap>
~~~
测试类
~~~
List<Userinfo> users = userinfoMapper.getUserinfoOrders2();
for(Userinfo u: users)
{
System.out.println(u.getUsername());
System.out.println("=======================");
List<Order> orders = u.getOrders();
for(Order o: orders)
{
System.out.println(o.getOrderid()+"\t"+o.getTotalprice());
}
}
~~~
3. sql片段
~~~
<sql id="getUserSql">
select u.*, o.*
from userinfo u, sorder o
where u.id = o.userid
</sql>
<select id="getAllUsers" resultMap="_userorder">
<include refid="getUserSql" />
</select>
<select id="getUserById" parameterType="int" resultMap="_userorder">
<include refid="getUserSql" />
and u.id = #{id}
</select>
~~~
练习：
员工表，部门表
1. 查询一个员工，包含员工所在的部门信息（association）
2. 查询一个部分，以及这个部门下的所有员工(collection)
