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 2012/05/23 23:28:56 UTC
svn commit: r1342058 - in
/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db:
DBCommand.java expr/join/DBJoinExpr.java expr/set/DBSetExpr.java
oracle/DBCommandOracle.java
Author: doebele
Date: Wed May 23 21:28:56 2012
New Revision: 1342058
URL: http://svn.apache.org/viewvc?rev=1342058&view=rev
Log:
EMPIREDB-144
allow Join with Update Statements (see DBCommand.getUpdate())
Modified:
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/set/DBSetExpr.java
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
Modified: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java?rev=1342058&r1=1342057&r2=1342058&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java (original)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java Wed May 23 21:28:56 2012
@@ -929,19 +929,30 @@ public abstract class DBCommand extends
if (set == null)
return null;
StringBuilder buf = new StringBuilder("UPDATE ");
- // addTableExpr(buf, CTX_NAME);
DBRowSet table = set.get(0).getTable();
- table.addSQL(buf, CTX_FULLNAME);
- // Set Expressions
- buf.append("\r\nSET ");
- addListExpr(buf, set, CTX_DEFAULT, ", ");
- // Constraints
- if (where != null)
- { // add where condition
- buf.append("\r\nWHERE ");
- if (where != null)
- addListExpr(buf, where, CTX_NAME | CTX_VALUE, " AND ");
+ if ( joins!=null )
+ { // Join Update
+ buf.append( table.getAlias() );
+ long context = CTX_DEFAULT;
+ // Set Expressions
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, context, ", ");
+ // From clause
+ addFrom(buf);
+ // Add Where
+ addWhere(buf, context);
+ }
+ else
+ { // Simple Statement
+ table.addSQL(buf, CTX_FULLNAME);
+ long context = CTX_NAME | CTX_VALUE;
+ // Set Expressions
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, context, ", ");
+ // Add Where
+ addWhere(buf, context);
}
+ // done
return buf.toString();
}
@@ -1043,7 +1054,7 @@ public abstract class DBCommand extends
// Join
boolean sep = false;
List<DBRowSet> tables = getTableList();
- if (joins!=null )
+ if (joins!=null && joins.size()>0)
{ // Join
List<DBRowSet> joinTables = new ArrayList<DBRowSet>();
// for (int i=0;i<joins.size();i++)
@@ -1089,16 +1100,21 @@ public abstract class DBCommand extends
}
}
- protected void addWhere(StringBuilder buf)
+ protected void addWhere(StringBuilder buf, long context)
{
if (where != null)
{
buf.append("\r\nWHERE ");
// add where expression
- addListExpr(buf, where, CTX_DEFAULT, " AND ");
+ addListExpr(buf, where, context, " AND ");
}
}
+ protected final void addWhere(StringBuilder buf)
+ {
+ addWhere(buf, CTX_DEFAULT);
+ }
+
protected void addGrouping(StringBuilder buf)
{
if (groupBy != null)
Modified: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java?rev=1342058&r1=1342057&r2=1342058&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java (original)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java Wed May 23 21:28:56 2012
@@ -163,6 +163,15 @@ public class DBJoinExpr extends DBExpr
}
/**
+ * Returns any additional constraints to the join
+ * @return a compare expression containing additional constraints or null
+ */
+ public DBCompareExpr getWhere()
+ {
+ return compExpr;
+ }
+
+ /**
* This function adds an additional constraint to the join.
*
* @param expr the compare expression
@@ -178,12 +187,15 @@ public class DBJoinExpr extends DBExpr
* @param c1 the first column
* @param c2 the second column
*
- * @return the compare expression
+ * @return the object itself
*/
- public DBCompareExpr and(DBColumnExpr c1, DBColumnExpr c2)
- { // Set Compare Expression
- compExpr = c1.is(c2);
- return compExpr;
+ public DBJoinExpr and(DBColumnExpr c1, DBColumnExpr c2)
+ { // Set Compare Expression
+ if (compExpr==null)
+ compExpr = c1.is(c2);
+ else
+ compExpr = compExpr.and(c1.is(c2));
+ return this;
}
/**
Modified: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/set/DBSetExpr.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/set/DBSetExpr.java?rev=1342058&r1=1342057&r2=1342058&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/set/DBSetExpr.java (original)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/expr/set/DBSetExpr.java Wed May 23 21:28:56 2012
@@ -123,6 +123,6 @@ public class DBSetExpr extends DBExpr
if ((context & CTX_NAME) != 0 && (context & CTX_VALUE) != 0)
buf.append("=");
if ((context & CTX_VALUE) != 0)
- buf.append(getObjectValue(column.getDataType(), value, CTX_NAME | CTX_VALUE, "+"));
+ buf.append(getObjectValue(column.getDataType(), value, context, "+"));
}
}
Modified: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java?rev=1342058&r1=1342057&r2=1342058&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java (original)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java Wed May 23 21:28:56 2012
@@ -19,11 +19,19 @@
package org.apache.empire.db.oracle;
// Imports
+import java.util.HashSet;
+import java.util.Set;
+
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBIndex;
+import org.apache.empire.db.DBRowSet;
import org.apache.empire.db.DBTable;
import org.apache.empire.db.expr.compare.DBCompareExpr;
+import org.apache.empire.db.expr.join.DBJoinExpr;
+import org.apache.empire.db.expr.set.DBSetExpr;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ObjectNotValidException;
@@ -124,9 +132,9 @@ public class DBCommandOracle extends DBC
}
/**
- * Creates the SQL statement the special characteristics of
- * the Oracle database are supported.
- *
+ * Creates an Oracle specific select statement
+ * that supports special features of the Oracle DBMS
+ * like e.g. CONNECT BY PRIOR
* @param buf the SQL statement
*/
@Override
@@ -175,10 +183,82 @@ public class DBCommandOracle extends DBC
addListExpr(buf, orderBy, CTX_DEFAULT, ", ");
}
}
+
+ /**
+ * Creates an Oracle specific update statement.
+ * If a join is required, this method creates a "MERGE INTO" expression
+ */
+ @Override
+ public synchronized String getUpdate()
+ {
+ // No Joins: Use Default
+ if (joins==null || set==null)
+ return super.getUpdate();
+ // Generate Merge expression
+ resetParamUsage();
+ StringBuilder buf = new StringBuilder("MERGE INTO ");
+ DBRowSet table = set.get(0).getTable();
+ table.addSQL(buf, CTX_FULLNAME|CTX_ALIAS);
+ // join (only one allowed yet)
+ DBJoinExpr updateJoin = null;
+ for (DBJoinExpr jex : joins)
+ { // The join
+ if (jex.isJoinOn(table)==false)
+ continue;
+ // found the join
+ updateJoin = jex;
+ break;
+ }
+ if (updateJoin==null)
+ throw new ObjectNotValidException(this);
+ Set<DBColumn> joinColumns = new HashSet<DBColumn>();
+ updateJoin.addReferencedColumns(joinColumns);
+ // using
+ buf.append("\r\nUSING ");
+ DBCommand inner = this.clone();
+ inner.clearSelect();
+ inner.clearOrderBy();
+ for (DBColumn jcol : joinColumns)
+ { // Select join columns
+ if (jcol.getRowSet()!=table)
+ inner.select(jcol);
+ }
+ for (DBSetExpr sex : set)
+ { // Select set expressions
+ Object val = sex.getValue();
+ if (val instanceof DBColumnExpr)
+ inner.select(((DBColumnExpr)val));
+ }
+ inner.removeJoinsOn(table);
+ inner.addSQL(buf, CTX_DEFAULT);
+ // find the source table
+ DBColumnExpr left = updateJoin.getLeft();
+ DBColumnExpr right = updateJoin.getRight();
+ DBRowSet source = right.getUpdateColumn().getRowSet();
+ if (source==table)
+ source = left.getUpdateColumn().getRowSet();
+ // add Alias
+ buf.append(" ");
+ buf.append(source.getAlias());
+ buf.append("\r\nON (");
+ left.addSQL(buf, CTX_DEFAULT);
+ buf.append(" = ");
+ right.addSQL(buf, CTX_DEFAULT);
+ // Compare Expression
+ if (updateJoin.getWhere() != null)
+ { buf.append(" AND ");
+ updateJoin.getWhere().addSQL(buf, CTX_DEFAULT);
+ }
+ // Set Expressions
+ buf.append(")\r\nWHEN MATCHED THEN UPDATE ");
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, CTX_DEFAULT, ", ");
+ // done
+ return buf.toString();
+ }
/**
- * Creates the delete SQL-Command.
- *
+ * Creates an Oracle specific delete statement.
* @return the delete SQL-Command
*/
@Override