熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java開源技術 >> 正文

Struts1.2實現MySQL數據庫分頁

2013-11-23 20:23:45  來源: Java開源技術 

  我的平台是Eclipse   MyEclipse Tomcat MySql

  第一步創建數據庫
    這沒什麼難的用下面的腳本就OK了
    CREATEDATABASEpage;
    usepage;
    CREATETABLE `product` (
     `id` varchar() NOTNULL
     `sortid` varchar() NOTNULL
     `name` varchar() NOTNULL
     `price` doubleNOTNULL
     `saleprice` doubleNOTNULL
     `descript` text NOTNULL
     `contents` text NOTNULL
      `saledate` varchar() NOTNULL
     `salecount` int() defaultNULL
     `image` text
     PRIMARYKEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf;

  第二步創建一個項目
    創建一個項目項目名為strutsPage導入Struts Struts的包采用默認引用MySql的驅動要是沒有驅動的話請到這下載
    下面設置webxml和strutsconfigxml配置文件我覺得直接COPY我的就好了
    webxml文件裡的內容如下直接換上就OK了基本是默認的
    <?xml version= encoding=UTF?>
    <webapp xmlns= xmlns:xsi=instance version= xsi:schemaLocation=app__xsd>
     <servlet>
        <servletname>action</servletname>
        <servletclass>orgapachestrutsactionActionServlet</servletclass>
        <initparam>
          <paramname>config</paramname>
          <paramvalue>/WEBINF/strutsconfigxml</paramvalue>
        </initparam>
        <initparam>
          <paramname>debug</paramname>
          <paramvalue></paramvalue>
        </initparam>
        <initparam>
          <paramname>detail</paramname>
          <paramvalue></paramvalue>
        </initparam>
        <loadonstartup></loadonstartup>
     </servlet>
     <servletmapping>
        <servletname>action</servletname>
        <urlpattern>*do</urlpattern>
     </servletmapping>
     <welcomefilelist>
        <welcomefile>indexjsp</welcomefile>
     </welcomefilelist>
    </webapp>

  strutsconfigxml的內容如下
    <?xml version= encoding=UTF?>
    <!DOCTYPE strutsconfig PUBLIC //Apache Software Foundation//DTD Struts Configuration //EN config__dtd>

  <strutsconfig>
     <datasources />
     <formbeans />
     <globalexceptions />
     <globalforwards />
     <actionmappings >
       <action
          attribute=productShowForm
          input=/indexjsp
          name=productShowForm
          path=/productShow
          scope=request
          type=comyourcompanystrutsactionProductShowAction>
          <forward name=success path=/indexjsp />
        </action>
     </actionmappings>
     <messageresources parameter=comyourcompanystrutsApplicationResources />
    </strutsconfig>

  第三步創建包和數據庫連接
   
    在SRC下創建 dao dbtool vofactory四個包
    在dbtool包裡主要放訪問JDBC數據庫的連接類等下面提供我用的JavaBean類

  DBConnectionjava的內容如下
    package comyourcompanydbtool;

  import javaioInputStream;
    import javasqlConnection;
    import javasqlDriverManager;
    import javasqlSQLException;
    import javautilProperties;

  /**
     * 這是一個連接數據的單模式
     * @author 樹下無影
     *
     */
    public class DBConnection {
          private static DBConnection instance;

  private String driver;
          private String url;
          private String user;
          private String password;

  private DBConnection() throws Exception{

  InputStream in=getClass()getClassLoader()getResourceAsStream(
                   com/yourcompany/dbtool/databaseproperties);
           Properties prop=new Properties();
           propload(in);
           driver=propgetProperty(driver);
           url=propgetProperty(url);
           user=propgetProperty(user);
           password=propgetProperty(password);
           try{
               ClassforName(driver);
           }catch(Exception e)
           {
               Systemoutprintln(數據庫初始化出錯);
                 throw e;
           }
           Systemoutprintln(driver+ +url+ +user+ +password);
          }
          public static DBConnection getInstance(){
           try{
               if(instance==null){
                   instance=new DBConnection();
               }
               return instance;
           }catch(Exception e){
               Systemoutprintln(實例化單模子出錯);

  return null;
           }
          }

  public Connection getConnection()throws SQLException{
           Connection con;
           try{
               con=DriverManagergetConnection(url user password);

  }catch(SQLException e){
              Systemoutprintln(Connection連接出錯);
                throw e;
           }
           return con;
          }

  public void closeConnection(Connection con){

  if(con!=null){
               try{
                   conclose();
               }catch(SQLException e)
               {
                   Systemoutprintln(關閉Connection連接出錯);

  }

  }
          }
    }


    這裡用一個配置文件databaseproperties 裡面存放數據庫的URL DriverUsername和Password等修改成你本機的相應數據能打開數據庫就好
    databaseproperties內容如下
    driver=orggjtmmmysqlDriver
    url=jdbc:mysql://localhost:/page
    user=root
    password=

  下面是我用的數據庫增刪改查的Bean> DBbusinessjava

  package comyourcompanydbtool;

  import javasql*;

  /**
     * 這是一個連接數據庫具有增刪改查的Bean
     * @author 樹下無影
     *
     */
    public class DBbusiness {
    /*
     * 定義連接參數等
     */
    Connection conn = null;

  PreparedStatement psps = null;

  ResultSet rs = null;
    public DBbusiness (){

  }
    /*
     * 定義公用的Connection
     */
    public Connection getConn() {

  try {
        DBConnection db=DBConnectiongetInstance();
        Connection conx = dbgetConnection();
        return conx;
     } catch (Exception e) {
         Systemoutprintln(Connection連接出錯);

  }
     return null;

  }
    /*
     * 獲取數據(查詢)方法
     */
    public ResultSet getData(String sql) {
         try {

  conn = getConn();
        psps = connprepareStatement(sql);
        rs = pspsexecuteQuery();
     } catch (Exception e) {
         Systemoutprintln(查詢數據庫操作出錯);
     }
     return rs;

  }
        /*
         * 定義插入數據和更新的方法
         */
    public boolean insert(String sql) {
         try {
            conn = getConn();
            psps = connprepareStatement(sql);
            pspsexecuteUpdate();
            return true;
         } catch (Exception e) {
             Systemoutprintln(數據庫更新出錯);

  }
         return false;

  }
    /*
     * 定義創建數據庫和表的方法
     */
    public boolean create(String sql) {
         try {
            conn = getConn();
            psps = connprepareStatement(sql);
            pspsexecute();
            return true;
         } catch (Exception e) {

  }
         return false;

  }

  /*
     * 定義關閉連接的方法
     */
    public void allClose() {
         try {
           if (rs != null)
                rsclose();
            if (psps != null)
                pspsclose();
            if (conn != null)
            {
                DBConnection db=DBConnectiongetInstance();
                dbcloseConnection(conn);
            }
         } catch (Exception e) {
             Systemoutprintln(數據庫關閉操作出錯);

  }
    }

  }
   第四步創建實體類
   
    在vo包裡創建一個實體類這步COPY過去就是
    Productjava
    package comyourcompanyvo;

  public class Product {
    String id;
    String sortid;
    String name;
    String price;
    String saleprice;
    String descript;
    String contents;
    String saledate;
    String salecount;
    String image;
    public Product(){}
        public Product(String idString sortidString nameString price
             String salepriceString descriptString contents
             String saledateString salecountString image){
         thisid=id;
         thissortid=sortid;
         thisname=name;
         thisprice=price;
         thissaleprice=saleprice;
         thisdescript=descript;
         ntents=contents;
         thissaledate=saledate;
         thissalecount=salecount;
         thisimage=image;

  }
    public String getContents() {
         return contents;
    }

  public void setContents(String contents) {
         ntents = contents;
    }

  public String getDescript() {
         return descript;
    }

  public void setDescript(String descript) {
         thisdescript = descript;
    }

  public String getId() {
         return id;
    }

  public void setId(String id) {
         thisid = id;
    }

  public String getImage() {
         return image;
    }

  public void setImage(String image) {
         thisimage = image;
    }

  public String getName() {
         return name;
    }

  public void setName(String name) {
         thisname = name;
    }

  public String getPrice() {
         return price;
    }

  public void setPrice(String price) {
         thisprice = price;
    }

  public String getSalecount() {
         return salecount;
    }

  public void setSalecount(String salecount) {
         thissalecount = salecount;
    }

  public String getSaledate() {
         return saledate;
    }

  public void setSaledate(String saledate) {
         thissaledate = saledate;
    }

  public String getSaleprice() {
         return saleprice;
    }

  public void setSaleprice(String saleprice) {
         thissaleprice = saleprice;
    }

  public String getSortid() {
         return sortid;
    }

  public void setSortid(String sortid) {
         thissortid = sortid;
    }

  }

  第五步創建接口並創建相應的實現類

    PageDaojava接口裡有兩個方法第一個方法是讀取指定數據表的行數第二個方法是讀取數據表並把信息放入一個ArrayList返回看代碼
    package comyourcompanydao;

  import javautilArrayList;
    import javautilList;

  public interface PageDao {
        public int getCount(String counSql);
        public ArrayList getProduct(String sql);

  }
    創建接口好後當然要創建實現類
    如下PageDaoImpljava

  package comyourcompanydao;

  import javasqlResultSet;
    import javasqlSQLException;
    import javautilArrayList;

  import comyourcompanydbtoolDBbusiness;
    import comyourcompanyvoProduct;

  /**
     * 這是接口的實現類
     * @author 樹下無影
     *
     */

  public class PageDaoImpl implements PageDao {
        /*
         * 獲取數據行數
         * @see comyourcompanydaoPageDao#getCount(javalangString)
         */
     public int getCount(String counSql){
          int result=;
          DBbusiness db=new DBbusiness();
          ResultSet rs= dbgetData(counSql);
          try {
                 rsnext();
                 result=rsgetInt();
            /*while(rsnext()){
                result=rsgetInt();
             }*/
         } catch (SQLException e) {
            // TODO Autogenerated catch block
            Systemoutprintln(讀取數據總數失敗);
         }finally{
            dballClose();
         }
          return result;
     }
     /*
     * 讀取數據表
     */
     public ArrayList getProduct(String sql){
            ArrayList arrayList=new ArrayList();

  DBbusiness db=new DBbusiness();
            ResultSet rs=dbgetData(sql);
            try {

  while(rsnext()){

  String id=rsgetString();
               String sortid=rsgetString();
               String name=rsgetString();
               String price=rsgetString();
               String saleprice=rsgetString();
               String descript=rsgetString();
               String contents=rsgetString();
               String saledate=rsgetString();
               String salecount=rsgetString();
               String image=rsgetString();
               Product productForm=new Product( idsortid name price
                     saleprice descript contents
                     saledatesalecountimage);
               arrayListadd(productForm);
            }

  } catch (SQLException e) {
            // TODO Autogenerated catch block
            Systemoutprintln(數據庫讀取出錯);
        }finally{
            dballClose();
        }
        return arrayList;
         }

  }

  第六步創建映射的工廠類
   
    這個類沒什麼解釋放在factoyr的包裡
    PageDaoFactoryjava

  package comyourcompanyfactory;

  import comyourcompanydaoPageDao;
    import comyourcompanydaoPageDaoImpl;

  public class PageDaoFactory {
        public static PageDao getPageDaoIntanse(){
         return new PageDaoImpl();
        }
    }
第七步分頁處理類
   
    呵呵這麼多步驟了還沒進入正題下面就開始講和分頁相關的
    在dbtool包裡創建如下類
    PageBeanjava
    package comyourcompanydbtool;

  import comyourcompanyfactoryPageDaoFactory;

  public class PageBean {
    /**
     * 這是一個分頁的類因為MySQL數據庫檢索可以使用分頁的SQL指令
     * 所在在這裡主要是處理出這樣的指令
     * 並獲得相應的頁數信息
     *MySql語句如下:select * from test limit ;這句是從的信息條數
     *select * from test limit ;   這句是第十條以後的五條
     */
    int curr; //當前頁

  int count; //總頁數

  int size; //每頁顯示數據數

  int rows=; //數據的所有行數

  boolean last; // 是否是最後一頁
    /**
     * 構造器
     * @param counSql
     */
    public PageBean(String counSql) {
         if (thisrows == ) {//獲取所有的數據條數
            thisrows = PageDaoFactorygetPageDaoIntanse()getCount(counSql);
         }
         thiscurr=getCurr();
         thissize = ;//設定頁面顯示數據大小
         unt = (int) Mathceil((double) thisrows / thissize);//獲得頁數
         thislast=isLast();
    }
        public PageBean(String counSqlint size){
         if (thisrows == ) {//獲取所有的數據條數
            thisrows = PageDaoFactorygetPageDaoIntanse()getCount(counSql);
         }
         thiscurr=getCurr();
         thissize = size;//設定頁面顯示數據大小
         unt = (int) Mathceil((double) thisrows / thissize);
         thislast=isLast();
        }
        public PageBean(String counSqlint currint size){
         if (thisrows == ) {//獲取所有的數據條數
            thisrows = PageDaoFactorygetPageDaoIntanse()getCount(counSql);
         }
         thiscurr=curr;
         thissize = size;//設定頁面顯示數據大小
         unt = (int) Mathceil((double) thisrows / thissize);
         thislast=isLast();
        }
    /**
     * 頁面指令處理及返回相應的查詢SQL語句
     */
    public String pageDeal(String pageDo String sql) {
         String str = limit ;
         //首頁
         if (pageDoequals(first)) {
            setCurr();
            str += + getSize();
         }
         //尾頁
         if (pageDoequals(end)) {
            setCurr(getCount());
            str += + ((getCount() ) * getSize());
            str += + (getRows() (getCount() ) * getSize());
         }
         //下一頁
         if (pageDoequals(next)) {

  if(getCurr()<getCount()){
                str += + (getCurr() * getSize());
                str += + getSize();
                setCurr(getCurr() + );
            }else{
                setCurr(getCount());
                str += + ((getCount() ) * getSize());
                str += + (getRows() (getCount() ) * getSize());
            }

  }
        //上一頁
         if (pageDoequals(prv)) {
            setCurr(getCurr() );
            str += + (getCurr() * getSize() getSize());
            str += + getSize();

  }
         return sql + str;
    }


    public static void main(String[] args) {

  }

  //返回總頁數總頁最小也等於
    public int getCount() {
         return (count == ) ? : count;
    }

  //設置總頁數
    public void setCount(int count) {
         unt = count;
    }

  //返回當前頁當前頁最小也等於
    public int getCurr() {
         return (curr == ) ? : curr;
    }
        //設置當前頁
    public void setCurr(int curr) {
         thiscurr = curr;
    }

  public int getRows() {
         return rows;
    }

  public void setRows(int rows) {
         thisrows = rows;
    }

  public int getSize() {
         return size;
    }

  public void setSize(int size) {
         thissize = size;
    }
    /**
     * 如果是最後一頁的返回true
     * @return
     */
    public boolean isLast() {
         return (curr==count)?true:false;
    }
    public void setLast(boolean last) {
         thislast = last;
    }

  }

  這個類寫了很多的注釋不過還是要講解一下由於在Struts的Action裡用到第三個構造器那就先講這個吧構造器裡主要的功能是通過Factory映射的接口類調用讀取數據表的行數獲得表的所有行數然後和傳進來的頁面顯示信息數除一下就獲得頁數的總數了
    當前頁的定義要是第一次讀取當前頁當然是第一頁了要是點了下一頁當前頁就加一頁點上一頁當前頁就減一面嘿嘿我這裡主要由頁面傳當前頁進來再根據傳進來的動作進行處理當前頁所以下一頁這樣的動作除了要傳一個動作外還要傳當時的當前頁
    Action 裡通過調用pageDeal()這方法就就可以獲取相應的分頁處理了當然還要加上select * from table這樣的語句才能實現
    好了看下一步Action裡是怎樣處理的

  第八步Action的處理

    在strutsaction的包裡創建如下類
    package comyourcompanystrutsaction;

  import javautilArrayList;

  import javaxservlethttpHttpServletRequest;
    import javaxservlethttpHttpServletResponse;
    import orgapachestrutsactionAction;
    import orgapachestrutsactionActionForm;
    import orgapachestrutsactionActionForward;
    import orgapachestrutsactionActionMapping;

  import comyourcompanydbtoolPageBean;
    import comyourcompanyfactoryPageDaoFactory;
    import comyourcompanyvoProduct;

  public class ProductShowAction extends Action {

  public ActionForward execute(ActionMapping mapping ActionForm form
               HttpServletRequest request HttpServletResponse response) {

  ArrayList aList = new ArrayList();

  /*
        * 定義頁面傳過來的動作如點下一頁 並因為這動作而決定處理
        */
       String pageDo = requestgetParameter(pageDo);

  /*
            * 定義獲取頁面傳過來的當前頁getCurr
            */
           int getCurr;
           String curr_page = requestgetParameter(curr_page);
           if (curr_page == null || curr_pageequals()) {
              getCurr = ;
           } else {
              getCurr = IntegerparseInt(requestgetParameter(curr_page));
              Systemoutprintln(getCurr);
           }
           /*
            * 實例化PageBean對象
            * PageBean有幾個構造器不過都要傳送一句獲取數據庫行數的SQL語句
            * getCurr是傳送一個當前頁給PageBean的構造器
            * 是定義每頁顯示幾行數據
            */
           PageBean pb = new PageBean(select count(*) from product getCurr
                  );
           // 定義查詢數據庫的SQL語句格式如下
           String sql;
           sql = pbpageDeal(pageDo select * from product );
           // 定義ArrayList獲取數據庫所查詢得到的數據

  aList = PageDaoFactorygetPageDaoIntanse()getProduct(sql);
           // 把值傳給客戶端
           requestsetAttribute(pageInfo pb);
           requestsetAttribute(data aList);

  return mappingfindForward(success);
           }
    }

  這個Action裡也寫了好多的注釋相信一看就明白
    步驟主要是
    定義兩個參數獲取前台傳進來的動作和當前頁
    實例化分頁的處理類PageBeanjava在它的構造器裡傳進查詢數據庫行數的SQL語句當前頁要在表裡顯示的規格
    獲取處理好的分頁SQL語句主要是調用PageBean裡的pageDeal方法給它傳進的是頁面傳進來的動作和查詢數據表的SQL語句
     用處理好的分布SQL語句去查詢數據
     把值傳給前台主要返回PageBean的對象和所查詢得的數據ArrayList

  第九步前台處理頁面
   
    由於後台傳回來的是一個ArrayList的數據表所以把它讀出來就是
    還返回一個PageBean的對象這裡包含的數據是當前頁總頁是否為最後一頁
    所以要是想弄不同的上一頁下一頁這樣的導航條的話修改傳回的參數再在Jsp頁面裡做相應的處理就OK了
    看我的Jsp頁面indexjsp

  <%@ page language=java pageEncoding=utf%>
    <%@ taglib uri=bean prefix=bean %>
    <%@ taglib uri=html prefix=html %>
    <%@ taglib uri=logic prefix=logic %>
    <%@ taglib uri=tiles prefix=tiles %>
    <% if(sessiongetAttribute(getData)==null)
                 {//這裡只是一個跳轉沒什麼
                 sessionsetAttribute(getDataok);
                 responsesetHeader(refresh
                         ;url=/strutsPage/productShowdo?pageDo=first&curr_page=);
                 }
     %>
    <html>
             <head>
              <title>JSP for ProductShowForm form</title>
             </head>
             <body><font color=#ff>
                這裡僅演示分頁操作部分已經做到獲取ID了相信直接調用就可以弄修改和刪除</font>
              <table width=% border=>
                  <tr>
                     <th>
                         商品名稱
                     </th>
                     <th>
                         價格
                     </th>
                     <th>
                         商品描述
                     </th>
                     <th>
                         商品詳細信息
                     </th>
                     <th >
                         上架日期
                     </th>
                     <th colspan= align=center>
                         操作
                     </th>
                  </tr>

  <logic:present name=data scope=request>
                     <logic:iterate id=show name=data
                         type=comyourcompanyvoProduct>
                         <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=contents />
                            </td>
                            %><td >
                                <bean:write name=show property=saledate />
                            </td>
                            <td>
                             <html:link action=/productShowdo?pageDo=updata
                    paramId=up_page paramName=show paramProperty=id>
                           修改</html:link>
                            </td>
                            <td>
                             <html:link action=/productShowdo?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=/productShowdo?pageDo=first
                    paramId=curr_page paramName=pageInfo paramProperty=curr>
                       首頁</html:link>
                  <logic:notEqual name=pageInfo property=curr value=>
                         <html:link action=/productShowdo?pageDo=prv
                         paramId=curr_page paramName=pageInfo paramProperty=curr>
                         上一頁</html:link>
                      </logic:notEqual>
                      <logic:equal name=pageInfo property=last value=false>
                         <html:link action=/productShowdo?pageDo=next
                         paramId=curr_page paramName=pageInfo paramProperty=curr>
                         下一頁</html:link>
                     </logic:equal>
                    <html:link action=/productShowdo?pageDo=end
                    paramId=curr_page paramName=pageInfo paramProperty=curr>
                    尾頁</html:link>

  </logic:present>

  </body>
    </html>

  總結
    這個分頁看起來很簡單做起來也很簡單只是對SQL語句做了一下過濾而已這個分頁功能很簡單但重在拋磚引玉!


From:http://tw.wingwit.com/Article/program/Java/ky/201311/28426.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.