Example 1 with BaseColumn
use of com.servoy.base.persistence.BaseColumn in project servoy-client by Servoy.
the class BaseSQLGenerator method parseFindExpression.
public static IBaseSQLCondition parseFindExpression(IQueryFactory queryFactory, Object raw, IBaseQuerySelectValue qCol, BaseQueryTable columnTable, int dataProviderType, String formatString, IBaseColumn c, boolean addNullPkNotNullCondition, IValueConverter valueConverter, ITypeConverter typeConverter, BaseColumn firstForeignPKColumn, ILogger logger) {
IBaseSQLCondition or = null;
// filter on the || (=or)
// $NON-NLS-1$
String[] rawElements = raw instanceof String[] ? (String[]) raw : raw.toString().split("\\|\\|");
for (String element : rawElements) {
String data = element;
if (!(c instanceof BaseColumn) || ((BaseColumn) c).getType() != Types.CHAR) {
// if char, it fills up with spaces, so don't trim
data = data.trim();
}
if (// filter out the zero length strings
data.length() == 0) {
continue;
}
try {
// find the format (only applicable for date columns)
if (dataProviderType == IColumnTypeConstants.DATETIME) {
int pipe_index = data.indexOf('|');
if (// the format is speced from within javascript '1-1-2003...30-1-2003|dd-MM-yyyy'
pipe_index != -1) {
formatString = data.substring(pipe_index + 1);
data = data.substring(0, pipe_index);
}
}
// find the operators and the modifiers
boolean isNot = false;
boolean hash = false;
int nullCheck = NULLCHECK_NONE;
int operator = IBaseSQLCondition.EQUALS_OPERATOR;
// for between
String data2 = null;
boolean parsing = true;
while (parsing && data.length() > 0) {
char first = data.charAt(0);
switch(first) {
case // ! negation
'!':
if (// $NON-NLS-1$
data.startsWith("!!")) {
parsing = false;
} else {
isNot = true;
}
data = data.substring(1);
break;
case // # case insensitive (Text) or day search (Date)
'#':
if (// $NON-NLS-1$
data.startsWith("##")) {
parsing = false;
} else {
hash = true;
}
data = data.substring(1);
break;
case // ^ or ^= nullchecks
'^':
if (// $NON-NLS-1$
data.startsWith("^^")) {
data = data.substring(1);
} else {
if (// $NON-NLS-1$
data.startsWith("^=")) {
nullCheck = NULLCHECK_NULL_EMPTY;
} else {
nullCheck = NULLCHECK_NULL;
}
}
parsing = false;
break;
default:
// unary operators
if (// $NON-NLS-1$ //$NON-NLS-2$
data.startsWith("<=") || data.startsWith("=<")) {
operator = IBaseSQLCondition.LTE_OPERATOR;
data = data.substring(2);
} else if (// $NON-NLS-1$ //$NON-NLS-2$
data.startsWith(">=") || data.startsWith("=>")) {
operator = IBaseSQLCondition.GTE_OPERATOR;
data = data.substring(2);
} else if (// $NON-NLS-1$
data.startsWith("==")) {
operator = IBaseSQLCondition.EQUALS_OPERATOR;
data = data.substring(2);
} else if (// $NON-NLS-1$
data.startsWith("<")) {
operator = IBaseSQLCondition.LT_OPERATOR;
data = data.substring(1);
} else if (// $NON-NLS-1$
data.startsWith(">")) {
operator = IBaseSQLCondition.GT_OPERATOR;
data = data.substring(1);
} else if (// $NON-NLS-1$
data.startsWith("=")) {
operator = IBaseSQLCondition.EQUALS_OPERATOR;
data = data.substring(1);
} else {
// between ?
// $NON-NLS-1$
int index = data.indexOf("...");
if (index != -1) {
data2 = data.substring(index + 3);
data = data.substring(0, index);
operator = IBaseSQLCondition.BETWEEN_OPERATOR;
}
// regular data
parsing = false;
}
}
}
IBaseSQLCondition condition = null;
if (nullCheck != NULLCHECK_NONE) {
// nullchecks
IBaseSQLCondition compareEmpty = null;
if (nullCheck == NULLCHECK_NULL_EMPTY) {
switch(dataProviderType) {
case IColumnTypeConstants.INTEGER:
case IColumnTypeConstants.NUMBER:
compareEmpty = queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, Integer.valueOf(0));
break;
case IColumnTypeConstants.TEXT:
// $NON-NLS-1$
compareEmpty = queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, "");
break;
}
}
condition = queryFactory.or(compareEmpty, queryFactory.createCompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qCol, null));
} else if (data.length() > 0) {
// get the operators
Object value = null;
// for between
Object value2 = null;
int modifier = 0;
switch(dataProviderType) {
case IColumnTypeConstants.INTEGER:
case IColumnTypeConstants.NUMBER:
Object initialObj = (raw instanceof String || raw instanceof String[]) ? data : raw;
Object objRightType = typeConverter.getAsRightType(dataProviderType, c.getFlags(), initialObj, formatString, c.getLength(), false);
// Because if it is already a Number then it shouldn't be converted to String and then back
if (initialObj != null && objRightType == null) {
// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
logger.log("Cannot convert (" + initialObj.getClass() + ") " + initialObj + " to a number/int.");
value = null;
} else {
value = objRightType;
}
// parse data2 (between)
if (data2 != null) {
value2 = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data2, formatString, c.getLength(), false);
if (value2 == null) {
// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
logger.log("Cannot convert (" + data2.getClass() + ") " + data2 + " to a number/int.");
}
}
break;
case IColumnTypeConstants.DATETIME:
// special date parsing
boolean dateSearch = hash;
Date date;
Date tmp = null;
if (// $NON-NLS-1$
data.equalsIgnoreCase("now")) {
date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), tmp = new Date(), c.getLength(), false);
} else if (// $NON-NLS-1$ //$NON-NLS-2$
data.startsWith("//") || data.equalsIgnoreCase("today")) {
date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), tmp = new Date(), c.getLength(), false);
dateSearch = true;
} else {
// Now get asRightType with RAW and not with the string.
// Because if it is already a Date then it shouldn't be converted to String and then back
Object initialObj1 = ((raw instanceof String || raw instanceof String[]) ? data : raw);
Object tst = typeConverter.getAsRightType(dataProviderType, c.getFlags(), initialObj1, formatString, c.getLength(), false);
if (tst == null && initialObj1 != null) {
// Format failed.. Reporting that to the user
// $NON-NLS-1$ //$NON-NLS-2$
logger.log("Cannot parse " + initialObj1 + " using format " + formatString + '.');
date = null;
} else {
date = (Date) tst;
}
}
if (dateSearch && date != null) {
if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
value = getStartOfDay(date, c, typeConverter);
value2 = getEndOfDay(date, c, typeConverter);
operator = IBaseSQLCondition.BETWEEN_OPERATOR;
} else if (operator == IBaseSQLCondition.BETWEEN_OPERATOR || operator == IBaseSQLCondition.LT_OPERATOR || operator == IBaseSQLCondition.GTE_OPERATOR) {
value = getStartOfDay(date, c, typeConverter);
} else {
value = getEndOfDay(date, c, typeConverter);
}
} else {
value = date;
}
// parse data2 (between)
if (data2 != null) {
dateSearch = hash;
if (// $NON-NLS-1$
data2.equalsIgnoreCase("now")) {
date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), (tmp != null ? tmp : new Date()), c.getLength(), false);
} else if (// $NON-NLS-1$ //$NON-NLS-2$
data2.startsWith("//") || data2.equalsIgnoreCase("today")) {
date = (Date) typeConverter.getAsRightType(dataProviderType, c.getFlags(), (tmp != null ? tmp : new Date()), c.getLength(), false);
dateSearch = true;
} else {
Object dt = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data2, formatString, c.getLength(), false);
if (dt instanceof Date) {
date = (Date) dt;
} else {
// $NON-NLS-1$ //$NON-NLS-2$
logger.log("Cannot parse '" + data2 + "' using format " + formatString + '.');
date = null;
}
}
if (dateSearch && date != null) {
value2 = getEndOfDay(date, c, typeConverter);
} else {
value2 = date;
}
}
break;
case IColumnTypeConstants.TEXT:
if (hash) {
modifier |= IBaseSQLCondition.CASEINSENTITIVE_MODIFIER;
}
if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
// count the amount of percents based upon the amount we decide what to do
char[] chars = data.toCharArray();
StringBuilder dataBuf = new StringBuilder();
boolean escapeNext = false;
for (char d : chars) {
if (!escapeNext && d == '\\') {
escapeNext = true;
} else {
if (!escapeNext && d == '%') {
// found a like operator, use backslash as escape in like,
// use unmodified value, db will use escape backslash from like expression
operator = IBaseSQLCondition.LIKE_OPERATOR;
if (data.indexOf('\\') >= 0) {
// escape char, put escape in sql when seen in string //$NON-NLS-1$
value2 = "\\";
}
break;
}
dataBuf.append(d);
escapeNext = false;
}
}
if (operator == IBaseSQLCondition.EQUALS_OPERATOR) {
data = dataBuf.toString();
}
// else escape in db will handle escape. use original data
} else {
value2 = data2;
}
value = data;
break;
default:
operator = IBaseSQLCondition.LIKE_OPERATOR;
// +2 for %...%
value = typeConverter.getAsRightType(dataProviderType, c.getFlags(), data, formatString, c.getLength() + 2, false);
}
// create the condition
if (value != null) {
Object operand;
// for like, value2 may be the escape character
if (value2 != null && operator == IBaseSQLCondition.BETWEEN_OPERATOR) {
operand = new Object[] { typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value : valueConverter.convertFromObject(value), null, c.getLength(), false), typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value2 : valueConverter.convertFromObject(value2), null, c.getLength(), false) };
} else if (operator == IBaseSQLCondition.LIKE_OPERATOR) {
operand = value2 == null ? value : new Object[] { value, value2 };
} else {
operand = typeConverter.getAsRightType(c.getDataProviderType(), c.getFlags(), valueConverter == null ? value : valueConverter.convertFromObject(value), null, c.getLength(), false);
}
condition = queryFactory.createCompareCondition(operator | modifier, qCol, operand);
}
}
if (condition != null) {
if (isNot) {
condition = condition.negate();
} else {
// Skip this if the search is on the related pk column, the user explicitly wants to find records that have no related record (left outer join)
if (addNullPkNotNullCondition && nullCheck != NULLCHECK_NONE && (c.getFlags() & IBaseColumn.IDENT_COLUMNS) == 0) {
// in case of composite pk, checking only the first pk column is enough
condition = queryFactory.and(condition, queryFactory.createCompareCondition(IBaseSQLCondition.NOT_OPERATOR, queryFactory.createQueryColumn(columnTable, firstForeignPKColumn.getID(), firstForeignPKColumn.getSQLName(), firstForeignPKColumn.getType(), firstForeignPKColumn.getLength(), firstForeignPKColumn.getScale(), firstForeignPKColumn.getFlags()), null));
}
}
or = queryFactory.or(or, condition);
}
} catch (Exception ex) {
logger.error("Error in parsing find expression '" + element + "'", ex);
}
}
return or;
}
Also used :
IBaseSQLCondition(com.servoy.base.query.IBaseSQLCondition)
BaseColumn(com.servoy.base.persistence.BaseColumn)
IBaseColumn(com.servoy.base.persistence.IBaseColumn)
Date(java.util.Date)
Aggregations
BaseColumn (com.servoy.base.persistence.BaseColumn)1
IBaseColumn (com.servoy.base.persistence.IBaseColumn)1
IBaseSQLCondition (com.servoy.base.query.IBaseSQLCondition)1
Date (java.util.Date)1
