struts2--操作数据库（数据分页）

很多时候，数据库中存在大量数据，一个jsp页面肯定无法全部显示出来，这时，数据分页就显示其强大了。

设计思路：数据分页总是围绕着三个量（总的记录数，当前页数，每页多少条记录），使用了两个Acton，PageAction和AllGoods，分别实现分页功能和获取当前页面信息（保持到list集合，以便在jsp页面输出）。使用了一个DAO类（PageDao）用于数据层的操作（如：从数据库中获取当前页面的记录，查询数据库中的总记录数）。接下来就是数据源的使用了：配置context.xml文件（WebRoot/META-INF下），自定义工具类连接数据库。

1、实体类bean：Goods.java

package com.bean;

public class Goods {
	private Integer id;
	private String goodsName;
	private Double price;
	private Integer num;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getGoodsName() {
		return goodsName;
	}

	public void setGoodsName(String goodsName) {
		this.goodsName = goodsName;
	}

	public Double getPrice() {
		return price;
	}

	public void setPrice(Double price) {
		this.price = price;
	}

	public Integer getNum() {
		return num;
	}

	public void setNum(Integer num) {
		this.num = num;
	}

}

2、Action

PageAction.java:

package com.action;

import com.dao.PageDao;
import com.opensymphony.xwork2.ActionSupport;

public class PageAction extends ActionSupport{
	private int pageNum;  //当前页
	private int totalPage; //总页数
	private int pageSize; //每页显示记录数
 
	public String execute(){
		PageDao pageDao = new PageDao();
		int goodAmount = pageDao.getGoodsAmount();
		pageSize = 4;
		totalPage = (goodAmount%pageSize == 0) ? (goodAmount/pageSize) : (goodAmount/pageSize + 1);
		if(pageNum <= 0){
			pageNum = 1; //如果pageName小于1将其赋值为1
		}
		if(pageNum > totalPage){
			pageNum = totalPage;
		}
		return SUCCESS;
	}
	
	public int getPageNum() {
		return pageNum;
	}

	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

}

AllGoods.java

package com.action;

import java.util.List;

import com.bean.Goods;
import com.dao.PageDao;
import com.opensymphony.xwork2.ActionSupport;
import com.tools.DbPool;

public class AllGoods extends ActionSupport{
	private int pageNum;
	private int totalPage;
	private int pageSize;
	private List<Goods> list;

	public String execute(){
		PageDao pageDao = new PageDao();
		list = pageDao.getGoods(pageNum, pageSize);
		return SUCCESS;
	}
	
	public int getPageNum() {
		return pageNum;
	}

	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public List<Goods> getList() {
		return list;
	}

	public void setList(List<Goods> list) {
		this.list = list;
	}

}

其实这两个Action可以合并，可以减少很多代码，这里为了练习就分开写了。

3、DAO

PageDao.java:

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.Goods;
import com.tools.DbPool;

