iBATIS多表查询之N+1 Select

1 数据表

book,user表。一对多关系，一本书有多个作者。

CREATE TABLE book (
  oid int(10) NOT NULL ,
  name varchar(50) DEFAULT NULL,
  PRIMARY KEY (oid)
);

CREATE TABLE user (
  id int(10) unsigned NOT NULL,
  name varchar(50) DEFAULT NULL,
  book_oid int(10) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY FK_user_1 (book_oid),
  CONSTRAINT FK_user_1 FOREIGN KEY (book_oid) REFERENCES book (oid)
);

2 Pojo类(getter,setter均省略)

package com.pojo;

public class User
{
 private Integer id;
 private String name;
 private Integer book_oid;
}

package com.pojo;
import java.util.List;
public class Book
{
 private Integer oid;
 private String name;
 private List users;
}

必须定义个list ，ibatis在实现多表关联查询时。

3 配置文件

对应pojo的book.xml

<sqlMap namespace="test"> 
  <typeAlias alias="User" type="com.pojo.User"/> 
  <typeAlias alias="Book" type="com.pojo.Book"/> 

  <resultMap id="BookResult" class="Book"> 
   <result property="oid" column="oid"/> 
   <result property="name" column="name"/> 
   <result property="users" column="oid" select="getUsersByBookId"/> 
  </resultMap>
 
  <select id="selectAllBooks" resultMap="BookResult"> 
   <![CDATA[ 
   select oid,b.name from book b
   ]]> 
  </select> 
  
  <select id="getUsersByBookId" parameterClass="int" resultClass="User"> 
   <![CDATA[ 
   select id,book_oid,u.name from user u where book_oid = #value# 
   ]]> 
  </select>
</sqlMap> 

通过book的oid实现的一对多关联,ibatis会使用getKeysByLockId(id)得到的List填充users属性

 

4 测试类

package com.test;
import java.io.IOException;
import java.io.Reader;
import java.util.Iterator;
import java.util.List;
import com.crfss.MainDb;
import com.crfss.Un;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import com.pojo.Book;
import com.pojo.User;

public class TestDB {

 public static void main(String[] args) {
        String resource = "sql-map-config.xml";
        try {
            Reader reader = Resources.getResourceAsReader(resource);
            SqlMapClient mapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
            reader.close();
            List<Book> books=mapClient.queryForList("selectAllBooks");
            Book book=(Book)books.get(0);
            List users=(List) book.getUsers();
      
           for (Iterator iterator = users.iterator(); iterator.hasNext();) {
            User user = (User) iterator.next();
            System.out.println(user.getBook_oid());
            System.out.println(user.getName());
            System.out.println(user.getId());
        }  
        catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5 总结

这就叫 N+1 Select。因为每查一次book，就要调用两次sql语句(一次查book,一次查user)。实际应用中要避免。

我们可以用dto的形式，避免了对象中间套对象。还可以有其它一些方法，大家上网查阅。

 

 

原帖地址:http://blog.sina.com.cn/s/blog_6b30a46b0100qgi5.html