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

数据库结构（mysql）

 

create table customerDetail(CustomerDetailId varchar(10) primary key,DataDetail varchar(10));
create table customers(id varchar(10) primary key,
                      firstname varchar(10),
                      lastname varchar(10),
                      customerDetail varchar(10),
                      customerGross varchar(10),
                      foreign key (customerDetail) references  customerDetail(CustomerDetailId),
                      foreign key (customerGross) references  customerDetail(CustomerDetailId));
insert into customerdetail values("1","detail1");
insert into customerdetail values("2","detail2");
insert into customerdetail values("3","detail3");
insert into customers values("1","firstname1","lastname1","1",null);
insert into customers values("2","firstname2","lastname2","2","3");

customer和customerDetail是1对1的关系
customer和customerGossIp是1对1的关系

 domain对象：

 

package ch10.SpringAndIbatisOneToOne;

import java.util.List;

public class Customers ...{
   private String customerId;
   private String firstName;
   private String lastName;
   private CustomerDetail customerDetail;
   private CustomerDetail customerGossIp;
public CustomerDetail getCustomerDetail() ...{
    return customerDetail;
}
public void setCustomerDetail(CustomerDetail customerDetail) ...{
    this.customerDetail = customerDetail;
}
public CustomerDetail getCustomerGossIp() ...{
    return customerGossIp;
}
public void setCustomerGossIp(CustomerDetail customerGossIp) ...{
    this.customerGossIp = customerGossIp;
}
public String getCustomerId() ...{
    return customerId;
}
public void setCustomerId(String customerId) ...{
    this.customerId = customerId;
}
public String getFirstName() ...{
    return firstName;
}
public void setFirstName(String firstName) ...{
    this.firstName = firstName;
}
public String getLastName() ...{
    return lastName;
}
public void setLastName(String lastName) ...{
    this.lastName = lastName;
}


}


package ch10.SpringAndIbatisOneToOne;

public class CustomerDetail ...{
   private String customerDetailId;
   private String dataDetail;
public String getCustomerDetailId() ...{
    return customerDetailId;
}
public void setCustomerDetailId(String customerDetailId) ...{
    this.customerDetailId = customerDetailId;
}
public String getDataDetail() ...{
    return dataDetail;
}
public void setDataDetail(String dataDetail) ...{
    this.dataDetail = dataDetail;
}

}

 

DAO接口：

 

package ch10.SpringAndIbatisOneToOne;

import java.util.List;

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

 

DAO实现类：

 

package ch10.SpringAndIbatisOneToOne;

import java.util.List;

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

public class TestDAO extends SqlMapClientDaoSupport implements IDAO ...{

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

}

 

Ibatis配置文件：

 

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

<sqlMap resource="ch10/SpringAndIbatisOneToOne/Ibatis.xml" /> 

</sqlMapConfig>

 

ibatis sql map文件：

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.//DTD SQL Map 2.0//EN" "http://ibatis./dtd/sql-map-2.dtd" >
<sqlMap>
    <!-- 定义类的别名，以下配置中使用别名 -->
    <typeAlias type="ch10.SpringAndIbatisOneToOne.Customers" alias="customer"/>
    <typeAlias type="ch10.SpringAndIbatisOneToOne.CustomerDetail" alias="customerDetail"/>
    
    <!-- 定义基本的resultMap -->
    <resultMap class="customer" id="result">
      <result property="customerId" column="CustomerId"/>
      <result property="firstName" column="FirstName"/>
      <result property="lastName" column="LastName"/>
    </resultMap>
    
    <resultMap class="customerDetail" id="gossipDetail">
      <result property="customerDetailId" column="CustomerDetailId"/>
      <result property="dataDetail" column="DataDetail"/>
    </resultMap>
    
    
    <!-- 使用如下这种集成关系的好处是我们可以只返回我们需要的数据，而不是总返回全部数据
         比如我们只关心customer，而不关心他其中的customerDetail和customerGossIp,那我们之用
         名为result的resultMap就可以了 --> 
    
    <!-- 定义符合resultMap,这种配置表明了customer的customerDetail不能为空，而customerGross可以为空 >-->
    <resultMap class="customer" id="resultDetail" extends="result">
       <result property="customerDetail.customerDetailId" column="CustomerDetailID"/>
       <result property="customerDetail.dataDetail" column="DataDetail"/>
        <result property="customerGossIp" column="CustomerGossIp" select="getCustomerGossById"/> 
    </resultMap>
    
    
    <select id="getCustomerGossById" resultMap="gossipDetail" parameterClass="string">
         select CustomerDetailId,DataDetail from CustomerDetail where CustomerDetailId=#value#     
    </select>
    
    <!-- 根据customerid获得Customer-->

   
    <select id="getCustomerById" resultMap="resultDetail" parameterClass="string">
     select c.CustomerId as CustomerId,
              c.FirstName as FirstName,
              c.LastName as LastName,
              c.CustomerDetail as CustomerDetail,
              c.CustomerGossIp as CustomerGossIp,
              cd.CustomerDetailID as CustomerDetailID,
              cd.Datadetail as DataDetail
      from customers c 
      inner join customerdetail cd  on c.CustomerDetail=cd.CustomerDetailId 
      where c.CustomerId=#value#
    </select>
</sqlMap>

 

spring配置文件

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

 

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www./schema/beans"
    xmlns:xsi="http://www./2001/XMLSchema-instance"
    xsi:schemaLocation="http://www./schema/beans http://www./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/SpringAndIbatisOneToOne/sqlMapConfig.xml</value>
  </property>
    <property name="dataSource">
   <ref bean="dataSource"/>
 </property>

</bean>

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

</beans>

 

测试代码：

 

package ch10.SpringAndIbatisOneToOne;

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

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/SpringAndIbatisOneToOne/applicationContext.xml");
        TestDAO testDAOImpl=(TestDAO)context.getBean("testDAO");

        Customers result1=(Customers)testDAOImpl.getCustomersById("1");
    
        Customers result2=(Customers)testDAOImpl.getCustomersById("2");
        
    
      
         System.out.println(result1.getFirstName()+"-"+result1.getLastName()+"-"+result1.getCustomerDetail().getDataDetail()+"-"+result1.getCustomerGossIp().getDataDetail());
      
         System.out.println("-----------------");
      
         System.out.println(result2.getFirstName()+"-"+result2.getLastName()+"-"+result2.getCustomerDetail().getDataDetail()+"-"+result2.getCustomerGossIp().getDataDetail());
        
    }

}

 

结果：

firstname1-lastname1-detail1-detail2
-----------------
firstname2-lastname2-detail2-detail3

from:http://blog.csdn.net/daryl715/archive/2007/08/28/1762439.aspx