ibatis多条件查询及相关练习

Emp.java

View Code

  1 package org.xiong.ibatis.demo.model;
  2 
  3 import java.util.Date;
  4 
  5 public class Emp
  6 {
  7     private int empno;
  8     private String ename;;
  9     private String job;
 10     private int mgr;
 11     private Date hiredate;
 12     private float sal;
 13     private float comm;
 14     private int deptno;
 15     private Emp empmgr;
 16     private Dept dept;
 17     private String photo;
 18 
 19     public Emp getEmpmgr()
 20     {
 21         return empmgr;
 22     }
 23 
 24     public void setEmpmgr(Emp empmgr)
 25     {
 26         this.empmgr = empmgr;
 27     }
 28 
 29     public Dept getDept()
 30     {
 31         return dept;
 32     }
 33 
 34     public void setDept(Dept dept)
 35     {
 36         this.dept = dept;
 37     }
 38 
 39     private String note;
 40 
 41     public int getEmpno()
 42     {
 43         return empno;
 44     }
 45 
 46     public void setEmpno(int empno)
 47     {
 48         this.empno = empno;
 49     }
 50 
 51     public String getEname()
 52     {
 53         return ename;
 54     }
 55 
 56     public void setEname(String ename)
 57     {
 58         this.ename = ename;
 59     }
 60 
 61     public String getJob()
 62     {
 63         return job;
 64     }
 65 
 66     public void setJob(String job)
 67     {
 68         this.job = job;
 69     }
 70 
 71     public int getMgr()
 72     {
 73         return mgr;
 74     }
 75 
 76     public void setMgr(int mgr)
 77     {
 78         this.mgr = mgr;
 79     }
 80 
 81     public Date getHiredate()
 82     {
 83         return hiredate;
 84     }
 85 
 86     public void setHiredate(Date hiredate)
 87     {
 88         this.hiredate = hiredate;
 89     }
 90 
 91     public float getSal()
 92     {
 93         return sal;
 94     }
 95 
 96     public void setSal(float sal)
 97     {
 98         this.sal = sal;
 99     }
100 
101     public float getComm()
102     {
103         return comm;
104     }
105 
106     public void setComm(float comm)
107     {
108         this.comm = comm;
109     }
110 
111     public int getDeptno()
112     {
113         return deptno;
114     }
115 
116     public void setDeptno(int deptno)
117     {
118         this.deptno = deptno;
119     }
120 
121     public String getPhoto()
122     {
123         return photo;
124     }
125 
126     public void setPhoto(String photo)
127     {
128         this.photo = photo;
129     }
130 
131     public String getNote()
132     {
133         return note;
134     }
135 
136     public void setNote(String note)
137     {
138         this.note = note;
139     }
140 
141     @Override
142     public String toString()
143     {
144         String empStr = "empno:" + this.empno + "  ename:" + this.ename
145                 + "  job:" + this.job + "  mgr:" + this.mgr + "  hiredate:"
146                 + this.hiredate + "  sal:" + this.sal + "  comm:" + this.comm
147                 + "  deptno:" + this.deptno + "  photo:" + this.photo
148                 + "  note:" + this.note;
149         return empStr;
150     }
151 }

Emp.xml

