java条件查询分页_Java分页查询&条件查询

1 分页查询

1.1 分页核心

设计一个用于封装当前页所有分页相关的数据的对象，叫分页对象PageBean

/**

* 分页对象。用于封装当前页的分页相关的所有数据

* @author h

*

*/

public class PageBean {

private List data;//当前页的数据

private Integer firstPage;//首页

private Integer prePage;//上一页

private Integer nextPage;//下一页

private Integer totalPage;//末页、总页数

private Integer currentPage;//当前页

private Integer totalCount;//总记录数

private Integer pageSize;//每页显示的记录数

}

1.2 分页的实现步骤

1)编写分页对象和实体对象

/**

* 分页对象。用于封装当前页的分页相关的所有数据

* @author h

*

*/

public class PageBean {

private List data;//当前页的数据

private Integer firstPage;//首页

private Integer prePage;//上一页

private Integer nextPage;//下一页

private Integer totalPage;//末页、总页数

private Integer currentPage;//当前页

private Integer totalCount;//总记录数

private Integer pageSize;//每页显示的记录数

public List getData() {

return data;

}

public void setData(List data) {

this.data = data;

}

public Integer getFirstPage() {

return 1;

}

public void setFirstPage(Integer firstPage) {

this.firstPage = firstPage;

}

/**

* 计算上一页

* @return

*/

public Integer getPrePage() {

return this.getCurrentPage()==this.getFirstPage() ? 1 : this.getCurrentPage()-1;

}

public void setPrePage(Integer prePage) {

this.prePage = prePage;

}

/**

* 计算下一页

* @return

*/

public Integer getNextPage() {

return this.getCurrentPage()==this.getTotalPage()?

this.getTotalPage()

: this.getCurrentPage()+1;

}

public void setNextPage(Integer nextPage) {

this.nextPage = nextPage;

}

public Integer getTotalPage() {

return this.getTotalCount()%this.getPageSize()==0 ?

this.getTotalCount()/this.getPageSize()

:this.getTotalCount()/this.getPageSize()+1;

}

public void setTotalPage(Integer totalPage) {

this.totalPage = totalPage;

}

public Integer getCurrentPage() {

return currentPage;

}

public void setCurrentPage(Integer currentPage) {

this.currentPage = currentPage;

}

public Integer getTotalCount() {

return totalCount;

}

public void setTotalCount(Integer totalCount) {

this.totalCount = totalCount;

}

public Integer getPageSize() {

return pageSize;

}

public void setPageSize(Integer pageSize) {

this.pageSize = pageSize;

}

}

以员工查询信息为例子

/**

* 员工对象

* @author h

*

*/

public class Employee {

private int id;

private String name;

private String gender;

private int age;

private String title;

private String phone;

private String email;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getGender() {

return gender;

}

public void setGender(String gender) {

this.gender = gender;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Employee(int id, String name, String gender, int age, String title,

String phone, String email) {

super();

this.id = id;

this.name = name;

this.gender = gender;

this.age = age;

this.title = title;

this.phone = phone;

this.email = email;

}

public Employee() {

super();

// TODO Auto-generated constructor stub

}

@Override

public String toString() {

return "Employee [age=" + age + ", email=" + email + ", gender="

+ gender + ", id=" + id + ", name=" + name + ", phone=" + phone

+ ", title=" + title + "]";

}

}

2)编写DAO层代码(查询总记录数和查询当前页数据)

/**

* 员工的DAO类

* @author h

*

*/

public class EmpDao {

/**

* 提供一个查询当前页员工的方法

*/

public List queryCurrentData(Integer currentPage,Integer pageSize){

try {

//1.创建QueryRunner对象

QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());

//2.执行查询sql操作

//计算查询的起始行

int startNo = (currentPage-1)*pageSize;

List list = (List)qr.query("SELECT * FROM employee LIMIT ?,?",

new BeanListHandler(Employee.class),

new Object[]{startNo,pageSize});

return list;

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

}

/**

* 提供查询总记录数的方法

* @param args

*/

public Integer queryTotalCount(){

try {

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());

//2.执行sql查询

Long count = (Long)qr.query("SELECT COUNT(*) FROM employee", new ScalarHandler(1));

return count.intValue();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

}

}

