通用分页（一）

通用分页

第一步：连接数据库（可别忘了导入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.SQLServerDriver
10 #url=jdbc:sqlserver://localhost:1423;DatabaseName=test
11 #user=sa
12 #pwd=sa
13 
14 
15 #sql2000
16 #driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
17 #url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB
18 #user=sa
19 #pwd=888888
20 
21 
22 #mysql5
23 driver=com.mysql.jdbc.Driver
24 url=jdbc:mysql://127.0.0.1:3306/my-data?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
25 user=root
26 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      * @return
62      */
63     public int getStartIndex() {
64         return (this.page - 1) * this.rows;
65     }
66 
67     @Override
68     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，否则返回false
11      * 
12      * @param s
13      * @return
14      */
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，否则返回false
25      * 
26      * @param s
27      * @return
28      */
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 <T>
 */
public class BaseDao<T> {

    /**
     * 
     * @param sql 查询不同的实体类，对应的sql不同，需要传递
     * @param clz 生产出不同的实体类对应的实例，然后装进list容器中返回
     * @param pageable  决定是否分页
     * @return
     * @throws SQLException
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     */
    public List<T> 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<T> 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<Book>{
18 /**
19  * 
20  * @param book  封装jsp传递过来的查询参数
21  * @param pageable  决定dao层list调用时是否分页
22  * @return
23  * @throws IllegalAccessException 
24  * @throws InstantiationException 
25  */
26 //    public List<Book> 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<Book> 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<Book> 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<Book> 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 block
73             e.printStackTrace();
74         }
75     }
76 }

实现结果