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,

  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;

}

import java.util.list;

public class book

 private integer oid;

 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"> 

   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 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) {

        }

    }

5 總結

這就叫 n+1 select。因為每查一次book，就要調用兩次sql語句(一次查book,一次查user)。實際應用中要避免。

我們可以用dto的形式，避免了對象中間套對象。還可以有其它一些方法，大家上網查閱。

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