ibatis_多对一关系映射

复合型数据查询多对一关系查询 

将一方作为多方的一个属性配置

出现N+1 select(N次用于查询“多”， 一次用于查询关于“一”的一个List)

联合查询和嵌套属性避免N+1 Select

public class Company {

    /**.
     * 公司Id
     */
    private Integer companyId;
    /**.
     * 公司名称
     */
    private String companyName;
    /**.
     * 默认构造器
     */
    public Company() {
    }
    /**.
     * 名称构造器
     * @param name 公司名称
     */
    public Company(final String name) {
        this.companyName = name;
    }

    /**.
     * @param id 公司id
     * @param name 公司名称
     */
    public Company(final Integer id, final String name) {
        super();
        this.companyId = id;
        this.companyName = name;
    }

    /**
     * @return the companyId
     */
    public Integer getCompanyId() {
        return companyId;
    }

    /**
     * @param companyId the companyId to set
     */
    public void setCompanyId(Integer companyId) {
        this.companyId = companyId;
    }

    /**
     * @return the companyName
     */
    public String getCompanyName() {
        return companyName;
    }

    /**
     * @param companyName the companyName to set
     */
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }
}

public class Staff {
    /**.
     * 员工Id
     */
    private Integer staffId;
    /**.
     * 员工姓名
     */
    private String staffName;
    /**.
     * 所属公司编号
     */
    private Integer company;
    /**.
     * 默认构造器
     */
    public Staff() {
    }
    /**.
     * id构造器
     * @param id 员工id
     */
    public Staff(final Integer id) {
        this.staffId = id;
    }
    /**.
     * name构造器
     * @param name 员工名字
     */
    public Staff(final String name) {
        this.staffName = name;
    }
    
    /**
     * @param staffName
     * @param company
     */
    public Staff(String staffName, Integer company) {
        this.staffName = staffName;
        this.company = company;
    }
    /**.
     * 构造器
     * @param id 员工id
     * @param name 员工名字
     * @param companyId 公司id
     */
    public Staff(final Integer id, final String name, final Integer company) {
        this.staffId = id;
        this.staffName = name;
        this.company = company;
    }
    /**
     * @return the staffId
     */
    public Integer getStaffId() {
        return staffId;
    }
    /**
     * @param staffId the staffId to set
     */
    public void setStaffId(Integer staffId) {
        this.staffId = staffId;
    }
    /**
     * @return the staffName
     */
    public String getStaffName() {
        return staffName;
    }
    /**
     * @param staffName the staffName to set
     */
    public void setStaffName(String staffName) {
        this.staffName = staffName;
    }
    /**
     * @return the company
     */
    public Integer getCompany() {
        return company;
    }
    /**
     * @param company the company to set
     */
    public void setCompany(Integer company) {
        this.company = company;
    }
}

public class IbatisUtil {
    private static SqlMapClient client;

    static {
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("sqlMapConfig.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        client = SqlMapClientBuilder.buildSqlMapClient(reader);
    }

    public static SqlMapClient getClient() {
        return client;
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
	<properties resource="database.properties" />
	
	<settings cacheModelsEnabled="true" enhancementEnabled="true"
		lazyLoadingEnabled="true" maxRequests="32"
		maxSessions="10"  maxTransactions="5" useStatementNamespaces="true" />

	
		<transactionManager type="JDBC">
			<dataSource type="SIMPLE">
				<property name="JDBC.Driver" value="${dataSource.driverClassName}"/>
				<property name="JDBC.ConnectionURL" value="${dataSource.url}"/>
				<property name="JDBC.Username" value="${dataSource.username}"/>
				<property name="JDBC.Password" value="${dataSource.password}"/>
				<property name="Pool.MaximumActiveConnections" value="10"/>
				<property name="Pool.MaximumIdleConnections" value="5"/>
				<property name="Pool.MaximumCheckoutTime" value="120000"/>
				<property name="Pool.TimeToWait" value="10000"/>
				<property name="Pool.PingQuery" value="select * from dual"/>
				<property name="Pool.PingEnabled" value="false"/>
				<property name="Pool.PingConnectionsOlderThan" value="0"/>
				<property name="Pool.PingConnectionsNotUsedFor" value="0"/>		
			</dataSource>
		</transactionManager>
		<!-- 
	<settings cacheModelsEnabled="true" enhancementEnabled="true"
		lazyLoadingEnabled="true" maxRequests="32"
		maxSessions="10"  maxTransactions="5" useStatementNamespaces="true" />
		 -->
	
	<sqlMap resource="dao.xml" />
	 
</sqlMapConfig>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">   
    <!-- 
    	复合型数据查询多对一关系查询
    	出现N+1 select(N次用于查询“多”， 一次用于查询关于“一”的一个List)
    	将一方作为多方的一个属性配置
     --> 
<sqlMap>
	<typeAlias alias="Staff" type="com.asarja.entity.Staff"/>
	<typeAlias alias="Company" type="com.asarja.entity.Company"/>
	<resultMap id="staffResultMap" class="Staff" >
		<result property="staffId" column="STAFF_ID"/>
		<result property="staffName" column="STAFF_NAME"/>
		<result property="company" column="STAFF_COMPANY_ID" select="getCompany"/>
	</resultMap>
	<resultMap class="Company" id="companyResultMap">
		<result property="companyId" column="COMPANY_ID"/>
		<result property="companyName" column="COMPANY_NAME"/>
	</resultMap>
	<select id="selectStaff" parameterClass="Staff" resultMap="staffResultMap">
		<![CDATA[select *  from STAFF where STAFF_ID=#staffId#]]>
	</select>
	<select id="getCompany" parameterClass="java.lang.Integer" resultClass="java.lang.Integer">
		<![CDATA[select COMPANY_ID  from COMPANY where COMPANY_ID=#companyId#]]>
	</select>

</sqlMap> 

以上是传统的多对一配置，会出现N+1 Select

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">   
    <!-- 
    	联合查询和嵌套属性避免N+1 Select
     --> 
<sqlMap>
	<typeAlias alias="Staff" type="com.asarja.entity.Staff"/>
	<typeAlias alias="Company" type="com.asarja.entity.Company"/>
	<resultMap id="staffResultMap" class="Staff" >
		<result property="staffId" column="STAFF_ID"/>
		<result property="staffName" column="STAFF_NAME"/>
		<result property="company" column="COMPANY_ID"/>
	</resultMap>
	<select id="selectStaff" parameterClass="Staff" resultMap="staffResultMap">
		<![CDATA[select *  from STAFF,COMPANY where STAFF_COMPANY_ID=COMPANY_ID AND STAFF_ID=#staffId#]]>
	</select>

</sqlMap> 

以上是联合查询，会避免N+1 Select