OOP编程iBatis 学习笔记之三 2个表或者多表关联查询

  普通程序编写都需要多表联合查询，本例子是展示如何关联2个表查询的效果，多表可以模拟本例子扩展

Note.java

 

public class Note {

 private int sid=0;

 private String sname=null;

 private String major=null;

 private Date birth=null;

 private int book_oid;
 public int getBook_oid() {
    return book_oid;
}

public void setBook_oid(int bookOid) {
    book_oid = bookOid;
}

public int getSid() {
    return sid;
}

public void setSid(int sid) {
    this.sid = sid;
}

public String getSname() {
    return sname;
}

public void setSname(String sname) {
    this.sname = sname;
}

public String getMajor() {
    return major;
}

public void setMajor(String major) {
   this.major = major;
}

public Date getBirth() {
    return birth;
}

public void setBirth(Date birth) {
    this.birth = birth;
}
}

 

 

 

Book.java

 

package com.it;
import java.util.List;

public class Book {
 private int oid;
 private String name;
 private List notes;

 public int getOid() {
    return oid;
}

public void setOid(int oid) {
    this.oid = oid;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public List getNotes() {
    return notes;
}

public void setNotes(List notes) {
    this.notes = notes;
}
}

 

 

 

Book.xml

 

<?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="Book" type="com.it.Book"/>
 <resultMap id="BookResult" class="Book"> 
 <result property="oid" column="oid"/> 
 <result property="name" column="name"/> 
 <result property="notes" column="oid" select="geNotesByBookId"/> 
  </resultMap>
   <select id="selectAllBooks" resultMap="BookResult"> 
 <![CDATA[ 
 select oid,b.name from book b
 ]]> 

 </select> 
 <select id="geNotesByBookId" parameterClass="int" resultClass="Note"> 

 <![CDATA[ 
 select u.book_oid,u.sname from note u where u.book_oid = #sid# 
 ]]> 
 </select>
</sqlMap>

 

Note.xml

 

<?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="Note" type="com.it.Note"/>
 <select id="selectAllNote" resultClass="Note">
   select * 

 from Note 

 </select>

</sqlMap>

 

测试

 

package com.it;
/***

 *2009-10-8 

 *2表关联 

 **/
import java.io.IOException;
import java.io.Reader;
import java.sql.Date;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

public class INoteDAOImpl implements INoteDao {
     private static SqlMapClient sqlMapClinet=null;

    static {

           Reader reader;
     try {

         String resource="com/it/SqlMapConfig.xml";

        reader = com.ibatis.common.resources.Resources.getResourceAsReader(resource);                   sqlMapClinet=com.ibatis.sqlmap.client.SqlMapClientBuilder.buildSqlMapClient(reader);

           reader.close();   

            } catch (IOException e) {

               e.printStackTrace();
            }
         }    

    public static void main( String[] args) {
     try {

      List<Book> books=sqlMapClinet.queryForList("selectAllBooks");
       System.out.println(books.size());
        Book book=(Book)books.get(0);
        //System.out.println(book.getName());
        List notes=(List) book.getNotes();

       for (Iterator iterator = notes.iterator(); iterator.hasNext();) {

       Note note = (Note) iterator.next();
        System.out.println(note.getBook_oid());
        System.out.println(note.getSname());
       }
                   } catch (SQLException e) {

                            e.printStackTrace();
                   }
         }
}