Mybatis 分页查询

	<select id="getAllMateriel" resultType="Materiel" parameterType="Page">
		Select *,(select count(*) from Materiel_Info)as total FROM (
		select ROW_NUMBER()Over(order by MaterielCode desc) as rowId,* from Materiel_Info)
		as mytable
		<where> <if test="start !=null and end !=null">
				rowId between #{start} and #{end}
				</if>
				<if test="materielCode != null and materielCode !=''">
				AND materielCode like '%${materielCode}%'
				</if>
				<if test="materielName !=null and materielCode !=''">
				AND materielName like '%${materielName}%'
				</if>
				<if test="spec !=null and spec !=''">
				AND spec like '%${spec}%'
				</if>
 		</where>
	</select>

XXXXMapper.xml      文件如上

 

POJO类

Materiel.java

package com.dms.pojo;

public class Materiel {

	public Materiel() {
		super();
	}

	String MaterielCode;
	String MaterielName;
	String spec;
	String unit;
	float volume;
	float weight;
	String Note;
	String GroupCode;

	int total;	//总数

	public String getMaterielCode() {
		return MaterielCode;
	}

	public void setMaterielCode(String materielCode) {
		MaterielCode = materielCode;
	}

	public String getMaterielName() {
		return MaterielName;
	}

	public void setMaterielName(String materielName) {
		MaterielName = materielName;
	}

	public String getSpec() {
		return spec;
	}

	public void setSpec(String spec) {
		this.spec = spec;
	}

	public String getUnit() {
		return unit;
	}

	public void setUnit(String unit) {
		this.unit = unit;
	}

	public float getVolume() {
		return volume;
	}

	public void setVolume(float volume) {
		this.volume = volume;
	}

	public float getWeight() {
		return weight;
	}

	public void setWeight(float weight) {
		this.weight = weight;
	}

	public String getNote() {
		return Note;
	}

	public void setNote(String note) {
		Note = note;
	}

	public String getGroupCode() {
		return GroupCode;
	}

	public void setGroupCode(String groupCode) {
		GroupCode = groupCode;
	}

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}

}

Page.java

package com.dms.pojo;

public class Page {
	public Page(){
		super();
	}
	
	String rp;  //显示条数
	String newp;//当前页码
	String key; //关键字
	String fields;//数据库字段
	int start;   //开始
	int end;     //结束
	String materielCode;//物料编码
	String materielName;//物料名称
	String spec;//规格型号
	
	public String getRp() {
		return rp;
	}

	public void setRp(String rp) {
		this.rp = rp;
	}

	public String getNewp() {
		return newp;
	}

	public void setNewp(String newp) {
		this.newp = newp;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}

	public String getFields() {
		return fields;
	}

	public void setFields(String fields) {
		this.fields = fields;
	}

	public int getStart() {
		return start;
	}

	public void setStart(int start) {
		this.start = start;
	}

	public int getEnd() {
		return end;
	}

	public void setEnd(int end) {
		this.end = end;
	}

	public String getMaterielCode() {
		return materielCode;
	}

	public void setMaterielCode(String materielCode) {
		this.materielCode = materielCode;
	}

	public String getMaterielName() {
		return materielName;
	}

	public void setMaterielName(String materielName) {
		this.materielName = materielName;
	}

	public String getSpec() {
		return spec;
	}

	public void setSpec(String spec) {
		this.spec = spec;
	}
}