Ibatis动态(dynamic)查询



Ibatis的动态查询使得数据操作变得非常的灵活，下次举出了常用的动态查询的属性信息：
Ibatis配置信息

Xml代码  

1. <!-- Dynamic Sql -->  
2.   <typeAlias alias="Student" type="com.ibatis.Student" />  
3.     <typeAlias alias="StudentDTO" type="com.ibatis.StudentDTO" />  
4.     <select id="dynamicGetStudent" parameterClass="StudentDTO"  
5.         resultClass="Student">  
6.         select *  
7.         from student  
8.         <dynamic prepend="WHERE">  
9.             <isNotEqual prepend="AND" property="sid" compareValue="0">  
10.                 sid = #sid#  
11.             </isNotEqual>  
12.               
13.             <isNotNull prepend="AND" property="sname">  
14.                 sname = #sname#  
15.             </isNotNull>  
16.         </dynamic>  
17.         <dynamic prepend="order by">  
18.             <isParameterPresent>  
19.                 <isEqual prepend="order by" property="sort" compareValue="1">  
20.                     sname desc,socre  
21.                 </isEqual>  
22.                   
23.                 <isEqual prepend="order by" property="sort" compareValue="2">  
24.                     sname asc,socre  
25.                 </isEqual>  
26.             </isParameterPresent>  
27.         </dynamic>  
28.     </select>  

<!-- Dynamic Sql -->  <typeAlias alias="Student" type="com.ibatis.Student" /><typeAlias alias="StudentDTO" type="com.ibatis.StudentDTO" /><select id="dynamicGetStudent" parameterClass="StudentDTO"resultClass="Student">select *from student<dynamic prepend="WHERE"><isNotEqual prepend="AND" property="sid" compareValue="0">sid = #sid#</isNotEqual><isNotNull prepend="AND" property="sname">sname = #sname#</isNotNull></dynamic><dynamic prepend="order by"><isParameterPresent><isEqual prepend="order by" property="sort" compareValue="1">sname desc,socre</isEqual><isEqual prepend="order by" property="sort" compareValue="2">sname asc,socre</isEqual></isParameterPresent></dynamic></select>

Student的信息

Java代码  

1. package com.ibatis;  
2.   
3. import java.util.Date;  
4.   
5. public class Student {  
6.     private int sid;  
7.     private String sname;  
8.     private String major;  
9.     private Date birth;  
10.     private int socre;  
11.     public int getSid() {  
12.         return sid;  
13.     }  
14.     public void setSid(int sid) {  
15.         this.sid = sid;  
16.     }  
17.     public String getSname() {  
18.         return sname;  
19.     }  
20.     public void setSname(String sname) {  
21.         this.sname = sname;  
22.     }  
23.     public String getMajor() {  
24.         return major;  
25.     }  
26.     public void setMajor(String major) {  
27.         this.major = major;  
28.     }  
29.     public Date getBirth() {  
30.         return birth;  
31.     }  
32.     public void setBirth(Date birth) {  
33.         this.birth = birth;  
34.     }  
35.     public int getSocre() {  
36.         return socre;  
37.     }  
38.     public void setSocre(int socre) {  
39.         this.socre = socre;  
40.     }  
41.       
42.     @Override  
43.     public String toString() {  
44.         String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();  
45.         return content;  
46.     }  
47.       
48. }  

package com.ibatis;import java.util.Date;public class Student {private int sid;private String sname;private String major;private Date birth;private int socre;public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public String getMajor() {return major;}public void setMajor(String major) {this.major = major;}public Date getBirth() {return birth;}public void setBirth(Date birth) {this.birth = birth;}public int getSocre() {return socre;}public void setSocre(int socre) {this.socre = socre;}@Overridepublic String toString() {String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();return content;}}

StudentDTO的信息

Java代码  