3)编写Service层代码(封装PageBean对象)

/**

* 员工的业务类

* @author h

*

*/

public class EmpService {

/**

* 提供用于封装PageBean对象方法(处理业务逻辑)

*/

public PageBean queryPageBean(Integer currentPage,Integer pageSize){

//封装PageBean分页对象数据

PageBean pageBean = new PageBean();

//设置当前页

pageBean.setCurrentPage(currentPage);

//设置每页显示的记录数

pageBean.setPageSize(pageSize);

EmpDao empDao = new EmpDao();

/**

* 从数据库中查询出总记录数

*/

int totalCount = empDao.queryTotalCount();

//设置总记录数

pageBean.setTotalCount(totalCount);

//设置当前页的数据

/**

* 从数据库中查询出当前页的员工数据

*/

List list = empDao.queryCurrentData(pageBean.getCurrentPage(), pageBean.getPageSize());

pageBean.setData(list);

return pageBean;

}

}

4)编写Servlet代码(接收用户输入)

public class PageServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

/***********一、获取用户输入**************/

//设置当前页(获取用户的输入)

String currentPageStr = request.getParameter("currentPage");

//如果用户没有输入，就是默认第1页

if(currentPageStr==null || currentPageStr.equals("")){

currentPageStr = "1";

}

//设置每页显示的记录数(获取用户的输入)

String pageSizeStr = request.getParameter("pageSize");

if(pageSizeStr==null || pageSizeStr.equals("")){

pageSizeStr = "5";

}

/***************二、调用业务方法，获取PageBean对象***********************/

EmpService empService = new EmpService();

PageBean pageBean = empService.queryPageBean(Integer.parseInt(currentPageStr), Integer.parseInt(pageSizeStr));

/****************三、得到业务数据，跳转视图*********************/

//把PageBean数据发送到jsp页面中显示

request.setAttribute("pageBean", pageBean);

//转发

request.getRequestDispatcher("/list.jsp").forward(request, response);

}

}

5)编写jsp页面代码(显示分页效果)

分页显示效果

编号	姓名	性别	年龄	职位	电话	邮箱
${emp.id }	${emp.name }	${emp.gender }	${emp.age}	${emp.title }	${emp.phone }	${emp.email }
1)如果当前页是首页，则不显示“首页”和“上一页”的连接 2)如果当前页是末页，则不显示“末页”和“下一页”的连接 --%> 首页 上一页 首页 上一页 下一页 末页 下一页 末页 当前第${pageBean.currentPage }页/共${pageBean.totalPage }页， 共${pageBean.totalCount }条 每页显示 条

//改变每页显示记录数的方法

function changePageSize(){

//获取用户输入的记录数

var pageSize = document.getElementById("pageSize").value;

//判断是否输入的数值

var reg = /^[1-9][0-9]?$/;

if(!reg.test(pageSize)){

alert("请输入数组类型！");

return;

}

//把记录数发送到后台

var url = "${pageContext.request.contextPath}/PageServlet?pageSize="+pageSize;

window.location.href=url;

}

2 条件查询

2.1 条件查询的核心

根据用户的查询条件组装sql语句：

//组装sql

StringBuffer sql = new StringBuffer("select * from department where 1=1 ");

if(query!=null){

//部门名称不为空时

if(query.getDeptName()!=null && !query.getDeptName().equals("")){

sql.append(" and deptName like '%"+query.getDeptName()+"%'");

}

//部门负责人不为空时

if(query.getPrincipal()!=null && !query.getPrincipal().equals("")){

sql.append(" and principal like '%"+query.getPrincipal()+"%'");

}

//部门职能不为空时

if(query.getFunctional()!=null && !query.getFunctional().equals("")){

sql.append(" and functional like '%"+query.getFunctional()+"%'");

}

}