TeaFramework——ORM框架的实现（四）

    在本篇博客中，聊聊分页的问题，对于每一个数据库，分页语句都不相同，如：mysql用limit分页、oracle用rownum分页等，那么要实现代码可以移植到不同数据库上，必须由框架来统一生成分页语句。

    分页有两个步骤，一个是count，一个是执行分页语句，于是，定义一个接口用于生成count语句和分页语句：

public interface PageProcessor {
	public String countSql(String sql);

	public String pageSql(String sql);
}

    对于大部分数据库count语句基本相同，于是定义了一个抽象类：

public abstract class AbstractPageProcessor implements PageProcessor {
	private static final String countSql = "SELECT COUNT(0) FROM (%s) t";

	@Override
	public String countSql(String sql) {
		return String.format(countSql, sql);
	}
}

    具体的分页语句，每种数据库都不相同，所以每种数据库需要定义一个实现类，如mysql和oracle对应的实现类分别是MysqlPageProcessor、OraclePageProcessor

    整个设计的UML图如下：

     

    具体分页语句的生成代码，我们看一个oracle的，封装在OraclePageProcessor类中。

public class OraclePageProcessor extends AbstractPageProcessor {

	private static final String pageSql = "select * from (select t.*,rownum as rn from (%s %s) t where rownum<=%s) where rn>%s";

	@Override
	public String pageSql(String sql) {
		int pageNum = PageThreadVariable.getPageNum();
		int pageSize = PageThreadVariable.getPageSize();
		long startRow = (pageNum - 1) * pageSize;
		long endRow = pageNum * pageSize > PageThreadVariable.getTotalRecordCount()
				? PageThreadVariable.getTotalRecordCount() : pageNum * pageSize;
		return String.format(pageSql, sql,
				PageThreadVariable.getOrderBy() != null ? "order by " + PageThreadVariable.getOrderBy() : "", endRow,
				startRow);
	}

}

    下面我们来看看分页参数怎么传递，由于web是一个多线程的开发模式，对于每个请求，web容器都会在线程池中拿一个线程来进行服务，大部分业务场景而言，一个请求中只有一个分页业务。这时候我将目光转向了ThreadLocal。将分页参数和排序参数封装在ThreadLocal中，只有当前线程能够访问到，线程对请求服务完成后清空。

    于是就有了PageThreadVariable类

public class PageThreadVariable {
	private static ThreadLocal<Map> threadLocal = new ThreadLocal<Map>();
	private static final String PAGE_NUM = "page_num";
	private static final String PAGE_SIZE = "page_size";
	private static final String ORDER_BY = "order_by";
	private static final String TOTAL_RECORD_COUNT = "total_record_count";

	public static Boolean isPaging() {
		return getPageNum() != null && getPageSize() != null;
	}

	public static void clear() {
		threadLocal.remove();
	}

	public static void setPageNum(Integer pageNum) {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map == null) {
			map = new HashMap<String, Object>();
		}
		map.put(PAGE_NUM, pageNum);
		threadLocal.set(map);
	}

	public static Integer getPageNum() {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map != null) {
			return (Integer) map.get(PAGE_NUM);
		}
		return null;
	}

	public static void setPageSize(Integer pageSize) {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map == null) {
			map = new HashMap<String, Object>();
		}
		map.put(PAGE_SIZE, pageSize);
		threadLocal.set(map);
	}

	public static Integer getPageSize() {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map != null) {
			return (Integer) map.get(PAGE_SIZE);
		}
		return null;
	}

	public static void setOrderBy(String orderBy) {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map == null) {
			map = new HashMap<String, Object>();
		}
		map.put(ORDER_BY, orderBy);
		threadLocal.set(map);
	}

	public static String getOrderBy() {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map != null) {
			return (String) map.get(ORDER_BY);
		}
		return null;
	}

	public static void setTotalRecordCount(Long totalRecordCount) {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map == null) {
			map = new HashMap<String, Object>();
		}
		map.put(TOTAL_RECORD_COUNT, totalRecordCount);
		threadLocal.set(map);
	}

	public static Long getTotalRecordCount() {
		Map<String, Object> map = (Map<String, Object>) threadLocal.get();
		if (map != null) {
			return (Long) map.get(TOTAL_RECORD_COUNT);
		}
		return null;
	}

}

    PageThreadVariable中，四个常量Key表示的意义如下：

    PAGE_NUM：当前页

    PAGE_SIZE：每页显示条数

    ORDER_BY：排序参数

    TOTAL_RECORD_COUNT：总记录数，总记录数通过count查询获得，然后set

    用一个工具类PageUtil来负责操作这个ThreadLocal对象

public class PageUtil {

	public static void setPageParameter(Integer pageNum, Integer pageSize, OrderBy orderBy) {
		PageThreadVariable.setPageNum(pageNum);
		PageThreadVariable.setPageSize(pageSize);
		if (orderBy != null) {
			PageThreadVariable.setOrderBy(orderBy.toString());
		}
	}

	public static void clearPageParameter() {
		PageThreadVariable.clear();
	}
}

    对于分页参数，也封装了一个基础类来add分页参数

public class OrderBy {
	private List<String[]> orderList;

	public static OrderBy builder() {
		return new OrderBy();
	}

	public OrderBy() {
		orderList = new ArrayList<String[]>();
	}

	public OrderBy addOrderPair(String filed, String sord) {
		orderList.add(new String[] { filed, sord });
		return this;
	}

	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		for (String[] orderPair : orderList) {
			if (builder.length() > 0) {
				builder.append(",");
			}
			builder.append(orderPair[0]).append(" ").append(orderPair[1]);
		}
		return builder.toString();
	}
}

    一个分页查询，只需要两行代码即可完成，示例代码如下：

PageUtil.setPageParameter(1, 10, OrderBy.builder().addOrderPair("id", Order.ASC));
PageInfo<User> pageInfo = new PageInfo<>(a.getAllUser());

    分页的使用还是相当比较方便的，目前只实现了oracle和mysql的分页，其他类型的数据库待实现。

     项目地址：https://git.oschina.net/lxkm/teaframework
     博客：https://blog.csdn.net/dong_lxkm