通用分页(一)

1.通用分页

思路：我们知道的通用分页就是就是把我们的分页方法封装起来，就是利用泛型类去传递类对象，然后利用反射获取对象属性给对象属性赋值。

1. PageBean
2. 分页三要素
3. page 页码 视图层传递过来
4. rows 页大小 视图层传递过来
5. total 总记录数 后台查出来
6. pagination 是否分页 视图层传递过来

一、首先我们封装了一个分页的工具类pageBean，来控制是否分页，页数，每页数量

package com.liuxia.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 + "]";
 }
 }

还有我们的实体类

package com.liuxia.entity;
public class Book {
private int bid;
 private String bname;
 private float price;
 public Book() {

 }
 public Book(int bid,String bname, float price) {
  this.bid=bid;
  this.bname = bname;
  this.price = 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;
 }
}

2、接下来我先利用之前学过的知识完成一个普通的查询方法
如下案例：

package com.liuxia.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.liuxia.entity.Book;
import com.liuxia.util.DBAccess;
import com.liuxia.util.PageBean;
import com.liuxia.util.StringUtils;
public class BookDao extends BaseDao<Book> {
/**
  * 
  * @param book 封装这jsp传递过来的查询参数
  * @param pageBean 决定dao层的list调用时是否分页
  * @return
  * @throws SQLException
  * @throws IllegalAccessException 
  * @throws InstantiationException 
  */



public List<Book> list (Book book,PageBean pageBean ) throws SQLException{
  String sql= "select * from t_mvc_book where true ";
  String bname=book.getBname();
  if(StringUtils.isNotBlank(bname)) {
   sql += " and bname like '%"+bname+"%'";
  }
       Connection con= DBAccess.getConnection();

   PreparedStatement ps=con.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      List<Book> list=new ArrayList<>();
    while(rs.next()) {
     list.add(new Book(rs.getInt("bid"), rs.getString("bname"),rs.getFloat("price")));
       }
  DBAccess.close(con, ps, rs);

  return list;

 }


