一對多
實體關系
~~~
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)
