java通用分页（一）

为什么使用通用分页

我们在查询显示数据时，如果数据比较多，那么用到分页会使我们整个界面数据显示更加简洁，这里我是截图的是某度的分页

通用分页的流程

看到别人的分页好像挺棒，自己动手写一个出来，下面给大家介绍一下小编的分页流程:

1. 首先是导入jar包和数据库连接的配置文件:

这里小编是web项目，所以jar包放在web-inf文件夹下:

properties配置文件:

#oracle9i
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#pwd=/马赛克/


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


#sql2000
#driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
#url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB
#user=sa
#pwd=/马赛克/
//以上为注释内容，这里小编用的是mysql

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

上面的配置文件数据库连接字符串和加载驱动字符串都有，有想用的小伙伴可以拿过去直接用，加入配置后，解析就可以用了

2.配置好后，下一步就是解析配置文件内容，连接数据库了:

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

写个main方法测试运行一下，看连接成功没:

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("数据库连接(关闭)成功");
	}

isOracle：false
isSQLServer：false
isMysql：true
数据库连接(关闭)成功

3.数据库连接成功后，就可以建实体类和通用方法类了:

实体类:

public class Job implements Serializable{

	private String id;
	private String job;
	private String company;
	private String address;
	private String salary;
	private String url;
	private String limits;
	private String time;
	private String descs;
	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 getLimits() {
		return limits;
	}
	public void setLimits(String limit) {
		this.limits = limit;
	}
	public String getTime() {
		return time;
	}
	public void setTime(String time) {
		this.time = time;
	}
	public String getDescs() {
		return descs;
	}
	public void setDescs(String desc) {
		this.descs = 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 Job(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.limits = limit;
		this.time = time;
		this.descs = desc;
		this.jobHandle = jobHandle;
		this.addressHandle = addressHandle;
	}
	
	
	@Override
	public String toString() {
		return "Job [id=" + id + ", job=" + job + ", company=" + company + ", address=" + address + ", salary=" + salary
				+ ", url=" + url + ", limits=" + limits + ", time=" + time + ", descs=" + descs + ", jobHandle="
				+ jobHandle + ", addressHandle=" + addressHandle + "]";
	}
	}

通用方法实体类:

public class PageBean {

	private int page = 1;// 页码
	private int rows = 10;// 页大小，每页显示多少条数据
	private int total = 0;// 总记录数，从数据库中统计

	private boolean pagination = true;

	private String url;// 请求路径
	public String getUrl() {
		return url;
	}

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

	public Map<String, String[]> getParameterMap() {
		return ParameterMap;
	}

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

	// 存储页面传的参数
	private Map<String, String[]> ParameterMap = new HashMap<String, String[]>();

	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 = Boolean.parseBoolean(pagination);
		}
	}

	public PageBean() {
		super();
	}

	public PageBean(int page, int rows, int total, boolean pagination) {
		super();
		this.page = page;
		this.rows = rows;
		this.total = total;
		this.pagination = pagination;
	}

	@Override
	public String toString() {
		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
	}
}

回调接口，遍历方法:

public interface Callback<k> {

	public List<k> foreach(ResultSet rs) throws SQLException;
}

通用分页方法类:

public class BaseDao<k> {
	public List<k> executeQuery(String sql, PageBean pageBean, Callback<k> callBack) {
		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()) {
					int total = rs.getInt(1);// 总记录数
					pageBean.setTotal(total);// 给pagebean的总记录数赋值
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				DBAccess.close(null, ps, rs);
			}
		}
		// 2.查询指定页码并满足条件的总记录数
		try {
			if(con==null) {
				con=DBAccess.getConnection();
			}
			String pageSql =sql;
			if(null!=pageBean&&pageBean.isPagination()) {
				pageSql = this.getPageSql(sql, pageBean);
			}
			ps = con.prepareStatement(pageSql);
			rs = ps.executeQuery();
			// 不同内容
			return callBack.foreach(rs);
		} catch (Exception e) {
			throw new RuntimeException();
		} finally {
			DBAccess.close(con, ps, rs);
		}
	}


	/**
	 * 专门写一个方法用来拼接查询语句的统计满足条件的总行数
	 * 
	 * @param sql
	 * @return
	 */
	public String getCountSql(String sql) {
		return "select count(*) from (" + sql + ") t1";
	}

	/**
	 * 专门写一个方法用来存储拼接分页的sql
	 * 
	 * @param sql
	 * @param pageBean
	 * @return
	 */
	public String getPageSql(String sql, PageBean pageBean) {
		return sql + " limit " + pageBean.getStartsIndex() + "," + pageBean.getRows();

	}

}

4.因为是通用分页，所以类的类型都用了泛型数据，dao方法继承通用分页类:

public class JobDao extends BaseDao<Job>{
	/**
	 * 查询方法 模糊查询和查询全部
	 * 
	 * @param stu
	 * @return
	 */
	public List<Job> find(Job job, PageBean pageBean) {
		List<Job> jobList = new ArrayList<Job>();
		String sql = "select * from t_solr_job where 1=1 ";
		return this.executeQuery(sql, pageBean, new Callback<Job>() {
			@Override
			public List<Job> foreach(ResultSet rs) throws SQLException {//通用分页遍历
				List<Job> jobList = new ArrayList<Job>();
				Job job1 = null;
				while (rs.next()) {
					job1 = new Job();
					job1.setId(rs.getString(1));
					job1.setJob(rs.getString(2));
					job1.setCompany(rs.getString(3));
					job1.setAddress(rs.getString(4));
					job1.setSalary(rs.getString(5));
					job1.setUrl(rs.getString(6));
					job1.setLimits(rs.getString(7));
					job1.setTime(rs.getString(8));
					job1.setDescs(rs.getString(9));
					job1.setJobHandle(rs.getString(10));
					job1.setAddressHandle(rs.getString(11));
					jobList.add(job1);
				}
				return jobList;
			}
		});
	}
}

尾言

是不是好奇为啥没有效果图，哈哈哈，不要着急，因为是抢先版，所以还有很多细节没处理和优化，明天写好了第一时间分享出来溜了溜了…