net.avcompris.commons.query.impl.SqlWhereClause Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of avc-commons3-query Show documentation
Show all versions of avc-commons3-query Show documentation
Common classes for avc-commons3 queries and filtering.
package net.avcompris.commons.query.impl;
import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkNotNull;
import static com.google.common.base.Preconditions.checkState;
import static com.google.common.collect.Maps.newHashMap;
import static com.google.common.collect.Sets.newHashSet;
import static org.apache.commons.lang3.StringUtils.isBlank;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.annotation.Nullable;
import org.apache.commons.lang3.NotImplementedException;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import net.avcompris.commons.query.DateTimePrecision;
import net.avcompris.commons.query.Filtering;
import net.avcompris.commons.query.FilteringHandler;
public final class SqlWhereClause {
private final String sql;
private static SqlWhereClause EMPTY = new SqlWhereClause("");
private SqlWhereClause(final String sql) {
this.sql = checkNotNull(sql, "sql");
}
public static SqlWhereClause build(@Nullable final Filtering filtering,
final Class fieldClass) {
return build(filtering, fieldClass, null);
}
public static SqlWhereClause build(@Nullable final Filtering filtering,
final Class fieldClass, @Nullable final String prefix) {
if (filtering == null) {
return EMPTY;
}
final SqlBuilder sqlBuilder = new SqlBuilder(fieldClass, //
isBlank(prefix) ? "" : prefix);
filtering.applyTo(sqlBuilder);
return new SqlWhereClause(sqlBuilder.build());
}
/**
* @param prefix e.g. " WHERE", or " AND"
*/
public String getSQL(final String prefix) {
checkNotNull(prefix, "prefix");
return isBlank(sql) //
? "" //
: isBlank(prefix) //
? sql //
: prefix + " " + sql;
}
public void setParameters(final PreparedStatement pstmt, final int parmIndex) throws SQLException {
checkNotNull(pstmt, "pstmt");
}
private static final class FieldSpec {
public final String sqlName;
public FieldSpec(final String sqlName) {
this.sqlName = checkNotNull(sqlName, "sqlName");
}
}
private static class SqlBuilder implements FilteringHandler {
// private String sql = "";
private static final Set> CLASSES_WITH_FIELD_DESCS = newHashSet();
private static final Map FIELD_SPECS = newHashMap();
private final String prefix;
private final Class fieldClass;
private String sqlClause = null;
// private Type type = null;
private SqlBuilder notMember = null;
private final List> orMembers = new ArrayList<>();
private final List> andMembers = new ArrayList<>();
private SqlBuilder(final Class fieldClass, final String prefix) {
this.fieldClass = checkNotNull(fieldClass, "fieldClass");
this.prefix = checkNotNull(prefix, "prefix");
checkArgument(fieldClass.isEnum(), //
"fieldClass should be enum: %s", fieldClass.getName());
if (!CLASSES_WITH_FIELD_DESCS.contains(fieldClass)) {
for (final U enumValue : fieldClass.getEnumConstants()) {
final Field field = FieldUtils.getEnumField(enumValue);
final String sqlName = FieldUtils.extractSqlName(field);
final FieldSpec fieldSpec = new FieldSpec(sqlName);
FIELD_SPECS.put(enumValue, fieldSpec);
}
}
}
public String build() {
// 1. VALIDATE STATE
checkState(sqlClause != null || notMember != null || !orMembers.isEmpty() || !andMembers.isEmpty(), //
"sqlClause: %s, orMembers: %s, andMembers: %s", sqlClause, orMembers.size(), andMembers.size());
checkState(sqlClause == null || notMember == null, //
"sqlClause: %s, orMembers: %s, andMembers: %s", sqlClause, orMembers.size(), andMembers.size());
checkState(sqlClause == null && notMember == null || orMembers.isEmpty() && andMembers.isEmpty(), //
"sqlClause: %s, orMembers: %s, andMembers: %s", sqlClause, orMembers.size(), andMembers.size());
checkState(orMembers.isEmpty() || andMembers.isEmpty(), //
"sqlClause: %s, orMembers: %s, andMembers: %s", sqlClause, orMembers.size(), andMembers.size());
// 2. COMPOSE
if (sqlClause != null) {
return sqlClause;
} else if (notMember != null) {
final StringBuilder sb = new StringBuilder();
sb.append("NOT(");
// final boolean hasMembers = !notMember.andMembers.isEmpty() || !notMember.andMembers.isEmpty();
// if (hasMembers) {
// sb.append("(");
// }
sb.append(notMember.build());
// if (hasMembers) {
sb.append(")");
// }
return sb.toString();
} else if (!orMembers.isEmpty()) {
final StringBuilder sb = new StringBuilder();
for (final SqlBuilder orMember : orMembers) {
if (sb.length() != 0) {
sb.append(" OR ");
}
final boolean hasMembers = true; // !orMember.andMembers.isEmpty() ||
// !orMember.andMembers.isEmpty();
if (hasMembers) {
sb.append("(");
}
sb.append(orMember.build());
if (hasMembers) {
sb.append(")");
}
}
return sb.toString();
} else if (!andMembers.isEmpty()) {
final StringBuilder sb = new StringBuilder();
for (final SqlBuilder andMember : andMembers) {
if (sb.length() != 0) {
sb.append(" AND ");
}
final boolean hasMembers = true; // !andMember.andMembers.isEmpty() ||
// !andMember.andMembers.isEmpty();
if (hasMembers) {
sb.append("(");
}
sb.append(andMember.build());
if (hasMembers) {
sb.append(")");
}
}
return sb.toString();
} else {
throw new NotImplementedException("");
}
}
@Override
public void setTrue() {
sqlClause = "1";
}
@Override
public void setFalse() {
sqlClause = "0";
}
// ======== STRING COMPARISONS
@Override
public void eq(final U field, @Nullable final String refValue) {
checkNotNull(field, "field");
if (refValue == null) {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " IS NULL";
} else {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " = '" + refValue.replace("'", "''") + "'";
}
}
@Override
public void neq(final U field, @Nullable final String refValue) {
checkNotNull(field, "field");
if (refValue == null) {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " IS NOT NULL";
} else {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " != '" + refValue.replace("'", "''") + "'";
}
}
@Override
public void contains(final U field, final String refValue) {
checkNotNull(field, "field");
checkNotNull(refValue, "refValue");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " LIKE '%" //
+ refValue //
.replace("'", "''") //
.replace("\\", "\\\\") //
.replace("%", "\\%") //
+ "%'";
}
@Override
public void doesntContain(final U field, final String refValue) {
checkNotNull(field, "field");
checkNotNull(refValue, "refValue");
sqlClause = "NOT(" + FIELD_SPECS.get(field).sqlName + " LIKE '%" //
+ refValue //
.replace("'", "''") //
.replace("\\", "\\\\") //
.replace("%", "\\%") //
+ "%')";
}
// ======== BOOLEAN COMPARISONS
@Override
public void eq(final U field, final boolean refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " = " + (refValue ? "TRUE" : "FALSE");
}
@Override
public void neq(final U field, final boolean refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " != " + (refValue ? "TRUE" : "FALSE");
}
// ======== INT COMPARISONS
@Override
public void eq(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " = " + refValue;
}
@Override
public void neq(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " != " + refValue;
}
@Override
public void gte(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " >= " + refValue;
}
@Override
public void gt(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " > " + refValue;
}
@Override
public void lte(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " <= " + refValue;
}
@Override
public void lt(final U field, final int refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " < " + refValue;
}
// ======== DATETIME COMPARISONS
private static String formatDateTimeField(final String fieldName, final DateTimePrecision precision) {
checkNotNull(fieldName, "fieldName");
checkNotNull(precision, "precision");
switch (precision) {
case DAY_OF_MONTH:
return "DATE_TRUNC('day', " + fieldName + ")";
case MINUTE:
return "DATE_TRUNC('minute', " + fieldName + ")";
case SECOND:
return "DATE_TRUNC('second', " + fieldName + ")";
case MILLISECOND:
return "DATE_TRUNC('milliseconds', " + fieldName + ")";
default:
throw new NotImplementedException("precision: " + precision);
}
}
private static String format(final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(refValue, "refValue");
checkNotNull(precision, "precision");
switch (precision) {
case DAY_OF_MONTH:
return "TO_TIMESTAMP('" //
+ refValue.toString(DateTimeFormat.forPattern("yyyy-MM-dd")) //
+ "', 'YYYY-MM-DD')";
case MINUTE:
return "TO_TIMESTAMP('" //
+ refValue.toString(DateTimeFormat.forPattern("yyyy-MM-dd HH:mm")) //
+ "', 'YYYY-MM-DD HH24:MI')";
case SECOND:
return "TO_TIMESTAMP('" //
+ refValue.toString(DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")) //
+ "', 'YYYY-MM-DD HH24:MI:SS')";
case MILLISECOND:
return "TO_TIMESTAMP('" //
+ refValue.toString(DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS")) //
+ "', 'YYYY-MM-DD HH24:MI:SS.MS')";
default:
throw new NotImplementedException("precision: " + precision);
}
}
@Override
public void eq(final U field, @Nullable final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
if (refValue == null) {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " IS NULL";
} else {
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " = " + format(refValue, precision);
}
}
@Override
public void neq(final U field, @Nullable final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
if (refValue == null) {
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " IS NOT NULL";
} else {
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " != " + format(refValue, precision);
}
}
@Override
public void gte(final U field, final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " >= " + format(refValue, precision);
}
@Override
public void gt(final U field, final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " > " + format(refValue, precision);
}
@Override
public void lte(final U field, final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " <= " + format(refValue, precision);
}
@Override
public void lt(final U field, final DateTime refValue, final DateTimePrecision precision) {
checkNotNull(field, "field");
sqlClause = formatDateTimeField(FIELD_SPECS.get(field).sqlName, precision)
+ " < " + format(refValue, precision);
}
// ======== ENUM COMPARISONS
@Override
public void eq(final U field, final Enum refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " = '" + refValue.name() + "'";
}
@Override
public void neq(final U field, final Enum refValue) {
checkNotNull(field, "field");
sqlClause = prefix + FIELD_SPECS.get(field).sqlName + " != '" + refValue.name() + "'";
}
// ======== STRUCTURAL
@Override
public SqlBuilder newNotMember() {
final SqlBuilder notMember = new SqlBuilder(fieldClass, prefix);
this.notMember = notMember;
return notMember;
}
@Override
public SqlBuilder newAndMember() {
final SqlBuilder andMember = new SqlBuilder(fieldClass, prefix);
andMembers.add(andMember);
return andMember;
}
@Override
public SqlBuilder newOrMember() {
final SqlBuilder orMember = new SqlBuilder(fieldClass, prefix);
orMembers.add(orMember);
return orMember;
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy