钢炮级 持久层 —— 下篇

Title：分页的扩展
    持久层对于分页功能的缺少，显然是不能接受，为弥补这个不足，我做了分页的扩展，如下：
ExpPublicDao.java

package com.pub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import com.pub.db.DBConnection;
import com.pub.page.Page;
/***
 * 扩展PublicDao，处理分页Sql
 * @author Administrator
 *
 */
public class ExpPublicDao extends PublicDao {
	
	/***
	 * 构造方法 
	 */
	public ExpPublicDao(){
		super();
	}

	/***【重】【增】
	 * 构造分页sql语句专用方法
	 * 分页查询可以不考虑操作类型，所以减少了操作类型的参数type
	 * 同时基于Page对象，在Page中做了表名、主键的记录，这里可以减少表名这个参数
	 * @param type
	 * @param page
	 * @param mk
	 * @param mv
	 * @return
	 */
	public String createPageSql(Map<Object, String> mk,Map<Object, Object> mv, String condition, Page page){
		
		StringBuffer sql = new StringBuffer();
		//接收查询部分
		sql.append(this.createSql(PublicDao.SELECT, page.getTableName(), mk, mv, condition));
		//SQL条件部分
		StringBuffer where = new StringBuffer();
		//在查询状态下，通过mv判断条件不为空
		if(mv.size() > 0 ){//配置条件部分
			where.append(" where 1=1 and ");
			if("".equals(condition) || null == condition){
				for(int i=1;i<=mv.size();i++){
					where.append(mv.get(i));
					if(i != mv.size()){
						where.append(" and ");
					}
				}
			}else{
				for(int i=1;i<=mv.size();i++){
					where.append(mv.get(i)+""+condition.split(", ")[i-1]);//逗号+空格
					if(i != mv.size()){
						where.append(" and ");
					}
				}
			}
			return this.getMysqlPagerSql(sql, page, where).toString();
		}else{//条件为空
			return this.getMysqlPagerSql(sql, page, where).toString();
		}
	}
	
	/***
	 * 构造SqlServer分页Sql
	 * @param sql
	 * @param page
	 * @param where
	 * @return getSqlServerPagerSql
	 */
	public StringBuffer getSqlServerPagerSql(StringBuffer sql, Page page, StringBuffer where){
		//添加
		sql = new StringBuffer(sql.toString().replaceFirst("select", "select top "+page.getPageSize()+" "));
		
		StringBuffer condition = new StringBuffer();
		//当前页为第1页
		if(page.getCurrentPage() <= 1){
			if(page.getCurrentPage() < 1){
				page.setCurrentPage(1);
			}
			condition.append(" ORDER BY " + page.getPrimaryKey());
		}else{//当前页不为第1页
			if(page.getCurrentPage() > page.getPageCount()){
				if(page.getPageCount() <= 1){
					page.setCurrentPage(1);
					getSqlServerPagerSql(sql, page, where);
					return sql;
				}
				//为便于测试，保证运行，判断当总页数小于当前页数的时候设置当前页为总页数
				page.setCurrentPage(page.getPageCount());
			}
			//对于分页的条件部分，拼凑SQL语句不算复杂
			condition.append(" where "+page.getPrimaryKey() + " > (SELECT MAX(" + page.getPrimaryKey() + ") "+
			"         FROM (SELECT TOP "+(page.getCurrentPage()-1)*page.getPageSize()+" " + page.getPrimaryKey() + " "+
			"               FROM " + page.getTableName() + " @where ORDER BY " + page.getPrimaryKey() + ") AS T) "+
			"ORDER BY " + page.getPrimaryKey()
			);
		} 
		//		最后，替换条件子查询中条件占位符
		sql = sql.append(condition.toString().replace("@where", (where == null || where.equals(""))?"":where));
		
		return sql;
	}
	