1. package com.ibatis;  
2.   
3. import java.util.Date;  
4.   
5. public class StudentDTO {  
6.     private int sid;  
7.     private String sname;  
8.     private String major;  
9.     private Date birth;  
10.     private int socre;  
11.     private int sort;  
12.     public int getSort() {  
13.         return sort;  
14.     }  
15.     public void setSort(int sort) {  
16.         this.sort = sort;  
17.     }  
18.     public int getSid() {  
19.         return sid;  
20.     }  
21.     public void setSid(int sid) {  
22.         this.sid = sid;  
23.     }  
24.     public String getSname() {  
25.         return sname;  
26.     }  
27.     public void setSname(String sname) {  
28.         this.sname = sname;  
29.     }  
30.     public String getMajor() {  
31.         return major;  
32.     }  
33.     public void setMajor(String major) {  
34.         this.major = major;  
35.     }  
36.     public Date getBirth() {  
37.         return birth;  
38.     }  
39.     public void setBirth(Date birth) {  
40.         this.birth = birth;  
41.     }  
42.     public int getSocre() {  
43.         return socre;  
44.     }  
45.     public void setSocre(int socre) {  
46.         this.socre = socre;  
47.     }  
48.       
49.     @Override  
50.     public String toString() {  
51.         String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();  
52.         return content;  
53.     }  
54.       
55. }  

package com.ibatis;import java.util.Date;public class StudentDTO {private int sid;private String sname;private String major;private Date birth;private int socre;private int sort;public int getSort() {return sort;}public void setSort(int sort) {this.sort = sort;}public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public String getMajor() {return major;}public void setMajor(String major) {this.major = major;}public Date getBirth() {return birth;}public void setBirth(Date birth) {this.birth = birth;}public int getSocre() {return socre;}public void setSocre(int socre) {this.socre = socre;}@Overridepublic String toString() {String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();return content;}}

Java中的调用信息

Java代码  

1. @SuppressWarnings("unchecked")  
2.     public static void main(String[] args) {  
3.         List<Student> list = dao.dynamicGetStudent(studentDTO);  
4.         for(Student student : list){  
5.             System.out.println("name:"+student.getSname());  
6.         }  
7.         System.out.println("size:"+list.size());  
8.     }  
9. @Override  
10.     public List dynamicGetStudent(StudentDTO studentDTO) {  
11.         List result = null;  
12.         try {  
13.             result = sqlMapClient.queryForList("dynamicGetStudent",studentDTO);  
14.         } catch (SQLException e) {  
15.             // TODO Auto-generated catch block  
16.             e.printStackTrace();  
17.         }  
18.         return result;  
19.     }  

@SuppressWarnings("unchecked")public static void main(String[] args) {List<Student> list = dao.dynamicGetStudent(studentDTO);for(Student student : list){System.out.println("name:"+student.getSname());}System.out.println("size:"+list.size());}@Overridepublic List dynamicGetStudent(StudentDTO studentDTO) {List result = null;try {result = sqlMapClient.queryForList("dynamicGetStudent",studentDTO);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return result;}

动态查询中的常用属性：
属性关键字含义<isEqual>如果参数相等于值则查询条件有效<isNotEqual>如果参数不等于值则查询条件有效<isGreaterThan>如果参数大于值则查询条件有效<isGreaterEqual>如果参数等于值则查询条件有效<isLessEqual>如果参数小于值则查询条件有效。如下所示：<isLessEqual prepend = ”AND” property = ”age” compareValue = ”18” >ADOLESCENT = ‘TRUE’</isLessEqual><isPropertyAvailable>如果参数有使用则查询条件有效。<isNotPropertyAvailable>如果参数没有使用则查询条件有效<isNull>如果参数为NULL则查询条件有效<isNotNull>如果参数不为NULL则查询条件有效<isEmpty>如果参数为空则查询条件有效<isNotEmpty>如果参数不为空则查询条件有效<isParameterPresent>如果参数类不为NULL则查询条件有效<isNotParameterPresent>Checks to see if the parameter object is not present (null). Example Usage