Mybatis多表关联查询之多对一和一对多
Mybatis多表关联可分成四种情况:一对一, 一对多, 多对一, 多对多.实现映射的关键是resultMap网上有很多介绍可自行搜索学习,如:MyBatis学习 之 二、SQL语句映射文件(1)resultMap
首先看下多对一实现
准备两张表person和order为1:n关系
类结构:
映射文件:OrderMapper.xml
第一种映射方式:
<mapper namespace="com.ittx.mybatis.demo1.dao.OrderDao"> <resultMap type="com.ittx.mybatis.demo1.model.Order" id="orderBean"> <id column="orderId" property="id"/> <result column="orderNumber" property="number"/> <result column="orderPrice" property="price"/> <!-- 多对一的关系 --> <!--column:指外键值, property: 指的是属性的值, javaType:指的是属性的类型--> <association column="pid" property="person" javaType="com.ittx.mybatis.demo1.model.Person"> <id column="personId" property="id"/> <result column="personName" property="name"/> <result column="personAddress" property="address"/> <result column="personTel" property="tel"/> </association> </resultMap> <!-- 根据id查询Order, 关联将Person查询出来 --> <select id="selectOrderById" parameterType="string" resultMap="orderBean"> select p.*, o.* from person p, orders o where p.personId = o.pid and o.orderId = #{id} </select> </mapper>
第二种映射方式:
<mapper namespace="com.ittx.mybatis.demo1.dao.OrderDao"> <resultMap type="com.ittx.mybatis.demo1.model.Person" id="personBaseBean"> <id column="personId" property="id"/> <result column="personName" property="name"/> <result column="personAddress" property="address"/> <result column="personTel" property="tel"/> </resultMap> <resultMap type="com.ittx.mybatis.demo1.model.Order" id="orderBean"> <id column="orderId" property="id"/> <result column="orderNumber" property="number"/> <result column="orderPrice" property="price"/> <!-- 多对一的关系 --> <!--column:指外键值, property: 指的是属性的值, resultMap:指的是属性的映射, --> <association column="pid" property="person" resultMap="com.ittx.mybatis.demo1.dao.OrderDao.personBaseBean"/> </resultMap> <!-- 根据id查询Order, 关联将Person查询出来 --> <select id="selectOrderById" parameterType="string" resultMap="orderBean"> select p.*, o.* from person p, orders o where p.personId = o.pid and o.orderId = #{id} </select> </mapper>
第三种映射方式:
<mapper namespace="com.ittx.mybatis.demo1.dao.OrderDao"> <resultMap type="com.ittx.mybatis.demo1.model.Order" id="orderBean"> <id column="orderId" property="id"/> <result column="orderNumber" property="number"/> <result column="orderPrice" property="price"/> <!-- 多对一的关系 --> <!--column:指外键值, property: 指的是属性的值, select:使用另一个查询封装的结果 --> <association column="orderId" property="person" select="selectPersonByOrderId"/> </resultMap> <select id="selectOrderById" parameterType="string" resultMap="orderBean"> select * from orders where orderId = #{id} </select> <select id="selectPersonByOrderId" parameterType="string" resultMap="com.ittx.mybatis.demo1.dao.OrderDao.personBaseBean"> select * from person where personId in (select pid from orders where orderId = #{id}) </select> </mapper>
接着看一对多关联实现
表结构不变
类结构如下:
映射文件:PersonMapper.xml
第一种方式
<resultMap type="com.ittx.mybatis.demo1.model.Person" id="personBean"> <id column="personId" property="id"/> <result column="personName" property="name"/> <result column="personAddress" property="address"/> <result column="personTel" property="tel"/> <!-- 一对多的关系 --> <!-- property: 指的是集合属性的值, ofType:指的是集合中元素的类型 --> <collection property="orders" ofType="com.ittx.mybatis.demo1.model.Order"> <id column="orderId" property="id"/> <result column="orderNumber" property="number"/> <result column="orderPrice" property="price"/> </collection> </resultMap> <!-- 根据id查询Person, 关联将Orders查询出来 --> <select id="selectPersonById" parameterType="string" resultMap="personBean"> select p.*, o.* from person p, orders o where p.personId = o.pid and p.personId = #{id} </select>
第二种方式
<resultMap type="com.ittx.mybatis.demo1.model.Order" id="orderBaseBean"> <id column="orderId" property="id"/> <result column="orderNumber" property="number"/> <result column="orderPrice" property="price"/> </resultMap> <resultMap type="com.ittx.mybatis.demo1.model.Person" id="personBean"> <id column="personId" property="id"/> <result column="personName" property="name"/> <result column="personAddress" property="address"/> <result column="personTel" property="tel"/> <!-- 一对多的关系 --> <collection property="orders" resultMap="com.ittx.mybatis.demo1.dao.OrderDao.orderBaseBean"/> </resultMap> <!-- 根据id查询Person, 关联将Orders查询出来 --> <select id="selectPersonById" parameterType="string" resultMap="personBean"> select p.*, o.* from person p, orders o where p.personId = o.pid and p.personId = #{id} </select>
转载请注明来源:Mybatis多表关联查询之多对一和一对多