当我们在写Hibernate Orm组件的时候，经常会遇到分页查询这种操作，分页查询的原理不在熬述，比较重要的一点是需要计算查询的总数count，大部分人还是采用传统的hql/sql字符串截取或者拼接等方式实现。下面给出的代码是通过字符串模板、正则匹配等方式实现的，直接上代码：
public class QueryTemplateUtil { public static final String COUNT_QUERY_STRING = "select count(%s) from %s x"; public static final String DELETE_ALL_QUERY_STRING = "delete from %s x"; private static final String COUNT_REPLACEMENT_TEMPLATE = "select count(%s) $5$6$7"; private static final String SIMPLE_COUNT_VALUE = "$2"; private static final String COMPLEX_COUNT_VALUE = "$3$6"; private static final Pattern COUNT_MATCH; private static final String IDENTIFIER = "[\\p{Alnum}._$]+"; private static final String IDENTIFIER_GROUP = String.format("(%s)", IDENTIFIER); private static final String EQUALS_CONDITION_STRING = "%s.%s = ?"; static { StringBuilder builder = new StringBuilder(); builder.append("(?<=from)"); // from as starting delimiter builder.append("(?: )+"); // at least one space separating builder.append(IDENTIFIER_GROUP); // Entity name, can be qualified (any builder.append("(?: as)*"); // exclude possible "as" keyword builder.append("(?: )+"); // at least one space separating builder.append("(\\w*)"); // the actual alias builder = new StringBuilder(); builder.append("(select\\s+((distinct )?(.+?)?)\\s+)?(from\\s+"); builder.append(IDENTIFIER); builder.append("(?:\\s+as)?\\s+)"); builder.append(IDENTIFIER_GROUP); builder.append("(.*)"); COUNT_MATCH = compile(builder.toString(), CASE_INSENSITIVE); } /** * Private constructor to prevent instantiation. */ private QueryTemplateUtil() { } /** * getCountQueryString:根据实体名称，count字段，查询条件生成count查询字符串 * <p> * <b>例如：</b>[email protected] entityName="User"},[email protected] countQueryPlaceHolder="*"}, * [email protected] conditionAttributes=["name","sex"]},生成的countQueryString是： * </p> * <p> * [email protected] select count(*) from User x where x.name=? and x.sex=?} * </p> * * @Title: getCountQueryString * @param entityName * 实体名称，[email protected] [email protected] empty} * @param countQueryPlaceHolder * count查询占位字段，[email protected] [email protected] empty} * @param conditionAttributes * 查询条件字段 * @return String */ public static String getCountQueryString(String entityName, String countQueryPlaceHolder, Iterable<String> conditionAttributes) { Assert.hasText(entityName, "实体名称不允许为null或empty!"); StringBuilder sb = new StringBuilder(String.format(COUNT_QUERY_STRING, countQueryPlaceHolder, entityName)); sb.append(" WHERE "); if (conditionAttributes != null) { for (String conditionAttribute : conditionAttributes) { sb.append(String.format(EQUALS_CONDITION_STRING, "x", conditionAttribute)); sb.append(" AND "); } } sb.append("1 = 1"); return sb.toString(); } /** * getCountQueryString:根据实体名称，生成count查询字符串 * <p> * <b>注意：</b>[email protected] *}，不带查询条件 * </p> * * @Title: getCountQueryString * @param entityName * 实体名称，[email protected] [email protected] empty} * @return [email protected] select count(*) from SomeEntity x } */ public static String getCountQueryString(String entityName) { return getCountQueryString(entityName, "*", null); } /** * getCountQueryString:根据实体名称、查询条件生成count查询字符串 * * @Title: getCountQueryString * @param entityName * 实体名称，[email protected] [email protected] empty} * @param conditionAttributes * 查询条件字段 * @return [email protected] select count(*) from SomeEntity x where condition1=? and condition2=? and 1=1} */ public static String getCountQueryString(String entityName, Iterable<String> conditionAttributes) { return getCountQueryString(entityName, "*", conditionAttributes); } /** * getQueryString:通过实体名称与查询字符串模板，创建查询字符串 * <p> * <b>参见：</b>[email protected] org.mk.mini.orm.util.DELETE_ALL_QUERY_STRING} * </p> * * @Title: getQueryStringByTemplate * @param template * 查询字符串模板 * @param entityName * 实体名称 * @return 查询字符串 */ public static String getQueryStringByTemplate(String template, String entityName) { Assert.hasText(entityName, "实体名称不允许为null或empty!"); return String.format(template, entityName); } /** * createCountQueryFor:通过给定的原始sql或hql创建count查询字符串. * * @Title: createCountQueryFor * @param originalQuery * 原始查询字符串，支持sql与hql 不能为 [email protected] null} 或者 [email protected] empty} * @return */ public static String createCountQueryFor(String originalQuery) { Assert.hasText(originalQuery, "查询字符串不允许为null或empty"); Matcher matcher = COUNT_MATCH.matcher(originalQuery); String variable = matcher.matches() ? matcher.group(4) : null; boolean useVariable = ExtraStringUtils.hasText(variable) && !variable.startsWith("new") && !variable.startsWith("count("); return matcher.replaceFirst(String.format(COUNT_REPLACEMENT_TEMPLATE, useVariable ? SIMPLE_COUNT_VALUE : COMPLEX_COUNT_VALUE)); }}
实例就不用写了，注释已经写的很清楚，关于Assert和ExtraStringUtils只是做了断言判断与字符串判断，需要copy该代码的同学们可以改造一下，写的不好，请指正。