通过Hql、Sql生成Hibernate CountQuery 字符串

当我们在写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>{@code entityName="User"},{@code countQueryPlaceHolder="*"},  * {@code conditionAttributes=["name","sex"]},生成的countQueryString是：  * </p>  * <p>  * {@code select count(*) from User x where x.name=? and x.sex=?}  * </p>  *   * @Title: getCountQueryString  * @param entityName  *            实体名称，不允许{@literal null}或者{@literal empty}  * @param countQueryPlaceHolder  *            count查询占位字段，不允许{@literal null}或者{@literal 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>count占位字符默认为{@literal *}，不带查询条件  * </p>  *   * @Title: getCountQueryString  * @param entityName  *            实体名称，不允许{@literal null}或者{@literal empty}  * @return {@code select count(*) from SomeEntity x }  */ public static String getCountQueryString(String entityName) {  return getCountQueryString(entityName, "*", null); } /**  * getCountQueryString:根据实体名称、查询条件生成count查询字符串  *   * @Title: getCountQueryString  * @param entityName  *            实体名称，不允许{@literal null}或者{@literal empty}  * @param conditionAttributes  *            查询条件字段  * @return {@code 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>{@link 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 不能为 {@literal null} 或者 {@literal 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该代码的同学们可以改造一下，写的不好，请指正。