通用分页（一）-白红宇

通用分页（一）

阅读量：4931 次

发布时间：2019-06-11

本文共 14929 字，大约阅读时间需要 49 分钟。

通用分页

第一步：连接数据库（可别忘了导入jar包）

数据库工具类（可连接Oracle sqlserver mysql 的不同版本）

config.properties

1 #oracle9i 2 #driver=oracle.jdbc.driver.OracleDriver 3 #url=jdbc:oracle:thin:@localhost:1521:ora9 4 #user=test 5 #pwd=test 6  7  8 #sql2005 9 #driver=com.microsoft.sqlserver.jdbc.SQLServerDriver10 #url=jdbc:sqlserver://localhost:1423;DatabaseName=test11 #user=sa12 #pwd=sa13 14 15 #sql200016 #driver=com.microsoft.jdbc.sqlserver.SQLServerDriver17 #url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB18 #user=sa19 #pwd=88888820 21 22 #mysql523 driver=com.mysql.jdbc.Driver24 url=jdbc:mysql://127.0.0.1:3306/my-data?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT25 user=root26 pwd=123

DBAccess连接工具类

1 package com.GeneralPaging.util;  2   3 import java.io.InputStream;  4 import java.sql.Connection;  5 import java.sql.DriverManager;  6 import java.sql.ResultSet;  7 import java.sql.SQLException;  8 import java.sql.Statement;  9 import java.util.Properties; 10  11 /** 12  * 提供了一组获得或关闭数据库对象的方法 13  *  14  */ 15 public class DBAccess { 16     private static String driver; 17     private static String url; 18     private static String user; 19     private static String password; 20  21     static {
   // 静态块执行一次，加载 驱动一次 22         try { 23             InputStream is = DBAccess.class 24                     .getResourceAsStream("config.properties"); 25  26             Properties properties = new Properties(); 27             properties.load(is); 28  29             driver = properties.getProperty("driver"); 30             url = properties.getProperty("url"); 31             user = properties.getProperty("user"); 32             password = properties.getProperty("pwd"); 33  34             Class.forName(driver); 35         } catch (Exception e) { 36             e.printStackTrace(); 37             throw new RuntimeException(e); 38         } 39     } 40  41     /** 42      * 获得数据连接对象 43      *  44      * @return 45      */ 46     public static Connection getConnection() { 47         try { 48             Connection conn = DriverManager.getConnection(url, user, password); 49             return conn; 50         } catch (SQLException e) { 51             e.printStackTrace(); 52             throw new RuntimeException(e); 53         } 54     } 55  56     public static void close(ResultSet rs) { 57         if (null != rs) { 58             try { 59                 rs.close(); 60             } catch (SQLException e) { 61                 e.printStackTrace(); 62                 throw new RuntimeException(e); 63             } 64         } 65     } 66  67     public static void close(Statement stmt) { 68         if (null != stmt) { 69             try { 70                 stmt.close(); 71             } catch (SQLException e) { 72                 e.printStackTrace(); 73                 throw new RuntimeException(e); 74             } 75         } 76     } 77  78     public static void close(Connection conn) { 79         if (null != conn) { 80             try { 81                 conn.close(); 82             } catch (SQLException e) { 83                 e.printStackTrace(); 84                 throw new RuntimeException(e); 85             } 86         } 87     } 88  89     public static void close(Connection conn, Statement stmt, ResultSet rs) { 90         close(rs); 91         close(stmt); 92         close(conn); 93     } 94  95     public static boolean isOracle() { 96         return "oracle.jdbc.driver.OracleDriver".equals(driver); 97     } 98  99     public static boolean isSQLServer() {100         return "com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driver);101     }102     103     public static boolean isMysql() {104         return "com.mysql.jdbc.Driver".equals(driver);105     }106 107     public static void main(String[] args) {108         Connection conn = DBAccess.getConnection();109         DBAccess.close(conn);110         System.out.println("isOracle：" + isOracle());111         System.out.println("isSQLServer：" + isSQLServer());112         System.out.println("isMysql：" + isMysql());113         System.out.println("数据库连接(关闭)成功");114     }115 }

检查是否连接成功

数据库连接成功够进行下一步

封装一个分页工具类

page = 页码

rows = 页大小

total = 总记录数

pagination = 是否分页（true或者false）

PageBean分页工具类

1 package com.GeneralPaging.util; 2  3  4 /** 5  * 分页工具类 6  * 7  */ 8 public class PageBean { 9 10     private int page = 1;// 页码11 12     private int rows = 10;// 页大小13 14     private int total = 0;// 总记录数15 16     private boolean pagination = true;// 是否分页17 18     public PageBean() {19         super();20     }21 22     public int getPage() {23         return page;24     }25 26     public void setPage(int page) {27         this.page = page;28     }29 30     public int getRows() {31         return rows;32     }33 34     public void setRows(int rows) {35         this.rows = rows;36     }37 38     public int getTotal() {39         return total;40     }41 42     public void setTotal(int total) {43         this.total = total;44     }45 46     public void setTotal(String total) {47         this.total = Integer.parseInt(total);48     }49 50     public boolean isPagination() {51         return pagination;52     }53 54     public void setPagination(boolean pagination) {55         this.pagination = pagination;56     }57 58     /**59      * 获得起始记录的下标60      * 61      * @return62      */63     public int getStartIndex() {64         return (this.page - 1) * this.rows;65     }66 67     @Override68     public String toString() {69         return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";70     }71 72 }

封装一个实体类

所查询的数据类

1 package com.GeneralPaging.entity; 2  3 public class Book { 4     private int bid; 5     private String bname; 6     private float price; 7  8     @Override 9     public String toString() {10         return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]";11     }12 13     public int getBid() {14         return bid;15     }16 17     public void setBid(int bid) {18         this.bid = bid;19     }20 21     public String getBname() {22         return bname;23     }24 25     public void setBname(String bname) {26         this.bname = bname;27     }28 29     public float getPrice() {30         return price;31     }32 33     public void setPrice(float price) {34         this.price = price;35     }36 37     public Book(int bid, String bname, float price) {38         super();39         this.bid = bid;40         this.bname = bname;41         this.price = price;42     }43 44     public Book() {45         super();46     }47 48 }

判断一个值是否为空值

StringUtils

1 package com.GeneralPaging.util; 2  3  4 public class StringUtils { 5     // 私有的构造方法，保护此类不能在外部实例化 6     private StringUtils() { 7     } 8  9     /**10      * 如果字符串等于null或去空格后等于""，则返回true，否则返回false11      * 12      * @param s13      * @return14      */15     public static boolean isBlank(String s) {16         boolean b = false;17         if (null == s || s.trim().equals("")) {18             b = true;19         }20         return b;21     }22     23     /**24      * 如果字符串不等于null或去空格后不等于""，则返回true，否则返回false25      * 26      * @param s27      * @return28      */29     public static boolean isNotBlank(String s) {30         return !isBlank(s);31     }32 33 }

中文乱码处理

EncodingFiter

package com.GeneralPaging.util;import java.io.IOException;import java.util.Iterator;import java.util.Map;import java.util.Set;import javax.servlet.Filter;import javax.servlet.FilterChain;import javax.servlet.FilterConfig;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;/** * 中文乱码处理 *  */public class EncodingFiter implements Filter {    private String encoding = "UTF-8";// 默认字符集    public EncodingFiter() {        super();    }    public void destroy() {    }    public void doFilter(ServletRequest request, ServletResponse response,            FilterChain chain) throws IOException, ServletException {        HttpServletRequest req = (HttpServletRequest) request;        HttpServletResponse res = (HttpServletResponse) response;        // 中文处理必须放到 chain.doFilter(request, response)方法前面        res.setContentType("text/html;charset=" + this.encoding);        if (req.getMethod().equalsIgnoreCase("post")) {            req.setCharacterEncoding(this.encoding);        } else {            Map map = req.getParameterMap();// 保存所有参数名=参数值(数组)的Map集合            Set set = map.keySet();// 取出所有参数名            Iterator it = set.iterator();            while (it.hasNext()) {                String name = (String) it.next();                String[] values = (String[]) map.get(name);// 取出参数值[注：参数值为一个数组]                for (int i = 0; i < values.length; i++) {                    values[i] = new String(values[i].getBytes("ISO-8859-1"),                            this.encoding);                }            }        }        chain.doFilter(request, response);    }    public void init(FilterConfig filterConfig) throws ServletException {        String s = filterConfig.getInitParameter("encoding");// 读取web.xml文件中配置的字符集        if (null != s && !s.trim().equals("")) {            this.encoding = s.trim();        }    }}

重点：泛型类调用分页类 sql语句的拼接模糊查询分页计算符合条件的总记录数

BaseDao<T>

package com.GeneralPaging.util;import java.awt.print.Pageable;import java.lang.reflect.Field;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.GeneralPaging.entity.Book;import com.mysql.jdbc.Connection;/** * T代表你要对哪个实体类对应表进行查询 * @author Administrator * * @param 
     
       */public class BaseDao
      
        {    /**     *      * @param sql 查询不同的实体类，对应的sql不同，需要传递     * @param clz 生产出不同的实体类对应的实例，然后装进list容器中返回     * @param pageable  决定是否分页     * @return     * @throws SQLException     * @throws IllegalAccessException      * @throws InstantiationException      */    public List
       
         executeQuery(String sql, Class clz,PageBean pageBean)throws SQLException, InstantiationException, IllegalAccessException{        //建立连接    Connection con= (Connection) DBAccess.getConnection();        PreparedStatement pst=null;        ResultSet rs=null;                if (pageBean!=null && pageBean.isPagination()) {            //需要分页            //算符合条件的总记录数            String countSql=getCountSql(sql);            pst = con.prepareStatement(countSql);            rs = pst.executeQuery();            if (rs.next()) {                pageBean.setTotal(rs.getLong(1)+"");                            }            //查询出符合条件的结果集            String pagSql=getPageSql(sql,pageBean);            pst = con.prepareStatement(pagSql);            rs = pst.executeQuery();                    }else {            pst = con.prepareStatement(sql);            rs = pst.executeQuery();        }                     List
        
          list=new ArrayList<>();        T t;        while(rs.next()) {            /*             * 1.实例化一个book对象             * 2.取book的所有属性，然后给其赋值             * 2.1获取所有属性对象             * 2.2给属性对象赋值             * 3.赋完值的book对象装进list容器中             */            //list.add(new Book(rs.getInt("bid"), rs.getString("bname"), rs.getFloat("price")));                                    t=(T) clz.newInstance();            Field[] fields = clz.getDeclaredFields();            for (Field field : fields) {                field.setAccessible(true);                field.set(t, rs.getObject(field.getName()));            }            list.add(t);        }        //关流        DBAccess.close(con, pst, rs);        return list;            }    /**     * 利用原生sql拼接出符合条件的结果集的sql     * @param sql     * @param pageBean     * @return     */    private String getPageSql(String sql, PageBean pageBean) {        // TODO Auto-generated method stub        return sql+" LIMIT "+pageBean.getStartIndex()+","+pageBean.getRows();    }    /**     * 获取符合条件的总记录数的sql     * @param sql     * @return     */    private String getCountSql(String sql) {        // TODO Auto-generated method stub        return "SELECT COUNT(1) FROM("+sql+")t;";    }    }

调用分页效果的dao方法，继承泛型BaseDao

在这个dao方法中只需要修改sql语句和你所要根据什么列来模糊查询的数据就可以任意实现分页效果

1 package com.GeneralPaging.dao; 2  3 import java.awt.print.Pageable; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import com.GeneralPaging.entity.Book;11 import com.GeneralPaging.util.BaseDao;12 import com.GeneralPaging.util.DBAccess;13 import com.GeneralPaging.util.PageBean;14 import com.GeneralPaging.util.StringUtils;15 import com.mysql.jdbc.Connection;16 17 public class BookDao extends BaseDao
     
      {18 /**19  * 20  * @param book  封装jsp传递过来的查询参数21  * @param pageable  决定dao层list调用时是否分页22  * @return23  * @throws IllegalAccessException 24  * @throws InstantiationException 25  */26 //    public List
      
        list(Book book,Pageable pageable)throws SQLException{27 //    String sql="select * from t_mvc_book where true";28 //    String bname=book.getBname();29 //    if (StringUtils.isNotBlank(bname)) {30 //        sql+=" and bname like '%"+bname+"%'";31 //        32 //    }33 //        //建立连接34 //    Connection con= (Connection) DBAccess.getConnection();35 //        PreparedStatement pst = con.prepareStatement(sql);36 //        ResultSet rs = pst.executeQuery();37 //        List
       
         list=new ArrayList<>();38 //        while(rs.next()) {39 //            list.add(new Book(rs.getInt("bid"), rs.getString("bname"), rs.getFloat("price")));            40 //        }41 //        //关流42 //        DBAccess.close(con, pst, rs);43 //        return list;44 //        45 //    }46     47     48     public List
        
          list(Book book,PageBean pageBean)throws SQLException, InstantiationException, IllegalAccessException{49         String sql="select * from t_mvc_book where true";50         String bname=book.getBname();51         if (StringUtils.isNotBlank(bname)) {52             sql+=" and bname like '%"+bname+"%'";53             54         }55         return super.executeQuery(sql, Book.class, pageBean);56     }57     58     public static void main(String[] args) throws InstantiationException, IllegalAccessException {59         BookDao bookdao=new BookDao();60         Book book =new Book();61         book.setBname("圣墟");62         PageBean pageBean=new PageBean();63         //pageBean.setPagination(false);64         //pageBean.setPage(2);65         66         try {67             List
         
           list=bookdao.list(book, pageBean);68             for (Book b : list) {69                 System.out.println(b);70             }71         } catch (SQLException e) {72             // TODO Auto-generated catch block73             e.printStackTrace();74         }75     }76 }