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

原创

leizhimin 2008-06-25 10:32:39 博主文章分类：ORM/持久化 ©著作权

文章标签 职场休闲 iBatis2 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者leizhimin的原创作品，如需转载，请与作者联系，否则将追究法律责任

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}

Process finished with exit code 0