	/***
	 * 构造Mysql分页Sql
	 * @param sql
	 * @param page
	 * @param where
	 * @return
	 */
	public StringBuffer getMysqlPagerSql(StringBuffer sql, Page page, StringBuffer where){
		
		StringBuffer condition = new StringBuffer(); 
		
		if(page.getCurrentPage() <= 1){
			if(page.getCurrentPage() < 1){
				page.setCurrentPage(1);
			}
			condition.append(" ORDER BY " + page.getPrimaryKey());
		}else{
			if(page.getCurrentPage() > page.getPageCount()){
				if(page.getPageCount() <= 1){
					page.setCurrentPage(1);
					getMysqlPagerSql(sql, page, where);
					return sql;
				}
				//为便于测试，保证运行，判断当总页数小于当前页数的时候设置当前页为总页数
				page.setCurrentPage(page.getPageCount());
			}
			//构造条件
			condition.append(" where "+page.getPrimaryKey() + " > (SELECT MAX(" + page.getPrimaryKey() + ") "+
			"         FROM (SELECT " + page.getPrimaryKey() + " "+
			"               FROM " + page.getTableName() + " @where ORDER BY " + page.getPrimaryKey() + 
							" LIMIT "+(page.getCurrentPage()-1)*page.getPageSize()+" ) AS T) "+
			"ORDER BY " + page.getPrimaryKey()
			);
		} 
		//			最后，替换条件子查询中条件占位符
		sql.append(condition.toString().replace("@where", (where == null || where.toString().equals(""))?"":where.toString()) + " LIMIT " + page.getPageSize());
		
		return sql;
	}
	
	/***
	 * 执行Sql，获取总记录数<br>
	 * @param sql
	 * @return count 总记录数
	 */
	public int executeSql(String sql){
		Connection con = 
			//DBConnection.getConnection();
			DBConnection.getMySqlConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			con = DBConnection.getConnection();
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while(rs.next()){
				count ++;
			}
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			close(con, ps, rs);
		}
	}
}

    此类继承了PublicDao，作为对分页扩展，比较简单，共四个方法createPageSql，getSqlServerPageSql，getMysqlPageSql，executeSql。
    其中createPageSql方法中根据不同数据库调用不同的构造分页条件的SQL的方法；
    executeSql方法为查询总记录数的方法，用于分页时计算总页数、以及构造分页条件；

    这个类提供了对SQLServer(getSqlServerPageSql)和MySql(getMysqlPageSql)数据库的分页支持，我本想做一个通用方法，兼容各类数据库，但是由于个数据库间的数据库语法差异，执行效率还不如分开来好。两者分页方案一致，如下：

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

    基于这个分页方案，可以扩展其他可能用到的数据库分页方法。
    并且由于数据库差异，DBConnect也应同步一致。在没有使用多数据源的情况下，编写一个兼容各数据库的方法显得毫无意义。
==========================================
    有了分页的持久层支持，还需要一个负责传递分页信息的中间对象，如下：
page.java

package com.pub.page;

/***
 * Page对象
 * 	设置表名、主键字段名，用于拼接分页Sql字符串，也可以通过Pojo中指定
 * @author Administrator
 * 2011-05-11
 */
public class Page {

    /**当前页*/
    private int currentPage;
    /**总页数*/
    private int pageCount;
    /**每页的记录条数*/
    private int pageSize;
    /**总的记录条数*/
    private int recordCount;
    /**表名*/
    private String tableName;
    /**主键字段名*/
    private String primaryKey;
    /**数据库*/
    private String DataBaseName;
    /***
     * 获取主键字段名
     * @return
     */
	public String getPrimaryKey() {
		return primaryKey;
	}
	public void setPrimaryKey(String primaryKey) {
		this.primaryKey = primaryKey;
	}
	/***
	 * 获取表名
	 * @return
	 */
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	/***
	 * 当前页
	 * @return
	 */
	public int getCurrentPage() {
		if(currentPage <= 0){
			this.setCurrentPage(1);
		}
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	/***
	 * 总页数
	 * @return
	 */
	public int getPageCount() {
		return pageCount;
	}
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
	}
	/***
	 * 获取页显示记录数大小，默认为10
	 * @return
	 */
	public int getPageSize() {
		if(pageSize <= 0){
			this.setPageSize(10);
		}
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	/***
	 * 总记录数
	 * @return
	 */
	public int getRecordCount() {
		return recordCount;
	}
	/***
	 * 设置总记录数，同时设置总页数
	 * @param recordCount
	 */
	public void setRecordCount(int recordCount) {
		
		this.setPageCount(
					recordCount%this.getPageSize() != 0 
						?
					recordCount/this.getPageSize()+1 
						:
					recordCount/this.getPageSize()
				);
		this.recordCount = recordCount;
	}
	public String getDataBaseName() {
		if(DataBaseName == null || DataBaseName.equals("")){
			return "SQLSERVER";
		}
		return DataBaseName;
	}
	public void setDataBaseName(String dataBaseName) {
		DataBaseName = dataBaseName;
	}
}

仔细看，page中就这一部分，包含了实现分页的基本信息，这个也是可以扩展的：

