EMPIREDB-314
SQL-format function support
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
index 3c2b33e..61e6492 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
@@ -864,6 +864,18 @@
}
/**
+ * 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(DBDatabaseDriver.SQL_FUNC_FORMAT, new Object[] { format }, getUpdateColumn(), false, DataType.VARCHAR);
+ }
+
+ /**
* Creates and returns a sql-expression that returns the string length of this expression.
* @return the new DBFuncExpr object
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
index 74778b3..caffa5a 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
@@ -778,11 +778,11 @@
return;
removeConstraintOn(where, col);
}
-
+
/**
* Returns a copy of the defined joins.
*
- * @return vector of joins
+ * @return the list of joins
*/
public List<DBJoinExpr> getJoins()
{
@@ -813,9 +813,29 @@
having = new ArrayList<DBCompareExpr>();
setConstraint(having, expr);
}
+
+ /**
+ * Returns true if the command has having-constraints or false if not.
+ *
+ * @return true if constraints have been set on the command
+ */
+ public boolean hasHavingConstraints()
+ {
+ return (having!=null && having.size()>0);
+ }
+
+ /**
+ * Returns a copy of the defined having clauses.
+ *
+ * @return list of having constraints
+ */
+ public List<DBCompareExpr> getHavingConstraints()
+ {
+ return (this.having!=null ? Collections.unmodifiableList(this.having) : null);
+ }
/**
- * removes a constraint on a particular column from the where clause
+ * removes a constraint on a particular column from the having clause
* @param col the column expression for which to remove the constraint
*/
public void removeHavingConstraintOn(DBColumnExpr col)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java b/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
index 44b09c9..4aacd7b 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
@@ -94,6 +94,7 @@
public static final int SQL_FUNC_FLOOR = 123; // Oracle: floor(?)
public static final int SQL_FUNC_CEILING = 124; // Oracle: ceil(?)
public static final int SQL_FUNC_MODULO = 125; // Oracle: mod(?)
+ public static final int SQL_FUNC_FORMAT = 126; // Oracle: TO_CHAR(?)
// Date
public static final int SQL_FUNC_DAY = 132; // MSSQL: month(?)
public static final int SQL_FUNC_MONTH = 133; // MSSQL: month(?)
diff --git a/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java b/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
index e60b6a8..8eed386 100644
--- a/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
+++ b/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
@@ -220,6 +220,8 @@
case SQL_FUNC_TRUNC: return "truncate(?,{0})";
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
+ case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
index ffff472..d89fd3d 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
@@ -27,7 +27,10 @@
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.xml.XMLUtil;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
import org.w3c.dom.Element;
@@ -37,6 +40,7 @@
public abstract class DBAbstractFuncExpr extends DBColumnExpr
{
private final static long serialVersionUID = 1L;
+ private static final Logger log = LoggerFactory.getLogger(DBAbstractFuncExpr.class);
protected final DBColumnExpr expr;
protected final DBColumn updateColumn; // optional
@@ -156,13 +160,37 @@
{
// Get Template
if (params != null)
- { // Replace Params
- DataType dataType = expr.getDataType();
+ { // Replace Params
for (int i = 0; i < params.length; i++)
- { // String test =(params[i] != null) ? params[i].toString() : "";
- String value = getObjectValue(dataType, params[i], CTX_DEFAULT, ",");
+ { // Detect placeholder and data type
+ DataType paramDataType = expr.getDataType();
+ int idx;
+ String ph = "{"+String.valueOf(i);
+ if ((idx=template.indexOf(ph))>=0) {
+ // param found
+ idx += ph.length();
+ int end = template.indexOf('}', idx);
+ if (end<idx)
+ throw new InvalidArgumentException("template", template);
+ // check if type is specified
+ if (template.charAt(idx)==':')
+ { // DataType is specified
+ String typeName = ((end>=idx) ? template.substring(idx+1, end) : null);
+ DataType dataType = ((typeName!=null) ? DataType.valueOf(typeName) : null);
+ if (dataType!=null)
+ paramDataType = dataType;
+ }
+ // complete placeholder
+ ph += template.substring(idx, end+1);
+
+ } else {
+ log.warn("No placeholder found in template {} for paramter {}", template, i);
+ continue;
+ }
+ // get param and replace
+ String paramAsString = getObjectValue(paramDataType, params[i], CTX_DEFAULT, ",");
// template = template.replaceAll("\\{" + String.valueOf(i) + "\\}", value);
- template = StringUtils.replaceAll(template, "{"+ String.valueOf(i) + "}", value);
+ template = StringUtils.replaceAll(template, ph, paramAsString);
}
}
// Get Prefix and Postfix
diff --git a/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java b/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
index 5b168ba..67289ec 100644
--- a/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
+++ b/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
@@ -268,6 +268,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java b/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
index 1ae3104..72a2f03 100644
--- a/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
@@ -160,6 +160,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "TO_CHAR(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java b/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
index 7b57ba5..11c9859 100644
--- a/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
+++ b/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
@@ -1028,6 +1028,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:INTEGER})"; /* TODO: supports only decimal places. Add support for a format string */
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java b/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
index 32556df..c25e7b9 100644
--- a/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
+++ b/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
@@ -189,6 +189,7 @@
case SQL_FUNC_CEILING: return "ceil(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "TO_CHAR(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return oracle8Compatibilty ? "to_number(to_char(?,'DD'))" : "extract(day from ?)";
case SQL_FUNC_MONTH: return oracle8Compatibilty ? "to_number(to_char(?,'MM'))" : "extract(month from ?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java b/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
index ce279b5..39965e5 100644
--- a/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
@@ -368,6 +368,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "extract(day from ?)";
case SQL_FUNC_MONTH: return "extract(month from ?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java b/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
index a516c1e..d038427 100644
--- a/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
+++ b/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
@@ -349,6 +349,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "printf({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
index 4804f5c..d013245 100644
--- a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
@@ -337,6 +337,7 @@
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "((?) % {0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
