N+1 Select of Ibatis multiple table query

1 Data Sheets

Book,user table. A one-to-many relationship, a book with multiple authors.

CREATE TABLE Book (
OID Int (a) not NULL,
Name varchar DEFAULT NULL,
PRIMARY KEY (OID)
);

CREATE TABLE User (
ID Int (a) unsigned not NULL,
Name varchar DEFAULT NULL,
book_oid Int (a) DEFAULT NULL,
PRIMARY KEY (ID),
KEY fk_user_1 (book_oid),
CONSTRAINT fk_user_1 FOREIGN KEY (book_oid) REFERENCES Book (OID)
);

2 Pojo Class (Getter,setter are omitted)

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;
}

You must define a list, Ibatis when implementing multiple table association queries.

3 Configuration Files

corresponding to the book.xml of Pojo

<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>&nbs P
  
  <select id= "Getusersbybookid" parameterclass= "int" resultclass= "User" > 
& nbsp  <! [cdata[ 
   select id,book_oid,u.name from user u where book_oid = #value # 
   ]]& gt; 
  </select>
</sqlMap> 

A One-to-many association that is implemented by the book's OID, Ibatis populates the Users property with a list of Getkeysbylockid (IDS)

4 test class

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 Summary

This is called N+1 Select. Because every time you check the book, you need to call two times the SQL statement (once book, check user). To avoid in practical applications.

We can use the form of a dto to avoid objects in the middle of the object. There are other ways to go online.

Original post address: http://blog.sina.com.cn/s/blog_6b30a46b0100qgi5.html