Java分页查询&条件查询
JAVA
9 分钟 读完 (大约 1408 个字) 0次访问

Java分页查询&条件查询

1 分页查询

1.1 分页核心

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * 分页对象。用于封装当前页的分页相关的所有数据
 * @author h
 *
 */
public class PageBean {
	private List<Employee> 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）编写分页对象和实体对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
/**
 * 分页对象。用于封装当前页的分页相关的所有数据
 * @author h
 *
 */
public class PageBean {
	private List<Employee> data;//当前页的数据
	private Integer firstPage;//首页
	private Integer prePage;//上一页
	private Integer nextPage;//下一页
	private Integer totalPage;//末页、总页数
	private Integer currentPage;//当前页
	private Integer totalCount;//总记录数
	private Integer pageSize;//每页显示的记录数
	public List<Employee> getData() {
		return data;
	}
	public void setData(List<Employee> 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;
	}
	
}

以员工查询信息为例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
/**
 * 员工对象
 * @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层代码（查询总记录数和查询当前页数据）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/**
 * 员工的DAO类
 * @author h
 *
 */
public class EmpDao {

	/**
	 * 提供一个查询当前页员工的方法
	 */
	public List<Employee> queryCurrentData(Integer currentPage,Integer pageSize){
		try {
			//1.创建QueryRunner对象
			QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
			//2.执行查询sql操作
			//计算查询的起始行
			int startNo = (currentPage-1)*pageSize; 
			List<Employee> list = (List<Employee>)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对象）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/**
 * 员工的业务类
 * @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<Employee> list = empDao.queryCurrentData(pageBean.getCurrentPage(), pageBean.getPageSize());
		pageBean.setData(list);
		
		return pageBean;
	}
}

4）编写Servlet代码（接收用户输入）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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页面代码（显示分页效果）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<%@taglib uri="http://java.sun.com/jsp/jstl/core"  prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head> 
    <title>分页显示效果</title>  
  </head>
  
  <body>
    <table border="1" align="center" width="700px">
    	<tr>
    		<th>编号</th>
    		<th>姓名</th>
    		<th>性别</th>
    		<th>年龄</th>
    		<th>职位</th>
    		<th>电话</th>
    		<th>邮箱</th>
    	</tr>
    	<c:forEach items="${requestScope.pageBean.data}" var="emp">
    	<tr>
    		<td>${emp.id }</td>
    		<td>${emp.name }</td>
    		<td>${emp.gender }</td>
    		<td>${emp.age}</td>
    		<td>${emp.title }</td>
    		<td>${emp.phone }</td>
    		<td>${emp.email }</td>
    	</tr>
    	</c:forEach>
    	<tr>
    		<td colspan="7" align="center">
    			<%--
    				1）如果当前页是首页，则不显示“首页”和“上一页”的连接
    				2）如果当前页是末页，则不显示“末页”和“下一页”的连接
    			 --%>
    			 <c:choose>
    			 	<c:when test="${pageBean.currentPage==pageBean.firstPage}">
    			 		首页 
    			 		上一页
    			 	</c:when>
    			 	<c:otherwise>
    			 		<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.firstPage }&pageSize=${pageBean.pageSize}">首页</a> 
    					<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.prePage }&pageSize=${pageBean.pageSize}">上一页</a> 
    			 	</c:otherwise>
    			 </c:choose>
    		
    			<c:choose>
    				<c:when test="${pageBean.currentPage==pageBean.totalPage}">
    					下一页 
    					末页
    				</c:when>
    				<c:otherwise>
    						<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.nextPage}&pageSize=${pageBean.pageSize}">下一页</a> 
    						<a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.totalPage }&pageSize=${pageBean.pageSize}">末页</a> 
    				</c:otherwise>
    			</c:choose>
    			当前第${pageBean.currentPage }页/共${pageBean.totalPage }页，
    			 共${pageBean.totalCount }条 每页显示 <input type="text" name="pageSize" id="pageSize" size="2" value="${pageBean.pageSize }" οnblur="changePageSize()"/> 条
    		</td>
    	</tr>
    </table>
    
    <script type="text/javascript">
    	//改变每页显示记录数的方法
    	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;
    	}
    </script>
  </body>
</html>

2 条件查询

2.1 条件查询的核心

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//组装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()+"%'");
				}
			}

本作品采用知识共享署名 4.0 中国大陆许可协议进行许可，欢迎转载，但转载请注明来自御前提笔小书童，并保持转载后文章内容的完整。本人保留所有版权相关权利。

本文链接：https://royalscholar.cn/2017/02/24/Java分页查询&条件查询/

# JAVA

喜欢这篇文章？打赏一下作者吧

解决web.xml 报错The content of element type "web-app" must match
java获取当前操作系统名称以及各系统的本机网卡地址

评论

关注我
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×