通用分页上

最新推荐文章于 2023-06-27 17:27:12 发布

沿途为何没爱河

最新推荐文章于 2023-06-27 17:27:12 发布

阅读量173

点赞数

分类专栏： MVC复习

本文链接：https://blog.csdn.net/Lzxccas/article/details/106443082

版权

MVC复习专栏收录该内容

13 篇文章 0 订阅

订阅专栏

通用分页上

通用分页核心思路
导入所需jar包
- 建包util 放入写好的辅助类
- - 写实体类 dao方法和 PageBean BaseDao

通用分页核心思路

将上一次查询请求再发一次，只不过页码变了
分页三要素
page 页码视图层传递过来
rows 页大小视图层传递过来
total 总记录数后台查出来
pagination 是否分页视图层传递过来

这是通用分页的核心思想只要掌握了通用分页就没什么问题了

导入所需jar包

下面是包

在这里插入图片描述

建包util 放入写好的辅助类

首先是第一个config.properties 这个是放连接数据库工具类
里面可连接Oracle sqlserver mysql 的不同版本这里我连接的是mysql 所以其他的先注释打开mysql
mysql://localhost:3306/xx? localhost是连接名字 3306是端口号 xx是表的名字
我mysql 密码为空所以什么都没写

#oracle9i
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#pwd=123


#sql2005
#driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#url=jdbc:sqlserver://localhost:1433;DatabaseName=test1
#user=sa
#pwd=123


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


#mysql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xx?useUnicode=true&characterEncoding=UTF-8
user=root
pwd=

接下来放入连接工具DBAccess

package com.xx.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;

/**
 * 提供了一组获得或关闭数据库对象的方法
 * @author 小曦
 *
 * http://www.javaxl.com
 */
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("数据库连接(关闭)成功");
	}
}

还有中文乱码处理类EncodingFiter

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

}

工具类StringUtils

package com.xx.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);
	}

}

拼音工具类，能将汉字转换成拼音的首字母 PinYinUtil 可有可无把看需求

package com.xx.util;

import java.util.regex.Pattern;

import net.sourceforge.pinyin4j.PinyinHelper;

/**
 * 拼音工具类，能将汉字转换成拼音的首字母
 */
public class PinYinUtil {
	/* 用于中文判断的正则表达式 */
	private static final String regexStr = "[\u4e00-\u9fa5]";

	/**
	 * 将一个字符串中的汉字转换成拼音首字母、非汉字则不变
	 * 
	 * @param cn
	 *            String
	 * @return String
	 */
	public static String toPinyin(String cn) {
		String pinyin = null;
		if (null == cn || 0 == cn.trim().length()) {
			return pinyin;
		}

		/* 去掉字符串前后的空格 */
		cn = cn.trim();
		char[] chineseCharacterArr = cn.toCharArray(); // 转换成汉字字符数组
		char[] letteCharacterArr = new char[chineseCharacterArr.length]; // 字母字符数组
		for (int i = 0; i < chineseCharacterArr.length; i++) {
			// 得到汉字拼音的首字母
			letteCharacterArr[i] = getFirstLetterFromPinyin(chineseCharacterArr[i]);
		}

		if (0 != letteCharacterArr.length) {
			pinyin = new String(letteCharacterArr);
			pinyin = pinyin.toUpperCase();
		}
		return pinyin;
	}

	/* 得到一个汉字的拼音的首字母 */
	private static char getFirstLetterFromPinyin(char cn) {
		// 判断cn是否为一个合法的汉字,不是则直接返回cn
		if (!isChineseCharacters(cn)) {
			return cn;
		}

		String[] pyArr = PinyinHelper.toHanyuPinyinStringArray(cn);
		char py = pyArr[0].charAt(0);
		return py;
	}

	/**
	 * 判断字符是否为一个汉字
	 * 
	 * @param cn
	 *            char
	 * @return boolean
	 */
	public static boolean isChineseCharacters(char cn) {
		boolean b = false;
		if (Pattern.matches(regexStr, String.valueOf(cn))) {
			b = true;
		}
		return b;
	}

