| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| package org.apache.empire.db; |
| |
| import java.util.Collection; |
| import java.util.HashMap; |
| import java.util.LinkedHashMap; |
| import java.util.Map; |
| |
| import org.apache.empire.commons.Attributes; |
| import org.apache.empire.commons.ObjectUtils; |
| import org.apache.empire.commons.OptionEntry; |
| import org.apache.empire.commons.Options; |
| import org.apache.empire.commons.StringUtils; |
| import org.apache.empire.data.ColumnExpr; |
| import org.apache.empire.data.DataType; |
| import org.apache.empire.db.expr.column.DBAliasExpr; |
| import org.apache.empire.db.expr.column.DBCalcExpr; |
| import org.apache.empire.db.expr.column.DBCaseExpr; |
| import org.apache.empire.db.expr.column.DBCoalesceExpr; |
| import org.apache.empire.db.expr.column.DBConcatExpr; |
| import org.apache.empire.db.expr.column.DBConcatFuncExpr; |
| import org.apache.empire.db.expr.column.DBConvertExpr; |
| import org.apache.empire.db.expr.column.DBCountExpr; |
| import org.apache.empire.db.expr.column.DBDecodeExpr; |
| import org.apache.empire.db.expr.column.DBFuncExpr; |
| import org.apache.empire.db.expr.column.DBParenthesisExpr; |
| import org.apache.empire.db.expr.column.DBValueExpr; |
| import org.apache.empire.db.expr.compare.DBCompareColExpr; |
| import org.apache.empire.db.expr.compare.DBCompareExpr; |
| import org.apache.empire.db.expr.order.DBOrderByExpr; |
| import org.apache.empire.dbms.DBSqlPhrase; |
| import org.apache.empire.exceptions.InvalidArgumentException; |
| import org.w3c.dom.Element; |
| |
| /** |
| * This class is the base class for all expressions that represent a single value. |
| * It provides a set of factory functions for SQL functions. |
| */ |
| public abstract class DBColumnExpr extends DBExpr |
| implements ColumnExpr |
| { |
| // *Deprecated* private static final long serialVersionUID = 1L; |
| |
| // Predefined column expression attributes |
| public static final String DBCOLATTR_TITLE = "title"; |
| public static final String DBCOLATTR_TYPE = "type"; |
| |
| // Properties |
| protected Attributes attributes = null; |
| protected Options options = null; |
| protected String beanPropertyName = null; |
| |
| /** |
| * Returns the data type of this column expression. |
| * @see org.apache.empire.data.DataType |
| * |
| * @return the expressions data type |
| */ |
| @Override |
| public abstract DataType getDataType(); |
| |
| /** |
| * Returns the column name for this column expression. |
| * The name must contain only alphanumeric characters and the underscore. |
| * For SQL functions this name may be generated. However subsequent calls to this function |
| * for the same object instance must return the same string. |
| * |
| * @return the column name |
| */ |
| @Override |
| public abstract String getName(); |
| |
| /** |
| * Indicates whether this function is an aggregate (sum, min, max, avg, ...) or not |
| * @return true if the column expression represents an aggregate |
| */ |
| public abstract boolean isAggregate(); |
| |
| /** |
| * Returns the underlying physical column which was used for this expression |
| * For functions involving none or more than one physical column this function return the first one |
| * @return a column used for this expression |
| */ |
| @Override |
| public abstract DBColumn getSourceColumn(); |
| |
| /** |
| * Returns the underlying physical column which may be used for updates. |
| * For functions involving none or more than one physical column this function returns null. |
| * @return the column to be used for updates if any. |
| */ |
| public abstract DBColumn getUpdateColumn(); |
| |
| /** |
| * Add a description of this column with relevant metadata |
| * to the supplied parent XML Element. |
| * |
| * @param parent the parent element to which to append the column description |
| * @param flags currently not used |
| * @return the newly created child element |
| */ |
| public abstract Element addXml(Element parent, long flags); |
| |
| /** |
| * @link {#org.apache.empire.commons.Unwrappable#isWrapper()} |
| */ |
| @Override |
| public boolean isWrapper() |
| { // Nope |
| return false; |
| } |
| |
| /** |
| * @link {#org.apache.empire.commons.Unwrappable#unwrap()} |
| */ |
| @Override |
| public DBColumnExpr unwrap() |
| { // Noting to unwrap |
| return this; |
| } |
| |
| /** |
| * returns an expression that renames the column with its alias name |
| */ |
| public DBColumnExpr qualified() |
| { |
| return this.as(getName()); |
| } |
| |
| /** |
| * Returns the value of a column attribute. |
| * Column attributes are used to provide metadata for a column. |
| * |
| * @param name the attribute name |
| * @return value of the attribute if it exists or null otherwise |
| */ |
| @Override |
| public synchronized Object getAttribute(String name) |
| { |
| if (attributes != null && attributes.indexOf(name)>=0) |
| return attributes.get(name); |
| // Otherwise ask expression |
| DBColumn column = getUpdateColumn(); |
| if (column==null || column==this) |
| return null; |
| return column.getAttribute(name); |
| } |
| |
| /** |
| * Sets the value of a column attribute. |
| * |
| * @param name the attribute name |
| * @param value the value of the attribute |
| */ |
| @SuppressWarnings("unchecked") |
| public synchronized <T extends DBColumnExpr> T setAttribute(String name, Object value) |
| { |
| if (attributes== null) |
| attributes = new Attributes(); |
| attributes.set(name, value); |
| return (T)this; |
| } |
| |
| /** |
| * Returns the list of options for this column |
| * containing all possible field values. |
| * |
| * @return the list of options |
| */ |
| @Override |
| public synchronized Options getOptions() |
| { |
| if (options != null) |
| return options; |
| // Otherwise ask expression |
| DBColumn column = getUpdateColumn(); |
| if (column==null || column==this) |
| return null; |
| return column.getOptions(); |
| } |
| |
| /** |
| * Sets the options for this column indicating all valid values. |
| * |
| * @param options the list of options |
| */ |
| @SuppressWarnings("unchecked") |
| public synchronized <T extends DBColumnExpr> T setOptions(Options options) |
| { |
| this.options = options; |
| return (T)this; |
| } |
| |
| /** |
| * Returns the title attribute. |
| * |
| * @return the column title |
| */ |
| @Override |
| public final String getTitle() |
| { |
| Object title = getAttribute(DBCOLATTR_TITLE); |
| return StringUtils.toString(title); |
| } |
| |
| /** |
| * Sets the title attribute. |
| * |
| * @param title the column title |
| */ |
| public <T extends DBColumnExpr> T setTitle(String title) |
| { |
| return setAttribute(DBCOLATTR_TITLE, title); |
| } |
| |
| /** |
| * Returns the column control type. |
| * The control type is a client specific name for the type of input control |
| * that should be used to display and edit values for this column. |
| * |
| * @return the column control type |
| */ |
| @Override |
| public final String getControlType() |
| { |
| Object type = getAttribute(DBCOLATTR_TYPE); |
| return StringUtils.toString(type); |
| } |
| |
| /** |
| * Sets the controlType attribute. |
| * |
| * @param controlType the column control type |
| */ |
| public final <T extends DBColumnExpr> T setControlType(String controlType) |
| { |
| return setAttribute(DBCOLATTR_TYPE, controlType); |
| } |
| |
| /** |
| * Gets the Java bean property name for this column |
| * i.e. ID = employeeId |
| * DATE_OF_BIRTH = dateOfBirth |
| * |
| * @return the name of the bean property used to get and set values |
| */ |
| @Override |
| public synchronized String getBeanPropertyName() |
| { |
| if (beanPropertyName==null) |
| { // Compute bean property name |
| String name = getName(); |
| if (name==null) |
| return null; // no name provided! |
| // compute name |
| name = name.toLowerCase(); |
| String res = ""; |
| int beg=0; |
| while (beg<name.length()) |
| { |
| int end = name.indexOf('_', beg); |
| if (end<0) |
| end = name.length(); |
| // assemble |
| if (end>beg) |
| { |
| if (beg==0) |
| { // begin with all lower cases |
| res = name.substring(beg, end); |
| } |
| else |
| { // add word where first letter is upper case |
| res += name.substring(beg, beg+1).toUpperCase(); |
| if (end-beg>1) |
| { |
| res += name.substring(beg+1, end); |
| } |
| } |
| } |
| // next |
| beg = end + 1; |
| } |
| // Result |
| beanPropertyName = res; |
| } |
| return beanPropertyName; |
| } |
| |
| /** |
| * Sets the Java bean property name for this column. |
| * |
| * @param propertyName |
| */ |
| @SuppressWarnings("unchecked") |
| public synchronized <T extends DBColumnExpr> T setBeanPropertyName(String propertyName) |
| { |
| this.beanPropertyName = propertyName; |
| return (T)this; |
| } |
| |
| /** |
| * Creates a new DBConcatExpr object with the specified value. |
| * |
| * @param value an Object value |
| * @return the new DBConcatExpr object |
| */ |
| public DBColumnExpr append(Object value) |
| { |
| return new DBConcatExpr(this, value); |
| } |
| |
| /** |
| * creates a new DBAliasExpr which renames the current expression to the supplied name. |
| * <P> |
| * @param alias the alias name |
| * @return the new DBAliasExpr object |
| */ |
| public DBColumnExpr as(String alias) |
| { |
| return new DBAliasExpr(this, alias); |
| } |
| |
| /** |
| * creates a new DBAliasExpr which renames the current expression to the name of the supplied column. |
| * <P> |
| * @param column the column whose name serves as an alias for the current expression |
| * @return the new DBAliasExpr object |
| */ |
| public final DBColumnExpr as(DBColumn column) |
| { |
| return as(column.getName()); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the given comparison operator and value. |
| * |
| * @param op the comparison operator |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public DBCompareColExpr cmp(DBCmpType op, Object value) |
| { |
| return new DBCompareColExpr(this, op, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "like" operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr like(Object value) |
| { |
| return cmp(DBCmpType.LIKE, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "like" operator. |
| * By converting column value and comparison value to upper case |
| * the like is effectively case insensitive. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public DBCompareColExpr likeUpper(String value) |
| { |
| DBValueExpr expr = new DBValueExpr(getDatabase(), value, DataType.VARCHAR); |
| return new DBCompareColExpr(this.upper(), DBCmpType.LIKE, expr.upper()); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "like" operator. |
| * By converting column value and comparison value to lower case |
| * the like is effectively case insensitive. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public DBCompareColExpr likeLower(String value) |
| { |
| DBValueExpr expr = new DBValueExpr(getDatabase(), value, DataType.VARCHAR); |
| return new DBCompareColExpr(this.lower(), DBCmpType.LIKE, expr.lower()); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "like" operator. |
| * |
| * @param value the Object value |
| * @param escape the escape character |
| * @return the new DBCompareColExpr object |
| */ |
| public DBCompareColExpr like(String value, char escape) |
| { |
| DBValueExpr textExpr = new DBValueExpr(getDatabase(), value, DataType.VARCHAR); |
| DBFuncExpr escapeExpr = new DBFuncExpr(textExpr, DBSqlPhrase.SQL_FUNC_ESCAPE, new Object[] { String.valueOf(escape) }, DataType.VARCHAR); |
| return cmp(DBCmpType.LIKE, escapeExpr); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "not like" operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr notLike(Object value) |
| { |
| return cmp(DBCmpType.NOTLIKE, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object for the SQL "=" (equal) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr is(Object value) |
| { |
| return cmp(DBCmpType.EQUAL, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL "<>" (not equal) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isNot(Object value) |
| { |
| return cmp(DBCmpType.NOTEQUAL, value); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "in" operator. |
| * |
| * @param list the values to compare this column with |
| * @return a DBCompareColExpr for the "in" operator |
| */ |
| public final DBCompareColExpr in(Collection<?> values) |
| { |
| if (values==null || values.isEmpty()) |
| return cmp(DBCmpType.EQUAL, null); |
| // create expression |
| return cmp(DBCmpType.IN, values); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "in" operator. |
| * |
| * @param values the values to compare this column with |
| * @return a DBCompareColExpr for the "in" operator |
| */ |
| @SafeVarargs |
| public final <T> DBCompareColExpr in(T... values) |
| { |
| if (values==null || values.length==0) |
| return cmp(DBCmpType.EQUAL, null); |
| // create expression |
| return cmp(DBCmpType.IN, values); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "not in" operator. |
| * |
| * @param expr a database expression to provide a list of values |
| * @return a DBCompareColExpr for the "not in" operator |
| */ |
| public final DBCompareColExpr in(DBExpr expr) |
| { |
| return cmp(DBCmpType.IN, expr); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "not in" operator. |
| * |
| * @param values the values to compare this column with |
| * @return a DBCompareColExpr for the "not in" operator |
| */ |
| public final DBCompareColExpr notIn(Collection<?> values) |
| { |
| if (values==null || values.isEmpty()) |
| return cmp(DBCmpType.NOTEQUAL, null); |
| // create expression |
| return cmp(DBCmpType.NOTIN, values); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "not in" operator. |
| * |
| * @param values the values to compare this column with |
| * @return a DBCompareColExpr for the "not in" operator |
| */ |
| public final DBCompareColExpr notIn(Object... values) |
| { |
| if (values==null || values.length==0) |
| return cmp(DBCmpType.NOTEQUAL, null); |
| // create expression |
| return cmp(DBCmpType.NOTIN, values); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "not in" operator. |
| * |
| * @param expr a database expression to provide a list of values |
| * @return a DBCompareColExpr for the "not in" operator |
| */ |
| public final DBCompareColExpr notIn(DBExpr expr) |
| { |
| return cmp(DBCmpType.NOTIN, expr); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL "between" operator. |
| * |
| * @param minValue the minimum value |
| * @param maxValue the maximum value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isBetween(Object minValue, Object maxValue) |
| { |
| return cmp(DBCmpType.BETWEEN, new Object[] { minValue, maxValue }); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL "not between" operator. |
| * |
| * @param minValue the minimum value |
| * @param maxValue the maximum value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isNotBetween(Object minValue, Object maxValue) |
| { |
| return cmp(DBCmpType.NOTBETWEEN, new Object[] { minValue, maxValue }); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL ">" (greater than) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isGreaterThan(Object value) |
| { |
| return cmp(DBCmpType.GREATERTHAN, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL ">=" (greater or equal) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isMoreOrEqual(Object value) |
| { |
| return cmp(DBCmpType.MOREOREQUAL, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL "<=" (less or equal) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isLessOrEqual(Object value) |
| { |
| return cmp(DBCmpType.LESSOREQUAL, value); |
| } |
| |
| /** |
| * Creates and returns a new comparison object |
| * for the SQL "<" (less than) operator. |
| * |
| * @param value the Object value |
| * @return the new DBCompareColExpr object |
| */ |
| public final DBCompareColExpr isSmallerThan(Object value) |
| { |
| return cmp(DBCmpType.LESSTHAN, value); |
| } |
| |
| // ------- calculations ------- |
| |
| /** |
| * Creates and returns a new calculation object |
| * for the SQL "*" (multiply) operator. |
| * |
| * @param value the multiply value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr multiplyWith(Object value) |
| { |
| return new DBCalcExpr(this, "*", value); |
| } |
| |
| /** |
| * Creates and returns a new calculation object |
| * for the SQL "/" (divide) operator. |
| * |
| * @param value the divide value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr divideBy(Object value) |
| { |
| return new DBCalcExpr(this, "/", value); |
| } |
| |
| /** |
| * Creates and returns a new calculation object |
| * for the SQL "+" (plus) operator. |
| * |
| * @param value the summate value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr plus(Object value) |
| { |
| return new DBCalcExpr(this, "+", value); |
| } |
| |
| /** |
| * Creates and returns a new calculation object |
| * for the SQL "-" (minus) operator. |
| * |
| * @param value the subtract value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr minus(Object value) |
| { |
| return new DBCalcExpr(this, "-", value); |
| } |
| |
| /** |
| * Creates and returns a new calculation object |
| * for either the SQL "+" (plus) or "-" (minus) operator |
| * depending on whether the supplied integer value is positive or negative. |
| * |
| * @param value the subtract value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr plus(int value) |
| { |
| return (value >= 0) ? new DBCalcExpr(this, "+", new Integer(value)) : new DBCalcExpr(this, "-", new Integer(-value)); |
| } |
| |
| /** |
| * Creates and returns a new calculation object |
| * for either the SQL "+" (plus) or "-" (minus) operator |
| * depending on whether the supplied integer value is negative or positive. |
| * |
| * @param value the subtract value |
| * @return the new DBCalcExpr object |
| */ |
| public DBCalcExpr minus(int value) |
| { |
| return (value >= 0) ? new DBCalcExpr(this, "-", new Integer(value)) : new DBCalcExpr(this, "+", new Integer(-value)); |
| } |
| |
| // ---------------------------------------------------------- |
| // --------------------- DBFuncExpr ------------------------- |
| // ---------------------------------------------------------- |
| |
| /** |
| * Creates a new DBFuncExpr from a given SQL-PRHASE and |
| * optional additional parameters. |
| * |
| * @param phrase the id of the SQL-phrase |
| * @param params the params to replace in the template |
| * @param isAggregate indicates whether the Function creates an aggregate |
| * @param dataType the resulting data Type |
| * @return the new DBCalcExpr object |
| */ |
| protected DBColumnExpr getExprFromPhrase(DBSqlPhrase phrase, Object[] params, DataType dataType) |
| { |
| return new DBFuncExpr(this, phrase, params, dataType); |
| } |
| |
| protected DBColumnExpr getExprFromPhrase(DBSqlPhrase phrase, Object[] params) |
| { |
| return getExprFromPhrase(phrase, params, getDataType()); |
| } |
| |
| /** |
| * Creates and returns a function object which |
| * encloses the current expression in parenthesis. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr parenthesis() |
| { |
| return new DBParenthesisExpr(this); |
| } |
| |
| /** |
| * Creates a sql-expression for the nvl() or coalesce() function. |
| * |
| * @param nullValue the Object value |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr coalesce(Object nullValue) |
| { |
| return new DBCoalesceExpr(this, nullValue); |
| } |
| |
| /** |
| * Creates a sql-expression for the modulo or mod() function. |
| * |
| * @param divisor the Object value |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr modulo(Object divisor) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MODULO, new Object[] { divisor }); |
| } |
| |
| /** |
| * Creates a sql-expression for the nvl() or coalesce() function. |
| * |
| * @param nullValue the int value |
| * @return the new DBFuncExpr object |
| * |
| * @deprecated Outdated oracle syntax - use coalesce instead |
| */ |
| @Deprecated |
| public DBColumnExpr nvl(Object nullValue) |
| { |
| return coalesce(nullValue); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the substring(...) function. |
| * |
| * @param pos the position number of the string |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(DBExpr pos) |
| { // Get Expression |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_SUBSTRING, new Object[] { pos }); |
| } |
| |
| /** |
| * Overloaded. @see substring(DBExpr pos) |
| * |
| * @param pos the position number of the string |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(int pos) |
| { |
| return substring(new DBValueExpr(getDatabase(), pos, DataType.INTEGER)); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the substring(...) function. |
| * |
| * @param pos the position number of the string |
| * @param count the length of the substring |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(DBExpr pos, DBExpr count) |
| { // Get Expression |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_SUBSTRINGEX, new Object[] { pos, count }); |
| } |
| |
| /** |
| * Overloaded. @see substring(DBExpr pos, DBExpr count) |
| * |
| * @param pos the position number of the string |
| * @param count the length of the substring |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(DBExpr pos, int count) |
| { |
| return substring(pos, new DBValueExpr(getDatabase(), count, DataType.INTEGER)); |
| } |
| |
| /** |
| * Overloaded. @see substring(DBExpr pos, DBExpr count) |
| * |
| * @param pos the position number of the string |
| * @param count the length of the substring |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(int pos, DBExpr count) |
| { |
| return substring(new DBValueExpr(getDatabase(), pos, DataType.INTEGER), count); |
| } |
| |
| /** |
| * Overloaded. @see substring(DBExpr pos, DBExpr count) |
| * |
| * @param pos the position number of the string |
| * @param count the length of the substring |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr substring(int pos, int count) |
| { |
| return substring(new DBValueExpr(getDatabase(), pos, DataType.INTEGER), |
| new DBValueExpr(getDatabase(), count, DataType.INTEGER)); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the replace(...) function. |
| * |
| * @param match string to replace |
| * @param replace string with replacement |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr replace(Object match, Object replace) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_REPLACE, new Object[] { match, replace }); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the reverse(...) function. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr reverse() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_REVERSE, null); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the trim() function. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr trim() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_TRIM, null); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the ltrim() function. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr trimLeft() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_LTRIM, null); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the rtrim() function. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr trimRight() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_RTRIM, null); |
| } |
| |
| /** |
| * Creates and returns a function object which |
| * converts the current expression to upper case. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr upper() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_UPPER, null); |
| } |
| |
| /** |
| * Creates and returns a function object which |
| * converts the current expression to lower case. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr lower() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_LOWER, null); |
| } |
| |
| /** |
| * Formats a column-expression using a format string |
| * This function is intended for formatting numbers. |
| * Formatting any other data types may not supported and be database specific |
| * @param format the format string. Beware: This is passed to the database "as is" and hence may be database specific. |
| * @return a string expression representing the formatted value |
| */ |
| public DBColumnExpr format(String format) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_FORMAT, new Object[] { format }, DataType.VARCHAR); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that returns the string length of this expression. |
| |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr length() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_LENGTH, null, DataType.INTEGER); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that returns the position of a string in the current column expression. |
| * |
| * @param str the string to find the position of |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr indexOf(Object str) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_STRINDEX, new Object[] { str }, DataType.INTEGER); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that returns the position of a string in the current column expression. |
| * |
| * @param str the string to find the position of |
| * @param fromPos the start position for the search |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr indexOf(Object str, DBExpr fromPos) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_STRINDEXFROM, new Object[] { str, fromPos }, DataType.INTEGER); |
| } |
| |
| /** |
| * Overloaded. @see indexOf(Object str, DBExpr fromPos) |
| * |
| * @param str the string to find the position of |
| * @param fromPos the start position for the search |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr indexOf(Object str, int fromPos) |
| { |
| return indexOf(str, new DBValueExpr(getDatabase(), fromPos, DataType.INTEGER)); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the absolute abs() function. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr abs() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_ABS, null); |
| } |
| |
| /** |
| * Create and returns an expression for the SQL-function floor() |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr floor() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_FLOOR, null); |
| } |
| |
| /** |
| * Create and returns an expression for the SQL-function ceil() |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr ceiling() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_CEILING, null); |
| } |
| |
| /** |
| * Creates and returns an function object that |
| * rounds a number espression with the given decimals. |
| * |
| * @param decimals the number of decimal to which to truncate the current value |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr round(int decimals) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_ROUND, new Object[] { new Integer(decimals) }); |
| } |
| |
| /** |
| * Creates and returns an function object that |
| * truncates a number espression with the given decimals. |
| * |
| * @param decimals the number of decimal to which to truncate the current value |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr trunc(int decimals) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_TRUNC, new Object[] { new Integer(decimals) }); |
| } |
| |
| /** |
| * Creates and returns an function object that |
| * calculates the year of a date value. |
| * |
| * @return the new DBColumnExpr object |
| */ |
| public DBColumnExpr year() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_YEAR, null); |
| } |
| |
| /** |
| * Creates and returns an function object that |
| * calculates the month of a date value. |
| * |
| * @return the new DBColumnExpr object |
| */ |
| public DBColumnExpr month() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MONTH, null); |
| } |
| |
| /** |
| * Creates and returns an function object that |
| * calculates the day of a date value. |
| * |
| * @return the new DBColumnExpr object |
| */ |
| public DBColumnExpr day() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_DAY, null); |
| } |
| |
| /** |
| * Creates and returns an aggregation function object |
| * which calculates the sum for the current expression over a group of rows. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr sum() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_SUM, null); |
| } |
| |
| /** |
| * Creates and returns an aggregation function object |
| * which returns the minimum value for the current expression over a group of rows. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr min() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MIN, null); |
| } |
| |
| /** |
| * Creates and returns an aggregation function object |
| * which returns the maximum value for the current expression over a group of rows. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr max() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MAX, null); |
| } |
| |
| /** |
| * Creates and returns an aggregation function object |
| * which returns the average value for the current expression over a group of rows. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr avg() |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_AVG, null); |
| } |
| |
| /** |
| * Creates and returns string aggregation expression |
| * @param separator the separator between string |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr strAgg(String separator) |
| { |
| return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_STRAGG, new Object[] { separator }); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "count()" function |
| * which returns the number of rows in the result set. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr count() |
| { |
| return new DBCountExpr(this, false); |
| } |
| |
| /** |
| * Creates and returns an expression for the SQL "count()" function |
| * which returns the number of unique values in the result set. |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr countDistinct() |
| { |
| return new DBCountExpr(this, true); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that maps enum values by name or ordinal to their string representation |
| * |
| * @param enumType an enumType to decode |
| * @param otherwise the varchar value to take if no key matches the given expression |
| * @return a DBDecodeExpr object |
| */ |
| public DBColumnExpr decodeEnum(Class<? extends Enum<?>> enumType, String otherwise) |
| { |
| if (enumType==null || !enumType.isEnum()) |
| throw new InvalidArgumentException("enumType", enumType); |
| // create map |
| boolean byOrdinal = getDataType().isNumeric(); |
| Enum<?>[] items = enumType.getEnumConstants(); |
| Map<Object, String> enumMap = new LinkedHashMap<Object, String>(items.length); |
| for (int i=0; i<items.length; i++) |
| { // key: ordinal (for numeric columns) or name (for CHAR columns) |
| Object key = ObjectUtils.getEnumValue(items[i], byOrdinal); |
| enumMap.put(key, items[i].toString()); |
| } |
| // Create the decode function |
| return new DBDecodeExpr(this, enumMap, otherwise, DataType.VARCHAR); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that maps enum values from name to ordinal |
| * This is useful for sorting. |
| * e.g. cmd.orderBy(SOME_EXPR.decodeSort(MyEnum.class, true) |
| * |
| * @param enumType an enumType to decode |
| * @param defaultToEnd true if non matching values (e.g. NULL) should be assigned the highest number, otherwise they get the lowest number |
| * @return a DBDecodeExpr object |
| */ |
| public DBColumnExpr decodeSort(Class<? extends Enum<?>> enumType, boolean defaultToEnd) |
| { |
| if (enumType==null || !enumType.isEnum()) |
| throw new InvalidArgumentException("enumType", enumType); |
| // create map |
| Enum<?>[] items = enumType.getEnumConstants(); |
| Map<String, Integer> enumMap = new LinkedHashMap<String, Integer>(items.length); |
| int sortOffset = (defaultToEnd ? 0 : 1); |
| for (int i=0; i<items.length; i++) |
| { |
| int sortValue = items[i].ordinal(); |
| String value = ObjectUtils.getString(items[i]); |
| enumMap.put(value, sortValue + sortOffset); |
| } |
| // Create the decode function |
| int defaultValue = (defaultToEnd ? items.length : 0); |
| return new DBDecodeExpr(this, enumMap, defaultValue, DataType.INTEGER); |
| } |
| |
| /** |
| * Creates and returns a sql-expression that compares the current column expression with |
| * a list of values and returns the corresponding alternative value.<BR> |
| * |
| * @param valueMap a list of key values pairs used for decoding |
| * @param otherwise the value to take if no key matches the given expression |
| * @return a DBDecodeExpr object |
| */ |
| public DBColumnExpr decode(Map<?,?> valueMap, Object otherwise) |
| { |
| // Detect data type |
| DataType dataType = DataType.UNKNOWN; |
| if (otherwise!=null) |
| { |
| dataType = getDatabase().detectDataType(otherwise); |
| } |
| if (dataType==DataType.UNKNOWN) |
| { |
| for (Object v : valueMap.values()) |
| { |
| dataType = getDatabase().detectDataType(v); |
| if (dataType!=DataType.UNKNOWN) |
| break; |
| } |
| } |
| // Create the decode function |
| return new DBDecodeExpr(this, valueMap, otherwise, dataType); |
| } |
| |
| public final DBColumnExpr decode(Object key1, Object value1, Object otherwise) |
| { |
| Map<Object, Object> list = new HashMap<Object, Object>(); |
| list.put(key1, value1); |
| return decode(list, otherwise); |
| } |
| |
| public final DBColumnExpr decode(Object key1, Object value1, Object key2, Object value2, Object otherwise) |
| { |
| Map<Object, Object> list = new HashMap<Object, Object>(); |
| list.put(key1, value1); |
| list.put(key2, value2); |
| return decode(list, otherwise); |
| } |
| |
| public final DBColumnExpr decode(Object key1, Object value1, Object key2, Object value2, Object key3, Object value3, |
| Object otherwise) |
| { |
| Map<Object, Object> list = new HashMap<Object, Object>(); |
| list.put(key1, value1); |
| list.put(key2, value2); |
| list.put(key3, value3); |
| return decode(list, otherwise); |
| } |
| |
| public final DBColumnExpr decode(Options options, Object otherwise) |
| { |
| int size = options.size() + (otherwise!=null ? 1 : 0); |
| Map<Object, Object> list = new HashMap<Object, Object>(size); |
| for (OptionEntry e : options) |
| list.put(e.getValue(), e.getText()); |
| return decode(list, otherwise); |
| } |
| |
| public final DBColumnExpr decode(Options options) |
| { |
| return decode(options, null); |
| } |
| |
| /** |
| * Creates and returns a sql-expression for the SQL case-phrase.<br> |
| * The result will be in the form:<br> |
| * "case when [compExpr] then [this] else [otherwise] end" |
| * |
| * @param compExpr the condition for which the current column expression is returned |
| * @param otherwise the value that is returned if the condition is false |
| * @return a DBCaseExpr representing the SQL case phrase. |
| */ |
| public final DBCaseExpr when(DBCompareExpr compExpr, Object otherwise) |
| { |
| DBColumnExpr elseExpr = null; |
| if (otherwise instanceof DBColumnExpr) |
| { // A column Expression |
| elseExpr = (DBColumnExpr)otherwise; |
| } |
| else if (otherwise != null) |
| { // A constant value |
| elseExpr = new DBValueExpr(getDatabase(), otherwise, getDataType()); |
| } |
| // Create DBCaseExpr |
| return new DBCaseExpr(compExpr, this, elseExpr); |
| } |
| |
| // ---------------------------------------------------------- |
| // --------------------- Conversion ------------------------- |
| // ---------------------------------------------------------- |
| |
| /** |
| * Creates a new DBFuncExpr object (to_char SQL statement) |
| * with the parameters prefix = "to_char(" and postfix = ")" |
| * |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr toChar() |
| { |
| return convertTo(DataType.VARCHAR); |
| } |
| |
| /** |
| * Creates a new DBFuncExpr object (to_char SQL statement) |
| * with the parameters prefix = "to_char(" and postfix = ", * |
| * '"+format+"')" |
| * |
| * @param format the string value |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr toChar(String format) |
| { |
| return convertTo(DataType.VARCHAR, format); |
| } |
| |
| /** |
| * Creates a new DBFuncExpr object that will convert |
| * the current column to the destination data type specified. |
| * |
| * @param dataType the destination data type |
| * @param format optional destination format (usually a string) |
| * @return the new DBFuncExpr object |
| */ |
| public DBColumnExpr convertTo(DataType dataType, Object format) |
| { |
| return new DBConvertExpr(this, dataType, format); |
| } |
| |
| /** |
| * Creates and returns a new DBFuncExpr object that will |
| * convert the current column to the destination data type specified. |
| * |
| * @param dataType Data type to which to convert the current expression to. |
| * @return the new DBFuncExpr object |
| */ |
| public final DBColumnExpr convertTo(DataType dataType) |
| { |
| return convertTo(dataType, null); |
| } |
| |
| /** |
| * creates a new DBOrderByExpr for ascending order |
| * <P> |
| * @return the new DBOrderByExpr object for ascending order |
| */ |
| public DBOrderByExpr asc() |
| { |
| return new DBOrderByExpr(this, false); |
| } |
| |
| /** |
| * creates a new DBOrderByExpr for descending order |
| * <P> |
| * @return the new DBOrderByExpr object for descending order |
| */ |
| public DBOrderByExpr desc() |
| { |
| return new DBOrderByExpr(this, true); |
| } |
| |
| /** |
| * concatenates a list of expressions to the current column |
| * @param concatExprs the expressions to concat |
| * @return the concat expression |
| */ |
| public DBColumnExpr concat(DBColumnExpr... concatExprs) |
| { |
| return new DBConcatFuncExpr(this, concatExprs); |
| } |
| |
| /** |
| * concatenates a list of expressions to the current column |
| * @param separator a string to insert between each of the expressions |
| * @param concatExprs the expressions to concat |
| * @return the concat expression |
| */ |
| public DBColumnExpr concat(String separator, DBColumnExpr... concatExprs) |
| { |
| return new DBConcatFuncExpr(this, separator, concatExprs); |
| } |
| |
| /** |
| * returns a corresponding Java type for this expression |
| * @param expr |
| * @return |
| */ |
| public Class<?> getJavaType() |
| { |
| return this.getDatabase().getColumnJavaType(this); |
| } |
| |
| /** |
| * For Debugging |
| */ |
| @Override |
| public String toString() |
| { |
| String name = getName(); |
| if (StringUtils.isNotEmpty(name)) |
| return getClass().getSimpleName() + "[" + name + "]"; |
| // default |
| return super.toString(); |
| } |
| } |
