条件查询和分页

创建查询条件的实体类 和分页类

条件实体类 

package com.oracle.domain;

public class Conditon {
	private String pname;
	private String cid;
	private String is_hot;
	public String getPname() {
		return pname;
	}
	public void setPname(String pname) {
		this.pname = pname;
	}
	public String getCid() {
		return cid;
	}
	public void setCid(String cid) {
		this.cid = cid;
	}
	public String getIs_hot() {
		return is_hot;
	}
	public void setIs_hot(String is_hot) {
		this.is_hot = is_hot;
	}
	@Override
	public String toString() {
		return "Conditon [pname=" + pname + ", cid=" + cid + ", is_hot=" + is_hot + "]";
	}
	
}

 分页实体类

package com.oracle.domain;

import java.util.List;

public class Pagebean<T> {
    //
    private int currentpage;
    private int totalpage;
    private int currencount;
    private int totalcount;
    private List<T> list;
    public int getCurrentpage() {
        return currentpage;
    }
    public void setCurrentpage(int currentpage) {
        this.currentpage = currentpage;
    }
    public int getTotalpage() {
        return totalpage;
    }
    public void setTotalpage(int totalpage) {
        this.totalpage = totalpage;
    }
    public int getCurrencount() {
        return currencount;
    }
    public void setCurrencount(int currencount) {
        this.currencount = currencount;
    }
    public int getTotalcount() {
        return totalcount;
    }
    public void setTotalcount(int totalcount) {
        this.totalcount = totalcount;
    }
    public List<T> getList() {
        return list;
    }
    public void setList(List<T> list) {
        this.list = list;
    }
    @Override
    public String toString() {
        return "Pagebean [currentpage=" + currentpage + ", totalpage=" + totalpage + ", currencount=" + currencount
                + ", totalcount=" + totalcount + ", list=" + list + "]";
    }
    
    
}

 

servlet层

package com.oracle.room.web;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;

import com.google.gson.Gson;
import com.oracle.domain.Condition;
import com.oracle.domain.Pagebean;
import com.oracle.domain.Room;
import com.oracle.service.Roomservice;


public class Pageservlet extends HttpServlet {
    private Roomservice roomservice=new Roomservice();
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //解决乱码
        request.setCharacterEncoding("UTF-8");
        //获取数据
        Map<String , String[] > map=request.getParameterMap();
        //
        Condition condition=new Condition();
        //
        try {
            BeanUtils.populate(condition, map);
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
        
        
        
        
        
        
        
        //获取当前页
        String  currentstr=request.getParameter("currentpage");
        //判断currenpage是否为空
        if(currentstr==null){
            //默认第一页
            currentstr="1";
        }
        //把currentpage转为int
        int currentpage=Integer.valueOf(currentstr);
        //每页显示条数
        int currentcount=5;
        //调用service类方法
        Pagebean<Room> page=roomservice.getpage(currentpage, currentcount,condition);
        request.setAttribute("page", page);
    
        
        
        
        
        
        
        
        request.getRequestDispatcher("manage/room/list.jsp").forward(request, response);
    
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

 service层

package com.oracle.service;

import java.sql.SQLException;
import java.util.List;

import com.oracle.dao.Roomdao;
import com.oracle.domain.Condition;
import com.oracle.domain.Pagebean;
import com.oracle.domain.Room;

public class Roomservice {
    private Roomdao roomdao=new Roomdao();
    //分页
    public Pagebean<Room> getpage(int currentpage,int currentcount, Condition condition){
        //创建pagebean对象
        Pagebean<Room> page= new Pagebean<Room>();
        //封装数据
        page.setCurrentcount(currentcount);
        page.setCurrentpage(currentpage);
        //查询总条数
        int totalcount=0;
        try {
            totalcount=roomdao.getpage(condition);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //总页数
        int  totalpage=(int)Math.ceil(totalcount*1.0/currentcount);
        page.setTotalpage(totalpage);
        //查询多少页
        //计算起始页 
        int  index=(currentpage-1)*currentcount;
        List<Room> list=null;
        try {
            list=roomdao.page(index, currentcount,condition);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //封装每页显示数据
        page.setList(list);
        return page;
    
    
    }
}

dao层

package com.oracle.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.oracle.domain.Condition;
import com.oracle.domain.Room;
import com.oracle.tools.MydDBUtils;

public class Roomdao {
    
    //查询所总条数
    public int getpage(Condition condition) throws SQLException{
        QueryRunner qr=new QueryRunner(MydDBUtils.getDataSource());
        String sql="select count(*) from room where 1=1";
        ArrayList<Object > arr=new ArrayList<Object >();
        
        if(condition!=null){
            if(condition.getRid()!=null&&!condition.getRid().trim().equals("")){
                sql+=" and rid like ?";
                arr.add("%"+condition.getRid()+"%");
            }
            if(condition.getType()!=null&&!condition.getType().trim().equals("")){
                sql+=" and type=? ";
                arr.add(condition.getType());
            }
            if(condition.getIs_hot()!=null&&!condition.getIs_hot().trim().equals("")){
                sql+=" and is_hot=? ";
                arr.add(condition.getIs_hot());
            }
            if(condition.getSta()!=null&&!condition.getSta().trim().equals("")){
                sql+=" and sta=? ";
                arr.add(condition.getSta());
            }
        }
            Long row=qr.query(sql, new ScalarHandler<Long>(),arr.toArray());
             System.out.println(row);
            return row.intValue();
        
    
    }
    //查询多少也
    public List<Room> page(int index,int currentcount,Condition condition ) throws SQLException{
        QueryRunner qr=new QueryRunner(MydDBUtils.getDataSource());
        String sql="select * from room where 1=1";
        ArrayList<Object > arr=new ArrayList<Object >();
        
    
            if(condition!=null){
                if(condition.getRid()!=null&&!condition.getRid().trim().equals("")){
                    sql+=" and rid like ?";
                    arr.add("%"+condition.getRid()+"%");
                }
                if(condition.getType()!=null&&!condition.getType().trim().equals("")){
                    sql+=" and type=? ";
                    arr.add(condition.getType());
                }
                if(condition.getIs_hot()!=null&&!condition.getIs_hot().trim().equals("")){
                    sql+=" and is_hot=? ";
                    arr.add(condition.getIs_hot());
                }
                if(condition.getSta()!=null&&!condition.getSta().trim().equals("")){
                    sql+=" and sta=?" ;
                    arr.add(condition.getSta());
                }
            
        }
        sql+=" limit ?,? ";
        arr.add(index);
        arr.add(currentcount);
        List<Room> list=qr.query(sql, new BeanListHandler<Room>(Room.class),arr.toArray());
        return list;
    
    }

}