通用分页

通用分页查询模板类：

public final class DBTemplate {

        

         private DBTemplate() {

         }

通有分页         

         public static interface IORMConvert<T> {

                   List<T> convert(ResultSet rs) throws SQLException;

         }

        

         public static <T> List<T> query(String sql,

                            Object[] params,

                            PageBean pageBean,

                            IORMConvert<T> convert) {

                  

                   List<T> datas = new ArrayList<>();

                  

                   Connection con = null;

                   PreparedStatement ps = null;

                   ResultSet rs = null;

                  

                   //不需要分页

                   if (pageBean == null || !pageBean.isPagination()) {

                            try {

                                     con = DBUtil.getConection();

                                     ps = con.prepareStatement(sql);

                                    

                                     setParam(params, ps);

                                    

                                     rs = ps.executeQuery();

                                    

                                     datas = convert.convert(rs);

                                    

                                     return datas;

                            } catch(Exception e) {

                                     e.printStackTrace();

                            } finally {

                                     DBUtil.closeDB(rs, ps, con);

                            }

                           

                   } else {

                            //1. 查询总记录数

                            //2. 查询当前页数据

                           

                            //1. 生成统计总记录数的SQL， 查询总记录数

                            try {

                                     String countSql = "select count(*) from (" + sql + ") tmp";

                                     con = DBUtil.getConection();

                                    

                                     ps = con.prepareStatement(countSql);

                                    

                                     setParam(params, ps);

                                    

                                     rs = ps.executeQuery();

                                    

                                     while(rs.next()) {

                                               pageBean.setTotal(rs.getInt(1));

                                     }

                                    

                                     /*

                                      * 如果统计的总记录数为0，则表示没有符合条件的记录，直接返回一个空结果集即可。

                                      */

                                     if(pageBean.getTotal() == 0) {

                                               return datas;

                                     }

                                    

                            } catch (Exception e) {

                                     e.printStackTrace();

                            } finally {

                                     if(pageBean.getTotal() == 0) {

                                               DBUtil.closeDB(rs, ps, con);

                                     }

                                     DBUtil.closeDB(rs, ps);

                            }

                           

                            //查询当前页数据

                            try {

                                     String pagingSql = sql

                                                        + " limit "

                                                        + pageBean.getStartRow()

                                                        + ", "

                                                        + pageBean.getRows();

                                     ps = con.prepareStatement(pagingSql);

                                    

                                     setParam(params, ps);

                                    

                                     rs = ps.executeQuery();

                                    

                                     datas = convert.convert(rs);

                                    

                            } catch (SQLException e) {

                                     e.printStackTrace();

                            } finally {

                                     DBUtil.closeDB(rs, ps, con);

                            }

                           

                   }

                  

                   return datas;

         }

         private static void setParam(Object[] params, PreparedStatement ps) throws SQLException {

                   if (params != null) {

                            int i = 1;

                            for (Object param : params) {

                                     ps.setObject(i, param);

                                     i++;

                            }

                   }

         }

}

 使用示例：

public class StudentDao2 {

        

         public List<Student> getStudents(String sname, PageBean pageBean) {

                  

                   String sql = "select * from t_student where sname like ?";

                  

                   return DaoTemplate.query(sql, new Object[] {sname}, pageBean, new IORMConvert<Student>() {

                           

                            @Override

                            public List<Student> convert(ResultSet rs) throws SQLException {

                                     List<Student> stus = new ArrayList<>();

                                     while(rs.next()) {

                                               Student stu = new Student();

                                               stu.setSid(rs.getInt("sid"));

                                               stu.setSname(rs.getString("sname"));

                                               stu.setAge(rs.getInt("age"));

                                               stu.setRemark(rs.getString("remark"));

                                               stus.add(stu);

                                     }

                                    

                                     return stus;

                            }

                   });

                  

         }

        

         public static void main(String[] args) {

                   StudentDao2 dao = new StudentDao2();

                   PageBean pageBean = new PageBean();

                   pageBean.setPage(3);

                   List<Student> students = dao.getStudents("张%", pageBean);

                   students.forEach(s -> System.out.println(s));

         }

}