java操作資料庫：分頁查詢，java分頁

java操作資料庫：分頁查詢，java分頁

直接上。。。。

還是用之前的goods表，增加了一些資料

1、實體類Goods

// 封裝資料public class Goods {    private int gid;    private String gname;    private String gprice;    private String gdate;    public int getGid() {        return gid;    }    public void setGid(int gid) {        this.gid = gid;    }    public String getGname() {        return gname;    }    public void setGname(String gname) {        this.gname = gname;    }    public String getGprice() {        return gprice;    }    public void setGprice(String gprice) {        this.gprice = gprice;    }    public String getGdate() {        return gdate;    }    public void setGdate(String gdate) {        this.gdate = gdate;    }    public Goods(int gid, String gname, String gprice, String gdate) {        super();        this.gid = gid;        this.gname = gname;        this.gprice = gprice;        this.gdate = gdate;    }    public Goods() {        super();            }}

2、DBHelper類

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;/** * 擷取資料庫操作的連線物件 * 關閉資料庫操作的各種資源 * @author 晏先政 * */public class DBHelper {    private static final String className = "com.mysql.jdbc.Driver";    private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true";    private static final String uname = "root";    private static final String upass = "";        /**     * 擷取資料庫連接對象的方法     */    public static Connection getConn(){        Connection conn = null;        try{            Class.forName(className);            conn = DriverManager.getConnection(url,uname, upass);        } catch(Exception e){            e.printStackTrace();        }                return conn;    }        /**     * 關閉資料庫連接對象     */    public static void closeConn(Connection conn){        try{            if(conn!=null){                conn.close();            }        } catch(Exception e){            e.printStackTrace();        }    }        /**     * 關閉資料庫操作對象     */    public static void closeStmt(Statement stmt){        try{            if(stmt!=null){                stmt.close();            }        } catch(Exception e){            e.printStackTrace();        }    }    /**     * 關閉資料庫操作對象     */    public static void closePstmt(PreparedStatement pstmt){        try{            if(pstmt!=null){                pstmt.close();            }        } catch(Exception e){            e.printStackTrace();        }    }        /**     * 關閉資料庫操作對象     */    public static void closeRs(ResultSet rs){        try{            if(rs!=null){                rs.close();            }        } catch(Exception e){            e.printStackTrace();        }    }}

3、實作類別GoodsDao：操作資料庫進行查詢

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;public class GoodsDao {    private Connection conn = null;    private PreparedStatement pstmt = null;    private ResultSet rs = null;    // 查詢當前頁的資料    public List<Goods> getListByCurPage(int curPage){        List<Goods> list = new ArrayList<Goods>();        try{            conn = DBHelper.getConn();            int num = (curPage-1)*5;            String sql = "select * from goods limit "+num+",5";                        pstmt = conn.prepareStatement(sql);                        rs = pstmt.executeQuery();                        while(rs.next()){                Goods ba = new Goods(rs.getInt("gid"),rs.getString("gname"),rs.getString("gprice"),rs.getString("gdate"));                ba.setGid(rs.getInt("gid"));                list.add(ba);            }                    } catch(Exception e){            e.printStackTrace();        } finally{            DBHelper.closeRs(rs);            DBHelper.closePstmt(pstmt);            DBHelper.closeConn(conn);        }                        return list;    }        //  查詢所有記錄的總條數    public int getCount(){        int i = 0;        try{            conn = DBHelper.getConn();            String sql = "select count(*) cnt from goods";                        pstmt = conn.prepareStatement(sql);                        rs = pstmt.executeQuery();                        if(rs.next()){                i = rs.getInt("cnt");            }                    } catch(Exception e){            e.printStackTrace();        } finally{            DBHelper.closeRs(rs);            DBHelper.closePstmt(pstmt);            DBHelper.closeConn(conn);        }        return i;    }}

4、展示類GoodsShow

import java.util.List;import java.util.Scanner;public class GoodsShow {        public static void main(String[] args) {        GoodsShow bs = new GoodsShow();        bs.show();    }        private Scanner input = new Scanner(System.in);    private GoodsDao dao = new GoodsDao();    int curPage = 1;    List<Goods> list = null;    public void show(){        int rowCount = dao.getCount();                int pageCount = rowCount%5==0?rowCount/5:rowCount/5+1;        list = dao.getListByCurPage(curPage);                print(list);                System.out.println("首頁【F】上一頁【P】下一頁【N】尾頁【L】請選擇：");        char choose = input.next().toUpperCase().charAt(0);                switch(choose){            case 'F':                curPage = 1;                break;            case 'P':                curPage = curPage -1;                if(curPage<1){                    curPage = 1;                                    }                break;            case 'N':                curPage = curPage +1;                if(curPage>pageCount){                    curPage = pageCount;                }                break;            case 'L':                curPage = pageCount;                break;        }                show();    }        public void print(List<Goods> list){        System.out.println("編號\t商品\t價格\t時間");        for(int i=0;i<list.size();i++){            System.out.println(list.get(i).getGid()+"\t"+list.get(i).getGname()+"\t"+list.get(i).getGprice()+"\t"+list.get(i).getGdate());        }    }}

then。。。。