ibatis的多表查询

一：描述下：

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

表语句：CREATE TABLE `book` (
  `oid` int(10) NOT NULL ,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ；

DROP TABLE IF EXISTS `user`;
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`)
) ；

二：对应的Pojo类

package com.pojo;

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

........一下是get set 注意在ibatis定义的pojo类中要有个空的构造函数哦~~，

package com.pojo;

import java.util.List;

public class Book {
private Integer oid;
private String name;
private List users;

.......get set  注意：必须定义个list ，ibatis在实现多表关联查询时。

配置文件：对应pojo的 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 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>
  <!-- 通过book的oid实现的一对多关联,ibatis会使用getKeysByLockId(id)得到的List填充users属性 -->
 
 
  <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> 

在定义个配置文件调用上面的配置文件:如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings cacheModelsEnabled="true"
           enhancementEnabled="true"
        lazyLoadingEnabled="true"
        errorTracingEnabled="true"
        maxRequests="132"
        maxSessions="110"
        maxTransactions="15"
        useStatementNamespaces="false" />
<transactionManager type="JDBC">
  <dataSource type="DBCP">
   <property name="JDBC.Driver" value="com.mysql.jdbc.Driver" />
   <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/test" />
   <property name="JDBC.Username" value="root" />
   <property name="JDBC.Password" value="root" />
 
  </dataSource>
</transactionManager>
<sqlMap resource="com/crfss/book.xml" />
</sqlMapConfig>

三：测试类：





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);
            //System.out.println(book.getName());
            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();
        }
    }
}
   

包自己导入吧~~~~~~~~ 

努力中~~~~~~~~~
转载的学习呀，望见谅呀