public class PageDao {
 private DbPool pool = new DbPool();  
  public List<Goods> getGoods(int pageNum,int pageSize) { //获取当前页的商品
	List<Goods> list = new ArrayList<Goods>();
	if(pool.getConnection() == null){  //如果连接不可用，则重新创建连接
		pool.getConn();
	}
	
	String sql = "select * from goods order by id limit ?,?";
	PreparedStatement ps = null;
	try {
		ps = pool.getConnection().prepareStatement(sql);
		ps.setInt(1, (pageNum-1)*pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Goods good = new Goods();
			good.setId(rs.getInt(1));
			good.setGoodsName(rs.getString(2));
			good.setPrice(rs.getDouble(3));
			good.setNum(rs.getInt(4));
			list.add(good);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
}
  
  public int getGoodsAmount() {  //获取总记录数
	int all = 0;
	if(pool.getConnection() == null){
		pool.getConn();
	}
	
	String sql = "select count(*) from goods";
	PreparedStatement ps = null;
	try {
		ps = pool.getConnection().prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			all = rs.getInt(1);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	return all;
}
  
}

4、struts.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
    "-//Apache Software Foundation//DTD Struts Configuration 2.1.7//EN"
    "http://struts.apache.org/dtds/struts-2.1.7.dtd">
    
<struts>
   <package name="default" namespace = "/" extends = "struts-default">
     <action name = "goods" class = "com.action.AllGoods">
       <result>/page.jsp</result>
     </action>
     
     <action name="page" class = "com.action.PageAction">
       <result type = "redirectAction">
         <param name="actionName">goods</param>
         <param name="pageNum">${pageNum}</param>
         <param name="totalPage">${totalPage}</param>
         <param name="pageSize">${pageSize}</param>
       </result>
     </action>
   </package>
</struts>    
    

5、page.jsp页面：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>My JSP 'page.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

</head>

<body>
	<div align="center">
		<table border="1">
			<tr>
				<td>商品编号</td>
				<td>商品名称</td>
				<td>商品价格</td>
				<td>商品数目</td>
			</tr>

			<s:iterator value="list" var="good" status = "ss">
				<tr>
					<td><s:property value="#good.id" /></td>
					<td><s:property value="#good.goodsName" /></td>
					<td><s:property value="#good.price" /></td>
					<td><s:property value="#good.num" /></td>
				</tr>
			</s:iterator>
		</table>

		<ul>
			<li><s:property value="pageNum" />/<s:property value="totalPage" />

				<s:url action="page" id="firstPage">
					<s:param name="pageNum">1</s:param>
				</s:url> <s:a href="%{firstPage}">首页</s:a>
				
				<s:url action = "page" id = "prePage">
				    <s:param name = "pageNum">
				      <s:property value = "pageNum-1"/>
				    </s:param>
				</s:url><s:a href = "%{prePage}">上一页</s:a>
				
				<s:url action = "page" id = "nextPage">
				  <s:param name = "pageNum">
				      <s:property value = "pageNum + 1"/>
				  </s:param>
				</s:url><s:a href = "%{nextPage}">下一页</s:a>
				
				<s:url action = "page" id ="lastPage">
				  <s:param name = "pageNum">
				     <s:property value = "totalPage"/>
				  </s:param>
				</s:url><s:a href = "%{lastPage}">末页</s:a>
		    </li>
		</ul>
	</div>
</body>
</html>

6、context.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<Context path = "/dbtom" docBase = "dbtom" reloadable = "true" crossContext = "true">
  <Resource
   name = "jdbc/dbtom"
   type = "javax.sql.DataSource"
   username = "root"
   password = "root"
   driverClassName = "com.mysql.jdbc.Driver"
   url = "jdbc:mysql://localhost:3306/user"
  />
</Context>

7、DBPool.java工具类：（这里需要注意！写完程序找了很久的bug出现在这里 private Connection connection属性要设置为static的，避免每次都通过数据源连接数据库。当时没写，结果点击几次上下页，就陷入了无限制的请求状态！！）

package com.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DbPool {
  private static Connection connection;  //这里必须设置为静态属性！！！
  public void getConn() {
	  try {
			Context initcContext = new InitialContext(); //初始化context对象
			Context envContext = (Context) initcContext.lookup("java:comp/env");	
			DataSource dSource = (DataSource) envContext.lookup("jdbc/dbtom"); //获取数据源
			
			connection = dSource.getConnection();
			System.out.println("获取连接成功");
	} catch (Exception e) {
		System.out.println("获取连接失败");
	}
}
  
  public ResultSet query(String sql) {
	ResultSet rs = null;
	if(connection == null){
		getConn();//如果为空，则获取连接
	}
	
	PreparedStatement ps;
	try {
		ps = connection.prepareStatement(sql);
		rs = ps.executeQuery();
	} catch (Exception e) {
		System.out.println("query is failed!");
	}
    return rs;	
}
  
  public Connection getConnection() {
	if(connection == null){
		getConn();
	}
	return connection;
}
  
}

实现效果：

首页显示

末页显示