Mybatis 48_带列前缀的多表连接的1-1关联

指定使用列前缀进行区分。

Person与Address存在两种关联：
- 所属关联
- 租客关联
在这种复杂的情况下，程序就需要对同一个表进行两次甚至多次关联，此时就需要使用列前缀进行区分。
【注意点】：
1. MyBatis的<association…/>只要增加一个columnPrefix属性即可。
2. 复杂反而是如何定义SELECT语句， 这种下程序需要对同一个表进行多次关联，所以比较复杂。

项目0516带列前缀的多表连接查询的1-1

主类

package lee;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.itcheng.app.dao.AddressMapper;
import org.itcheng.app.dao.PersonMapper;
import org.itcheng.app.domain.Address;
import org.itcheng.app.domain.Person;

public class PersonManager
{
	// SqlSessionFactory应该是应用级别
	private static SqlSessionFactory sqlSessionFactory;
	public static void main(String[] args) throws IOException
	{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1. 创建SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 2. 打开SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
//		findPerson(sqlSession);
		/* 结果
		 * DEBUG [main] org.itcheng.app.dao.PersonMapper.getPerson ==>  Preparing: select p.*, ad.*, rental.addr_id renter_addr_id, rental.addr_detail renter_addr_detail from person_inf p join address_inf ad on ad.owner_id = p.person_id join address_inf rental on rental.renter_id = p.person_id where person_id = ? 
		 * DEBUG [main] org.itcheng.app.dao.PersonMapper.getPerson ==> Parameters: 1(Integer)
		 * DEBUG [main] org.itcheng.app.dao.PersonMapper.getPerson <==      Total: 1
		 * 孙悟空
		 * -----------------
		 * 花果山水帘洞
		 * 福陵山云栈洞
		 * */
	
		findAddresses(sqlSession);
		/* 结果
		 * DEBUG [main] org.itcheng.app.dao.AddressMapper.findAddressesById ==>  Preparing: select ad.*, p.*, renter.person_id renter_person_id, renter.person_name renter_person_name, renter.person_age renter_person_age from address_inf ad join person_inf p on ad.owner_id = p.person_id join person_inf renter on ad.renter_id = renter.person_id where addr_id > ? 
		 * DEBUG [main] org.itcheng.app.dao.AddressMapper.findAddressesById ==> Parameters: 1(Integer)
		 * DEBUG [main] org.itcheng.app.dao.AddressMapper.findAddressesById <==      Total: 4
		 * 花果山水帘洞 --> 孙悟空--蜘蛛精
		 * 福陵山云栈洞 --> 猪八戒--孙悟空
		 * 陷空山无底洞 --> 白鼠精--猪八戒
		 * 积雷山摩云洞 --> 玉面狐狸--白鼠精
		 * -----------------
		 * */
	}
	
	public static void findPerson(SqlSession sqlSession)
	{
		PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
		
		Person p = personMapper.getPerson(1);
		System.out.println(p.getName());
		System.out.println("-----------------");
		
		// Person获取关联实体是延迟加载，要等到真正访问关联实体时才去执行额外的SELECT语句
		System.out.println(p.getOwnerAddr().getDetail());
		System.out.println(p.getRenterAddr().getDetail());
		
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}
	
	public static void findAddresses(SqlSession sqlSession)
	{
		AddressMapper addressMapper = sqlSession.getMapper(AddressMapper.class);
		
		List<Address> addrList = addressMapper.findAddressesById(1);
		//  由于Address获取关联实体是立即加载，因此它会立即执行额外的SELECT语句
		// 对于立即加载策略,即使不访问关联实体,它也会立即执行额外的SELECT语句
		addrList.forEach(e -> {
			System.out.println(e.getDetail() + " --> " + e.getOwner().getName()
				+ "--" + e.getRental().getName());
		});
		System.out.println("-----------------");
		
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}	
		
}

接口

package org.itcheng.app.dao;

import java.util.List;

import org.itcheng.app.domain.Address;

// Mapper组件相当于DAO组件
public interface AddressMapper
{
	List<Address> findAddressesById(Integer id);
}

package org.itcheng.app.dao;

import org.itcheng.app.domain.Person;

// Mapper组件相当于DAO组件
public interface PersonMapper
{
	Person getPerson(Integer id);
}

配置文件

<?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属性值相当于该mapper的唯一标识 -->	
<mapper namespace="org.itcheng.app.dao.AddressMapper">
<!-- 
person_inf p      主人
person_inf renter 租客
 -->
	<select id="findAddressesById" resultMap="addrMap">
		select ad.*, p.*,
		renter.person_id renter_person_id, 
		renter.person_name renter_person_name, 
		renter.person_age renter_person_age 
		from address_inf ad
		join person_inf p
		on ad.owner_id = p.person_id
		join person_inf renter
		on ad.renter_id = renter.person_id
		where addr_id > #{id}
	</select>
	
	<resultMap type="address" id="addrMap">
		<id column="addr_id" property="id"/>
		<result column="addr_detail" property="detail"/>
		<!-- 由于select是多表连接查询，
			因此关联实体直接使用resultMap来定义映射  
			owner 主任，	rental 租客
			columnPrefix 列别名前缀
		-->	
		<association property="owner" 
			resultMap="org.itcheng.app.dao.PersonMapper.personMap" />
		<association property="rental" columnPrefix="renter_"
			resultMap="org.itcheng.app.dao.PersonMapper.personMap" />		
	</resultMap>
	
</mapper>

<?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属性值相当于该mapper的唯一标识 -->	
<mapper namespace="org.itcheng.app.dao.PersonMapper">
	<select id="getPerson" resultMap="personMap">
		select 
		p.*, ad.*,
		rental.addr_id renter_addr_id, 
		rental.addr_detail renter_addr_detail 
		from person_inf p
		join address_inf ad
		on ad.owner_id = p.person_id
		join address_inf rental
		on rental.renter_id = p.person_id
		where person_id = #{id}
	</select>
	
	<resultMap type="person" id="personMap">
		<id column="person_id" property="id"/>
		<result column="person_name" property="name"/>
		<result column="person_age" property="age"/>
		<!-- 由于select是多表连接查询，
			因此关联实体直接使用resultMap来定义映射  -->	
		<association property="ownerAddr" resultMap="org.itcheng.app.dao.AddressMapper.addrMap"/>
		<association property="renterAddr" columnPrefix="renter_"
			 resultMap="org.itcheng.app.dao.AddressMapper.addrMap"/>
	</resultMap>
</mapper>

bean类

package org.itcheng.app.domain;

public class Address
{
	private Integer id;
	private String detail;
	// Address关联的对象
	private Person owner;
	private Person rental;
}

package org.itcheng.app.domain;

public class Person
{
	private Integer id;
	private String name;
	private int age;
	
	// Person关联的对象
	private Address ownerAddr;
	private Address renterAddr;
}