通用分页之详解】-阿里云开发者社区

1.通用分页的优势

1.简化代码

：Java中通用的分页工具类可以集成到项目中，避免每个分页查询都需要手动编写分页代码，可以减少重复代码的编写量。

2.可维护性

：使用通用的分页工具类可以使代码更加可维护，当需要改变分页参数时，只需要修改分页工具类，而不需要修改每个分页查询代码。

3.可移植性

：通用分页工具类可以应用于不同的数据库和ORM框架，具有较强的可移植性。

4更高的效率

：通用的分页工具类可以通过封装分页参数，减少分页查询的重复代码量，提高代码执行效率。

5.良好的扩展性

：使用通用的分页工具类可以支持更多的分页参数设置，例如默认分页大小、最大分页大小、排序规则等。

2.反射完成通用查询功能

1.实体类

package com.niyin.entity;
public class Book {
  private int bid;
  private String bname;
  private float price;
  @Override
  public String toString() {
    return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]";
  }
  public int getBid() {
    return bid;
  }
  public void setBid(int bid) {
    this.bid = bid;
  }
  public String getBname() {
    return bname;
  }
  public void setBname(String bname) {
    this.bname = bname;
  }
  public float getPrice() {
    return price;
  }
  public void setPrice(float price) {
    this.price = price;
  }
}

1.定义分页工具类

package com.niyin.util;
/**
 * 分页工具类
 *
 */
public class PageBean {
  private int page = 1;// 页码
  private int rows = 10;// 页大小
  private int total = 0;// 总记录数
  private boolean pagination = true;// 是否分页
  public PageBean() {
    super();
  }
  public int getPage() {
    return page;
  }
  public void setPage(int page) {
    this.page = page;
  }
  public int getRows() {
    return rows;
  }
  public void setRows(int rows) {
    this.rows = rows;
  }
  public int getTotal() {
    return total;
  }
  public void setTotal(int total) {
    this.total = total;
  }
  public void setTotal(String total) {
    this.total = Integer.parseInt(total);
  }
  public boolean isPagination() {
    return pagination;
  }
  public void setPagination(boolean pagination) {
    this.pagination = pagination;
  }
  /**
   * 获得起始记录的下标
   * 
   * @return
   */
  public int getStartIndex() {
    return (this.page - 1) * this.rows;
  }
  @Override
  public String toString() {
    return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
  }
}

3. 连接数据库帮助类

package com.niyin.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
 * 提供了一组获得或关闭数据库对象的方法
 * 
 */
public class DBAccess {
  private static String driver;
  private static String url;
  private static String user;
  private static String password;
  static {// 静态块执行一次，加载 驱动一次
    try {
      InputStream is = DBAccess.class
          .getResourceAsStream("config.properties");
      Properties properties = new Properties();
      properties.load(is);
      driver = properties.getProperty("driver");
      url = properties.getProperty("url");
      user = properties.getProperty("user");
      password = properties.getProperty("pwd");
      Class.forName(driver);
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    }
  }
  /**
   * 获得数据连接对象
   * 
   * @return
   */
  public static Connection getConnection() {
    try {
      Connection conn = DriverManager.getConnection(url, user, password);
      return conn;
    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    }
  }
  public static void close(ResultSet rs) {
    if (null != rs) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
      }
    }
  }
  public static void close(Statement stmt) {
    if (null != stmt) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
      }
    }
  }
  public static void close(Connection conn) {
    if (null != conn) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException(e);
      }
    }
  }
  public static void close(Connection conn, Statement stmt, ResultSet rs) {
    close(rs);
    close(stmt);
    close(conn);
  }
  public static boolean isOracle() {
    return "oracle.jdbc.driver.OracleDriver".equals(driver);
  }
  public static boolean isSQLServer() {
    return "com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driver);
  }
  public static boolean isMysql() {
    return "com.mysql.cj.jdbc.Driver".equals(driver);
  }
  public static void main(String[] args) {
    Connection conn = DBAccess.getConnection();
    System.out.println(conn);
    DBAccess.close(conn);
    System.out.println("isOracle：" + isOracle());
    System.out.println("isSQLServer：" + isSQLServer());
    System.out.println("isMysql：" + isMysql());
    System.out.println("数据库连接(关闭)成功");
  }
}

4.定义bookdao

package Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.niyin.entity.Book;
import com.niyin.util.DBAccess;
import com.niyin.util.PageBean;
import com.niyin.util.StringUtils;
public class BookDao {
  public List<Book> list(Book book,PageBean pageBean) throws Exception {
  List<Book>list=new ArrayList<Book>();
    Connection conn = DBAccess.getConnection();
    String sql="select * from t_mvc_book where 1=1 ";
    String bname = book.getBname();
    if (StringUtils.isNotBlank(bname)) {
      sql +=" and bname like '%"+bname+"%'";
    }
    PreparedStatement pst = conn.prepareStatement(sql);
    ResultSet rs = pst.executeQuery();
    while(rs.next()) {
      Book b=new Book();
      b.setBid(rs.getInt("bid"));
      b.setBname(rs.getString("bname"));
      b.setPrice(rs.getFloat("price"));
      list.add(b);
    }
    return list;
  }
  public static void main(String[] args) throws Exception {
    BookDao bookdao=new BookDao();
    Book book=new Book();
    book.setBname("圣墟");
    PageBean pageBean=new PageBean();
    List<Book> list = bookdao.list(book, pageBean);
    for (Book book2 : list) {
      System.out.println(book2);
    }
  }
}

5定义basedao

package com.niyin.util;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class BaseDao<T> {
  public List<T> executeQuery(String sql, PageBean pageBean, Class clz) throws Exception {
    List<T> lst = new ArrayList<T>();
    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    if (pageBean != null && pageBean.isPagination()) {
      String countSQL = getcountSQL(sql);
      conn = DBAccess.getConnection();
      pst = conn.prepareStatement(countSQL);
      rs = pst.executeQuery();
      if (rs.next()) {
        pageBean.setTotal(rs.getObject("n").toString());
      }
      String pageSQl = getpageSQl(sql, pageBean);
      conn = DBAccess.getConnection();
      pst = conn.prepareStatement(pageSQl);
      rs = pst.executeQuery();
    } else {
      conn = DBAccess.getConnection();
      pst = conn.prepareStatement(sql);
      rs = pst.executeQuery();
    }
    while (rs.next()) {
      T t = (T) clz.newInstance();
      Field[] fields = clz.getDeclaredFields();
      for (Field f : fields) {
        f.setAccessible(true);
        // System.out.println(f.getName());
        // rs.getObject(f.getName());
        f.set(t, rs.getObject(f.getName()));
      }
      lst.add(t);
      // Book b=new Book();
      // b.setBid(rs.getInt("bid"));
      // b.setBname(rs.getString("bname"));
      // b.setPrice(rs.getFloat("price"));
      // list.add(b);
    }
    return lst;
  }
  private String getpageSQl(String sql, PageBean pageBean) {
    return sql +=" limit " + pageBean.getStartIndex() + "," + pageBean.getRows();
  }
  private String getcountSQL(String sql) {
    return "select count(1) as n from ("+sql+") t";
  }
}

运行结果

数据库的资源我会放在我的资源，可以自行下载。

3通用分页查询

结合上面的代码和例子，只要修改bookdao即可

代码如下

1.Bookdao

package Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.niyin.entity.Book;
import com.niyin.util.BaseDao;
import com.niyin.util.DBAccess;
import com.niyin.util.PageBean;
import com.niyin.util.StringUtils;
public class BookDao2 extends BaseDao<Book> {
//  public List<Book> list(Book book,PageBean pageBean) throws Exception {
//  List<Book>list=new ArrayList<Book>();
//    Connection conn = DBAccess.getConnection();
//    String sql="select * from t_mvc_book where 1=1";
//    String bname = book.getBname();
//    if (StringUtils.isNotBlank(bname)) {
//      sql +="and bname like '%"+bname+"%'";
//    }
//    PreparedStatement pst = conn.prepareStatement(sql);
//    ResultSet rs = pst.executeQuery();
//    while(rs.next()) {
//      Book b=new Book();
//      b.setBid(rs.getInt("bid"));
//      b.setBname(rs.getString("bname"));
//      b.setPrice(rs.getFloat("price"));
//      list.add(b);
//    }
//    return list;
//    
//  }
  public List<Book> list(Book book,PageBean pageBean) throws Exception {
    List<Book>list=new ArrayList<Book>();
      Connection conn = DBAccess.getConnection();
      String sql="select * from t_mvc_book where 1=1 ";
      String bname = book.getBname();
      if (StringUtils.isNotBlank(bname)) {
        sql +=" and bname like '%"+bname+"%'";
      }
      return super.executeQuery(sql, pageBean, Book.class);
    }
  public static void main(String[] args) throws Exception {
    BookDao2 bookdao=new BookDao2();
    Book book=new Book();
    PageBean pageBean=new PageBean();
    book.setBname("圣墟");
    List<Book> list = bookdao.list(book, pageBean);
    for (Book book2 : list) {
      System.out.println(book2);
    }
  }
}

结果如下

通过分页工具类，只取了前面10条数据。

通用分页之详解】