Mybatis多表查询
4.1 数据库表关系介绍
关系型数据库表关系分为
* 一对一
* 一对多
* 多对多
一对一:
1.Order实体类:
2.OrderMapper接口
3.OrderMapper.xml映射
<!--
assocation:在进行一对一关联查询配置时,使用assocation标签进行关联
property="user":要封装实体的属性名
javaType="com.lagou.domain.User":要封装实体的属性类型
-->
<association property="user" javaType="com.lagou.domain.User">
<id property="id" column="uid"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
</resultMap>
<!--一对一关联查询,查询所有订单,与此同时还要查询出每个订单所属的用户信息-->
<select id="findAllWithUser"r esultMap="orderMap">
SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;
</select>
一对多:
1.User实体
2.UserMapper接口
3.UserMapper.xml映射
<!--一对多关联查询:查询所有用户,同时还要查询每个用户所关联的订单信息-->
<resultMap id="userMap" type="com.lagou.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--
collection:一对多使用collection标签进行关联
-->
<collection property="ordersList" ofType="com.lagou.domain.Orders">
<id property="id" column="oid"></id>
<result property="orderTime" column="ordertime"/>
<result property="total" column="total"/>
<result property="uid" column="uid"/>
</collection>
</resultMap>
<select id="findAllWithOrders" resultMap="userMap">
SELECT u.*,o.id oid,o.ordertime,o.total,o.uid FROM orders o RIGHT JOIN USER u ON o.`uid`=u.`id`;
</select>
多对多:
1.User和Role 实体
2.UserMapper接口
3 .UserMapper.xml映射
<resultMap id="userRoleMap" type="com.lagou.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--
collection:一对多使用collection标签进行关联
-->
<collection property="roleList" ofType="com.lagou.domain.Role">
<id property="id" column="rid"></id>
<result property="rolename" column="rolename"/>
<result property="roleDesc" column="roleDesc"/>
</collection>
</resultMap>
<select id="findAllWithRole" resultMap="userRoleMap">
SELECT u.*,r.id rid,r.role name,r.roleDesc FROM USER u--用户表
LEFT JOIN sys_user_roleur--左外连接中间表
ON u.`id`=ur.`userid` LEFT JOIN sys_roler--左外连接中间表
ON ur.`roleid`=r.`id`;
</select>