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

冷不防 2024-02-18 14:26 40阅读 0赞

很多时候，数据库中存在大量数据，一个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">
    
    
    </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;
    }
      
    }

实现效果：

![Center][]首页显示

![Center 1][]末页显示

![Center 2][]

![Center 3][]

[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/36b04192c9124834854ca9b22d66f94d.png
[Center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1dce8a384ae34bcd9d0be40186d46cf5.png
[Center 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ae488843dd1a4008b7a3084ee47d0001.png
[Center 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/77cfb2271da0466b94e5438f83101d7a.png