iBatis2学习笔记：一对多映射（双向）

iBatis2学习笔记：一对多映射（双向）

 

环境：
MySQL5.51b
iBatis 2.3
Spring 2.5.4

 

本来是单向的，稍微改改就成为双向的了。呵呵。

 

一、模型

二、SQL

/*==============================================================*/ 
/* Table: customer                                              */ 
/*==============================================================*/ 
create table customer 
( 
   id                   bigint not null, 
   address              varchar(120), 
   postcode             varchar(6), 
   sex                  varchar(2), 
   name                 varchar(24), 
   primary key (id) 
); 

alter table customer comment '客户'; 


/*==============================================================*/ 
/* Table: orders                                                */ 
/*==============================================================*/ 
create table orders 
( 
   id                   bigint not null, 
   code                 varchar(24), 
   customerId           bigint not null, 
   primary key (id) 
); 

alter table orders comment '订单'; 

alter table orders add constraint FK_rf1 foreign key (customerId) 
      references customer (id) on delete restrict on update restrict; 

 

二、POJO

public class Customer { 
    private Long id; 
    private String name; 
    private String address; 
    private String postcode; 
    private String sex; 
    private List<Orders> orderlist = new ArrayList<Orders>();

 

    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", address='" + address + '\'' +
                ", postcode='" + postcode + '\'' +
                ", sex='" + sex + '\'' +
                ", orderlist=" + orderlist.size() +
                '}';
    }

 

public class Orders { 
    private Long id; 
    private String code; 
    private Long customerId; 
    private Customer customer;

 

    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", code='" + code + '\'' +
                ", customerId=" + customerId +
                '}';
    }

 

