iBatis2学习笔记

getSqlMapClientTemplate.insert() 返回的是新插入数据的主键值
getSqlMapClientTemplate.update() 返回的是更新的记录
public int deleteEntity(Map key)throws DataAccessException{
    return getSqlMapClientTemplate().delete(getDeleteStatement(), key);
  }

  public Entity retrieveEntity(Map key)throws DataAccessException{
    return ((Entity)getSqlMapClientTemplate().queryForObject(getSelectStatement(), key));
  }

  public int updateEntity(Entity entity)throws DataAccessException{
    return getSqlMapClientTemplate().update(getUpdateStatement(), entity);
  }

public int deleteEntities(String entityClassName, List keies)
  {
    int iCount = 0;
    Iterator it = keies.iterator();
    while (it.hasNext())
      iCount += deleteEntity(entityClassName, (Map)it.next());

    return iCount;
  }
===========================================================================================================================================

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 http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

    <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='插入数据测试！'}