View Code

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!DOCTYPE sqlMap      
  3     PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
  4     "http://ibatis.apache.org/dtd/sql-map-2.dtd">
  5 <sqlMap>
  6     <typeAlias alias="Emp" type="org.xiong.ibatis.demo.model.Emp" />
  7     
  8     <select id="selectAllEmp" resultClass="Emp">
  9         select empno,ename,job,NVL(mgr,0),hiredate,NVL(sal,0),NVL(comm,0),NVL(deptno,0),photo,note from Emp
 10     </select>
 11     <select id="selectOneEmp" resultClass="Emp" parameterClass="Emp" >
 12         select empno,ename,job,NVL(mgr,0),hiredate,NVL(sal,0),NVL(comm,0),NVL(deptno,0),photo,note 
 13         from Emp
 14         where empno=#empno#
 15     </select>
 16     
 17     <!-- 动态生成sql，单一条件查询 -->
 18     <select id="dynamicQuery" resultClass="Emp" parameterClass="Integer">
 19         select * from emp 
 20         <dynamic prepend=" where ">
 21             empno=#param#        
 22         </dynamic>
 23     </select>
 24     
 25     <!-- 动态生成sql，多个条件查询 -->
 26     <parameterMap class="java.util.HashMap" id="manyParameters">
 27         <parameter property="empno1" javaType="Integer" jdbcType="NUMBER" />
 28         <parameter property="empno2" javaType="Integer" jdbcType="NUMBER" /> 
 29         <parameter property="sal" javaType="Integer" jdbcType="NUMBER" />
 30         <parameter property="comm" javaType="Integer" jdbcType="NUMBER" />
 31         <parameter property="hiredate" javaType="String" jdbcType="DATE" />            
 32     </parameterMap>
 33     <select id="dynamicQueryWithMultipleconditions1" resultClass="Emp" parameterMap="manyParameters" >
 34         select * from emp 
 35         <dynamic prepend=" where ">            
 36                 empno in (#empno1#,#empno2#)            
 37         </dynamic>
 38     </select>
 39     
 40     <select id="dynamicQueryWithMultipleconditions" resultClass="Emp" parameterMap="manyParameters" >
 41         select empno,ename,job,mgr,hiredate,sal,comm,deptno,photo,note 
 42         from emp 
 43         <dynamic prepend=" where ">            
 44                 <![CDATA[ sal<#sal# and comm>#comm# and hiredate>to_date(#hiredate#,'yyyy-mm-dd') and mgr between #empno1# and #empno2# ]]> 
 45         </dynamic>
 46     </select>
 47     
 48     <!-- FindById -->
 49     <resultMap class="Emp" id="ResultObject">
 50         <result property="empno" column="EMPNO" javaType="Integer" jdbcType="NUMBER"/>
 51         <result property="ename" column="ENAME" javaType="String" jdbcType="VARCHAR2"/>
 52         <result property="job" column="JOB" javaType="String" jdbcType="VARCHAR2"/>
 53         <result property="mgr" column="MGR" javaType="Integer" jdbcType="NUMBER"/>
 54         <result property="empmgr.empno" column="MGREMPNO" javaType="Integer" jdbcType="NUMBER"/>
 55         <result property="empmgr.ename" column="MGRENAME" javaType="String" jdbcType="VARCHAR2"/>
 56         <result property="hiredate" column="HIREDATE" javaType="java.util.Date" jdbcType="DATE"/>
 57         <result property="sal" column="SAL" javaType="Float" jdbcType="NUMBER"/>
 58         <result property="comm" column="COMM" javaType="Float" jdbcType="NUMBER"/>
 59         <result property="dept.deptno" column="DEPTNO" javaType="Integer" jdbcType="NUMBER"/>
 60         <result property="dept.dname" column="DNAME" javaType="String" jdbcType="VARCHAR2"/>
 61         <result property="photo" column="PHOTO" javaType="String" jdbcType="VARCHAR2"/>
 62         <result property="note" column="NOTE" javaType="String" jdbcType="VARCHAR2"/>
 63     </resultMap>
 64     <select id="empFindById" resultClass="Emp" parameterClass="Integer" resultMap="ResultObject">
 65         select e.empno,e.ename,e.job,e.mgr,m.empno MGREMPNO,m.ename MGRENAME,e.hiredate,e.sal,e.comm,d.deptno,d.dname,e.photo,e.note 
 66         from emp e,emp m,dept d 
 67         <dynamic prepend=" where ">
 68             e.empno=#id# and e.mgr=m.empno(+) and e.deptno=d.deptno(+)
 69         </dynamic>         
 70     </select>
 71     
 72     <!-- FindByKeyWord -->
 73     <select id="findByKeyword" parameterClass="String" resultClass="Emp">
 74         select empno,ename,job,NVL(mgr,0),hiredate,NVL(sal,0),NVL(comm,0),NVL(deptno,0),photo,note
 75         from emp e 
 76         where empno like '%$keyword$%' or ename like '%$keyword$%' or job like '%$keyword$%' or mgr like '%$keyword$%' or to_char(hiredate,'yyyy-mm-dd') like '%$keyword$%' or sal like '%$keyword$%' or comm like '%$keyword$%' or deptno like '%$keyword$%'
 77     </select>
 78     
 79     <!-- EmpPaging -->
 80     <parameterMap class="java.util.Map" id="pageSizeRownum">
 81         <parameter property="keyword" javaType="String" jdbcType="VARCHAR2"/>
 82         <parameter property="beginRownum" javaType="Integer" jdbcType="NUMBER"/>
 83         <parameter property="endRownum" javaType="Integer" jdbcType="NUMBER"/>
 84     </parameterMap>
 85     <select id="pagingQuery" parameterMap="pageSizeRownum" resultClass="Emp">
 86         select temp.empno,temp.ename,temp.job,m.ename,m.empno,temp.hiredate,NVL(temp.sal,0),NVL(temp.comm,0),d.deptno,d.dname,temp.photo,temp.note 
 87         from (SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,ROWNUM rn,photo,note FROM emp 
 88         <dynamic prepend=" where ">
 89         <![CDATA[(empno like '%$keyword$%' or ename like '%$keyword$%' or job like '%$keyword$%' or mgr like '%$keyword$%' or to_char(hiredate,'yyyy-mm-dd') like '%$keyword$%' or sal like '%$keyword$%' or comm like '%$keyword$%' or deptno like '%$keyword$%') and ROWNUM<=#endRownum# ORDER BY empno) temp,emp m,dept d where temp.rn>#beginRownum# and temp.mgr=m.empno(+) and temp.deptno=d.deptno ]]>
 90         </dynamic>
 91     </select>
 92     
 93     <!-- Recordcount -->
 94     <select id="allRecordcount" resultClass="Long" parameterClass="String">
 95         SELECT count(e.empno) 
 96         FROM emp e 
 97         WHERE empno like '%$keyword$%' or ename like '%$keyword$%' or job like '%$keyword$%' or mgr like '%$keyword$%' or to_char(hiredate,'yyyy-mm-dd') like '%$keyword$%' or sal like '%$keyword$%' or comm like '%$keyword$%' or deptno like '%$keyword$%' ORDER BY empno
 98     </select>    
 99     
100     <insert id="addEmployee" parameterClass="Emp">
101         insert into Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,photo,note) values(#empno#,#ename#,#job#,#mgr#,#hiredate#,#sal#,#comm#,#deptno#,#photo#,#note#)
102     </insert>
103     <update id="updateOneEmployee" parameterClass="Emp">
104         update Emp set ename=#ename#,job=#job#,mgr=#mgr#,hiredate=#hiredate#,sal=#sal#,comm=#comm#,deptno=#deptno#,photo=#photo#,note=#note# where empno=#empno#
105     </update>
106     <delete id="deleteOneEmp" parameterClass="Integer">
107         delete from emp where empno=#id#
108     </delete>
109 </sqlMap>

testEmployee.java

View Code

  1 package org.xiong.ibatis.demo.model.test;
  2 
  3 import java.io.IOException;
  4 import java.io.Reader;
  5 import java.sql.SQLException;
  6 import java.text.ParseException;
  7 import java.text.SimpleDateFormat;
  8 import java.util.HashMap;
  9 import java.util.List;
 10 
 11 import org.junit.BeforeClass;
 12 import org.junit.Test;
 13 import org.xiong.ibatis.demo.model.Emp;
 14 
 15 import com.ibatis.common.resources.Resources;
 16 import com.ibatis.sqlmap.client.SqlMapClient;
 17 import com.ibatis.sqlmap.client.SqlMapClientBuilder;
 18 
 19 public class testEmployee
 20 {
 21     private static SqlMapClient sqlMapClient;
 22     private static Reader reader;
 23 
 24     @BeforeClass
 25     public static void beforeClass()
 26     {
 27         try
 28         {
 29             reader = Resources
 30                     .getResourceAsReader("org/xiong/ibatis/demo/model/SqlMapConfig.xml");
 31             sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
 32         }
 33         catch (IOException e)
 34         {
 35             e.printStackTrace();
 36         }
 37         finally
 38         {
 39             try
 40             {
 41                 reader.close();
 42             }
 43             catch (IOException e)
 44             {
 45                 e.printStackTrace();
 46             }
 47         }
 48     }
 49 
 50     @Test
 51     public void testFindAllEmp()
 52     {
 53 
 54         try
 55         {
 56             List<Emp> allEmp = sqlMapClient.queryForList("selectAllEmp");
 57             for (Emp e : allEmp)
 58             {
 59                 System.out.println(e);
 60             }
 61         }
 62         catch (SQLException e)
 63         {
 64             e.printStackTrace();
 65         }
 66 
 67     }
 68 
 69     @Test
 70     public void testFindOneEmp()
 71     {
 72         Emp emp = new Emp();
 73         emp.setEmpno(7369);
 74 
 75         try
 76         {
 77             Emp e = (Emp) sqlMapClient.queryForObject("selectOneEmp", emp);
 78             System.out.println(e.getEname());
 79         }
 80         catch (SQLException e)
 81         {
 82             e.printStackTrace();
 83         }
 84 
 85     }
 86 
 87     @Test
 88     public void testDynamicQuery()
 89     {
 90 
 91         List<Emp> emps = null;
 92         try
 93         {
 94             emps = sqlMapClient.queryForList("dynamicQuery", 7369);
 95             for (Emp e : emps)
 96             {
 97                 System.out.println(e);
 98             }
 99         }
100         catch (SQLException e)
101         {
102             e.printStackTrace();
103         }
104     }
105 
106     @Test
107     public void testAddOneEmp()
108     {
109         Emp emp = new Emp();
110         emp.setEmpno(1097);
111         emp.setEname("张三");
112         emp.setJob("要饭的");
113         try
114         {
115             emp.setHiredate(new SimpleDateFormat("yyyy-MM-dd")
116                     .parse("2010-12-01"));
117         }
118         catch (ParseException e)
119         {
120             e.printStackTrace();
121         }
122         emp.setMgr(7369);
123         emp.setSal(451.45f);
124         emp.setComm(100.15f);
125         emp.setDeptno(10);
126         emp.setPhoto("nophoto.jpg");
127         emp.setNote("暂无简介");
128         try
129         {
130             sqlMapClient.insert("addEmployee", emp);
131         }
132         catch (SQLException e)
133         {
134             e.printStackTrace();
135         }
136 
137     }
138 
139     @Test
140     public void testUpdateOneEmp()
141     {
142         Emp emp = new Emp();
143         emp.setEmpno(1097);
144         emp.setEname("张大大");
145         emp.setJob("画家");
146         try
147         {
148             emp.setHiredate(new SimpleDateFormat("yyyy-MM-dd")
149                     .parse("2010-12-22"));
150         }
151         catch (ParseException e)
152         {
153             e.printStackTrace();
154         }
155         emp.setMgr(7369);
156         emp.setSal(451.45f);
157         emp.setComm(100.15f);
158         emp.setDeptno(10);
159         emp.setPhoto("nophoto.jpg");
160         emp.setNote("暂无简介");
161         try
162         {
163             System.out.println("影响了:"
164                     + sqlMapClient.update("updateOneEmployee", emp) + "行！");
165         }
166         catch (SQLException e)
167         {
168             e.printStackTrace();
169         }
170     }
171 
172     @Test
173     public void testDeleteOneEmp()
174     {
175         try
176         {
177             System.out.println("影响了:"
178                     + sqlMapClient.delete("deleteOneEmp", 1097) + "行！");
179         }
180         catch (SQLException e)
181         {
182             e.printStackTrace();
183         }
184     }
185 
186     @Test
187     public void testFindEmpsByPaging()
188     {
189         HashMap<String, Object> parameterMap = new HashMap<String, Object>();
190         parameterMap.put("keyword", "S");
191         parameterMap.put("endRownum", 16);
192         parameterMap.put("beginRownum", 5);
193 
194         try
195         {
196             @SuppressWarnings("unchecked")
197             List<Emp> emps = sqlMapClient.queryForList("pagingQuery",
198                     parameterMap);
199             for (Emp e : emps)
200             {
201                 System.out.println(e);
202             }
203 
204         }
205         catch (SQLException e)
206         {
207             e.printStackTrace();
208         }
209 
210     }
211 
212     @Test
213     public void testGetAllCount()
214     {
215         long allCount = 0;
216         try
217         {
218             allCount = (Long) sqlMapClient
219                     .queryForObject("allRecordcount", "S");
220             System.out.println(allCount);
221         }
222         catch (SQLException e)
223         {
224             // TODO Auto-generated catch block
225             e.printStackTrace();
226         }
227     }
228 
229     @Test
230     public void testFindAllByKeyword()
231     {
232         String keyword = "S";
233         try
234         {
235             @SuppressWarnings("unchecked")
236             List<Emp> emps = sqlMapClient
237                     .queryForList("findByKeyword", keyword);
238             for (Emp e : emps)
239             {
240                 System.out.println(e);
241             }
242 
243         }
244         catch (SQLException e)
245         {
246             e.printStackTrace();
247         }
248     }
249 
250     @Test
251     public void testFindById()
252     {
253         Emp e = null;
254         int id = 7369;
255 
256         try
257         {
258             e = (Emp) sqlMapClient.queryForObject("empFindById", id);
259             System.out.println(e);
260         }
261         catch (SQLException e1)
262         {
263             e1.printStackTrace();
264         }
265     }
266 
267     @Test
268     public void testDynamicQueryWithMC()
269     {
270 
271         List<Emp> emps = null;
272         HashMap<String, Object> parameterMap = new HashMap<String, Object>();
273         parameterMap.put("empno1", 7369);
274         parameterMap.put("empno2", 7788);
275         parameterMap.put("comm", 400);
276         parameterMap.put("sal", 2000);
277         parameterMap.put("hiredate", "1980-12-22");
278         try
279         {
280             emps = sqlMapClient.queryForList(
281                     "dynamicQueryWithMultipleconditions", parameterMap);
282             for (Emp e : emps)
283             {
284                 System.out.println(e);
285             }
286         }
287         catch (SQLException e)
288         {
289             e.printStackTrace();
290         }
291     }
292 }