iBATIS一对一查询操作

iBATIS配置
1。webapp/web-info/spring/appServlet/datasource.xml中配置数据源 事务Aop 日志Aop等信息
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<context:component-scan base-package="com.appress.jdbctemplate"/>
<!-- 数据数的配置信息 -->
<util:properties id="dbconfig" location="classpath:dbconfig.properties"/>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="#{dbconfig.driverClassName}" />
<property name="username" value="#{dbconfig.username}"/>
<property name="password" value="#{dbconfig.password}"/>
<property name="url" value="#{dbconfig.url}"/>
<property name="initialSize" value="2"/>
<property name="maxActive" value="3"/>
</bean>

<bean id="mySqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocations">
<list>
<value>classpath:SqlMapConfig.xml</value>
</list>
</property>
<property name="dataSource" ref="dataSource"/>
</bean>

<!-- 把 customerDao 注入 在 WEB中用注解 直接导入-->
<bean id="customerDao" class="com.appress.jdbctemplate.SqlMapClientCustomerDao">
<!-- <property name="sqlMapClient" ref="sqlMapClient"></property> -->
</bean>
</beans>
2.Resource/dbconfig.properites 配置数据库信息
#Created by JInto - www.guh-software.de
#Thu Jan 14 21:38:08 CST 2010
driverClassName=com.mysql.jdbc.Driver
password=123456
url=jdbc:mysql://127.0.0.1:3306/test
username=root
3。Resource/SqlMapconfig.xml 导入各个resource

<?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>
<!-- enhancementEnabled 是否使用cglib来提高性能，如果classpath下没有cglib则被禁用 -->
<!-- cacheModelsEnabled 是否开启高速缓存，默认为true -->
<!-- lazyLoadingEnabled 是否开启延时加载，默认为true -->
<settings enhancementEnabled="true" cacheModelsEnabled="true"
lazyLoadingEnabled="true" errorTracingEnabled="true"/>
<!--
放所有的 具体的配置文件放在这
每一个 domain放在这
<sqlMap resource="1/appress/domain/CustomerInfo.xml"/>
</sqlMapConfig>
4.dao
package com.appress.jdbctemplate;

import java.util.List;

public interface CustomerDao {
public CustomerInfo getById(int id);
}
5.
package com.appress.jdbctemplate;

import java.util.List;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.ibatis.sqlmap.client.SqlMapClient;

public class SqlMapClientCustomerDao extends SqlMapClientDaoSupport implements CustomerDao {

// 在web中 直接注入
@Autowired
private SqlMapClient mySqlMapClient;

@PostConstruct
public void injectSqlMapClient(){
setSqlMapClient(mySqlMapClient);
System.out.println("******injectSqlMapClient()********"+mySqlMapClient.getClass().getName());
}

@SuppressWarnings("unchecked")
public List<CustomerInfo> getAll() {
return getSqlMapClientTemplate().queryForList("getAllCustomer");
}

public CustomerInfo getById(int id){
CustomerInfo customer=(CustomerInfo)this.getSqlMapClientTemplate().queryForObject("getCustomerById", id);
return customer;
}

}
6测试类
package com.appress.jdbctemplate;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

public class Test {

/**
* @param args
*/
public static void main(String[] args) {

ApplicationContext context=new FileSystemXmlApplicationContext("src/main/webapp/WEB-INF/spring/appServlet/datasourcetest.xml");
SqlMapClientCustomerDao customerDao=(SqlMapClientCustomerDao)context.getBean("customerDao");
//SqlMapClientCustomerDao customerDao=new SqlMapClientCustomerDao();
System.out.println(customerDao.getById(1000));

}

}
7。两个domain
(1)
package com.appress.jdbctemplate;

public class GoodsInfo {
private int id;
private String name;
private double price;

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 double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@Override
public String toString(){
return "GoodsInfo{"+"id:"+id+"name:"+name+"price:"+price+"}";
}
}
(2)
package com.appress.jdbctemplate;

import java.sql.Date;

public class CustomerInfo {

private int id;
private String first_name;
private String last_name;
private Date last_login;
private CustomerDetail customerDetail;

public CustomerDetail getCustomerDetail() {
return customerDetail;
}
public void setCustomerDetail(CustomerDetail customerDetail) {
this.customerDetail = customerDetail;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String firstName) {
first_name = firstName;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String lastName) {
last_name = lastName;
}

public Date getLast_login() {
return last_login;
}
public void setLast_login(Date lastLogin) {
last_login = lastLogin;
}

public String toString(){
return "{"+"id:"+id+" first_name:"+first_name+" last_name:"+last_name+" last_login:"
+last_login+customerDetail;
}


}
8 最重要的customer.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="CustomerInfo" type="com.appress.jdbctemplate.CustomerInfo"/>
<!-- (1)种方式 -->
[color=red]<typeAlias alias="customerDetail" type="com.appress.jdbctemplate.CustomerDetail"/>[/color]
<resultMap class="CustomerInfo" id="result">
<result property="id" column="id"/>
<result property="first_name" column="first_name"/>
<result property="last_name" column="last_name"/>
<result property="last_login" column="last_login"/>
</resultMap>
[color=cyan] <!-- （2）种方式
<resultMap class="customerDetail" id="customerDetail2">
<result property="customerDetailId" column="id"/>
<result property="data" column="data0"/>
</resultMap>

<resultMap class="CustomerInfo" id="resultDetail" extends="result">
<result property="customerDetail.customerDetailId" column="customer_detail_id"/>
<result property="customerDetail.data" column="customer_detail_data"/>
</resultMap>-->
<!--
<resultMap class="" id=""></resultMap>
<select id="getAllCustomer" resultMap="result">
select * from t_customer
</select>


<select id="getCustomerById" resultMap="resultDetail" parameterClass="int">
select c.id as id,c.first_name as first_name ,c.last_name as last_name,
c.last_login as last_login ,
cd.id as customer_detail_id,
cd.data0 as customer_detail_data

from t_customer c inner join t_customer_detail cd on
c.customer_detail=cd.id
where c.id=#value#
</select>
-->[/color] <!-- （1）中方式 -->
[color=red]<resultMap class="customerDetail" id="detailResult">
<result property="customerDetailId" column="id"/>
<result property="data" column="data0"/>
</resultMap>
<resultMap class="CustomerInfo" id="resultDetail" extends="result">
<result property="customerDetail" column="customer_detail" select="getCustomerDetailById"/>
</resultMap>
<select id="getCustomerById" resultMap="resultDetail" parameterClass="int">
select * from t_customer where id=#value#
</select>
<select id="getCustomerDetailById" resultMap="detailResult" parameterClass="int">
select * from t_customer_detail where id=#value#
</select>[/color]
</sqlMap>

数据库表：
create table t_customer(
id int not null,
first_name varchar(50) not null,
last_name varchar(50) not null,
last_login date ,
customer_detail int ,
CONSTRAINT pk_CustomerId PRIMARY key(id),
CONSTRAINT fk_CustomerDetail foreign key(customer_detail)
REFERENCES t_customer_detail(id)
)

create table t_customer_detail(
id int not null,
data0 varchar(100) not null,
constraint pk_customerdetail primary key(id)
)