ibatis 动态条件查询

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


Xml代码

<!-- 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>  

<!-- 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代码

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;   
    }   
       
    @Override  
    public String toString() {   
        String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();   
        return content;   
    }   
       
}  

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;
	}
	
	@Override
	public String toString() {
		String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();
		return content;
	}
	
}

StudentDTO的信息


Java代码

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;   
    }   
       
    @Override  
    public String toString() {   
        String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();   
        return content;   
    }   
       
}  

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;
	}
	
	@Override
	public String toString() {
		String content = "Sid="+this.sid+"\t"+"Sname="+this.sname+"\t"+this.birth.toLocaleString();
		return content;
	}
	
}

Java中的调用信息


Java代码 

@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());   
    }   
@Override  
    public List dynamicGetStudent(StudentDTO studentDTO) {   
        List result = null;   
        try {   
            result = sqlMapClient.queryForList("dynamicGetStudent",studentDTO);   
        } catch (SQLException e) {   
            // TODO Auto-generated catch block   
            e.printStackTrace();   
        }   
        return result;   
    }