RepositorySearchBaseImpl.java
package com.tradecloud.repository.base.impl;
import com.tradecloud.domain.dto.base.SearchBase;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.RepositorySearchBase;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.time.StopWatch;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory;
import org.hibernate.hql.spi.QueryTranslator;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public abstract class RepositorySearchBaseImpl<T, S> extends BaseHibernateDaoSupport implements RepositorySearchBase<T, S> {
protected static final String GREATER_THAN_PARAM_PREFIX = "gt_";
protected static final String LESS_THAN_PARAM_PREFIX = "lt_";
private static final String ASC = "ASC";
private static final long serialVersionUID = 1L;
private static final Logger log = Logger.getLogger(RepositorySearchBaseImpl.class);
protected static String getParamName(String key) {
return getParamName("", key);
}
private static String getParamName(String prefix, String key) {
// eg, shippingInformation.shippingMode = :shippingMode
return prefix + (!key.contains(".") ? key : key.substring(key.lastIndexOf(".") + 1));
}
@Override
public List<T> searchByCriteria(S search) {
Query query = createQuery(search, false);
log.debug("Query: " + query.getQueryString());
return getQueryList(query, ((SearchBase) search).getSearchMetaParams());
}
public String criteriaToSQLQuery(org.hibernate.query.Query query) {
String hqlQueryString = query.unwrap(Query.class).getQueryString();
//#hql to sql
//String hqlQueryString = sb.toString();
ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
SessionImplementor hibernateSession = getCurrentSession().getSession().unwrap(SessionImplementor.class);
QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory(), null);
queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
return queryTranslator.getSQLString();
}
@Override
public long countByCriteria(S search) {
Query query = createQuery(search, true);
return getQueryCount(query);
}
/**
* {@inheritDoc}
* <p>
* The CriteriaOperation API was the initial approach for building up the search query but using it was showing unreliable results. Depending on
* the search parameters the result-set could contain more results than there were rows in the database. The database only had two rows in it
* while I was testing but the CriteriaOperation query was returning 6 for some searches, possibly the Cartesian product. From reading on the web
* it seems that the CriteriaOperation API is not suited to handling unidirectional associations such as those in the {@link Product} class.
* <p>
* Hence the decision to use HQL in this method. Although it is slightly cumbersome to dynamically build up the HQL, the fact that there are only
* seven search fields in {@link ProductSearch} makes it somewhat easier. Performance should not be affected too much once we start paging the
* results from the database.
* <p>
* TODO Paging
*
* @param search The search object whose names and values will create the map of search parameters
* @param count
* @param selectSuffix Anything you want after all the params from the search
* @return The query to execute
*/
protected Query createQuery(S search, boolean count, String selectSuffix) {
if (search instanceof SearchBase) {
return createQuery(search, count, selectSuffix, mapFieldsToValues(search));
} else {
// we can't do anything with a search that won't provide it's table
// name i.e. SearchBase.getTableName()
throw new UnsupportedOperationException("A search object that extends SearchBase must be used otherwise we won't be able to determine "
+ "the entity table name");
}
// return null;
}
protected Query createQuery(S search, boolean count) {
return createQuery(search, count, "");
}
protected Query createQuery(S search, boolean count, Collection<CriteriaValue> fieldsToValues) {
return createQuery(search, count, "", fieldsToValues);
}
protected Query createQuery(S search, boolean count, String selectSuffix, Collection<CriteriaValue> fieldsToValues) {
String tableName = ((SearchBase) search).getTableName();
String alias = tableName.toLowerCase();
//String selectPrefix = count ? "select count(*) " : "";
String selectPrefix = count ? "select count(distinct(" + alias + ".id)) " : "select distinct " + alias + " ";
// The list that holds the select elements that will form the query
List<String> selects = new ArrayList<String>();
// Prepare the select part of the query
addSelectIfPresent(selects, fieldsToValues);
// Prepare the main query
StringBuilder sb = new StringBuilder(selectPrefix + "from " + tableName + " as " + alias + " ");
addJoins(search, sb, alias);
if (!selects.isEmpty()) {
sb.append(" where ");
sb.append(StringUtils.join(selects, " and "));
if (!selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
} else {
if (!selectSuffix.isEmpty()) {
sb.append(" where ").append(selectSuffix);
}
}
// Don't order count statements as there's no columns to order by
if (!count) {
SearchMetaParams searchMetaParams = ((SearchBase) search).getSearchMetaParams();
if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
sb.append(" order by " + alias + "." + searchMetaParams.getOrderBy() + " ");
sb.append((searchMetaParams.isAsc() ? ASC : "DESC"));
} else {
// insert default 'order by' clause.
// product, orders, consignment and shipment tables all have a 'created' field.
sb.append(" order by " + alias + ".created DESC");
}
}
if (log.isDebugEnabled()) {
log.debug("Creating query [" + sb.toString() + "]");
}
Query query = getSessionCustom().createQuery(sb.toString());
// Now that we know what fields are part of the select we can set their
// respective parameter values
setParametersIfPresent(query, fieldsToValues);
return query;
}
protected void addJoins(S search, StringBuilder sb, String alias) {
}
/**
* Below query creator creates a query with an 'orderByField' value and 'ascending' function.
* Ignores pagination.
*
* @param search
* @param selectSuffix
* @param fieldsToValues
* @return
*/
protected Query
createSortedQuery(S search, String selectSuffix, Collection<CriteriaValue> fieldsToValues, String orderByField, boolean ascending) {
String selectPrefix = "";
// The list that holds the select elements that will form the query
List<String> selects = new ArrayList<String>();
// Prepare the select part of the query
addSelectIfPresent(selects, fieldsToValues);
String tableName = ((SearchBase) search).getTableName();
// Prepare the main query
StringBuilder sb = new StringBuilder(selectPrefix + "from " + tableName + " as " + tableName.toLowerCase() + " ");
if (!selects.isEmpty()) {
sb.append(" where ");
sb.append(StringUtils.join(selects, " and "));
if (selectSuffix != null && !selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
} else {
if (selectSuffix != null && !selectSuffix.isEmpty()) {
sb.append(" where ").append(selectSuffix);
}
}
sb.append(" order by " + orderByField + " ");
sb.append(ascending ? " ASC " : " DESC ");
if (log.isDebugEnabled()) {
log.debug("Creating query [" + sb.toString() + "]");
}
Query query = getSessionCustom().createQuery(sb.toString());
// Now that we know what fields are part of the select we can set their
// respective parameter values
setParametersIfPresent(query, fieldsToValues);
return query;
}
/**
* Return the list of search results.
*
* @param query
* @param searchMetaParams
* @return
*/
protected List<T> getQueryList(Query query, SearchMetaParams searchMetaParams) {
StopWatch sw = new StopWatch();
sw.start();
if (searchMetaParams != null) {
if (searchMetaParams.getRowCount() != -1) {
query.setMaxResults(searchMetaParams.getRowCount());
}
if (searchMetaParams.getRowIndex() != -1) {
query.setFirstResult(searchMetaParams.getRowIndex());
}
}
@SuppressWarnings("unchecked")
List<T> results = query.list();
sw.stop();
log.debug("Found " + results.size() + " results for list. Meta params=" + searchMetaParams + ". Time=" + sw.getTime());
return results;
}
protected long getQueryCount(Query query) {
StopWatch sw = new StopWatch();
sw.start();
long count = ((Long) query.uniqueResult());
sw.stop();
log.debug("Found " + count + " results for count. Time=" + sw.getTime());
return count;
}
/**
* Hook method designed to be overridden by sub-classes that use the {@link #search(Object, SearchMetaParams)} method. The override method must
* implement 3 maps for the EQUALS_CRITERIA, GREATER_THAN_CRITERIA and LESS_THAN_CRITERIA they require in the query to be executed.
*
* @param search
* @return
* @throws {@code UnsupportedOperationException} each time this is invoked to prevent misuse
* @see {@link OrderRepositoryImpl#mapFieldsToValues(SearchBase)} for an example of a method that implements this pattern.
*/
protected abstract Collection<CriteriaValue> mapFieldsToValues(S search);
/**
* Populates the list of select clauses from the fields provided.
* <p>
* For every field that has a corresponding non-null value, we add a select clause to the list in the format: "fieldName = :fieldName" e.g.
* "supplier = :supplier".
* <p>
* The ":supplier" is the bind parameter whose value will be set later in the query-generation process.
*
* @param selects A list of select clauses to populate.
* @param fields A map of maps (equals, greater/less than criteria maps) for each field-name to field-value that will be used to build the select
* clauses.
* @see #setParametersIfPresent(Query, Map)
*/
protected void addSelectIfPresent(List<String> selects, Collection<CriteriaValue> fields) {
for (CriteriaValue field : fields) {
if (field.getValue() != null) {
switch (field.getCriteriaOperation()) {
case LIKE:
// selects.add(field.getKey() + " LIKE :" + getParamName(field.getKey()));
selects.add("LOWER(" + field.getKey() + ")" + " LIKE LOWER(:" + getParamName(field.getKey()) + ")");
break;
case EQUALS:
selects.add(field.getKey() + " = :" + getParamName(field.getKey()));
break;
case EQUALS_IGNORE_CASE:
selects.add("UPPER(" + field.getKey() + ")" + " = UPPER(:" + getParamName(field.getKey()) + ")");
break;
case GREATER_THAN:
// need to specify the prefix as we can have multiple
// parameter names e.g. for greater and less than values for
// the same param
selects.add(field.getKey() + " >= :" + getParamName(GREATER_THAN_PARAM_PREFIX, field.getKey()));
break;
case LESS_THAN:
// need to specify the prefix as we can have multiple
// parameter names e.g. for greater and less than values for
// the same param
selects.add(field.getKey() + " <= :" + getParamName(LESS_THAN_PARAM_PREFIX, field.getKey()));
break;
case IN:
selects.add(field.getKey() + " in (:" + getParamName(field.getKey()) + ")");
break;
case NOT_IN:
selects.add(field.getKey() + " not in (:" + getParamName(field.getKey()) + ")");
break;
case NULL:
selects.add(field.getKey() + " is null");
break;
case NOT_NULL:
selects.add(field.getKey() + " is not null");
break;
case BETWEEN:
// TODO.
break;
case NOT_IN_KEY:
selects.add(field.getKey() + " not in (:" + getParamName(field.getKey()) + "_key)");
break;
case LIKE_ANY:
List<String> fieldValues = (List<String>) field.getValue();
if (fieldValues != null && !fieldValues.isEmpty()) {
AtomicInteger orderIndex = new AtomicInteger(1);
StringBuilder sb = new StringBuilder();
String key = field.getKey();
sb.append(" (");
String allOrders = fieldValues.stream()
.map(s -> " LOWER (" + key + ") LIKE LOWER (:" + key + orderIndex.getAndIncrement() + ")")
.collect(Collectors.joining(" OR "));
sb.append(allOrders);
sb.append(" ) ");
selects.add(sb.toString());
}
break;
}
}
}
}
/**
* Binds the parameter values onto the {@code Query} instance for those non-null field values from the provided {@code fields} map.
*
* @param query The query to bind the values to.
* @param fields A map of maps (equals, greater/less than criteria maps) for each field-name to field-value that will be bound to the query (if
* they are non-null).
*/
private void setParametersIfPresent(Query query, Collection<CriteriaValue> fields) {
for (CriteriaValue field : fields) {
if (field.getValue() != null) {
switch (field.getCriteriaOperation()) {
case EQUALS:
query.setParameter(getParamName(field.getKey()), field.getValue());
break;
case EQUALS_IGNORE_CASE:
query.setParameter(getParamName(field.getKey()), field.getValue().toString().toUpperCase());
break;
case GREATER_THAN:
// need to specify the prefix as we can have multiple
// parameter names e.g. for greater and less than values for
// the same param
query.setParameter(getParamName(GREATER_THAN_PARAM_PREFIX, field.getKey()), field.getValue());
break;
case LESS_THAN:
// need to specify the prefix as we can have multiple
// parameter names e.g. for greater and less than values for
// the same param
query.setParameter(getParamName(LESS_THAN_PARAM_PREFIX, field.getKey()), field.getValue());
break;
case IN:
// set the list
query.setParameterList(getParamName(field.getKey()), (Object[]) field.getValue());
break;
case NOT_IN:
// set the list
query.setParameterList(getParamName(field.getKey()), (Object[]) field.getValue());
break;
case LIKE:
query.setParameter(getParamName(field.getKey()), getFieldValueForLikeCriteria(field));
break;
case NOT_IN_KEY:
// set the list
query.setParameterList(getParamName(field.getKey() + "_key"), (Object[]) field.getValue());
break;
case LIKE_ANY:
List<String> strings = (List<String>) field.getValue();
AtomicInteger orderIndex = new AtomicInteger(1);
for (String string : strings) {
query.setParameter(field.getKey() + orderIndex.getAndIncrement(), "%" + string + "%");
}
break;
case PARAMS_ONLY:
query.setParameter(getParamName(field.getKey()), field.getValue());
}
}
}
}
protected Query createQueryWithJoin(S search, boolean count, String selectSuffix, Collection<CriteriaValue> fieldsToValues, String other) {
String selectPrefix = count ? "select count(*) " : "";
// The list that holds the select elements that will form the query
List<String> selects = new ArrayList<String>();
// Prepare the select part of the query
addSelectIfPresent(selects, fieldsToValues);
String tableName = ((SearchBase) search).getTableName();
// Prepare the main query
StringBuilder sb = new StringBuilder(selectPrefix + "from " + tableName + " as " + tableName.toLowerCase() + " ");
sb.append(other);
if (!selects.isEmpty()) {
sb.append(" and ");
sb.append(StringUtils.join(selects, " and "));
if (!selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
} else {
if (!selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
}
// Don't order count statements as there's no columns to order by
if (!count) {
SearchMetaParams searchMetaParams = ((SearchBase) search).getSearchMetaParams();
if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
sb.append(" order by " + searchMetaParams.getOrderBy() + " ");
sb.append((searchMetaParams.isAsc() ? ASC : "DESC"));
} else {
// insert default 'order by' clause.
// product, orders, consignment and shipment tables all have a 'created' field.
sb.append(" order by " + tableName.toLowerCase() + ".created DESC");
}
}
if (log.isDebugEnabled()) {
log.debug("Creating query [" + sb.toString() + "]");
}
Query query = getSessionCustom().createQuery(sb.toString());
// Now that we know what fields are part of the select we can set their
// respective parameter values
setParametersIfPresent(query, fieldsToValues);
return query;
}
protected Query createQueryWithJoin(S search, boolean count, String selectSuffix, Collection<CriteriaValue> fieldsToValues, String other,
String[] orderBy) {
String selectPrefix = count ? "select count(*) " : "";
// The list that holds the select elements that will form the query
return getQuery((SearchBase) search, count, selectSuffix, fieldsToValues, other, orderBy, selectPrefix);
}
protected Query getQuery(SearchBase search, boolean count, String selectSuffix, Collection<CriteriaValue> fieldsToValues, String other,
String[] orderBy, String selectPrefix) {
List<String> selects = new ArrayList<String>();
// Prepare the select part of the query
addSelectIfPresent(selects, fieldsToValues);
String tableName = search.getTableName();
// Prepare the main query
StringBuilder sb = new StringBuilder(selectPrefix + "from " + tableName + " as " + tableName.toLowerCase() + " ");
sb.append(other);
if (!selects.isEmpty()) {
sb.append(" and ");
sb.append(StringUtils.join(selects, " and "));
if (!selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
} else {
if (!selectSuffix.isEmpty()) {
sb.append(" and ").append(selectSuffix);
}
}
// Don't order count statements as there's no columns to order by
if (count == false) {
/*
* SearchMetaParams searchMetaParams = ((SearchBase) search).getSearchMetaParams(); if (searchMetaParams != null &&
* searchMetaParams.getOrderBy() != null) { sb.append(" order by " + searchMetaParams.getOrderBy() + " ");
* sb.append((searchMetaParams.isAsc() ? ASC : "DESC")); } else { //insert default 'order by' clause. //product, orders, consignment and
* shipment tables all have a 'created' field. sb.append(" order by " + tableName.toLowerCase() + ".created DESC"); }
*/
sb.append(" order by ");
boolean isFirstRun = true;
for (String ordering : orderBy) {
if (isFirstRun) {
sb.append(ordering);
isFirstRun = false;
} else {
sb.append(", " + ordering);
}
}
}
if (log.isDebugEnabled()) {
log.debug("Creating query [" + sb.toString() + "]");
}
Query query = getSessionCustom().createQuery(sb.toString());
// Now that we know what fields are part of the select we can set their
// respective parameter values
setParametersIfPresent(query, fieldsToValues);
return query;
}
private Object getFieldValueForLikeCriteria(CriteriaValue field) {
Object fieldValue = field.getValue();
if (fieldValue != null) {
return "%" + fieldValue.toString() + "%";
}
return fieldValue;
}
}