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