    /**当前页*/
    private int currentPage;
    /**总页数*/
    private int pageCount;
    /**每页的记录条数*/
    private int pageSize;
    /**总的记录条数*/
    private int recordCount;
    /**表名*/
    private String tableName;
    /**主键字段名*/
    private String primaryKey;
    /**数据库*/
    private String DataBaseName;

同时，在给总记录数recordCount赋值的时候，总页数pageCount也被赋值了，如下：

	/***
	 * 设置总记录数，同时设置总页数
	 * @param recordCount
	 */
	public void setRecordCount(int recordCount) {
		
		this.setPageCount(
					recordCount%this.getPageSize() != 0 
						?
					recordCount/this.getPageSize()+1 
						:
					recordCount/this.getPageSize()
				);
		this.recordCount = recordCount;
	}

还是以role表为例，简单测试：

		ExpPublicDao dao = new ExpPublicDao();
		Page page = new Page();
		page.setTableName("role");
		page.setPrimaryKey(Role.getPrimaryKey());
		page.setCurrentPage(2);
		page.setPageSize(5);
		//这里为了方便，就手写SQL代码啦，实际调用不会这样哈
		page.setRecordCount(dao.executeSql("select * from role"));
		
		Map<Object, String> mk = new HashMap<Object, String>();
		Map<Object, Object> mv = new HashMap<Object, Object>();
		//设置要查询的列
		mk.put(1, Role.RoleId());
		mk.put(2, Role.RoleLevel());
		mk.put(3, Role.RoleName());
		mk.put(4, Role.RoleResource());
		mk.put(5, Role.Remark());
		 
		String sql = dao.createPageSql(mk, mv, "", page);
		System.out.println(sql);
		//执行获得数据集
		List list = dao.executeSql(PublicDao.SELECT, sql);

这里得到SQL：

SELECT role_id,role_level,role_name,role_resource,remark FROM role  
WHERE role_id > (SELECT MAX(role_id) FROM (
	SELECT role_id FROM role  ORDER BY role_id LIMIT 5 ) AS T
	) ORDER BY role_id LIMIT 5

先到数据库里查询一下：

得到数据集之后，在解析它，再封装为以role对象为内容的数据结构，解析办法如下：

		//执行获得数据集
		List list = dao.executeSql(PublicDao.SELECT, sql)//接上
                /**用于封装并返回数据的集合对象*/
		List<Role> list_t = new ArrayList<Role>();
		//取出列名Map
		Map mk_n = (Map) list.get(0);
		//取出每一行数据
		List list_mv = (List) list.get(1);
		//封装的对象
		Role role = null;
		for(int i=0;i<list_mv.size();i++){
			Map mv_n = (Map) list_mv.get(i);
			//这里每循环一次代表每一行数据，即一个对象
			role = new Role();
			for(int j=1;j<=mk_n.size();j++){
				Object temp = mv_n.get(mk_n.get(j));
				if(Role.RoleId().equals(mk_n.get(j))){
					role.setRoleId(temp==null?null:(Integer)temp);
				}
				if(Role.RoleName().equals(mk_n.get(j))){
					role.setRoleName(temp==null?"":temp.toString());
				}
				if(Role.RoleResource().equals(mk_n.get(j))){
					role.setRoleResource(temp==null?"":temp.toString());
				}
				if(Role.RoleLevel().equals(mk_n.get(j))){
					role.setRoleLevel(temp==null?"":temp.toString());
				}
			}
			list_t.add(role);
		}

看图看真相：

    总结，对于返回的数据结构，可以自定义，方式方法很多，但最终目的是如何方便的使用查询得到的数据，而不用考虑数据怎么来的，这就是持久层存在的意义