通用分页简介

it2022-05-05 66

一.通用分页简介

通用分页思路:

1、将原有的查询向上抽取 2、让返回值变成泛型 3、使用回调函数处理resultset 4、利用反射处理回调函数 5、获取总记录数（页面展示，计算总页数） 6、拼接分页sql语句，获取对应的结果集

通用分页步骤:

1.链接mysql数据库（本人是用MySQL），导入jar包

数据库工具类：config.properties

#oracle9i #driver=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@localhost:1521:ora9 #user=test #pwd=test

#sql2005 #driver=com.microsoft.sqlserver.jdbc.SQLServerDriver #url=jdbc:sqlserver://localhost:1423;DatabaseName=test #user=sa #pwd=sa

#sql2000 #driver=com.microsoft.jdbc.sqlserver.SQLServerDriver #url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB #user=sa #pwd=888888

#mysql5 driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/mybatis_ssm?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT user=mybatis_ssm pwd=xiaoli

链接数据库DBAccess代码如下：

package 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.jdbc.Driver".equals(driver); }

public static void main(String[] args) { Connection conn = DBAccess.getConnection(); DBAccess.close(conn); System.out.println("isOracle：" + isOracle()); System.out.println("isSQLServer：" + isSQLServer()); System.out.println("isMysql：" + isMysql()); System.out.println("数据库连接(关闭)成功"); } }

然后检查是否链接成功

PageBean分页工具类

package 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 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; }

}

这个是判断一个值是否为空

package util;

public class StringUtils { // 私有的构造方法，保护此类不能在外部实例化 private StringUtils() { }

/** * 如果字符串等于null或去空格后等于""，则返回true，否则返回false * * @param s * @return */ public static boolean isBlank(String s) { boolean b = false; if (null == s || s.trim().equals("")) { b = true; } return b; } /** * 如果字符串不等于null或去空格后不等于""，则返回true，否则返回false * * @param s * @return */ public static boolean isNotBlank(String s) { return !isBlank(s); }

}

出来中文乱码代码：

package 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(); } }

}

测试

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

/** * 第一个版本（利用s2的知识完成数据的查询） * @author * */ public class BookDao{ public List<Book> list(Book book,PageBean pageBean) throws SQLException{ String sql="select * from t_mvc_book where true "; List<Book> list=new ArrayList<Book>(); 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(); while(rs.next()) { list.add(new Book(rs.getInt("bid"),rs.getString("bname"),rs.getFloat("price"))); } return list; } public static void main(String[] args) { BookDao bkd=new BookDao(); Book bk=new Book(); //bk.setBname("圣墟");//注意：注释则显示全部信息，不注释则显示带圣虚的 try { List<Book> list = bkd.list(bk, null); for (Book b : list) { System.out.println(b); } } catch (SQLException e) { e.printStackTrace(); } }

}

通用分页查询

BASEDAO.JAVA

package com.lww.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 util.DBAccess; import util.PageBean;

/** * 用来处理所有表的增删改查 * * 查询值得是：通用的分页查询 * @author 86135 * * @param <T> */ public class BaseDao<T>{ //第三 public List<T> executeQuery(String sql,Class clz,PageBean pageBean) throws SQLException, InstantiationException, IllegalAccessException{ List<T> list=new ArrayList<T>(); Connection con=DBAccess.getConnection(); PreparedStatement ps =null; ResultSet rs = null; if(pageBean!=null && pageBean.isPagination()) { //分页代码 /** * 1.分页是与PageBean中total有关，意味着需要查询数据库得到得到total给pageBean * 2。查询出符合条件的某一页的数据 * select count(*) from (select * from t_mvc_book where bname like '%圣墟%') t; */ //通过原生sql拼接出符合条件的 String countSql=getCountSql(sql); ps = con.prepareStatement(countSql); rs=ps.executeQuery(); if(rs.next()) { pageBean.setTotal(rs.getObject(1).toString()); } //第二部拼接 select * from t_mvc_book where bname like '%圣墟%' limit 10,10; String pageSql=getPageSql(sql,pageBean); ps = con.prepareStatement(pageSql); rs=ps.executeQuery(); }else { //不分页代码 ps = con.prepareStatement(sql); rs=ps.executeQuery(); } T t=null; while(rs.next()) { //list.add(new Book(rs.getInt("bid"),rs.getString("bname"),rs.getFloat("price"))); /** * 1.实例化了一个Book对象 * 2.通过book对象的属性名去游标中取出属性值 * 2.1获取到类对象中的属性 * 2.2然后给属性赋值 * 3.已经赋值好的book实例对象放入list集合中 * * clz>Book.class */ t=(T) clz.newInstance(); Field[] field = clz.getDeclaredFields(); for (Field f : field) { f.setAccessible(true); f.set(t, rs.getObject(f.getName())); } list.add( t); } return list; }

private String getPageSql(String sql, PageBean pageBean) { return sql+"limit "+pageBean.getStartIndex()+","+pageBean.getRows(); }

private String getCountSql(String sql) { return "select count(1) from ("+sql+") t"; } }

BookDao.java

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

//继承BaseDao public List<Book> list(Book book,PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException { 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) { BookDao bkd=new BookDao(); Book bk=new Book(); PageBean pageBean=new PageBean(); //后加 bk.setBname("圣墟"); List<Book> list; try { list = bkd.list(bk, pageBean); for (Book b : list) { System.out.println(b); } } catch (InstantiationException | IllegalAccessException | SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println(pageBean); } }

结果如下：

专利

最新回复(0)