	public static void main(String[] args) {
		String s = "保存并加入A题库";
		System.out.println(PinYinUtil.toPinyin(s).toLowerCase());
	}

}

写实体类 dao方法和 PageBean BaseDao

实体类字段名要和数据库的一样

package com.xx.entity;

import java.io.Serializable;

public class Student implements Serializable{
	
	
	private static final long serialVersionUID = 1L;
	private String id;
	private String job;
	private String company;
	private String address;
	private String salary;
	private String url;
	private String limit;
	private String time;
	private String desc;
	private String jobHandle;
	private String addressHandle;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getCompany() {
		return company;
	}
	public void setCompany(String company) {
		this.company = company;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getSalary() {
		return salary;
	}
	public void setSalary(String salary) {
		this.salary = salary;
	}
	public String getUrl() {
		return url;
	}
	public void setUrl(String url) {
		this.url = url;
	}
	public String getLimit() {
		return limit;
	}
	public void setLimit(String limit) {
		this.limit = limit;
	}
	public String getTime() {
		return time;
	}
	public void setTime(String time) {
		this.time = time;
	}
	public String getDesc() {
		return desc;
	}
	public void setDesc(String desc) {
		this.desc = desc;
	}
	public String getJobHandle() {
		return jobHandle;
	}
	public void setJobHandle(String jobHandle) {
		this.jobHandle = jobHandle;
	}
	public String getAddressHandle() {
		return addressHandle;
	}
	public void setAddressHandle(String addressHandle) {
		this.addressHandle = addressHandle;
	}
	
	public Student() {

	}
	public Student(String id, String job, String company, String address, String salary, String url, String limit,
			String time, String desc, String jobHandle, String addressHandle) {
		this.id = id;
		this.job = job;
		this.company = company;
		this.address = address;
		this.salary = salary;
		this.url = url;
		this.limit = limit;
		this.time = time;
		this.desc = desc;
		this.jobHandle = jobHandle;
		this.addressHandle = addressHandle;
	}
	
	
	
}

dao方法增加删除修改模糊查询

package com.xx.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.xx.Callback;
import com.xx.entity.Student;
import com.xx.util.DBAccess;
import com.xx.util.PageBean;
import com.xx.util.StringUtils;

public class StudentDao extends BaseDao<Student>{
	/*Connection con=null;
	PreparedStatement ps=null;
	ResultSet rs=null;
	public void add(Student stu) {
		
	}*/
	
	/*public List<Student> getAll(String name){
		List<Student> ls=new ArrayList<Student>();
		String sql="select * from t_solr_job";
		if(name!=null) {
			 sql="select * from t_solr_job where company like '%"+name+"%'";
		}
		try {
			con=DBAccess.getConnection();
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Student stu=new Student();
				stu.setId(rs.getString("id"));
				stu.setJob(rs.getString("job"));
				stu.setCompany(rs.getString("company"));
				stu.setAddress(rs.getString("address"));
				stu.setSalary(rs.getString("salary"));
				stu.setUrl(rs.getString("url"));
				stu.setLimit(rs.getString("limit"));
				stu.setTime(rs.getString("time"));
				stu.setDesc(rs.getString("desc"));
				stu.setJobHandle(rs.getString("jobHandle"));
				stu.setAddressHandle(rs.getString("addressHandle"));
				ls.add(stu);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			DBAccess.close(con, ps, rs);
		}
		return ls;
	}*/
	
