ibatis的增删改查 一对一、一对多等
项目需要，需要用ibatis，我花了一天时间学习，一天时间整理，希望能和刚入门的ibatis朋友，一起分享.....
/** * IbatisTest.java * com.isoftstone.cry.ibatisTest * * Function： TODO * * ver date author * ────────────────────────────────── * Mar 10, 2011 * * Copyright (c) 2011, All Rights Reserved.*/package com.isoftstone.cry.ibatis;import java.io.IOException;import java.io.Reader;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.ibatis.common.resources.Resources;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;import com.isoftstone.cry.pojo.KeyBean;import com.isoftstone.cry.pojo.LockBean;/** * * ClassName:IbatisDemo * Project: * Company: isoftStone * * @author * @version * @since Ver 1.1 * @Date 2011Mar 14 * @see */public class IbatisDemo {public static void main(String[] args) throws IOException,SQLException{//读取配置文件Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);//单表的增删改查//①、查询表集合/*List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockList");for(LockBean lb : lockList){System.out.println(lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory() );}*///②、查询表对象 by id/*LockBean lb = (LockBean)sqlMap.queryForObject("lockNameSpace.getLockObjectById",new Long(1));System.out.println(lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory());*///③、查询表对象 by like/*List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockObjectByLike",new String("南京"));for(LockBean lb : lockList){System.out.println(lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory() );}*///④、查询表对象 动态组合查询//test 前台http 获取属性值/*LockBean lb = new LockBean();lb.setLockFactory("南京");lb.setLockType(new Integer(12));List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockListByDynamic",lb);for(LockBean lock : lockList){System.out.println(lock.getId()+" "+lock.getLockName()+" "+lock.getLockFactory() );}*///⑤、多表查询 - 一对一 关联查询 ... (所谓“n+1”Select问题)/*List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockList");for(LockBean lock : lockList){System.out.println(lock.getId()+" "+lock.getLockName()+" "+lock.getLockInfo().getLockMoney());}*///⑥、多表查询 - 一对一 （解决所谓“n+1”Select问题）/*List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockListOneToOne");for(LockBean lock : lockList){System.out.println(lock.getId()+" "+lock.getLockName()+" "+lock.getLockInfo().getLockMoney());}*///7、 多表查询 一对多(基本解决，有个bug.....)/*List<LockBean> lockList = (List<LockBean>)sqlMap.queryForList("lockNameSpace.getLockList");System.out.println("size = "+lockList.size());for(int i=0;i<lockList.size();i++){LockBean lb = lockList.get(i);System.out.println(lb.getId()+" - ");for(int j=0;j<lb.getKeyList().size();j++){KeyBean kb = lb.getKeyList().get(j);System.out.println(kb.getLockId()+" "+kb.getId()+" "+kb.getKeyName());}}*///8、插入操作 insert/*LockBean lb = new LockBean();lb.setLockName("lockName");lb.setLockFactory("lockFactory");lb.setLockColor("lockColor");lb.setLockType(new Integer(44));sqlMap.insert("lockNameSpace.insertLock",lb);System.out.println("insert success .... ");*///9、批量插入操作 insert/*List<LockBean> lbList = new ArrayList<LockBean>();for(int i=0;i<5;i++){LockBean lb = new LockBean();lb.setLockName("lockName"+i);lb.setLockFactory("lockFactory"+i);lb.setLockColor("lockColor"+i);lb.setLockType(new Integer(44)+i);lbList.add(lb);}*///结合spring批量处理/*SqlMapClientCallback callback = new SqlMapClientCallback() { public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException { executor.startBatch(); for (T tObject : memberList) { executor.insert(statement, tObject); // statement在*MapSql.xml一条语句的id } executor.executeBatch(); return null; } };*///ibatis 批量插入 - 删除跟新类似/*sqlMap.startTransaction();for(int i=0;i<lbList.size();i++){sqlMap.insert("lockNameSpace.batchInsertLock",lbList.get(i));}sqlMap.executeBatch();sqlMap.commitTransaction();sqlMap.endTransaction();System.out.println("批量插入完成................");*///10、 删除操作 delete/*sqlMap.delete("lockNameSpace.deleteLock",100);*///11、批量删除/*sqlMap.startTransaction();for(int i=51;i<100;i++){sqlMap.delete("lockNameSpace.deleteLock",i);}sqlMap.executeBatch();sqlMap.commitTransaction();sqlMap.endTransaction();System.out.println("批量删除完成................");*///12、更新操作 update ../*LockBean lb = new LockBean();lb.setLockName("updatelockName");lb.setLockColor("upColor");lb.setId(50);sqlMap.update("lockNameSpace.updateLock",lb);System.out.println("更新完成。。。。。");*///13、批量更新/*List<LockBean> lbList = new ArrayList<LockBean>();for(int i=50;i<60;i++){LockBean lb = new LockBean();lb.setLockName("lockName"+i);lb.setLockFactory("lockFactory"+i);lb.setLockColor("Color"+i);lb.setLockType(new Integer(44)+i);lb.setId(i);lbList.add(lb);}sqlMap.startTransaction();for(int i=0;i<lbList.size();i++){sqlMap.update("lockNameSpace.updateLock",lbList.get(i));}sqlMap.executeBatch();sqlMap.commitTransaction();sqlMap.endTransaction();System.out.println("批量更新完成................");*/}}<?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="lockNameSpace"><!-- ibatis add,delete,modify,query demo --><!-- 设计映射 --><typeAlias alias="lock" type="com.isoftstone.cry.pojo.LockBean"/><typeAlias alias="lockInformation" type="com.isoftstone.cry.pojo.LockInfoBean"/><typeAlias alias="lockKey" type="com.isoftstone.cry.pojo.KeyBean"/><resultMap id="lockResult" column="id"/><result property="lockName" column="lock_Name"/><result property="lockColor" column="lock_Color"/><result property="lockFactory" column="lock_Factory"/><result property="lockType" column="lock_Type"/><!-- 一对一设置 n+1 select 延时加载--><result property="lockInfo" column="id" select="lockNameSpace.getLockVsInfo"/><!-- 一对多 --><result property="keyList" column="id" select="lockNameSpace.getKeyList"/><!-- <result property="lockInfo" resultMap="resultLockInfo"/>--></resultMap><!-- 备注：resultMap 映射机制，resultClass 隐式映射（前提是javaBean属性和表字段column相同） --><!-- query object list --><!-- query all --><select id="getLockList" resultMap="lockResult"><![CDATA[SELECT * FROM LOCK_]]></select><!-- query by id --><select id="getLockObjectById" resultMap="lockResult">SELECT * FROM LOCK_ WHERE ID = #VALUE#</select><!-- query by like --><select id="getLockObjectByLike" resultMap="lockResult">SELECT * FROM LOCK_ WHERE LOCK_FACTORY LIKE '%$VALUE$%'</select><!-- query dynamik --><select id="getLockListByDynamic" resultMap="lockResult">SELECT * FROM LOCK_<dynamic prepend="WHERE"><isNotEmpty prepend="and" property="lockFactory">(lock_factory like '%$lockFactory$%')</isNotEmpty><isNotNull prepend="AND" property="lockType">(lock_type = #lockType#)</isNotNull></dynamic></select><!-- 多表查询 一对一 --><!-- 关联lockInfo表属性对应数据库字段 --><resultMap id="resultLockInfo" column="id"/><result property="lockId" column="lock_Id"/><result property="lockWeight" column="lock_Weight"/><result property="lockMoney" column="lock_Money"/><result property="lockRemark" column="lock_Remark"/></resultMap><!-- 一对一查询 SQL --><!-- 在lock_表POJO中增加了 lockInfo对象result property="lockInfo" column="id" select="lockNameSpace.getLockVsInfo"用2次SQL查询实现 一对一 关联也就是所谓“n+1”Select问题（如果lock表有十几万数据，而lockinfo表就几条数据问题） --><select id="getLockVsInfo" resultMap="resultLockInfo">select * from lock_info where lock_id = #value#</select><!-- 解决一对一的 n+1 问题 (待解决...)<select id="getLockListOneToOne" parameterresultMap="lockResult"><![CDATA[SELECT l.*,i.* FROM lock_ l,lock_info i WHERE l.id=i.lock_id and l.id=#value#]]></select>--><!-- 一对多 (单向关联和双向关联)--><resultMap id="keyResultMap"><result property="id" column="id"/><result property="keyName" column="key_Name"/><result property="lockId" column="lock_Id"/></resultMap><select id="getKeyList" parameterresultMap="keyResultMap">select * from key_ where LOCK_ID = #id#</select><!-- insert --><insert id="insertLock" parameterresultparameterresultparameterparametername="code"><?xml version="1.0" encoding="UTF-8"?><!DOCTYPE sqlMapConfigPUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN""http://ibatis.apache.org/dtd/sql-map-config-2.dtd"><sqlMapConfig><settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" maxRequests="32" maxSessions="10"maxTransactions="5" useStatementNamespaces="true" /> <transactionManager type="JDBC" commitRequired="false"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL"/> <property name="JDBC.Username" value="SYSTEM"/> <property name="JDBC.Password" value="suypower"/> </dataSource> </transactionManager> <sqlMap resource="com/isoftstone/cry/pojo/lock.xml"/></sqlMapConfig>
public class KeyBean implements Serializable{private long id ;private String keyName ;private long lockId ;。。。。。。。get、set----------------------------public class LockBean implements Serializable{private long id ; // 主键private String lockName ; // 锁名称private String lockColor ; // 锁颜色private String lockFactory ; // 锁出厂公司private Integer lockType ; // 锁类型 - 枚举字段 (用封装类型，允许null)private LockInfoBean lockInfo ; // 一对一private List<KeyBean> keyList = new ArrayList<KeyBean>(); // 一对多get、set-----------------------------public class LockInfoBean implements Serializable{private long id ;private long lockId ;private String lockWeight ;private String lockMoney ;private String lockRemark ;