org.bitbucket.brunneng.qb.SqlQueryBuilder Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of query-builder Show documentation
Show all versions of query-builder Show documentation
Compact tool for building SQL, JPA or hibernate queries.
Supports utilities for spring pagination and sorting.
Supports extending to other types of query languages.
Has no transitive dependencies - use only what you need.
The newest version!
package org.bitbucket.brunneng.qb;
import java.util.Collection;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* A query builder which has useful methods to build sql queries.
*
* Has feature of auto removing last 'while' keyword, if there are no conditions added afterwards. Can be useful, if
* you have some dynamic list of conditions, and it's possible that none of them will be applied. For example:
*
* SELECT * FROM order_item WHERE
*
* Will be fixed to:
*
* SELECT * FROM order_item
*
* It will also work if "order by" or "group by" goes immediately after "while".
*
*
* Has feature of auto removing first 'and' operator in appended part, if there are no conditions added before.
* Can be useful, if you have some dynamic list of conditions, and it's unknown which one will be applied first.
* For example:
*
* SELECT p.* FROM person p WHERE AND p.name = 'Joe' AND p.age > 20
*
* Will be fixed to:
*
* SELECT p.* FROM person p WHERE p.name = 'Joe' AND p.age > 20
*
*/
public class SqlQueryBuilder extends QueryBuilder {
protected static final String ANY_SYMBOLS = "%";
protected static final String LIKE_OPERATOR = "like";
protected static final String IN_OPERATOR = "in";
protected static final String NOT_OPERATOR_PART = "not";
protected final Pattern targetAliasPattern;
protected final Pattern endsWithWherePattern;
protected final Pattern whereWithoutConditionsPattern;
protected final Pattern andOperatorPattern;
public SqlQueryBuilder() {
targetAliasPattern = Pattern.compile("from\\s+" + identifierPattern + "\\s+(" + identifierPattern + ")",
Pattern.CASE_INSENSITIVE);
endsWithWherePattern = Pattern.compile("\\s+where\\s*$", Pattern.CASE_INSENSITIVE);
whereWithoutConditionsPattern = Pattern.compile("(\\s+where)(" +
"\\s*$|" +
"\\s*\\)|" +
"\\s+order\\s+by|" +
"\\s+group\\s+by)",
Pattern.CASE_INSENSITIVE);
andOperatorPattern = Pattern.compile("^\\s*and\\s+", Pattern.CASE_INSENSITIVE);
registerOperator(LIKE_OPERATOR);
registerOperator(IN_OPERATOR);
registerOperator(NOT_OPERATOR_PART);
ignoreCaseOfOperators = true;
}
/**
* Appends the queryPart which contains operator like if the given value is not null nor empty.
* Otherwise - do nothing. Surrounds the given value with %.
* @param queryPart the part of query which contains operator like
* @param value the value anywhere in matched string
* @return this query builder
*/
public SqlQueryBuilder appendLike(String queryPart, String value) {
validateHasOperator(queryPart, LIKE_OPERATOR);
if (isNotEmpty(value)) {
appendInternal(queryPart, ANY_SYMBOLS + value + ANY_SYMBOLS);
}
return this;
}
/**
* Appends the queryPart which contains operator like if the given value is not null nor empty.
* Otherwise - do nothing. Adds % to the end of the given value.
* @param queryPart the part of query which contains operator like
* @param prefix the value at the start of matching string
* @return this query builder
*/
public SqlQueryBuilder appendLikeStartsWith(String queryPart, String prefix) {
validateHasOperator(queryPart, LIKE_OPERATOR);
if (isNotEmpty(prefix)) {
appendInternal(queryPart, prefix + ANY_SYMBOLS);
}
return this;
}
/**
* Appends queryPart which contains operator like if the given value is not null nor empty.
* Otherwise - do nothing. Adds % to the start of the given value.
* @param queryPart the part of query which contains operator likelike
* @param suffix the value at the end of matching string
* @return this query builder
*/
public SqlQueryBuilder appendLikeEndsWith(String queryPart, String suffix) {
validateHasOperator(queryPart, LIKE_OPERATOR);
if (isNotEmpty(suffix)) {
appendIfNotEmpty(queryPart, ANY_SYMBOLS + suffix);
}
return this;
}
/**
* Appends the queryPart which contains operator like if the given value is not null nor empty.
* Otherwise - do nothing. Modifies queryPart and value to ignore characters case during search.
* @param queryPart the part of query which contains operator like
* @param value the value anywhere in matched string
* @return this query builder
* @throws IllegalArgumentException if operator like is not found in queryPart
*/
public SqlQueryBuilder appendLikeCaseInsensitive(String queryPart, String value) {
validateHasOperator(queryPart, LIKE_OPERATOR);
if (value != null) {
appendLike(wrapPropertiesBeforeLikeOperatorsInLowerFunction(queryPart), value.toLowerCase());
}
return this;
}
/**
* Appends the queryPart which contains operator in if the given elements is not null nor empty.
* Otherwise - do nothing.
* @param queryPart the part of query which contains operator in
* @param elements the matching elements
* @return this query builder
*/
public SqlQueryBuilder appendIn(String queryPart, Collection elements) {
validateHasOperator(queryPart, IN_OPERATOR);
appendIfNotEmpty(queryPart, elements);
return this;
}
/**
* Appends query with interval intersection condition.
* It's assumed that intervals are valid: searchIntervalStart ≤ searchIntervalEnd
* and value by startColumnPath is ≤ than value by endColumnPath.
* @param startColumnPath path to column (like alias.column) with start date; can be nullable column,
* which means unbound interval down to minus infinity.
* @param endColumnPath path to column (like alias.column) with end date; can be nullable column
* which means unbound interval up to plus infinity.
* @param searchIntervalStart start of search interval of some type, which supports compare operations;
* can be null, which means unbound interval up to plus infinity.
* @param searchIntervalEnd end of search interval of some type, which supports compare operations;
* can be null, which means unbound interval up to plus infinity.
* @param includeEndsOfInterval if true, then intervals considered as intersected when the end of one interval
* is the same as the beginning of another interval.
*/
protected void appendIntervalIntersection(String startColumnPath, String endColumnPath,
Object searchIntervalStart, Object searchIntervalEnd,
boolean includeEndsOfInterval) {
// (StartA <= EndB) and (EndA >= StartB)
final String cmpOperationSuffix = includeEndsOfInterval ? "=" : "";
if (searchIntervalEnd != null && searchIntervalStart == null) {
append(String.format("and (%s is null or %s <%s :v)", startColumnPath, startColumnPath, cmpOperationSuffix),
searchIntervalEnd);
}
else {
append(String.format("and ((%s is null or %s <%s :v)", startColumnPath, startColumnPath, cmpOperationSuffix),
searchIntervalEnd);
append(String.format(" and (%s is null or %s >%s :v)", endColumnPath, endColumnPath, cmpOperationSuffix),
searchIntervalStart);
if (searchIntervalEnd != null) {
query.append(")");
}
}
}
private void validateHasOperator(String queryString, String operator) {
if (!getOperatorMatcher(queryString, operator).find()) {
throw new IllegalArgumentException(String.format("Operator '%s' is not found in query part '%s'", operator,
queryString));
}
}
/**
* @param queryPart queryPart to convert
* @return original queryPart with properties before like operators wrapped with lower() SQL function.
*/
private String wrapPropertiesBeforeLikeOperatorsInLowerFunction(String queryPart) {
Matcher likeMatcher = getOperatorMatcher(queryPart, LIKE_OPERATOR);
int replacedCount = 0;
int nextStart = 0;
while (likeMatcher.find(nextStart)) {
int i = likeMatcher.start();
String beforeLike = queryPart.substring(0, i).trim();
i = beforeLike.lastIndexOf(' '); // -1 is OK
String property = beforeLike.substring(i + 1).replaceAll("[()]", "");
String replacedProperty = "lower(" + property + ")";
queryPart = queryPart.replace(property, replacedProperty);
nextStart = likeMatcher.start() + replacedProperty.length() - property.length() + LIKE_OPERATOR.length() + 1;
replacedCount++;
likeMatcher = getOperatorMatcher(queryPart, LIKE_OPERATOR);
}
if (replacedCount == 0) {
throw new IllegalArgumentException("'like' is not found in specified queryPart");
}
return queryPart;
}
private Matcher getOperatorMatcher(String queryPart, String operator) {
final String regex = "\\s" + operator + "\\s";
Pattern likePattern = ignoreCaseOfOperators ? Pattern.compile(regex, Pattern.CASE_INSENSITIVE) :
Pattern.compile(regex);
return likePattern.matcher(queryPart);
}
protected QueryWithParams buildCountQueryInternal(boolean supportsMissingSelect) {
String queryString = getQuery();
final String selectPart = "select ";
Pattern selectPattern = Pattern.compile("select\\s", Pattern.CASE_INSENSITIVE);
Matcher selectMatcher = selectPattern.matcher(queryString);
String countQuery;
if (!selectMatcher.find()) {
if (!supportsMissingSelect) {
throw new IllegalStateException("\"select \" can't be detected to build count query.");
}
Pattern fromInTheBeginning = Pattern.compile("^\\s*from\\s", Pattern.CASE_INSENSITIVE);
if (!fromInTheBeginning.matcher(queryString).find()) {
throw new IllegalStateException("\"select \" can't be detected to build count query. " +
"And \" from\" is not in the start of the query.");
}
countQuery = "select count(" + findTargetAlias() + ") " + queryString;
}
else {
int indexOfSelect = selectMatcher.start();
if (indexOfSelect == -1) {
throw new IllegalStateException("\"select \" can't be detected to build count query");
}
Pattern fromPattern = Pattern.compile("\\sfrom", Pattern.CASE_INSENSITIVE);
Matcher fromMatcher = fromPattern.matcher(queryString);
if (!fromMatcher.find()) {
throw new IllegalStateException("\" from\" can't be detected to build count query");
}
int indexOfFrom = fromMatcher.start();
if (indexOfSelect > indexOfFrom) {
throw new IllegalStateException("Index of select should be less then index of from");
}
String targetPart = queryString.substring(indexOfSelect + selectPart.length(), indexOfFrom).trim();
countQuery = selectPart + "count(" + targetPart + ")" + queryString.substring(indexOfFrom);
}
return new QueryWithParams(countQuery, getParameters());
}
String findTargetAlias() {
final String query = getQuery();
Matcher m = targetAliasPattern.matcher(query);
if (!m.find()) {
throw new IllegalStateException("Can't detect target alias in query '" + query + "'");
}
return m.group(1);
}
@Override
protected String preprocessQueryPart(String queryPart) {
String res = super.preprocessQueryPart(queryPart);
return tryRemoveFistAnd(res);
}
protected String tryRemoveFistAnd(String queryPart) {
if (endsWithWherePattern.matcher(query.toString()).find()) {
Matcher matcher = andOperatorPattern.matcher(queryPart);
if (matcher.find()) {
queryPart = queryPart.substring(matcher.end());
}
}
return queryPart;
}
protected void postprocessQueryBeforeGet(StringBuilder query) {
Matcher matcher = whereWithoutConditionsPattern.matcher(query.toString());
while (matcher.find()) {
query.delete(matcher.start(1), matcher.end(1));
matcher = whereWithoutConditionsPattern.matcher(query.toString());
}
}
}