 public static void main(String[] args) throws InstantiationException, IllegalAccessException {
  BookDao bookDao=new BookDao();
  Book book=new Book();
   PageBean pageBean=new PageBean();
    /*pageBean.setPage(2);*/
  try {
   List<Book> list = bookDao.list(book, pageBean);
      for (Book b : list) {
    System.out.println(b);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

运行结果：

我们看到我以前学过的知识也可以实现查询方法，我们不觉得这种方法很繁琐吗，所以今天的重头戏来了 我会将原有的查询方法进行反射优化，转变成一个可以被所有实体类dao层所继承的通用查询方法这样就简洁多了，话不多说了，走代码。

目的：针对所有实体类，进行通用查询

package com.liuxia.dao;
import java.lang.reflect.Field;
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.liuxia.entity.Book;
import com.liuxia.util.DBAccess;
import com.liuxia.util.PageBean;
import com.liuxia.util.StringUtils;

/**
 * T代表你要对那个实体类对应表进行分页查询
 * @author 2018120102
 *
 * @param <T>
 */

public class BaseDao<T> {
	/**
	 * 
	 * @param sql 查询不同的实体类，那么对应的sql不同，所以需要传递
	 * @param clz 生产出不同的实体类对应的实列，然后装进list容器中返回
	 * @param book
	 * @param pageBean 决定是否分页
	 * @return
	 * @throws SQLException
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */

public List<T> executeQuery (String sql,Class clz,PageBean pageBean ) throws SQLException, InstantiationException, IllegalAccessException{
           Connection con= DBAccess.getConnection();
	    PreparedStatement ps=con.prepareStatement(sql);
		    ResultSet rs = ps.executeQuery();
		    List<Book> list=new ArrayList<>();
		  while(rs.next()) {
			  list.add(new Book(rs.getInt("bid"), rs.getString("bname"),rs.getFloat("price")));
		  	  }
	
		return list;
		
	}

  while(rs.next()) {
			  /**
			   * 1.实列化一个book对象
			   * 2.取book的所有属性，然后给其赋值
			   * 2.1获取所有属性赋值
			   * 2.2给属性对象赋值
			   * 3.赋完值的book对象装进list容器中
			   */

               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);
		  }
		  	return list;
		
	}

}

package com.liuxia.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.liuxia.entity.Book;
import com.liuxia.util.DBAccess;
import com.liuxia.util.PageBean;
import com.liuxia.util.StringUtils;



public class BookDao extends BaseDao<Book> {
 /**
  * 
  * @param book 封装这jsp传递过来的查询参数
  * @param pageBean 决定dao层的list调用时是否分页
  * @return
  * @throws SQLException
  * @throws IllegalAccessException 
  * @throws InstantiationException 
  */
public List<Book> list (Book book,PageBean pageBean ) throws SQLException, InstantiationException, IllegalAccessException{
  String sql= "select * from t_mvc_book where true ";
  String bname=book.getBname();
  if(StringUtils.isNotBlank(bname)) {
   sql += " and bname like '%"+bname+"%'";
  }
  return super.executeQuery(sql, Book.class, pageBean);
 }
 //我们用main方法来测试一下
  public static void main(String[] args) throws InstantiationException, IllegalAccessException {
  BookDao bookDao=new BookDao();
  Book book=new Book();
  /*book.setBname("十里桃花");*/
  try {
   List<Book> list = bookDao.list(book, pageBean);
    for (Book b : list) {
    System.out.println(b);
   }
   } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 }

运行结果：

同样我们实现了我们的查询目的。
我们还可以根据名字进行查询

book.setBname("十里桃花");

两个类准备好，就该封装我们的BaseDao，也就是我们的分页代码类了

public class BaseDao<T> {
 /**
  * 
  * @param sql 查询不同的实体类，那么对应的sql不同，所以需要传递
  * @param clz 生产出不同的实体类对应的实列，然后装进list容器中返回
  * @param book
  * @param pageBean 决定是否分页
  * @return
  * @throws SQLException
  * @throws IllegalAccessException 
  * @throws InstantiationException 
  */

public List<T> executeQuery (String sql,Class clz,PageBean pageBean ) throws SQLException, InstantiationException, IllegalAccessException{
          Connection con= DBAccess.getConnection();
       PreparedStatement ps=null;
       ResultSet rs = null;

if(pageBean!=null&& pageBean.isPagination()) {
       //需要分页
       //算符合条件的总记录数
       String countSql=getCountSql(sql);
       ps=con.prepareStatement(countSql);
       rs=ps.executeQuery();
       if(rs.next()) {
        pageBean.setTotal(rs.getLong(1)+"");
       }

//查询出符合条件的结果集
       String pageSql=getPageSql(sql,pageBean);
       ps=con.prepareStatement( pageSql);
       rs=ps.executeQuery();


 }else {
       ps=con.prepareStatement(sql);
       rs=ps.executeQuery();
      }

  List<T> list=new ArrayList<>();
      T t;
    while(rs.next()) {
     /**
      * 1.实列化一个book对象
      * 2.取book的所有属性，然后给其赋值
      * 2.1获取所有属性赋值
      * 2.2给属性对象赋值
      * 3.赋完值的book对象装进list容器中
      */


 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, ps, rs);

  return list;

 }


/**
  * 利用原生sql 拼接出符合条件的结果集的查询sql
  * @param sql
  * @param pageBean
  * @return
  */
    private String getPageSql(String sql, PageBean pageBean) {
  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

public class BookDao extends BaseDao<Book> {
 /**
  * 
  * @param book 封装这jsp传递过来的查询参数
  * @param pageBean 决定dao层的list调用时是否分页
  * @return
  * @throws SQLException
  * @throws IllegalAccessException 
  * @throws InstantiationException 
  */


public List<Book> list (Book book,PageBean pageBean ) throws SQLException, InstantiationException, IllegalAccessException{
  String sql= "select * from t_mvc_book where true ";
  String bname=book.getBname();
  if(StringUtils.isNotBlank(bname)) {
   sql += " and bname like '%"+bname+"%'";
  }
  return super.executeQuery(sql, Book.class, pageBean);
 }

public static void main(String[] args) throws InstantiationException, IllegalAccessException {
  BookDao bookDao=new BookDao();
  Book book=new Book();
   pageBean.setPage(1);
   PageBean pageBean=new PageBean();
   
try {
   List<Book> list = bookDao.list(book, pageBean);
      for (Book b : list) {
    System.out.println(b);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
     }
       }

  pageBean.setPage(1);

运行结果：我查询条件的第一页

当然也可以选择不分页

pageBean.setPagination(false);

运行结果：