您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Struts 1.2 實作 MySQL 資料庫分頁完整教學

編輯：Java 相關知識

環境與準備

IDE：Eclipse 3.2 + MyEclipse 5.5
伺服器：Tomcat 5.5
資料庫：MySQL 5.0
專案名稱：strutsPage
所需 JAR：Struts 1.2 預設庫、MySQL Connector/J（下載連結）

步驟一：建立資料庫與資料表

CREATE DATABASE page;
USE page;
CREATE TABLE `product` (
  `id` varchar(11) NOT NULL,
  `sortid` varchar(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` double NOT NULL,
  `saleprice` double NOT NULL,
  `descript` text NOT NULL,
  `contents` text NOT NULL,
  `saledate` varchar(255) NOT NULL,
  `salecount` int(11) DEFAULT NULL,
  `image` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

步驟二：設定核心組態檔

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="https://java.sun.com/xml/ns/j2ee"
         xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
         version="2.4"
         xsi:schemaLocation="https://java.sun.com/xml/ns/j2ee https://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
  <servlet>
    <servlet-name>action</servlet-name>
    <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
    <init-param>
      <param-name>config</param-name>
      <param-value>/WEB-INF/struts-config.xml</param-value>
    </init-param>
    <init-param>
      <param-name>debug</param-name>      <param-value>3</param-value>
    </init-param>
    <init-param>
      <param-name>detail</param-name>
      <param-value>3</param-value>
    </init-param>
    <load-on-startup>0</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>action</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

struts-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN" "https://struts.apache.org/dtds/struts-config_1_2.dtd">
<struts-config>
  <data-sources />
  <form-beans />
  <global-exceptions />
  <global-forwards />
  <action-mappings>
    <action
      attribute="productShowForm"
      input="/index.jsp"
      name="productShowForm"
      path="/productShow"
      scope="request"
      type="com.yourcompany.struts.action.ProductShowAction">
      <forward name="success" path="/index.jsp" />
    </action>
  </action-mappings>
  <message-resources parameter="com.yourcompany.struts.ApplicationResources" />
</struts-config>

步驟三：資料庫連線工具類

採用單例模式管理連線，配置資訊外部化至 database.properties。

database.properties

driver=org.gjt.mm.mysql.Driver
url=jdbc:mysql://localhost:3306/page
user=root
password=1234

DBConnection.java（單例連線管理）

package com.yourcompany.dbtool;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {
    private static DBConnection instance;
    private String driver, url, user, password;

    private DBConnection() throws Exception {
        InputStream in = getClass().getClassLoader().getResourceAsStream("com/yourcompany/dbtool/database.properties");
        Properties prop = new Properties();
        prop.load(in);
        driver = prop.getProperty("driver");
        url = prop.getProperty("url");
        user = prop.getProperty("user");
        password = prop.getProperty("password");
        Class.forName(driver);
    }

    public static DBConnection getInstance() {
        try {
            if (instance == null) instance = new DBConnection();
            return instance;
        } catch (Exception e) { return null; }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public void closeConnection(Connection con) {
        if (con != null) try { con.close(); } catch (SQLException ignored) {}
    }
}

DBbusiness.java（CRUD 封裝）

package com.yourcompany.dbtool;
import java.sql.*;

public class DBbusiness {
    private Connection conn;
    private PreparedStatement psps;
    private ResultSet rs;

    public Connection getConn() {
        try { return DBConnection.getInstance().getConnection(); }
        catch (Exception e) { return null; }
    }

    public ResultSet getData(String sql) {
        try {
            conn = getConn();
            psps = conn.prepareStatement(sql);
            rs = psps.executeQuery();
        } catch (Exception e) { System.out.println("查詢錯誤"); }
        return rs;
    }

    public boolean insert(String sql) {
        try { conn = getConn(); psps = conn.prepareStatement(sql); psps.executeUpdate(); return true; }
        catch (Exception e) { return false; }
    }

    public void allClose() {
        try {
            if (rs != null) rs.close();
            if (psps != null) psps.close();
            if (conn != null) DBConnection.getInstance().closeConnection(conn);
        } catch (Exception ignored) {}
    }
}

步驟四：實體類別

package com.yourcompany.vo;
public class Product {
    private String id, sortid, name, price, saleprice, descript, contents, saledate, salecount, image;
    // 建構子、Getter/Setter 略...
}

步驟五：DAO 介面與實作

PageDao.java

package com.yourcompany.dao;
import java.util.ArrayList;
public interface PageDao {
    int getCount(String countSql);
    ArrayList getProduct(String sql);
}

PageDaoImpl.java

package com.yourcompany.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.yourcompany.dbtool.DBbusiness;
import com.yourcompany.vo.Product;

public class PageDaoImpl implements PageDao {
    public int getCount(String countSql) {
        int result = 0;
        DBbusiness db = new DBbusiness();
        ResultSet rs = db.getData(countSql);
        try {
            if (rs.next()) result = rs.getInt(1);
        } catch (SQLException e) { System.out.println("讀取總筆數失敗"); }
        finally { db.allClose(); }
        return result;
    }

    public ArrayList getProduct(String sql) {
        ArrayList list = new ArrayList();
        DBbusiness db = new DBbusiness();
        ResultSet rs = db.getData(sql);
        try {
            while (rs.next()) {
                Product p = new Product(
                    rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4),
                    rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
                    rs.getString(9), rs.getString(10)
                );
                list.add(p);
            }
        } catch (SQLException e) { System.out.println("資料讀取錯誤"); }
        finally { db.allClose(); }
        return list;
    }
}

步驟六：工廠類別

package com.yourcompany.factory;
import com.yourcompany.dao.PageDao;
import com.yourcompany.dao.PageDaoImpl;

public class PageDaoFactory {
    public static PageDao getPageDaoInstance() {
        return new PageDaoImpl();
    }
}

步驟七：分頁處理核心類別

利用 MySQL LIMIT offset, size 語法實作分頁邏輯，封裝頁碼計算、SQL 組裝。

package com.yourcompany.dbtool;
import com.yourcompany.factory.PageDaoFactory;

public class PageBean {
    private int curr = 1, count, size = 5, rows = 0;
    private boolean last;

    public PageBean(String countSql) { this(countSql, 1, 5); }
    public PageBean(String countSql, int size) { this(countSql, 1, size); }
    public PageBean(String countSql, int curr, int size) {
        if (rows == 0) rows = PageDaoFactory.getPageDaoInstance().getCount(countSql);
        this.curr = curr > 0 ? curr : 1;
        this.size = size;
        this.count = (int) Math.ceil((double) rows / size);
        this.last = (this.curr == this.count);
    }

    /** 依動作組裝分頁 SQL */
    public String pageDeal(String action, String baseSql) {
        String limit = " LIMIT ";
        switch (action) {
            case "first": curr = 1; limit += size; break;
            case "end":   curr = count; limit += (count-1)*size + "," + (rows - (count-1)*size); break;
            case "next":  if (curr < count) { limit += curr*size + "," + size; curr++; }
                          else { limit += (count-1)*size + "," + (rows - (count-1)*size); } break;
            case "prv":   if (curr > 1) { curr--; limit += (curr-1)*size + "," + size; } break;
        }
        return baseSql + limit;
    }
    // Getter/Setter 略...
}

步驟八：Action 處理邏輯

package com.yourcompany.struts.action;
import java.util.ArrayList;
import javax.servlet.http.*;
import org.apache.struts.action.*;
import com.yourcompany.dbtool.PageBean;
import com.yourcompany.factory.PageDaoFactory;
import com.yourcompany.vo.Product;

public class ProductShowAction extends Action {
    public ActionForward execute(ActionMapping mapping, ActionForm form,
                                 HttpServletRequest request, HttpServletResponse response) {
        String pageDo = request.getParameter("pageDo");
        int curr = 1;
        String cp = request.getParameter("curr_page");
        if (cp != null && !cp.isEmpty()) curr = Integer.parseInt(cp);

        PageBean pb = new PageBean("SELECT COUNT(*) FROM product", curr, 2);
        String sql = pb.pageDeal(pageDo, "SELECT * FROM product");
        ArrayList data = PageDaoFactory.getPageDaoInstance().getProduct(sql);

        request.setAttribute("pageInfo", pb);
        request.setAttribute("data", data);
        return mapping.findForward("success");
    }
}

步驟九：前端 JSP 頁面

<%@ page language="java" pageEncoding="utf-8"%>
<%@ taglib uri="https://struts.apache.org/tags-bean" prefix="bean" %>
<%@ taglib uri="https://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="https://struts.apache.org/tags-logic" prefix="logic" %>
<%@ taglib uri="https://struts.apache.org/tags-tiles" prefix="tiles" %>
<% if (session.getAttribute("getData") == null) {
       session.setAttribute("getData", "ok");
       response.setHeader("refresh", "1;url=/strutsPage/productShow.do?pageDo=first&curr_page=1");
   } %>
<html><body>
<table width="80%" border="1">
  <tr><th>商品名稱</th><th>價格</th><th>描述</th><th>詳細資訊</th><th>上架日期</th><th colspan="2">操作</th></tr>
  <logic:present name="data" scope="request">
    <logic:iterate id="show" name="data" type="com.yourcompany.vo.Product">
      <tr>
        <td><bean:write name="show" property="name"/></td>
        <td><bean:write name="show" property="saleprice"/></td>
        <td><bean:write name="show" property="descript"/></td>
        <td><bean:write name="show" property="saledate"/></td>
        <td><html:link action="/productShow.do?pageDo=updata" paramId="up_page" paramName="show" paramProperty="id">修改</html:link></td>
        <td><html:link action="/productShow.do?pageDo=dele" paramId="dele_page" paramName="show" paramProperty="id">刪除</html:link></td>
      </tr>
    </logic:iterate>
  </logic:present>
</table>
<logic:present name="pageInfo">
  第 <bean:write name="pageInfo" property="curr"/> 頁 / 共 <bean:write name="pageInfo" property="count"/> 頁.
  <html:link action="/productShow.do?pageDo=first" paramId="curr_page" paramName="pageInfo" paramProperty="curr">首頁</html:link>
  <logic:notEqual name="pageInfo" property="curr" value="1">
    <html:link action="/productShow.do?pageDo=prv" paramId="curr_page" paramName="pageInfo" paramProperty="curr">上一頁</html:link>
  </logic:notEqual>
  <logic:equal name="pageInfo" property="last" value="false">
    <html:link action="/productShow.do?pageDo=next" paramId="curr_page" paramName="pageInfo" paramProperty="curr">下一頁</html:link>
  </logic:equal>
  <html:link action="/productShow.do?pageDo=end" paramId="curr_page" paramName="pageInfo" paramProperty="curr">尾頁</html:link>
</logic:present>
</body></html>

總結

本教學示範以 Struts 1.2 + MySQL 實作分頁核心邏輯：利用 LIMIT 語法搭配 PageBean 封裝頁碼計算與 SQL 組裝，Action 負責協調參數、呼叫 DAO、轉發結果，JSP 呈現資料與導航列。雖架構屬經典 MVC，但分頁核心思想（總筆數查詢、動態 LIMIT、頁碼狀態管理）在現代框架（Spring MVC、MyBatis Plus 等）中依然適用，值得深入理解。