SQL
订单orders
:id,user_id,number,createtime,user
用户 user
:id,username,birthday,sex,address,ordersList
Java类CustomerOrders
:order、user
一对一查询
根据订单查询用户
一对一自动映射
<select id="findOrdersAndUser1" resultType="Demo01.pojo.CustomerOrders">
select a.*,b.id uid,username,birthday,sex,address
from orders a,`user` b
where a.user_id=b.id
</select>
一对一手动映射
定义resultMap
- id:resultMap的唯一标识
- type:将查询出的数据放入指定的对象中
指定单个对象的映射关系:
id
标签指定主键字段的对应关系result
标签指定非主键字段的对应关系- column:列,数据库列名称
- property:属性,Java属性名称
<resultMap type="Demo01.pojo.Orders" id="orderAndUserResultMap">
<!-- 订单数据 -->
<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"/>
</resultMap>
在resultMap中,分别指定一一对应的关系
- property:将指定数据放入orders中user属性中
- javaType:user属性的类型
<!-- 用户数据 -->
<association property="user" javaType="Demo01.pojo.User">
<id column="uid" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birtaday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
查询语句
<select id="findOrdersAndUser2" resultMap="orderAndUserResultMap">
select a.*,b.id uid,username,birthday,sex,address
from orders a,`user` b
where a.user_id=b.id
</select>
一对多查询
根据用户查询订单
定义resultMap
- id主键对应关系,result非主键对应关系
- property:将数据放入User对象中的orderList属性
- ofType:指定ordersList属性的泛型类型
<resultMap type="Demo01.pojo.User" id="userAndOrdersResultMap">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birtaday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<collection property="ordersList" ofType="Demo01.pojo.Orders">
<id column="oid" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
</collection>
</resultMap>
查询语句
<select id="findUserAndOrders" resultMap="userAndOrdersResultMap">
select a.*,b.id oid,user_id,number,createtime
from `user` a,orders b
where a.id=b.user_id
</select>