	public List<Student> list(String sname,PageBean pageBean){
		List<Student> list=new ArrayList<Student>();
		//模糊查询的条件
		String sql="select * from t_solr_job";
		if(sname!=null) {
			sql="select * from t_solr_job where company like '%"+sname+"%'";
		}
		return this.query(sql, pageBean, new Callback<Student>() {
			
			@Override
			public List<Student> foreach(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				Student stu=null;
				while(rs.next()) {
					stu=new Student();
					stu.setId(rs.getString("id"));
					stu.setJob(rs.getString("job"));
					stu.setCompany(rs.getString("company"));
					stu.setAddress(rs.getString("address"));
					stu.setSalary(rs.getString("salary"));
					stu.setUrl(rs.getString("url"));
					stu.setLimit(rs.getString("limitt"));
					stu.setTime(rs.getString("time"));
					stu.setDesc(rs.getString("descc"));
					stu.setJobHandle(rs.getString("jobHandle"));
					stu.setAddressHandle(rs.getString("addressHandle"));
					list.add(stu);
				}
				return list;
			}
		});
	} 
	
	public int delete(String id){
		int n=0;
		Connection	con=null;
		PreparedStatement ps=null;
		try {
			con=DBAccess.getConnection();
			String sql="delete from t_solr_job where id="+id;
			ps=con.prepareStatement(sql);
			n=ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			DBAccess.close(con, ps, null);
		}
		return n;
	}
	
	public List<Student> list(String id){
		List<Student> ls=new ArrayList<Student>();
		Connection	con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			con=DBAccess.getConnection();
			String sql="select * from t_solr_job where id="+id;
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				Student stu=new Student();
				stu.setId(rs.getString("id"));
				stu.setJob(rs.getString("job"));
				stu.setCompany(rs.getString("company"));
				stu.setAddress(rs.getString("address"));
				stu.setSalary(rs.getString("salary"));
				stu.setUrl(rs.getString("url"));
				stu.setLimit(rs.getString("limitt"));
				stu.setTime(rs.getString("time"));
				stu.setDesc(rs.getString("descc"));
				stu.setJobHandle(rs.getString("jobHandle"));
				stu.setAddressHandle(rs.getString("addressHandle"));
				ls.add(stu);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			DBAccess.close(con, ps, rs);
		}
		return ls;
	}
	
	public int update(String id,Student stu){
		int n=0;
		Connection	con=null;
		PreparedStatement ps=null;
		try {
			con=DBAccess.getConnection();
			String sql="update t_solr_job set job=?,company=?,address=?,salary=?,url=?,limitt=? where id="+id;
			ps=con.prepareStatement(sql);
			ps.setString(1, stu.getJob());
			ps.setString(2, stu.getCompany());
			ps.setString(3, stu.getAddress());
			ps.setString(4, stu.getSalary());
			ps.setString(5, stu.getUrl());
			ps.setString(6, stu.getLimit());
			n=ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			DBAccess.close(con, ps, null);
		}
		return n;
	}
	public int insert(Student stu){
		int n=0;
		Connection	con=null;
		PreparedStatement ps=null;
		try {
			con=DBAccess.getConnection();
			String sql="insert into t_solr_job(id,job,company,address,salary,url,limitt,time,descc,jobHandle,addressHandle)values(?,?,?,?,?,?,?,?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, stu.getId());
			ps.setString(2, stu.getJob());
			ps.setString(3, stu.getCompany());
			ps.setString(4, stu.getAddress());
			ps.setString(5, stu.getSalary());
			ps.setString(6, stu.getUrl());
			ps.setString(7, stu.getLimit());
			ps.setString(8, stu.getTime());
			ps.setString(9, stu.getDesc());
			ps.setString(10, stu.getJobHandle());
			ps.setString(11, stu.getAddressHandle());
			n=ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			DBAccess.close(con, ps, null);
		}
		return n;
	}
}

接下来就是分页了
PageBean
分页三要素
page 页码视图层传递过来
rows 页大小视图层传递过来
total 总记录数后台查出来

pagination 是否分页视图层传递过来

package com.xx.util;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

public class PageBean {

	private int page = 1;
	private int rows = 10;
	private int total = 0;
	private boolean pagination = true;
	//请求的地址
	private String url;
	//请求的参数
	private Map<String, String[]> parameterMap;
	
	public Map<String, String[]> getParameterMap() {
		return parameterMap;
	}

