Chinaunix首页 | 论坛 | 博客

IT民工窝棚qbq.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

qbq

  • 博客访问： 29952760
  • 博文数量： 708
  • 博客积分： 12163
  • 博客等级： 上将
  • 技术积分： 8240
  • 用 户 组： 普通用户
  • 注册时间： 2007-12-04 20:59
文章分类

全部博文（708）

推荐博文
相关博文
ibatis的增删改查 一对一、一对多等

分类： Java

2011-11-11 13:37:07

  1. /**
  2.  * IbatisTest.java
  3.  * com.isoftstone.cry.ibatisTest
  4.  *
  5.  * Function： TODO
  6.  *
  7.  * ver date         author
  8.  * ──────────────────────────────────
  9.  *          Mar 10, 2011     
  10.  *
  11.  * Copyright (c) 2011, All Rights Reserved.
  12. */

  14. package com.isoftstone.cry.ibatis;

  16. import java.io.IOException;
  17. import java.io.Reader;
  18. import java.sql.SQLException;
  19. import java.util.ArrayList;
  20. import java.util.List;
  21. import com.ibatis.common.resources.Resources;
  22. import com.ibatis.sqlmap.client.SqlMapClient;
  23. import com.ibatis.sqlmap.client.SqlMapClientBuilder;
  24. import com.isoftstone.cry.pojo.KeyBean;
  25. import com.isoftstone.cry.pojo.LockBean;
  26. /**
  27.  *
  28.  * ClassName:IbatisDemo
  29.  * Project:
  30.  * Company: isoftStone
  31.  *
  32.  * @author
  33.  * @version
  34.  * @since Ver 1.1
  35.  * @Date     2011    Mar 14
  36.  * @see
  37.  */
  38. public class IbatisDemo {
  39.     public static void main(String[] args) throws IOException,SQLException{
  40. //        读取配置文件
  41.         Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
  42.         SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
  43. //        单表的增删改查
  44. //        ①、查询表集合
  45.         /*List lockList = (List)sqlMap.queryForList("lockNameSpace.getLockList");
  46.         for(LockBean lb : lockList){
  47.             System.out.println(
  48.                     lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory()
  49.             );
  50.         }*/
  51. //        ②、查询表对象 by id
  52.         /*LockBean lb = (LockBean)sqlMap.queryForObject("lockNameSpace.getLockObjectById",new Long(1));
  53.         System.out.println(
  54.                 lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory()
  55.         );*/
  56. //        ③、查询表对象 by like
  57.         /*List lockList = (List)sqlMap.queryForList("lockNameSpace.getLockObjectByLike",new String("南京"));
  58.         for(LockBean lb : lockList){
  59.             System.out.println(
  60.                     lb.getId()+" "+lb.getLockName()+" "+lb.getLockFactory()
  61.             );
  62.         }*/
  63. //        ④、查询表对象 动态组合查询
  64. //        test 前台http 获取属性值
  65.     /*    LockBean lb = new LockBean();
  66.         lb.setLockFactory("南京");
  67.         lb.setLockType(new Integer(12));
  68.         List lockList = (List)sqlMap.queryForList
  69.         ("lockNameSpace.getLockListByDynamic",lb);
  70.         for(LockBean lock : lockList){
  71.             System.out.println(
  72.                     lock.getId()+" "+lock.getLockName()+" "+lock.getLockFactory()
  73.             );
  74.         }*/
  75. //        ⑤、多表查询 - 一对一 关联查询 ... (所谓“n+1”Select问题)
  76.         /*List lockList = (List)sqlMap.queryForList
  77.         ("lockNameSpace.getLockList");
  78.         for(LockBean lock : lockList){
  79.             System.out.println(
  80.                     lock.getId()+" "+lock.getLockName()+" "+lock.getLockInfo().getLockMoney()
  81.             );
  82.         }*/
  83. //        ⑥、多表查询 - 一对一 （解决所谓“n+1”Select问题）
  84.         /*List lockList = (List)sqlMap.queryForList
  85.         ("lockNameSpace.getLockListOneToOne");
  86.         for(LockBean lock : lockList){
  87.             System.out.println(
  88.                     lock.getId()+" "+lock.getLockName()+" "+lock.getLockInfo().getLockMoney()
  89.             );
  90.         }*/
  91. //        7、 多表查询 一对多(基本解决，有个bug.....)
  92.         /*List lockList = (List)sqlMap.queryForList
  93.         ("lockNameSpace.getLockList");
  94.         System.out.println("size = "+lockList.size());
  95.         for(int i=0;i
  96.         {
  97.             LockBean lb = lockList.get(i);
  98.             System.out.println(lb.getId()+" - ");
  99.             for(int j=0;j
  100.                 KeyBean kb = lb.getKeyList().get(j);
  101.                 System.out.println(kb.getLockId()+" "+kb.getId()+" "+kb.getKeyName());
  102.             }
  103.         }*/
  104. //        8、插入操作 insert
  105.         /*LockBean lb = new LockBean();
  106.         lb.setLockName("lockName");
  107.         lb.setLockFactory("lockFactory");
  108.         lb.setLockColor("lockColor");
  109.         lb.setLockType(new Integer(44));
  110.         sqlMap.insert("lockNameSpace.insertLock",lb);
  111.         System.out.println("insert success .... ");*/
  112.         
  113. //        9、批量插入操作 insert
  114.         /*List lbList = new ArrayList();
  115.         for(int i=0;i<5;i++){
  116.             LockBean lb = new LockBean();
  117.             lb.setLockName("lockName"+i);
  118.             lb.setLockFactory("lockFactory"+i);
  119.             lb.setLockColor("lockColor"+i);
  120.             lb.setLockType(new Integer(44)+i);
  121.             lbList.add(lb);
  122.         }*/
  123. //        结合spring批量处理
  124.         /*SqlMapClientCallback callback = new SqlMapClientCallback() {
  125.             public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
  126.                 executor.startBatch();
  127.                 for (T tObject : memberList) {
  128.                     executor.insert(statement, tObject); // statement在*MapSql.xml一条语句的id
  129.                 }
  130.                 executor.executeBatch();
  131.                 return null;
  132.             }
  133.         };*/
  134. //        ibatis 批量插入 - 删除跟新类似
  135.         /*sqlMap.startTransaction();
  136.         for(int i=0;i
  137.             sqlMap.insert("lockNameSpace.batchInsertLock",lbList.get(i));
  138.         }
  139.         sqlMap.executeBatch();
  140.         sqlMap.commitTransaction();
  141.         sqlMap.endTransaction();
  142.         System.out.println("批量插入完成................");*/
  143.         
  144. //        10、 删除操作 delete
  145.         /*sqlMap.delete("lockNameSpace.deleteLock",100);*/
  146.         
  147. //        11、批量删除
  148.         /*sqlMap.startTransaction();
  149.         for(int i=51;i<100;i++){
  150.             sqlMap.delete("lockNameSpace.deleteLock",i);
  151.         }
  152.         sqlMap.executeBatch();
  153.         sqlMap.commitTransaction();
  154.         sqlMap.endTransaction();
  155.         System.out.println("批量删除完成................");*/
  156.         
  157. //        12、更新操作 update ..
  158.         /*LockBean lb = new LockBean();
  159.         lb.setLockName("updatelockName");
  160.         lb.setLockColor("upColor");
  161.         lb.setId(50);
  162.         sqlMap.update("lockNameSpace.updateLock",lb);
  163.         System.out.println("更新完成。。。。。");*/
  164. //        13、批量更新
  165.         /*List lbList = new ArrayList();
  166.         for(int i=50;i<60;i++){
  167.             LockBean lb = new LockBean();
  168.             lb.setLockName("lockName"+i);
  169.             lb.setLockFactory("lockFactory"+i);
  170.             lb.setLockColor("Color"+i);
  171.             lb.setLockType(new Integer(44)+i);
  172.             lb.setId(i);
  173.             lbList.add(lb);
  174.         }
  175.         
  176.         sqlMap.startTransaction();
  177.         for(int i=0;i
  178.             sqlMap.update("lockNameSpace.updateLock",lbList.get(i));
  179.         }
  180.         sqlMap.executeBatch();
  181.         sqlMap.commitTransaction();
  182.         sqlMap.endTransaction();
  183.         System.out.println("批量更新完成................");*/
  184.     }
  185. }

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE sqlMap
  3. PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
  4. "">

  6. <sqlMap namespace="lockNameSpace">
  7.     <!-- ibatis add,delete,modify,query demo -->
  8.     <!-- 设计映射 -->
  9.     <typeAlias alias="lock" type="com.isoftstone.cry.pojo.LockBean"/>
  10.     <typeAlias alias="lockInformation" type="com.isoftstone.cry.pojo.LockInfoBean"/>
  11.     <typeAlias alias="lockKey" type="com.isoftstone.cry.pojo.KeyBean"/>
  12.     
  13.     <resultMap id="lockResult" class="lock">
  14.         <result property="id" column="id"/>
  15.         <result property="lockName" column="lock_Name"/>
  16.         <result property="lockColor" column="lock_Color"/>
  17.         <result property="lockFactory" column="lock_Factory"/>
  18.         <result property="lockType" column="lock_Type"/>
  19.         <!-- 一对一设置 n+1 select 延时加载-->
  20.         <result property="lockInfo" column="id" select="lockNameSpace.getLockVsInfo"/>
  21.         <!-- 一对多 -->
  22.         <result property="keyList" column="id" select="lockNameSpace.getKeyList"/>
  23.         <!-- <result property="lockInfo" resultMap="resultLockInfo"/>-->
  24.     </resultMap>
  25.     
  26.     <!-- 备注：resultMap 映射机制，resultClass 隐式映射（前提是javaBean属性和表字段column相同） -->
  27.     <!-- query object list -->
  28.     <!-- query all -->
  29.     <select id="getLockList" resultMap="lockResult">
  30.         <![CDATA[
  31.         SELECT * FROM LOCK_
  32.         ]]>
  33.     </select>
  34.     <!-- query by id -->
  35.     <select id="getLockObjectById" resultMap="lockResult">
  36.         SELECT * FROM LOCK_ WHERE ID = #VALUE#
  37.     </select>
  38.     <!-- query by like -->
  39.     <select id="getLockObjectByLike" resultMap="lockResult">
  40.         SELECT * FROM LOCK_ WHERE LOCK_FACTORY LIKE '%$VALUE$%'
  41.     </select>
  42.     <!-- query dynamik -->
  43.     <select id="getLockListByDynamic" resultMap="lockResult">
  44.         SELECT * FROM LOCK_
  45.         <dynamic prepend="WHERE">
  46.             <isNotEmpty prepend="and" property="lockFactory">
  47.                 (lock_factory like '%$lockFactory$%')
  48.             </isNotEmpty>
  49.             <isNotNull prepend="AND" property="lockType">
  50.                 (lock_type = #lockType#)
  51.             </isNotNull>
  52.         </dynamic>
  53.     </select>
  54.     <!-- 多表查询 一对一 -->
  55.     <!-- 关联lockInfo表属性对应数据库字段 -->
  56.     <resultMap id="resultLockInfo" class="lockInformation">
  57.         <result property="id" column="id"/>
  58.         <result property="lockId" column="lock_Id"/>
  59.         <result property="lockWeight" column="lock_Weight"/>
  60.         <result property="lockMoney" column="lock_Money"/>
  61.         <result property="lockRemark" column="lock_Remark"/>
  62.     </resultMap>
  63.     <!-- 一对一查询 SQL -->
  64.     <!--
  65.         在lock_表POJO中增加了 lockInfo对象
  66.         result property="lockInfo" column="id" select="lockNameSpace.getLockVsInfo"
  67.         用2次SQL查询实现 一对一 关联
  68.         也就是所谓“n+1”Select问题（如果lock表有十几万数据，而lockinfo表就几条数据问题）
  69.      -->
  70.     <select id="getLockVsInfo" resultMap="resultLockInfo">
  71.         select * from lock_info where lock_id = #value#
  72.     </select>
  73.     <!--
  74.     解决一对一的 n+1 问题 (待解决...)
  75.     <select id="getLockListOneToOne" parameterClass="int" resultMap="lockResult">
  76.         <![CDATA[
  77.             SELECT l.*,i.* FROM lock_ l,lock_info i WHERE l.id=i.lock_id and l.id=#value#
  78.         ]]>
  79.     </select>
  80.     -->
  81.     <!-- 一对多 (单向关联和双向关联)-->
  82.     <resultMap class="lockKey" id="keyResultMap">
  83.         <result property="id" column="id"/>
  84.         <result property="keyName" column="key_Name"/>
  85.         <result property="lockId" column="lock_Id"/>
  86.     </resultMap>
  87.     <select id="getKeyList" parameterClass="int" resultMap="keyResultMap">
  88.         select * from key_ where LOCK_ID = #id#
  89.     </select>
  90.     <!-- insert -->
  91.     <insert id="insertLock" parameterClass="lock">
  92.         <!-- 获取序列 -->
  93.         <selectKey keyProperty="id" resultClass="long">
  94.             SELECT pagetest_seq.nextval AS id FROM dual
  95.         </selectKey>
  96.         insert into lock_ (id,lock_Name,lock_Color,lock_Factory,lock_Type) values (#id#,#lockName#,#lockColor#,#lockFactory#,#lockType#)
  97.     </insert>
  98.     <!-- batch insert -->
  99.     <insert id="batchInsertLock" parameterClass="lock">
  100.         <!-- 获取序列 -->
  101.         <selectKey keyProperty="id" resultClass="long">
  102.             SELECT pagetest_seq.nextval AS id FROM dual
  103.         </selectKey>
  104.         insert into lock_ (id,lock_Name,lock_Color,lock_Factory,lock_Type) values (#id#,#lockName#,#lockColor#,#lockFactory#,#lockType#)
  105.     </insert>
  106.     <!-- delete -->
  107.     <delete id="deleteLock" parameterClass="int">
  108.         delete from lock_ where id =#id#
  109.     </delete>
  110.     <!-- update -->
  111.     <update id="updateLock" parameterClass="lock">
  112.         update lock_ set lock_name=#lockName# , lock_color=#lockColor# where id=#id#
  113.     </update>
  114.     <!-- page -->
  115.     <!-- batch -->
  116. </sqlMap>

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE sqlMapConfig
  3. PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
  4. "">

  6. <sqlMapConfig>
  7.     <settings
  8.         cacheModelsEnabled="true"
  9.         enhancementEnabled="true"
  10.         lazyLoadingEnabled="true"
  11.         maxRequests="32"
  12.         maxSessions="10"
  13.         maxTransactions="5"
  14.         useStatementNamespaces="true" />
  15.         
  16.     <transactionManager type="JDBC" commitRequired="false">
  17.        <dataSource type="SIMPLE">
  18.          <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
  19.          <property name="JDBC.ConnectionURL"
  20.               value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL"/>
  21.          <property name="JDBC.Username" value="SYSTEM"/>
  22.          <property name="JDBC.Password" value="suypower"/>
  23.        </dataSource>
  24.     </transactionManager>
  25.     
  26.     <sqlMap resource="com/isoftstone/cry/pojo/lock.xml"/>
  27. </sqlMapConfig>

  1. public class KeyBean implements Serializable{
  2.     private long id ;
  3.     private String keyName ;
  4.     private long lockId ;
  5. 。。。。。。。get、set

  7. ----------------------------
  8. public class LockBean implements Serializable{
  9.     private long id ; // 主键
  10.     private String lockName ; // 锁名称
  11.     private String lockColor ; // 锁颜色
  12.     private String lockFactory ; // 锁出厂公司
  13.     private Integer lockType ; // 锁类型 - 枚举字段 (用封装类型，允许null)
  14.     
  15.     private LockInfoBean lockInfo ; // 一对一
  16.     private List<KeyBean> keyList = new ArrayList<KeyBean>(); // 一对多

  18. get、set
  19. -----------------------------
  20. public class LockInfoBean implements Serializable{
  21.     private long id ;
  22.     private long lockId ;
  23.     private String lockWeight ;
  24.     private String lockMoney ;
  25.     private String lockRemark ;

阅读(3240) | 评论(0) | 转发(0) |
0

上一篇：ibatis 泛型dao设计

下一篇：资源预加载

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6