iBatis2学习笔记：单表映射

环境：
MySQL5.51b
iBatis 2.3

Spring 2.5.4

 

 

一、SQL

/*==============================================================*/ 
/* Table: foo                                                   */ 
/*==============================================================*/ 
create table foo 
( 
   id                   bigint, 
   name                 varchar(20), 
   type                 varchar(20), 
   remark               varchar(600) 
); 

alter table foo comment '单表';

 

二、POJO

public class Foo {  
    private Long id;  
    private String name;  
    private String type;  
    private String remark;  

 

三、SqlMap

<?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="foo"> 
    <typeAlias alias="foo" type="com.lavasoft.ssi.domain.Foo"/> 

    <!-- 映射结果集 --> 
    <resultMap id="result_base" class="foo"> 
        <result property="id" column="id"/> 
        <result property="name" column="name"/> 
    </resultMap> 
    <!-- 继承映射结果集 --> 
    <resultMap id="result" class="foo" extends="result_base"> 
        <result property="type" column="type"/> 
        <result property="remark" column="remark"/> 
    </resultMap> 
    <resultMap id="result_map" class="foo"> 
        <result property="id" column="fid"/> 
        <result property="name" column="fname"/> 
        <result property="remark" column="fremark"/> 
    </resultMap> 

    <!-- 插入操作：以域对象foo做参数 --> 
    <insert id="insert" parameterClass="foo"> 
        insert into foo(name,type,remark) values(#name#,#type#,#remark#) 
        <selectKey keyProperty="id" resultClass="long"> 
            select LAST_INSERT_ID() 
        </selectKey> 
    </insert> 
    <!-- 更新操作：以域对象foo做参数 --> 
    <update id="update" parameterClass="foo"> 
        update foo set name = #name#, type = #type#, remark = #remark# where id = #id# 
    </update> 
    <!-- 多个参数情况下，用Map或者域对象做参数均可以，但域对象通常有更好的性能 --> 
    <!-- 更新操作：以Map做参数 --> 
    <update id="updateSomeByMap" parameterClass="map"> 
        update foo set name = #name#, remark = #remark# where id = #id# 
    </update> 
    <!-- 更新操作：以域对象做参数 --> 
    <update id="updateSomeByObject" parameterClass="foo"> 
        update foo set name = #name#, remark = #remark# where id = #id# 
    </update> 
    <!-- where条件的三种写法“id = #value#，id = #id# id = #?#”均正确，查询结果相同  --> 
    <!-- 结果集应该优先使用域对象 --> 
    <!-- resultClass表示结果封装为foo类型，parameterClass表示参数类型，resultMap指定查询结果要填充的字段和对应关系 --> 
    <select id="getById" resultClass="foo" parameterClass="long" resultMap="result"> 
        select * from foo where id = #value# 
    </select> 
    <!-- 查询多条记录：结果封装为List<Foo> --> 
    <select id="foo.getAll" resultClass="foo" resultMap="result"> 
        <![CDATA[ 
        select id,name,type,remark from foo where id > 0 and id < 8 
        ]]> 
    </select> 
    <!-- 查询多条记录：结果封装在List<HashMap>中 --> 
    <select id="getAll2" resultMap="result_map" resultClass="map"> 
        <![CDATA[ 
        select id as fid, name as fname, remark as fremark from foo where id > 0 and id < 8 
        ]]> 
    </select> 
    <select id="getAll3"> 
        <![CDATA[ 
        select id as xid, name as xname from foo 
        ]]> 
    </select> 
    <select id="getAll4"> 
        <![CDATA[ 
        select id,type as name from foo  where id > 0 and id < 8 
        ]]> 
    </select> 
    <delete id="deleteById" parameterClass="long"> 
        delete from foo where id = #value# 
    </delete> 
    <delete id="deleteAll"> 
        delete from foo 
    </delete> 
    <select id="getCount" resultClass="int"> 
        select count(id) from foo 
    </select> 
    <select id="getByDynamic" resultMap="result" parameterClass="foo"> 
        select * from foo 
        <dynamic prepend="where"> 
            <isNotNull prepend="and" property="name"> 
                (name like #name#) 
            </isNotNull> 
            <isNotEmpty prepend="and"> 
                (type like '%'|| #type# ||'%') 
            </isNotEmpty> 
        </dynamic> 
        <isGreaterThan prepend="where" property="id" compareValue="10"> 
            remark is not null 
        </isGreaterThan> 
    </select> 
</sqlMap>         
  

 

三、DAO即测试代码

 

public interface FooDAO { 
    public Long insert(Foo foo); 
    public int getCount(); 
    public int update(Foo foo); 
    public int updateSomeByMap(Long id,String name,String remark); 
    public int updateSomeByObject(Long id,String name,String remark); 
    public Foo getById(Long id); 
    public List getAll(); 
    public Object getAll2(); 
    public List getAll3(); 
    public List getAll4(); 
    public List getByDynamic(Foo foo); 
    public int deleteById(Long id); 
    public int deteteAll(); 
}

 

public class FooDAOImpl extends SqlMapClientDaoSupport implements FooDAO { 

    public Long insert(Foo foo) { 
       return  (Long)getSqlMapClientTemplate().insert("foo.insert", foo); 
    } 

    public int getCount() { 
        return (Integer)getSqlMapClientTemplate().queryForObject("foo.getCount"); 
    } 

    public int update(Foo foo) { 
        return getSqlMapClientTemplate().update("foo.update", foo); 
    } 

    public int updateSomeByMap(Long id, String name, String remark) { 
        Map pm = new HashMap(); 
        pm.put("id", id); 
        pm.put("name", name); 
        pm.put("remark", remark); 
        return getSqlMapClientTemplate().update("foo.updateSomeByMap", pm); 
    } 

    public int updateSomeByObject(Long id,String name,String remark) { 
        Foo foo = new Foo(); 
        foo.setId(id); 
        foo.setName(name); 
        foo.setRemark(remark); 
        return getSqlMapClientTemplate().update("foo.updateSomeByObject", foo); 
    } 

    public Foo getById(Long id) { 
        return (Foo) getSqlMapClientTemplate().queryForObject("foo.getById",id); 
    } 

    public List getAll() { 
        return getSqlMapClientTemplate().queryForList("foo.getAll"); 
    } 

    public Object getAll2() { 
//        return getSqlMapClientTemplate().queryForList(); 
        List<String> ls = new ArrayList<String>(); 


        return getSqlMapClientTemplate().queryForMap("foo.getAll2",null,"id"); 
    } 

    public List getAll3() { 
        Object obj =getSqlMapClientTemplate().queryForObject("foo.getAll3"); 
        return getSqlMapClientTemplate().queryForList("foo.getAll3"); 
    } 

    public List getAll4() { 
        return getSqlMapClientTemplate().queryForList("foo.getAll4"); 
    } 

    public List getByDynamic(Foo foo) { 
        return getSqlMapClientTemplate().queryForList("foo.getByDynamic",foo); 
    } 

    public int deleteById(Long id) { 
        return getSqlMapClientTemplate().delete("foo.deleteById",id); 
    } 

    public int deteteAll() { 
        return getSqlMapClientTemplate().delete("foo.deleteAll"); 
    } 
}

 

package com.lavasoft.ssi.test; 

import com.lavasoft.ssi.dao.FooDAO; 
import com.lavasoft.ssi.common.utils.ApplicationContextUtils; 
import com.lavasoft.ssi.domain.Foo; 

import java.util.Random; 
import java.util.List; 
import java.util.Map; 
import java.util.Iterator; 

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-6-15 9:29:26<br> 
* <b>Note</b>: FooDAO的测试 
*/ 
public class FooDAOTest { 
    private FooDAO fooDAO = (FooDAO) ApplicationContextUtils.getApplicationContext().getBean("fooDAO"); 

    public void testInsert() { 
        System.out.println("--------insert(Foo foo)--------"); 
        Foo foo = new Foo("变态", "ttt", "插入数据测试！"); 
        for (int i = 0; i < 15; i++) { 
            Long pk = fooDAO.insert(foo); 
            System.out.println("所插入数据的ID=" + pk); 
        } 
    } 

    public void testGetCount() { 
        System.out.println("--------testGetCount()--------"); 
        int count = fooDAO.getCount(); 
        System.out.println("记录总数=" + count); 

    } 

    public void testUpdate() { 
        System.out.println("--------update(Foo foo)--------"); 
        String upRemark = "已更新" + new Random(new Random().nextLong()).nextInt(); 
        Long pk = 1L; 

        Foo foo = fooDAO.getById(pk); 
        System.out.println("更新前" + foo); 

        foo.setRemark(upRemark); 
        fooDAO.update(foo); 
        Foo foo1 = fooDAO.getById(pk); 
        System.out.println("更新后" + foo1); 
    } 

    public void testUpdateSomeByMap() { 
        System.out.println("------updateSomeByMap(Long id,String name,String remark)------"); 
        String upRemark = "已更新" + new Random(new Random().nextLong()).nextInt(); 
        Long pk = 2L; 
        String name = null; 

        Foo foo = fooDAO.getById(pk); 
        System.out.println("更新前" + foo); 

        fooDAO.updateSomeByMap(pk, name, upRemark); 

        Foo foo1 = fooDAO.getById(pk); 
        System.out.println("更新后" + foo1); 
    } 

    public void testUpdateSomeByObject() { 
        System.out.println("------updateSomeByObject(Long id,String name,String remark)------"); 
        String upRemark = "已更新" + new Random(new Random().nextLong()).nextInt(); 
        Long pk = 3L; 
        String name = null; 

        Foo foo = fooDAO.getById(pk); 
        System.out.println("更新前" + foo); 

        fooDAO.updateSomeByObject(pk, name, upRemark); 

        Foo foo1 = fooDAO.getById(pk); 
        System.out.println("更新后" + foo1); 
    } 

    public void testGetById() { 
        System.out.println("------getById(Long id)------"); 
        Long pk = 4L; 
        Foo foo = fooDAO.getById(pk); 
        System.out.println("结果集" + foo); 
    } 

    public void testGetAll() { 
        System.out.println("------getAll()------"); 
        List<Foo> list = fooDAO.getAll(); 
        for (Foo foo : list) { 
            System.out.println("getAll结果集>>>" + foo); 
        } 
    } 

    public void testGetAll2() { 
        System.out.println("------getAll2()------"); 
        Object rs = fooDAO.getAll2(); 
        if (rs instanceof List) { 
            System.out.println("结果集为List"); 
            for (Object obj : (List) rs) { 
                if (obj instanceof Map) { 
                    System.out.println("结果集Map的元素");                     
                    Map objMap = (Map) obj; 
                    for (Iterator<Map.Entry> it = objMap.entrySet().iterator(); it.hasNext();) { 
                        Map.Entry me = it.next(); 
                        System.out.println("\t" + me.getKey() + "\t" + me.getValue()); 
                    } 
                }else if(obj instanceof Foo){ 
                    System.out.println("结果集为Foo类型:"+obj); 
                } 
            } 
        } 

    } 

    public void testGetAll3() { 
        System.out.println("------getAll3()------"); 
        List<Foo> list = fooDAO.getAll3(); 
        for (Foo foo : list) { 
            System.out.println("getAll3结果集>>>" + foo); 
        } 
    } 

    public void testGetAll4() { 
        System.out.println("------getAll4()------"); 
        List<Foo> list = fooDAO.getAll4(); 
        for (Foo foo : list) { 
            System.out.println("getAll4结果集>>>" + foo); 
        } 
    } 

    public void testGetByDynamic() { 
        System.out.println("------getByDynamic(Foo foo)------"); 
        Foo foo = new Foo(); 
        foo.setName("变态"); 
        foo.setType("t"); 

        List<Foo> list = fooDAO.getByDynamic(foo); 
        for (Foo f : list) { 
            System.out.println("getByDynamic结果集>>>" + f); 
        } 
    } 

    public void testDeleteById() { 
        System.out.println("------deleteById(Long id)------"); 
        Long pk = 10L; 
        int ef = fooDAO.deleteById(pk); 
        System.out.println("删除记录数 = " + ef); 
    } 

    public void testDeleteAll() { 
        System.out.println("------deteteAll()------"); 
        int ef = fooDAO.deteteAll(); 
        System.out.println("删除记录数 = " + ef); 
    } 

    public static void main(String args[]) { 
        System.out.println("正在测试FooDAO"); 
        FooDAOTest fooDAOTest = new FooDAOTest(); 
        fooDAOTest.testInsert(); 
        fooDAOTest.testGetCount(); 
        fooDAOTest.testUpdate(); 
        fooDAOTest.testUpdateSomeByMap(); 
        fooDAOTest.testUpdateSomeByObject(); 
        fooDAOTest.testGetById(); 
        fooDAOTest.testGetAll(); 
        fooDAOTest.testGetAll2(); 
        fooDAOTest.testGetAll3(); 
        fooDAOTest.testGetAll4(); 
        fooDAOTest.testGetByDynamic(); 
//        fooDAOTest.testDeleteById(); 
//        fooDAOTest.testDeleteAll(); 
    } 
}

 

 

三、其他的资源文件

 

Spring2 的 application.xml

<?xml version="1.0" encoding="UTF-8"?> 

<beans default-autowire="byType" 
       xmlns="http://www.springframework.org/schema/beans" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
       xsi:schemaLocation="http://www.springframework.org/schema/beans [url]http://www.springframework.org/schema/beans/spring-beans-2.0.xsd[/url]"> 

    <bean id="propertyConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
        <property name="locations"> 
            <list> 
                <value>classpath:jdbc.properties</value> 
            </list> 
        </property> 
    </bean> 
    <!-- 配置系统的数据源 --> 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
        <property name="driverClassName"> 
            <value>${jdbc.driver}</value> 
        </property> 
        <property name="url"> 
            <value>${jdbc.url}</value> 
        </property> 
        <property name="username"> 
            <value>${jdbc.username}</value> 
        </property> 
        <property name="password"> 
            <value>${jdbc.password}</value> 
        </property> 
    </bean> 


    <bean id="transactionManager" 
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
        <property name="dataSource" ref="dataSource"/> 
    </bean> 

    <!--根据dataSource和configLocation创建一个SqlMapClient--> 
    <bean id="sqlMapClient" 
          class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> 
        <property name="configLocation" value="sqlMapConfig.xml"/> 
        <property name="dataSource" ref="dataSource"/> 
    </bean> 

    <bean id="fooDAO" class="com.lavasoft.ssi.dao.ibatis.FooDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 
    <bean id="customerDAO" class="com.lavasoft.ssi.dao.ibatis.CustomerDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 
    <bean id="ordersDAO" class="com.lavasoft.ssi.dao.ibatis.OrdersDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 
    <bean id="userDAO" class="com.lavasoft.ssi.dao.ibatis.UserDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 
    <bean id="roleDAO" class="com.lavasoft.ssi.dao.ibatis.RoleDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 
    <bean id="tlinkDAO" class="com.lavasoft.ssi.dao.ibatis.TlinkDAOImpl"> 
        <property name="sqlMapClient" ref="sqlMapClient"/> 
    </bean> 

</beans>

 

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver 
jdbc.url=jdbc:mysql://localhost:3306/ssitest 
jdbc.username=root 
jdbc.password=leizhimin 

 

sqlMapConfig.xml

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

<sqlMapConfig> 
    <settings 
    cacheModelsEnabled="true" 
    errorTracingEnabled="true" 
    enhancementEnabled="true" 
    lazyLoadingEnabled="true" 
    maxRequests="32" 
    maxSessions="10" 
    maxTransactions="5" 
    useStatementNamespaces="true"/> 

    <!-- 单表映射 --> 
    <sqlMap resource="com/lavasoft/ssi/domain/sqlmap/Foo.xml"/> 

    <!-- 一对多映射 --> 
    <!--<sqlMap resource="com/lavasoft/ssi/domain/sqlmap/Customer.xml"/>--> 
    <!--<sqlMap resource="com/lavasoft/ssi/domain/sqlmap/Orders.xml"/>--> 

    <!-- 多对多映射 --> 
    <!--<sqlMap resource="com/lavasoft/ssi/domain/sqlmap/User.xml"/>--> 
    <!--<sqlMap resource="com/lavasoft/ssi/domain/sqlmap/Role.xml"/>--> 
    <!--<sqlMap resource="com/lavasoft/ssi/domain/sqlmap/Tlink.xml"/>--> 
</sqlMapConfig> 

 

四、测试结果：

正在测试FooDAO 
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext). 
log4j:WARN Please initialize the log4j system properly. 
--------insert(Foo foo)-------- 
所插入数据的ID=1 
所插入数据的ID=2 
所插入数据的ID=3 
所插入数据的ID=4 
所插入数据的ID=5 
所插入数据的ID=6 
所插入数据的ID=7 
所插入数据的ID=8 
所插入数据的ID=9 
所插入数据的ID=10 
所插入数据的ID=11 
所插入数据的ID=12 
所插入数据的ID=13 
所插入数据的ID=14 
所插入数据的ID=15 
--------testGetCount()-------- 
记录总数=15 
--------update(Foo foo)-------- 
更新前Foo{id=1, name='变态', type='ttt', remark='插入数据测试！'} 
更新后Foo{id=1, name='变态', type='ttt', remark='已更新-1425040370'} 
------updateSomeByMap(Long id,String name,String remark)------ 
更新前Foo{id=2, name='变态', type='ttt', remark='插入数据测试！'} 
更新后Foo{id=2, name='null', type='ttt', remark='已更新1143492135'} 
------updateSomeByObject(Long id,String name,String remark)------ 
更新前Foo{id=3, name='变态', type='ttt', remark='插入数据测试！'} 
更新后Foo{id=3, name='null', type='ttt', remark='已更新1570858341'} 
------getById(Long id)------ 
结果集Foo{id=4, name='变态', type='ttt', remark='插入数据测试！'} 
------getAll()------ 
getAll结果集>>>Foo{id=1, name='变态', type='ttt', remark='已更新-1425040370'} 
getAll结果集>>>Foo{id=2, name='null', type='ttt', remark='已更新1143492135'} 
getAll结果集>>>Foo{id=3, name='null', type='ttt', remark='已更新1570858341'} 
getAll结果集>>>Foo{id=4, name='变态', type='ttt', remark='插入数据测试！'} 
getAll结果集>>>Foo{id=5, name='变态', type='ttt', remark='插入数据测试！'} 
getAll结果集>>>Foo{id=6, name='变态', type='ttt', remark='插入数据测试！'} 
getAll结果集>>>Foo{id=7, name='变态', type='ttt', remark='插入数据测试！'} 
------getAll2()------ 
------getAll3()------ 
------getAll4()------ 
------getByDynamic(Foo foo)------ 
getByDynamic结果集>>>Foo{id=1, name='变态', type='ttt', remark='已更新-1425040370'} 
getByDynamic结果集>>>Foo{id=4, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=5, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=6, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=7, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=8, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=9, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=10, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=11, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=12, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=13, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=14, name='变态', type='ttt', remark='插入数据测试！'} 
getByDynamic结果集>>>Foo{id=15, name='变态', type='ttt', remark='插入数据测试！'} 

Process finished with exit code 0

 

 本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/83901，如需转载请自行联系原作者