818
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 >
<result property="oid" column="oid"/>
<result property="name" column="name"/>
<result property="users" column="oid" select="getUsersByBookId"/>
</resultMap>
<select resultMap="BookResult">
<![CDATA[
select oid,b.name from book b
]]>
</select>
<select 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的形式，避免了對象中間套對象。還可以有其它一些方法，大家上網查閱。
原帖地址:https://blog.sina.com.cn/s/blog_6b30a46b0100qgi5.html
最後更新：2017-04-02 17:51:26