struts2 derby paging 分页

来源：互联网发布：枪神纪刷枪软件大全编辑：程序博客网时间：2024/06/06 08:49

1.package com.tian.web.action;

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

import org.apache.derby.client.am.Connection;
import org.apache.derby.client.am.ResultSet;
import org.apache.derby.client.am.Statement;

import com.googlecode.jsonplugin.annotations.JSON;
import com.opensymphony.xwork2.Action;
import com.tian.web.model.InfoIssue;
import com.tian.web.utils.PageBean;

public class InfoIssueAction extends PageBean implements Action {
List<InfoIssue> rows = new ArrayList<InfoIssue>();

@Override
public String execute() {
    Connection conn = null;
    try {
      Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
      conn = (Connection) DriverManager.getConnection(
          "jdbc:derby://localhost:1527/paging;create=true", "user", "user");

      String strSql = "select count(*) from app.issue ";
      Statement stat = (Statement) conn.createStatement();

      ResultSet rs = (ResultSet) stat.executeQuery(strSql);
      while (rs.next()) {
        //get total count
        total = rs.getInt(1);
      }
      //count total pages
      totalpage = (total + pageSize - 1) / pageSize;

      String strPaing = "SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, app.issue.* FROM app.issue) AS tmp WHERE rownum > "
          + (pageNow * pageSize - pageSize)
          + " AND rownum <= "
          + (pageNow * pageSize) + " ";

      rs = (ResultSet) stat.executeQuery(strPaing);
      while (rs.next()) {
        InfoIssue ii = new InfoIssue();
        ii.setIssueid(rs.getInt("issueid"));
        ii.setCaption(rs.getString("caption"));
        ii.setMaketop(rs.getString("maketop"));
        ii.setIssue(rs.getString("issued"));
        rows.add(ii);
      }
    } catch (Exception e) {
      return ERROR;
    } finally {
      try {
        if (conn != null) {
          conn.close();
        }
        System.out.print("error");
      } catch (SQLException e) {

      }
    }
    return SUCCESS;
}

public static void main(String[] args) throws Exception {
    InfoIssueAction iia = new InfoIssueAction();
    iia.execute();
}

@JSON(name = "rows")
public List<InfoIssue> getRows() {
    return rows;
}

public void setRows(List<InfoIssue> rows) {
    this.rows = rows;
}

}

2.package com.tian.web.utils;

public class PageBean {
protected int pageSize = 10;
protected int pageNow = 1;
protected int total;
protected int totalpage;
public int getPageSize() {
    return pageSize;
}
public void setPageSize(int pageSize) {
    this.pageSize = pageSize;
}
public int getPageNow() {
    return pageNow;
}
public void setPageNow(int pageNow) {
    this.pageNow = pageNow;
}
public int getTotal() {
    return total;
}
public void setTotal(int total) {
    this.total = total;
}
public int getTotalpage() {
    return totalpage;
}
public void setTotalpage(int totalpage) {
    this.totalpage = totalpage;
}

}

3.<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="s" uri="/struts-tags"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css"
href="<%request.getContextPath();%>/css/flexigrid.css"></link>
<script type="text/javascript"
src="<%request.getContextPath();%>/js/jquery-1.3.2.js"></script>
<script type="text/javascript"
src="<%request.getContextPath();%>/js/page/flexigrid.js"></script>
<title>Paging Jquery Flexigrid</title>
</head>
<body>

total size:
<s:property value="total" />
<br />
page now:
<s:property value="pageNow" />
<br />
<s:iterator value="rows">
<s:property value="issueid" />
<s:property value="caption" />
<s:property value="maketop" />
<s:property value="issue" />
<br />
</s:iterator>

<s:url id="url_pre2" value="infoIssue.action">
<s:param name="pageNow" value="pageNow-2"></s:param>
</s:url>
<s:url id="url_next2" value="infoIssue.action">
<s:param name="pageNow" value="pageNow+2"></s:param>
</s:url>
<s:url id="url_pre" value="infoIssue.action">
<s:param name="pageNow" value="pageNow-1"></s:param>
</s:url>
<s:url id="url_next" value="infoIssue.action">
<s:param name="pageNow" value="pageNow+1"></s:param>
</s:url>

<s:set name="pageNow" value="pageNow"></s:set>
<s:set name="totalpage" value="totalpage"></s:set>
<s:if test="#pageNow > 1 ">
<s:a href="%{url_pre}">Pre</s:a>
</s:if>
<s:else>
Pre
</s:else>

<s:if test="#pageNow > 2 ">
<s:a href="%{url_pre2}">
<s:property value="pageNow-2" />
</s:a>
  
</s:if>

<s:if test="#pageNow > 1 ">
<s:a href="%{url_pre}">
<s:property value="pageNow-1" />
</s:a>
  
</s:if>

<s:property value="pageNow" />
  

<s:if test="#pageNow < #totalpage">

<s:a href="%{url_next}">
<s:property value="pageNow+1" />
</s:a>
  
</s:if>

<s:if test="#pageNow < #totalpage-1">
<s:a href="%{url_next2}">
<s:property value="pageNow+2" />
</s:a>
</s:if>

<s:if test="#pageNow < #totalpage">
<s:a href="%{url_next}">Next</s:a>
</s:if>
<s:else>
Next
</s:else>

</body>
</html>

4.package com.tian.web.model;

public class InfoIssue implements java.io.Serializable {

/**
   *
   */
private static final long serialVersionUID = 1147469609838504419L;

private int issueid;
private String caption;
private String maketop;
private String issue;

public int getIssueid() {
    return issueid;
}

public void setIssueid(int issueid) {
    this.issueid = issueid;
}

public String getCaption() {
    return caption;
}

public void setCaption(String caption) {
    this.caption = caption;
}

public String getMaketop() {
    return maketop;
}

public void setMaketop(String maketop) {
    this.maketop = maketop;
}

public String getIssue() {
    return issue;
}

public void setIssue(String issue) {
    this.issue = issue;
}

}

5.sql

CREATE TABLE APP.GLOSSARY

(TERM_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY

    (start WITH 1, INCREMENT BY 1),

      TERM VARCHAR(80) NOT NULL,

      DEFINITION VARCHAR(500) NOT NULL);

INSERT INTO APP.GLOSSARY VALUES (DEFAULT,'Velocity','Rapidity or speed of motion; specifically, the distance traveled per unit time.');

INSERT INTO APP.GLOSSARY VALUES (DEFAULT,'Value','Monetary or material worth.');

INSERT INTO APP.GLOSSARY VALUES (DEFAULT,'Speed','The magnitude of a velocity.');

INSERT INTO APP.GLOSSARY VALUES (DEFAULT,'Agile','Characterized by quickness, lightness, and ease of movement; nimble.');

CREATE TABLE APP.ISSUE

(issueid INT NOT NULL GENERATED ALWAYS AS IDENTITY

    (start WITH 1, INCREMENT BY 1),

      caption VARCHAR(80) NOT NULL,

      maketop VARCHAR(500) NOT NULL,

      issued VARCHAR(500) NOT NULL);

INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionA','makeTopA','issuedA');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionB','makeTopB','issuedB');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionC','makeTopC','issuedC');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionD','makeTopD','issuedD');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionE','makeTopE','issuedE');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionA','makeTopA','issuedA');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionB','makeTopB','issuedB');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionC','makeTopC','issuedC');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionD','makeTopD','issuedD');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionE','makeTopE','issuedE');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionA','makeTopA','issuedA');
INSERT INTO APP.ISSUE VALUES (DEFAULT,'captionB','makeTopB','issuedB');