Spring+Ibatis处理1对多数据表的例子

数据库脚本（MYsqL）

 

create table Orders (OrderId varchar(10) primary key,Customer varchar(10));
create table OrderLines(OrderLineId varchar(10) primary key,Product varchar(10),OrderId varchar(10),
                        foreign key(OrderId) references Orders(OrderId));

insert into Orders values("1","john");
insert into Orders values("2","marry");

insert into OrderLines values("1","computer","1");
insert into OrderLines values("2","bike","1");
insert into OrderLines values("3","cat","2");
insert into OrderLines values("4","dog","2");
insert into OrderLines values("5","basketball","2");

domain对象

 

package ch10.SpringAndIbatisOneToMany;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

public class Order implements Serializable...{
   private String orderid;
   private String customer;

   private List orderLines;
public String getCustomer() ...{
    return customer;
}
public void setCustomer(String customer) ...{
    this.customer = customer;
}
public String getOrderid() ...{
    return orderid;
}
public void setOrderid(String orderid) ...{
    this.orderid = orderid;
}
public List getOrderLines() ...{
    return orderLines;
}
public void setOrderLines(List orderLines) ...{
    this.orderLines = orderLines;
}

}

 

package ch10.SpringAndIbatisOneToMany;

import java.io.Serializable;

public class OrderLine implements Serializable ...{
   private String orderLineId;
   private String product;
   public OrderLine()...{
       
   }
public OrderLine(String orderLineId, String product) ...{
    super();
    this.orderLineId = orderLineId;
    this.product = product;
}
public String getOrderLineId() ...{
    return orderLineId;
}
public void setOrderLineId(String orderLineId) ...{
    this.orderLineId = orderLineId;
}
public String getProduct() ...{
    return product;
}
public void setProduct(String product) ...{
    this.product = product;
}
}

 

dao接口

 

package ch10.SpringAndIbatisOneToMany;

import java.util.List;

public interface IDAO ...{
   public Object getOrderById(String id);
}

 

dao实现

 

package ch10.SpringAndIbatisOneToMany;

import java.util.List;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

public class TestDAO extends SqlMapClientDaoSupport implements IDAO ...{

    public Object getOrderById(String id) ...{
        
        return this.getSqlMapClientTemplate().queryForObject("getOrderById",id);
    }

    
    

}

 

ibatis配置文件：

 

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE sqlMapConfig 
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-config-2.dtd"> 
<sqlMapConfig> 
<sqlMap resource="ch10/SpringAndIbatisOneToMany/Ibatis.xml" />
</sqlMapConfig>

 

ibatis sql map配置文件：

 

<?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 type="ch10.SpringAndIbatisOneToMany.Order" alias="order"/>
    <typeAlias type="ch10.SpringAndIbatisOneToMany.OrderLine" alias="order_line"/>
    
     <resultMap class="order" id="result">
       <result property="orderid" column="OrderId"/>
       <result property="customer" column="Customer"/>
       <result property="orderLines" select="getOrderLinesByOrder" column="OrderId"/>
     </resultMap>
     
     <resultMap class="order_line" id="resultLine">
      <result property="orderLineId" column="orderLineId"/>
       <result property="product" column="Product"/>
     </resultMap>
     
     <select id="getOrderLinesByOrder" resultMap="resultLine" parameterClass="string">select orderLineId,Product from orderlines where orderid=#value#</select>
     
     <select id="getOrderById" resultMap="result" parameterClass="string">select OrderId,Customer,OrderId from Orders where OrderId=#value#</select>
</sqlMap>

 

Spring配置文件：

如果要使用1对1，1对多这样牵扯两个表的情况，一定要为SqlMapClientFactoryBean设置dataSource属性

 

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName">
    <value>com.mysql.jdbc.Driver</value>
  </property>
  <property name="username">
    <value>root</value>
  </property>
  <property name="password">
    <value>1234</value>
  </property>
  <property name="url">
    <value>jdbc:mysql://localhost:3306/spring</value>
  </property>
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <!-- 此处应注入ibatis配置文件，而非sqlMap文件，否则会出现“there is no statement.....异常” -->
  <property name="configLocation">
     <value>ch10/SpringAndIbatisOneToMany/sqlMapConfig.xml</value>
  </property>
  <property name="dataSource">
   <ref bean="dataSource"/>
 </property>

</bean>

<bean id="testDAO" class="ch10.SpringAndIbatisOneToMany.TestDAO">
  <property name="dataSource">
   <ref bean="dataSource"/>
   </property>
  <property name="sqlMapClient">
    <ref bean="sqlMapClient"/>
  </property>
</bean>

</beans>

 

测试代码：

 

package ch10.SpringAndIbatisOneToMany;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.omg.PortableInterceptor.SYSTEM_EXCEPTION;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import ch10.SpringAndIbatis.Ibatis;

public class Test ...{

    /** *//**
     * @param args
     */
    public static void main(String[] args) ...{
        ApplicationContext context=new ClassPathXmlApplicationContext("ch10/SpringAndIbatisOneToMany/applicationContext.xml");
        TestDAO testDAOImpl=(TestDAO)context.getBean("testDAO");

        Order order=(Order)testDAOImpl.getOrderById("1");
        
        System.out.println("Order info:");
        System.out.println(order.getCustomer());
        System.out.println("OrderLine info:");
        
        List result=order.getOrderLines();
    
        for (Iterator iter = result.iterator(); iter.hasNext();) ...{
            OrderLine element = (OrderLine) iter.next();
            System.out.println(element.getProduct());
            
        }
        
        
    }

}

 

结果：

Order info:
marry
OrderLine info:
cat
dog
basketball