ibatis连接查询

本例子连接tb_account,tb_order,tb_orderItem三张表进行连接查询。

Account.java代码如下：

package com.ibatis.chap6.test;

import java.util.List;

public class Account {

	/* 用户id */
	private int id ;
	/* 用户名*/
	private String name ;
	/* 和该用户关联的订单*/
	private List<Order> orderList;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

	public List<Order> getOrderList() {
		return orderList;
	}

	public void setOrderList(List<Order> orderList) {
		this.orderList = orderList;
	}
}

Order.java代码如下：

package com.ibatis.chap6.test;

import java.util.List;

public class Order {
	/*订单id*/
	private int id ;
	/*订单名字*/
	private String name ;
	/* 与订单相关联的订单项*/
	private List<OrderItem> orderItemList;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

	public List<OrderItem> getOrderItemList() {
		return orderItemList;
	}

	public void setOrderItemList(List<OrderItem> orderItemList) {
		this.orderItemList = orderItemList;
	}

}

OrderItem.java代码如下：

package com.ibatis.chap6.test;

public class OrderItem {

	/*订单项id*/
	private int id ;
	/*订单项的名字*/
	private String name ;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

}

SqlMap.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>
	<resultMap class="com.ibatis.chap6.test.Account" id="ResultAccountInfoNMap" >
		<result property="id" column="accountid"/>
		<result property="name" column="accountName"/>
		<result property="orderList" resultMap="ResultOrderInfoNmap" />
	</resultMap>

	<resultMap class="com.ibatis.chap6.test.Order" id="ResultOrderInfoNmap">
		<result property="id" column="orderid" />
		<result property="name" column="ordername"/>
		<result property="orderItemList" resultMap="ResultOrderItemNMap" />
	</resultMap>

	<resultMap class="com.ibatis.chap6.test.OrderItem" id="ResultOrderItemNMap">
		<result property="id" column="orderItemid" />
		<result property="name" column="orderItemname"/>
		<!-- <result property="order" column="orderId" /> -->
	</resultMap>

	<select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap" parameterClass="int">
	 select
	   tb_account.accountid as accountId,
	   tb_account.accountname as accountName,
	   tb_order.orderid as orderId,
	   tb_order.ordername as ordername,
	   tb_orderItem.orderItemid as orderItemId,
	   tb_orderItem.orderItemname as orderItemname
	 from tb_account join tb_order on tb_account.accountid = tb_order.accountId
	 join tb_orderItem on tb_order.orderid = tb_orderItem.orderId
	 order by accountId,orderId,orderItemId
	</select>
</sqlMap>

SqlMapConfig.xml代码如下：

<?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>
  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
       <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
       <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/ibatis"/>
       <property name="JDBC.Username" value="root"/>
       <property name="JDBC.Password" value="admin"/>
    </dataSource>
  </transactionManager>
   <sqlMap resource="com//ibatis//chap6//test//SqlMap.xml"/>
</sqlMapConfig>

Test.java代码如下：

package com.ibatis.chap6.test;

import java.io.Reader;
import java.util.List;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Test {

	public static void main(String[] args) throws Exception{
		String resource = "com//ibatis//chap6//test//SqlMapConfig.xml";
		Reader reader = Resources.getResourceAsReader(resource);
		SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
		List<Account> accountList =  sqlMap.queryForList("getAccountInfoListN",null);
		System.out.println("===================查询数据如下===================");
		for(int i=0;i<accountList.size();i++){
		System.out.println("accountid : "+accountList.get(i).getId()+" , accountName : "+accountList.get(i).getName()+",orderId : "+
		accountList.get(i).getOrderList().get(0).getId()+",orderName : "+accountList.get(i).getOrderList().get(0).getName()+
		", orderItemId : "+accountList.get(i).getOrderList().get(0).getOrderItemList().get(0).getId()+",orderItemName : "+
		accountList.get(i).getOrderList().get(0).getOrderItemList().get(0).getName());	
		}
	}

}

数据库表记录如下：

[img]http://dl2.iteye.com/upload/attachment/0087/6004/d5dd987a-d7e5-3ea9-a31b-2fdbd3efb44d.jpg[/img]

程序运行结果如下：

===================查询数据如下===================
accountid : 1 , accountName : YANGJIANZHOU,orderId : 1,orderName : order1, orderItemId : 1,orderItemName : orderItem1
accountid : 1 , accountName : YANGJIANZHOU,orderId : 1,orderName : order1, orderItemId : 2,orderItemName : orderItem2
accountid : 1 , accountName : YANGJIANZHOU,orderId : 2,orderName : order2, orderItemId : 3,orderItemName : orderItem3
accountid : 1 , accountName : YANGJIANZHOU,orderId : 2,orderName : order2, orderItemId : 4,orderItemName : orderItem4
accountid : 2 , accountName : CTGU,orderId : 3,orderName : order3, orderItemId : 5,orderItemName : orderItem5
accountid : 2 , accountName : CTGU,orderId : 3,orderName : order3, orderItemId : 6,orderItemName : orderItem6
accountid : 2 , accountName : CTGU,orderId : 4,orderName : order4, orderItemId : 7,orderItemName : orderItem7
accountid : 2 , accountName : CTGU,orderId : 4,orderName : order4, orderItemId : 8,orderItemName : orderItem8
accountid : 2 , accountName : CTGU,orderId : 4,orderName : order4, orderItemId : 9,orderItemName : orderItem9