You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by do...@apache.org on 2020/01/09 10:56:32 UTC
[empire-db] branch master updated: EMPIREDB-324 caseWhen with
multiple conditions and results
This is an automated email from the ASF dual-hosted git repository.
doebele pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/empire-db.git
The following commit(s) were added to refs/heads/master by this push:
new d4b0992 EMPIREDB-324 caseWhen with multiple conditions and results
d4b0992 is described below
commit d4b0992404e667d813e0b1c6288a7f4016be5841
Author: Rainer Döbele <do...@apache.org>
AuthorDate: Thu Jan 9 11:56:28 2020 +0100
EMPIREDB-324
caseWhen with multiple conditions and results
---
.../main/java/org/apache/empire/db/DBDatabase.java | 7 +
.../empire/db/expr/column/DBCaseWhenExpr.java | 174 +++++++++++++++++++++
2 files changed, 181 insertions(+)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java b/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
index 35bec7c..089bf46 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
@@ -46,6 +46,7 @@ import org.apache.empire.db.exceptions.EmpireSQLException;
import org.apache.empire.db.exceptions.QueryFailedException;
import org.apache.empire.db.exceptions.QueryNoResultException;
import org.apache.empire.db.exceptions.StatementFailedException;
+import org.apache.empire.db.expr.column.DBCaseWhenExpr;
import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.compare.DBCompareExpr;
import org.apache.empire.exceptions.InternalException;
@@ -1700,6 +1701,12 @@ public abstract class DBDatabase extends DBObject
DBColumnExpr trueExpr = ((trueValue instanceof DBColumnExpr) ? (DBColumnExpr)trueValue : this.getValueExpr(trueValue, dataType));
return trueExpr.when(condition, falseValue);
}
+
+
+ public DBColumnExpr caseWhen(Map<DBCompareExpr, DBColumnExpr> whenMap, DBColumnExpr elseValue)
+ {
+ return new DBCaseWhenExpr(whenMap, elseValue);
+ }
/**
* Creates a case column expression that check whether a column or column expression is null
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCaseWhenExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCaseWhenExpr.java
new file mode 100644
index 0000000..5b0b766
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCaseWhenExpr.java
@@ -0,0 +1,174 @@
+/*
+ * 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.expr.column;
+
+import java.util.Map;
+import java.util.Set;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.expr.compare.DBCompareExpr;
+import org.apache.empire.xml.XMLUtil;
+import org.w3c.dom.Element;
+
+/**
+ * This class is used to create a SQL CASE constraint in the form of
+ * case when {cond1} then {result1}
+ * when {cond2} then {result2}
+ * ...
+ * else {defaultResult} end"
+ * <P>
+ * There is no need to explicitly create instances of this class.<BR>
+ * Instead use {@link DBDatabase#caseWhen(Map<DBCompareExpr, DBColumnExpr>, DBColumnExpr) }
+ * <P>
+ * @author doebele
+ */
+public class DBCaseWhenExpr extends DBColumnExpr
+{
+ private final static long serialVersionUID = 1L;
+
+ private final Map<DBCompareExpr, DBColumnExpr> whenMap;
+ private final DBColumnExpr elseExpr;
+
+ /**
+ * Constructs a DBCaseExpr
+ * @param whenMap a map of compareExpressions with the corresponding result values
+ * @param elseExpr the expression returned if no condition is true (may be null)
+ */
+ public DBCaseWhenExpr(Map<DBCompareExpr, DBColumnExpr> whenMap, DBColumnExpr elseExpr)
+ {
+ this.whenMap = whenMap;
+ this.elseExpr = elseExpr;
+ }
+
+ @Override
+ public DBDatabase getDatabase()
+ {
+ return getFirstColumnExpr().getDatabase();
+ }
+
+ @Override
+ public DataType getDataType()
+ {
+ DBColumnExpr cexp = getFirstColumnExpr();
+ return cexp.getDataType();
+ }
+
+ @Override
+ public String getName()
+ {
+ DBCompareExpr firstCmpExpr = whenMap.keySet().iterator().next();
+ StringBuilder b = new StringBuilder("CASE_");
+ firstCmpExpr.addSQL(b, CTX_NAME);
+ return b.toString();
+ }
+
+ @Override
+ public DBColumn getUpdateColumn()
+ {
+ return null;
+ }
+
+ @Override
+ public boolean isAggregate()
+ {
+ return getFirstColumnExpr().isAggregate();
+ }
+
+ @Override
+ public void addReferencedColumns(Set<DBColumn> list)
+ {
+ for (Map.Entry<DBCompareExpr, DBColumnExpr> entry : whenMap.entrySet())
+ {
+ if (entry.getKey()!=null)
+ entry.getKey().addReferencedColumns(list);
+ if (entry.getValue()!=null)
+ entry.getValue().addReferencedColumns(list);
+ }
+ if (elseExpr!=null)
+ elseExpr.addReferencedColumns(list);
+ }
+
+ @Override
+ public void addSQL(StringBuilder sql, long context)
+ {
+ context &= ~CTX_ALIAS; // No column aliases
+ // append case
+ if (!whenMap.isEmpty())
+ { // add case
+ sql.append("CASE");
+ for (Map.Entry<DBCompareExpr, DBColumnExpr> entry : whenMap.entrySet())
+ {
+ sql.append(" WHEN ");
+ DBCompareExpr compExpr = entry.getKey();
+ compExpr.addSQL(sql, context);
+ sql.append( " THEN ");
+ DBColumnExpr trueExpr = entry.getValue();
+ if (trueExpr!=null)
+ trueExpr.addSQL(sql, context);
+ else
+ sql.append("NULL");
+ }
+ sql.append(" ELSE ");
+ }
+ // append else
+ if (elseExpr!=null)
+ { // Else
+ elseExpr.addSQL(sql, context);
+ }
+ else
+ { // Append NULL
+ sql.append("NULL");
+ }
+ // append end
+ if (!whenMap.isEmpty())
+ {
+ sql.append(" END");
+ }
+ }
+
+ @Override
+ public Element addXml(Element parent, long flags)
+ {
+ Element elem = XMLUtil.addElement(parent, "column");
+ elem.setAttribute("name", getName());
+ // Add Other Attributes
+ if (attributes!=null)
+ attributes.addXml(elem, flags);
+ // add All Options
+ if (options!=null)
+ options.addXml(elem, flags);
+ // Done
+ elem.setAttribute("function", "case");
+ return elem;
+ }
+
+ private DBColumnExpr getFirstColumnExpr()
+ {
+ for (DBColumnExpr expr : whenMap.values())
+ {
+ if (expr!=null)
+ return expr;
+ }
+ return this.elseExpr;
+ }
+
+}