Mybatis关联查询一对一和一对多的实现

一对多

MyBatis中使用collection标签来解决一对多的关联查询，collection标签可用的属性如下：

1. property:指的是集合属性的值
2. ofType:指的是集合中元素的类型
3. column:所对应的列查询条件(延迟加载使用)
4. select:使用另一个查询封装的结果(延迟加载使用)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mucfc.model.CustomerMapper">
 <!-- 定义数据库字段与实体对象的映射关系 -->
	<resultMap type="Customer" id="customerBean">
		<id column="customerId" property="customerId"/>
		<result column="customerName" property="customerName"/>
		<result column="customerTel" property="customerTel"/>		
		<!-- 一对多的关系 -->
		<!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
		<collection property="tickets" ofType="Ticket">
			<id column="ticketId" property="ticketId"/>
			<result column="ticketAddress" property="ticketAddress"/>
			<result column="ticketPrice" property="ticketPrice"/>
			<result column="ticketCId" property="ticketCId"/>
		</collection>
	</resultMap>	
	
	<!-- 根据id查询Person, 关联将Orders查询出来 -->
	<select id="selectCustomerByName" parameterType="string" resultMap="customerBean">
		select c.*,t.* from t_customer c,t_ticket t  where  c.customerId=t.ticketCId and c.customerName =#{customerName};
	</select>
	
 </mapper>

一对一

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

1. property:对象属性的名称
2. javaType:对象属性的类型
3. column:所对应的查询列名称
4. select:使用另一个查询封装的结果

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mucfc.model.TicketMapper">
	<!-- 定义数据库字段与实体对象的映射关系  -->
	<resultMap type="Ticket" id="ticketBean">
		<id column="ticketId" property="ticketId" />
		<result column="ticketAddress" property="ticketAddress" />
		<result column="ticketPrice" property="ticketPrice" />
		<result column="ticketCId" property="ticketCId" />
		<!-- 一对一的关系 -->
		<!-- property: 指的是属性的值, javaType：指的是元素的类型 -->
		<association property="customer" javaType="Customer">
			<id column="customerId" property="customerId" />
			<result column="customerName" property="customerName" />
			<result column="customerTel" property="customerTel" />
		</association>
	</resultMap>
	<!-- 根据id查询ticket, 关联将Customer查询出来 -->
	<select id="selectTicketById" parameterType="int" resultMap="ticketBean">
		select c.*,t.* from t_customer c,t_ticket t where
		c.customerId=t.ticketCId and t.ticketId =#{ticketId}
	</select>
</mapper>

延迟加载

<!--userMapper.xml-->

....
<resultMap id="BaseResultMap" type="com.redstar.basemapper.pojo.User">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="INTEGER" property="age"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
    </resultMap>
    <resultMap id="userRoleMapSelect" type="com.redstar.basemapper.pojo.UserVo">
        <association property="user" resultMap="BaseResultMap"/>
        <association property="role" fetchType="lazy" column="{id=role_id}" 传入的列名作为sql的查询条件
                     select="com.redstar.basemapper.dao.RoleMapper.getRoleById"/>
    </resultMap>
    <sql id="Base_Column_List">
    id, `name`, age, role_id
  </sql>
    <select id="getUserVo" resultMap="userRoleMapSelect">
      select * from user where id=#{userId}
    </select>
...
    
    
    
    <!--roleMapper.xml-->
...    
    <resultMap id="BaseResultMap" type="com.redstar.basemapper.pojo.Role">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="role_name" jdbcType="VARCHAR" property="roleName" />
  </resultMap>
  <sql id="Base_Column_List">
    id, role_name
  </sql>
  <select id="getRoleById" resultMap="BaseResultMap">
    select * from role where id=#{id}
  </select>
...