	public void setParameterMap(Map<String, String[]> parameterMap) {
		this.parameterMap = parameterMap;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public PageBean() {
		super();
	}

	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}
	public void setPage(String page) {
		if(null!=page&&!"".equals(page.trim())) {
			this.page = Integer.parseInt(page);
		}
		
	}
	public int getRows() {
		return rows;
	}

	public void setRows(int rows) {
		this.rows = rows;
	}
	public void setRows(String rows) {
		if(null!=rows&&"".equals(rows.trim())) {
		this.rows = Integer.parseInt(rows);
		}
	}

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}

	public boolean isPagination() {
		return pagination;
	}

	public void setPagination(boolean pagination) {
		this.pagination = pagination;
	}
	public void setPagination(String pagination) {
		if("false".equals(pagination)) {
			this.pagination=false;
		}
	}
	@Override
	public String toString() {
		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
	}
	/**
	 * 获取起始记录的下标
	 * @return
	 */
	public int getStartIndex() {
		int start=(this.page-1)*this.rows;
		return start;
	}
	
	public void initPageBean(HttpServletRequest request,PageBean pageBean) {
		pageBean.setPage(request.getParameter("page"));
		pageBean.setRows(request.getParameter("rows"));
		pageBean.setPagination(request.getParameter("pagination"));
	
		pageBean.setUrl(request.getContextPath()+request.getServletPath());
		pageBean.setParameterMap(request.getParameterMap());
	}
	/**
	 * 获取最大页码数
	 * @return
	 */
	public int getMaxPage() {
		int maxPage=this.total/this.rows;
		if(0!=this.total%this.rows) {
			maxPage=maxPage+1;
		}
		return maxPage;
	}
	/**
	 * 获取下一页
	 * @return
	 */
	public int getNextPage() {
		int nextPage=this.page+1;
		if(nextPage>this.getMaxPage()) {
			nextPage=this.getMaxPage();
		}
		return nextPage;
	}
	/**
	 * 获取上一页
	 * @return
	 */
	public int getFrontPage() {
		int frontPage=this.page-1;
		if(frontPage<1) {
			frontPage=1;
		}
		return frontPage;
	}
	
	
	
}

有了PageBean以后可以写dao方法了
重点：泛型类调用分页类 sql语句的拼接模糊查询分页计算符合条件的总记录数

package com.xx.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.xx.Callback;
import com.xx.entity.Student;
import com.xx.util.DBAccess;
import com.xx.util.PageBean;

public class BaseDao<E> {
	public List<E> query(String sql,PageBean pageBean,Callback<E> callback){
		List<E> ls=new ArrayList<E>();
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		//第一次查询 查总记录数
		if(null!=pageBean&&pageBean.isPagination()) {
			try {
				con=DBAccess.getConnection();
				String countsql=this.getCountSql(sql);
				ps=con.prepareStatement(countsql);
				rs=ps.executeQuery();
				if(rs.next()) {
					pageBean.setTotal(rs.getInt(1));
				}
			} catch (SQLException e) {
				// TODO: handle exception
				throw new RuntimeException();
			}finally {
				DBAccess.close(null, ps, rs);
			}
			
		}
		//第二次查询 查询满足条件的记录数
		if(null!=pageBean&&pageBean.isPagination()) {
			sql=this.getPageSql(sql, pageBean);
		}
		try {
			con=DBAccess.getConnection();
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			return callback.foreach(rs);
		} catch (SQLException e) {
			// TODO: handle exception
			throw new RuntimeException();
		}finally {
			DBAccess.close(null, ps, rs);
		}
	}
	
	/**
	 * 把普通sql转成查总记录数sql
	 * @param sql
	 * @return
	 */
	public String getCountSql(String sql) {
		String countSql="select count(*) from ("+sql+") t";
		return countSql;
	}
	/**
	 * 查满足分页的sql
	 * @param sql
	 * @return
	 */
	public String getPageSql(String sql,PageBean pageBean) {
		String pageSql=sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows()+"";
		return pageSql;
	}
}

效果图：

在这里插入图片描述