ibatis:延迟加载和N+1问题-CSDN博客

为什么80%的码农都做不了架构师？>>>

数据库I/O可以很好的度量数据库的使用情况，同时也是影响数据库性能的主要瓶颈之一。数据从磁盘到内存或则从内容写到磁盘这种操作时非常耗时。高速缓存能很好的应到到这一问题来提高性能。
  N+1问题是由于试图加载和父记录相关的子记录而造成的。当如果你已经获取一个父记录时，若想获得所有的这个父记录的子记录时，你需要多执行N个查询，为了把子记录查询出来。
  延迟加载将加载分层了更小的易管理的小过程，只有在需要的时候才会加载额外的数据。这样很好解决了数据库I/O问题，但它导致了N+1问题。数据库I/O和N+1问题两个问题必有其一。这往往要考虑到实际的需要来决定采用哪个策略。

iBATIA延迟加载
  Person 可以有多个Address。
Java代码
package com.min.ibatis.model;

import java.util.List;

public class Person {
     private int id;
     private String name;
     private List<Address> addresses;
     public int getId() {
         return id;
    }
     public void setId( int id) {
         this.id = id;
    }
     public String getName() {
         return name;
    }
     public void setName(String name) {
         this.name = name;
    }
     public List<Address> getAddresses() {
         return addresses;
    }
     public void setAddresses(List<Address> addresses) {
         this.addresses = addresses;
    }
}
Java代码
package com.min.ibatis.model;

public class Address {
     private int id;
     private String address;
     public int getId() {
         return id;
    }
     public void setId( int id) {
         this.id = id;
    }
     public String getAddress() {
         return address;
    }
     public void setAddress(String address) {
         this.address = address;
    }
}

iBATIS的sql映射配置文件
Java代码
<?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">

<sqlMap>
    <typeAlias alias= "Person" type= "com.min.ibatis.model.Person"/>
    <typeAlias alias= "Address" type= "com.min.ibatis.model.Address"/>

    <resultMap class= "Person" id= "personMap">
        <result property= "id" column= "id"/>
        <result property= "name" column= "name"/>
        <result property= "addresses" select= "getAddresses" column= "id"/>
    </resultMap>
    <resultMap class= "Address" id= "addressMap">
        <result property= "id" column= "id"/>
        <result property= "address" column= "address"/>
    </resultMap>

    <select id= "getAddresses" resultMap= "addressMap">
        select * from address where personId=#value#
    </select>

    <select id= "getPerson" resultMap= "personMap">
        select * from person where id = #value#
    </select>
</sqlMap>
测试类
Java代码
package com.min.ibatis.model;

import java.io.Reader;
import java.sql.SQLException;

import junit.framework.TestCase;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;


public class Test extends TestCase {
     private SqlMapClient sqlMap;
     @Override
     protected void setUp() throws Exception {
        String resource = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

    }

     public void testLoadLazy(){
         int id = 1;
         try {
            Person person = (Person)sqlMap.queryForObject( "getPerson",id);
            System.out.println(person.getName());
            Address address = person.getAddresses().get( 0);
            System.out.println(address.getAddress());
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
默认情况下，SqlMapConfig.xml中
<settings lazyLoadingEnabled="true" enhancementEnabled="true"/>
enhancementEnabled="true" 说明使用延迟加载cglib增强版。只需加入cglib.jar包就可以了。
通过debug，我们发现 person的addresses属性引用的是代理类。

避免N+1查询问题
Java代码
<?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">

<sqlMap>
    <typeAlias alias= "Person" type= "com.min.ibatis.model.Person"/>
    <typeAlias alias= "Address" type= "com.min.ibatis.model.Address"/>

    <resultMap class= "Person" id= "personMap" groupBy= "id">
        <result property= "id" column= "id"/>
        <result property= "name" column= "name"/>
        <result property= "addresses"  resultMap= "addressMap"/>
    </resultMap>

    <resultMap class= "Address" id= "addressMap">
        <result property= "id" column= "id"/>
        <result property= "address" column= "address"/>
    </resultMap>



    <select id= "getPerson" resultMap= "personMap">
        select * from person join address on person.id = address.personId
    </select>
</sqlMap>

延迟加载适用于大型的但并非所有的数据都有用的那些数据集。
避免N+1查询方案使用于小型的或则所有的数据都会被使用的那些数据集。

转载于:https://my.oschina.net/zhk/blog/111023