三、SqlMap

 

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 namespace="customer"> 
    <typeAlias alias="customer" type="com.lavasoft.ssi.domain.Customer"/> 

    <resultMap id="result_base" class="customer"> 
        <result property="id" column="id"/> 
        <result property="name" column="name"/> 
        <result property="address" column="address"/> 
        <result property="postcode" column="postcode"/> 
        <result property="sex" column="sex"/> 
    </resultMap> 
    <resultMap id="result" class="customer" extends="result_base"> 
        <result property="orderlist" column="id" select="orders.findByCustomerId"/> 
    </resultMap> 

    <insert id="insert" parameterClass="customer"> 
        insert into customer(address,postcode,sex,name) values(#address#,#postcode#,#sex#,#name#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 
    <select id="getById" parameterClass="long" resultMap="result_base"> 
        select * from customer where id = #value# 
    </select> 
    <select id="getWithCashById" parameterClass="long" resultMap="result"> 
        select * from customer where id = #value# 
    </select> 
    <select id="getWithCashByIdInnerjoin" parameterClass="long" resultClass="customer" resultMap="result"> 
        select c.* from customer c inner join orders o on c.id=o.customerId 
    </select> 


</sqlMap>

 

Orders.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="orders"> 
    <typeAlias alias="orders" type="com.lavasoft.ssi.domain.Orders"/> 
    <resultMap id="result_base" class="orders"> 
        <result property="id" column="id"/> 
        <result property="code" column="code"/> 
        <result property="customerId" column="customerId"/> 
    </resultMap> 
    <resultMap id="result" class="orders" extends="result_base"> 
        <result property="customer" column="customerId" select="customer.getById"/> 
    </resultMap> 

    <insert id="insert" parameterClass="orders"> 
        insert into orders(id,code,customerId) values(#id#,#code#,#customerId#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 

    <select id="findByCustomerId" resultMap="result_base" parameterClass="long"> 
        select * from orders where customerId = #value# 
    </select> 
    <select id="getById" parameterClass="long" resultMap="result_base"> 
        select * from orders where id = #value# 
    </select> 
    <select id="getByIdWithCash" resultMap="result" resultClass="orders" parameterClass="long"> 
        select * from orders where id = #value# 
    </select> 

</sqlMap>

 

四、DAO

public interface CustomerDAO { 
    public Long insert(Customer c); 
    public List<Customer> getById(Long id); 
    public List<Customer> getWithCashById(Long id); 
    public List<Customer> getWithCashByIdInnerjoin();     
}

 

public interface OrdersDAO { 
    public Long insert(Orders o); 
    public Orders getById(Long id); 
    public List<Orders> findByCustomerId(Long cid); 
    public List<Orders> getByIdWithCash(Long id); 
}

 

public class CustomerDAOImpl extends SqlMapClientDaoSupport implements CustomerDAO { 

    public Long insert(Customer c) { 
        return (Long) getSqlMapClientTemplate().insert("customer.insert",c); 
    } 

    public List<Customer> getById(Long id) { 
        return getSqlMapClientTemplate().queryForList("customer.getById",id); 
    } 

    public List<Customer> getWithCashById(Long id) { 
        return getSqlMapClientTemplate().queryForList("customer.getWithCashById",id); 
    } 
    public List<Customer> getWithCashByIdInnerjoin(){ 
        return getSqlMapClientTemplate().queryForList("customer.getWithCashByIdInnerjoin"); 
    } 
}

 

public class OrdersDAOImpl extends SqlMapClientDaoSupport implements OrdersDAO { 

    public Long insert(Orders o) { 
        return (Long) getSqlMapClientTemplate().insert("orders.insert", o); 
    } 

    public Orders getById(Long id) { 
        return (Orders) getSqlMapClientTemplate().queryForObject("orders.getById", id); 
    } 

    public List<Orders> findByCustomerId(Long cid) { 
        return getSqlMapClientTemplate().queryForList("orders.findByCustomerId", cid); 
    } 

    public List<Orders> getByIdWithCash(Long id) { 
        return (List<Orders>) getSqlMapClientTemplate().queryForList("orders.getByIdWithCash",id); 
    } 
}

 

五、测试

public class ApplicationContextUtils { 
    private static ApplicationContext applicationContext; 

    static { 
        if (applicationContext == null) 
            applicationContext = rebuildApplicationContext(); 
    } 

    public static ApplicationContext rebuildApplicationContext() { 
        return new ClassPathXmlApplicationContext("ApplicationContext.xml"); 
    } 

    public static ApplicationContext getApplicationContext() { 
        return applicationContext; 
    } 

    /** 
     * @param args 
     */ 
    public static void main(String[] args) { 
        rebuildApplicationContext(); 
        if (applicationContext == null) { 
            System.out.println("ApplicationContext is null"); 
        } else { 
            System.out.println("ApplicationContext is not null!"); 
        } 
    } 
}

 

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-6-15 22:50:15<br> 
* <b>Note</b>: 客户订单一对多模型：客户 
*/ 
public class CustomerDAOTest { 
    private CustomerDAO customerDAO = (CustomerDAO) ApplicationContextUtils.getApplicationContext().getBean("customerDAO"); 

    public void testInsert() { 
        System.out.println("--------insert(Customer c)--------"); 
        Customer c = new Customer(); 
        //fuck!竟然不支持级联保存！ 
//        Orders order1 = new Orders("o1"); 
//        Orders order2 = new Orders("o2"); 
//        c.getOrderlist().add(order1); 
//        c.getOrderlist().add(order2); 
        c.setName("多对一"); 
        c.setSex("M"); 
        c.setPostcode("450003"); 
        c.setAddress("郑州市花园路"); 
        Long pk = customerDAO.insert(c); 
        System.out.println("插入数据的ID=" + pk); 
    } 

    public void testGetById() { 
        System.out.println("--------getById(Long id)--------"); 
        Long pk = 1L; 
        List<Customer> list = customerDAO.getById(pk); 
        for (Customer c : list) { 
            System.out.println(c); 
        } 
    } 

    public void testGetWithCashById() { 
        System.out.println("--------getWithCashById(Long id)--------"); 
        Long pk = 1L; 
        List<Customer> list = customerDAO.getWithCashById(pk); 
        for (Customer c : list) { 
            System.out.println(c); 
        } 
    } 

    public void testGetWithCashByIdInnerjoin() { 
        System.out.println("--------getWithCashByIdInnerjoin()--------"); 
        List<Customer> list = customerDAO.getWithCashByIdInnerjoin(); 
        for (Customer c : list) { 
            System.out.println(c); 
        } 
    } 

    public static void main(String args[]) { 
        System.out.println("正在测试CustomerDAO"); 
        CustomerDAOTest customerDAOTest = new CustomerDAOTest(); 
        customerDAOTest.testInsert(); 
        customerDAOTest.testGetById(); 
        customerDAOTest.testGetWithCashById(); 
        customerDAOTest.testGetWithCashByIdInnerjoin(); 

    } 
}

 

public class OrdersDAOTest { 
    OrdersDAO ordersDAO = (OrdersDAO) ApplicationContextUtils.getApplicationContext().getBean("ordersDAO"); 

    public void testInsert() { 
        System.out.println("--------getWithCashById(Long id)--------"); 
        Orders o = new Orders("o1"); 
        o.setCustomerId(1L); 
        Long pk = ordersDAO.insert(o); 
        System.out.println("所插入数据ID=" + pk); 
    } 

    public void testGetById() { 
        System.out.println("--------getById(Long id)--------"); 
        Orders o = ordersDAO.getById(1L); 
        System.out.println("查询结果：" + o.toString()); 
    } 

    public void testFindByCustomerId() { 
        System.out.println("--------findByCustomerId(Long cid)--------"); 
        List<Orders> list = ordersDAO.findByCustomerId(1L); 
        for(Orders o : list){ 
            System.out.println(o); 
        } 
    } 
    public static void main(String args[]){ 
        System.out.println("正在测试OrderDAO"); 
        OrdersDAOTest ordersDAOTest = new OrdersDAOTest(); 
        ordersDAOTest.testInsert(); 
        ordersDAOTest.testGetById(); 
        ordersDAOTest.testFindByCustomerId(); 
        ordersDAOTest.testGetByIdWithCash(); 
    } 

    public void testGetByIdWithCash(){ 
        System.out.println("------------getByIdWithCash(Long id)----------"); 
        List<Orders> list = ordersDAO.getByIdWithCash(1L); 
        for(Orders o : list){ 
            System.out.println(o +"\n\t"+o.getCustomer().toString()); 
        } 
    } 

}

 

测试结果分别为：

正在测试CustomerDAO 
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext). 
log4j:WARN Please initialize the log4j system properly. 
--------insert(Customer c)-------- 
插入数据的ID=1 
--------getById(Long id)-------- 
Customer{id=1, name='多对一', address='郑州市花园路', postcode='450003', sex='M', orderlist=0} 
--------getWithCashById(Long id)-------- 
Customer{id=1, name='多对一', address='郑州市花园路', postcode='450003', sex='M', orderlist=0} 
--------getWithCashByIdInnerjoin()-------- 

Process finished with exit code 0

 

正在测试OrderDAO 
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext). 
log4j:WARN Please initialize the log4j system properly. 
--------getWithCashById(Long id)-------- 
所插入数据ID=1 
--------getById(Long id)-------- 
查询结果：Orders{id=1, code='o1', customerId=1} 
--------findByCustomerId(Long cid)-------- 
Orders{id=1, code='o1', customerId=1} 
------------getByIdWithCash(Long id)---------- 
Orders{id=1, code='o1', customerId=1} 
    Customer{id=1, name='多对一', address='郑州市花园路', postcode='450003', sex='M', orderlist=0}