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 ea1170aa EMPIREDB-402 Improved Join definition allowing the on(...)
syntax
ea1170aa is described below
commit ea1170aac7eedb3d0c29302ff7fe8b63d9b6400d
Author: Rainer Döbele <[email protected]>
AuthorDate: Thu Mar 2 15:08:39 2023 +0100
EMPIREDB-402
Improved Join definition allowing the on(...) syntax
---
.../empire/samples/db/advanced/SampleAdvApp.java | 2 +-
.../org/apache/empire/samples/db/SampleApp.java | 14 +++----
.../java/org/apache/empire/db/DBColumnExpr.java | 17 +++++++++
.../main/java/org/apache/empire/db/DBCommand.java | 44 +++++++++++++---------
.../empire/db/expr/join/DBColumnJoinExpr.java | 31 +++++++++++++--
.../empire/db/expr/join/DBCompareJoinExpr.java | 10 +++++
.../empire/db/expr/join/DBCrossJoinExpr.java | 10 +++++
.../org/apache/empire/db/expr/join/DBJoinExpr.java | 6 +++
8 files changed, 105 insertions(+), 29 deletions(-)
diff --git
a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
index 489a2822..10081ee8 100644
---
a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
+++
b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
@@ -376,7 +376,7 @@ public class SampleAdvApp
.select (SALES.MODEL_ID.count(), SALES.PRICE.avg())
.select
(SALES.PRICE.avg().minus(MODEL.BASE_PRICE.avg()).round(2).as("DIFFERENCE"))
.join (MODEL.WMI, BRAND.WMI)
- .joinLeft(MODEL.ID, SALES.MODEL_ID, SALES.YEAR.is(2021)) // only
year 2021
+ .joinLeft(MODEL.ID.on(SALES.MODEL_ID).and(SALES.YEAR.is(2021))) //
only year 2021
.where (MODEL.ENGINE_TYPE.in(EngineType.P, EngineType.H,
EngineType.E)) // Petrol, Hybrid, Electric
.where (MODEL.BASE_PRICE.isGreaterThan(30000))
.groupBy (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE)
diff --git
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
index c7e6d88f..97cdee2c 100644
---
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
+++
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
@@ -483,8 +483,8 @@ public class SampleApp
.selectQualified(DEP.NAME) // "DEPARMENT_NAME"
.select(DEP.BUSINESS_UNIT) // "BUSINESS_UNIT"
// Joins
- .join(EMP.DEPARTMENT_ID, DEP.ID)
- .joinLeft(EMP.ID, PAY.EMPLOYEE_ID, PAY.YEAR.is(lastYear))
+ .join(EMP.DEPARTMENT_ID.on(DEP.ID))
+ .joinLeft(EMP.ID.on(PAY.EMPLOYEE_ID).and(PAY.YEAR.is(lastYear)))
// Where constraints
.where(EMP.LAST_NAME.length().isGreaterThan(0)) // always true,
just for show
.where(EMP.GENDER.in(Gender.M, Gender.F)) // always true,
just for show
@@ -609,7 +609,7 @@ public class SampleApp
DBColumnExpr DEP_TOTAL = PAY.AMOUNT.sum().as("DEP_TOTAL");
DBCommand cmdDepTotal = context.createCommand()
.select(EMP.DEPARTMENT_ID, DEP_TOTAL)
- .join (PAY.EMPLOYEE_ID, EMP.ID)
+ .join (PAY.EMPLOYEE_ID.on(EMP.ID))
.where (PAY.YEAR.is(lastYear))
.groupBy(EMP.DEPARTMENT_ID);
DBQuery Q_DEP_TOTAL = new DBQuery(cmdDepTotal, "qdt");
@@ -622,10 +622,10 @@ public class SampleApp
.select(Q_EMP_TOTAL.column(EMP_TOTAL))
.select(PCT_OF_DEP_COST.as("PCT_OF_DEPARTMENT_COST"))
// join Employee with Department
- .join(EMP.DEPARTMENT_ID, DEP.ID)
+ .join(EMP.DEPARTMENT_ID.on(DEP.ID))
// Join with Subqueries
- .joinLeft(EMP.ID, Q_EMP_TOTAL.column(PAY.EMPLOYEE_ID))
- .joinLeft(DEP.ID, Q_DEP_TOTAL.column(EMP.DEPARTMENT_ID))
+ .joinLeft(EMP.ID.on(Q_EMP_TOTAL.column(PAY.EMPLOYEE_ID)))
+ .joinLeft(DEP.ID.on(Q_DEP_TOTAL.column(EMP.DEPARTMENT_ID)))
// Order by
.orderBy(DEP.NAME.desc())
.orderBy(EMP.LAST_NAME);
@@ -671,7 +671,7 @@ public class SampleApp
* Test RecordList
*/
DBCommand cmd = context.createCommand();
- cmd.join(EMP.DEPARTMENT_ID, DEP.ID);
+ cmd.join(EMP.DEPARTMENT_ID.on(DEP.ID));
cmd.where(DEP.NAME.is("Development"));
// query now
List<DBRecordBean> list = context.getUtils().queryRecordList(cmd, EMP,
DBRecordBean.class);
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 201b50ed..f9bd0428 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
@@ -44,9 +44,11 @@ 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.join.DBColumnJoinExpr;
import org.apache.empire.db.expr.order.DBOrderByExpr;
import org.apache.empire.dbms.DBSqlPhrase;
import org.apache.empire.exceptions.InvalidArgumentException;
+import org.apache.empire.exceptions.NotSupportedException;
import org.w3c.dom.Element;
/**
@@ -1400,6 +1402,21 @@ public abstract class DBColumnExpr extends DBExpr
return new DBOrderByExpr(this, true);
}
+ /*
+ * Joins
+ */
+ public DBColumnJoinExpr on(DBColumnExpr joinWith)
+ {
+ // Must have source column
+ if (getSourceColumn()==null)
+ throw new NotSupportedException(this, "join");
+ if (joinWith.getSourceColumn()==null)
+ throw new NotSupportedException(joinWith, "join");
+ // create the expression
+ DBColumnJoinExpr join = new DBColumnJoinExpr(this, joinWith,
DBJoinType.INNER);
+ return join;
+ }
+
/*
* Other
*/
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 7e23997b..d15ed678 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
@@ -677,14 +677,19 @@ public abstract class DBCommand extends DBCommandExpr
// create list
if (joins == null)
joins = new ArrayList<DBJoinExpr>();
- // Create a new join
+ // check join list
for (int i = 0; i < joins.size(); i++)
- { // Check whether join exists
+ { // Check whether join already exists
DBJoinExpr item = joins.get(i);
if (item.equals(join))
return this;
}
joins.add(join);
+ // Check if prepared statements are enabled
+ if (isPreparedStatementsEnabled())
+ { // use command params
+ join.prepareCommand(this);
+ }
return this;
}
@@ -700,6 +705,16 @@ public abstract class DBCommand extends DBCommandExpr
return join(left, right, DBJoinType.INNER, addlConstraints);
}
+ /**
+ * Adds a left join to the list of join expressions.
+ * @return itself (this)
+ */
+ public final DBCommand joinLeft(DBJoinExpr join)
+ {
+ join.setType(DBJoinType.LEFT);
+ return join(join);
+ }
+
/**
* Adds a left join based on two columns to the list of join expressions.
* Added for convenience
@@ -714,6 +729,16 @@ public abstract class DBCommand extends DBCommandExpr
return join(left, right, DBJoinType.LEFT, addlConstraints);
}
+ /**
+ * Adds a left join to the list of join expressions.
+ * @return itself (this)
+ */
+ public final DBCommand joinRight(DBJoinExpr join)
+ {
+ join.setType(DBJoinType.RIGHT);
+ return join(join);
+ }
+
/**
* Adds a right join based on two columns to the list of join expressions.
* Added for convenience
@@ -753,11 +778,6 @@ public abstract class DBCommand extends DBCommandExpr
DBCompareExpr cmpExpr = addlConstraints[i];
if (cmpExpr==null)
continue;
- // Check if prepared statements are enabled
- if (isPreparedStatementsEnabled())
- { // use command params
- cmpExpr.prepareCommand(this);
- }
// Chain with previouss
where = (where!=null ? where.and(cmpExpr) : cmpExpr);
}
@@ -792,22 +812,12 @@ public abstract class DBCommand extends DBCommandExpr
for (int i=1; i<left.length; i++)
{ // add to where list
DBCompareExpr cmpExpr = right[i].is(left[i]);
- // Check if prepared statements are enabled
- if (isPreparedStatementsEnabled())
- { // use command params
- cmpExpr.prepareCommand(this);
- }
where = (where!=null ? where.and(cmpExpr) : cmpExpr);
}
// additional constraints
for (int i=0; i<addlConstraints.length; i++)
{
DBCompareExpr cmpExpr = addlConstraints[i];
- // Check if prepared statements are enabled
- if (isPreparedStatementsEnabled())
- { // use command params
- cmpExpr.prepareCommand(this);
- }
where = (where!=null ? where.and(cmpExpr) : cmpExpr);
}
if (where!=null)
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBColumnJoinExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBColumnJoinExpr.java
index e48fe4c1..df04a790 100644
---
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBColumnJoinExpr.java
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBColumnJoinExpr.java
@@ -169,6 +169,16 @@ public class DBColumnJoinExpr extends DBJoinExpr
right = swap;
type = DBJoinType.reversed(type); // (type * -1);
}
+
+ /**
+ * prepareCommand
+ */
+ @Override
+ public void prepareCommand(DBCommand cmd)
+ {
+ if (compExpr!=null)
+ compExpr.prepareCommand(cmd);
+ }
/**
* Copy Command
@@ -212,15 +222,28 @@ public class DBColumnJoinExpr extends DBJoinExpr
*
* @return the object itself
*/
- public DBJoinExpr and(DBColumnExpr c1, DBColumnExpr c2)
- { // Set Compare Expression
+ public DBColumnJoinExpr and(DBCompareExpr expr)
+ { // Add Compare Expression
if (compExpr==null)
- compExpr = c1.is(c2);
+ compExpr = expr;
else
- compExpr = compExpr.and(c1.is(c2));
+ compExpr = compExpr.and(expr);
return this;
}
+ /**
+ * This function adds an additional constraint to the join.
+ *
+ * @param c1 the first column
+ * @param c2 the second column
+ *
+ * @return the object itself
+ */
+ public DBColumnJoinExpr and(DBColumnExpr c1, DBColumnExpr c2)
+ { // Add Compare Expression
+ return and(c1.is(c2));
+ }
+
/**
* @see org.apache.empire.db.DBExpr#addReferencedColumns(Set)
*/
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
index 2779ee17..93149b9b 100644
---
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
@@ -87,6 +87,16 @@ public class DBCompareJoinExpr extends DBColumnJoinExpr
this.cmp = cmp;
}
+ /**
+ * prepareCommand
+ */
+ @Override
+ public void prepareCommand(DBCommand cmd)
+ {
+ cmp.prepareCommand(cmd);
+ super.prepareCommand(cmd);
+ }
+
/**
* Copy Command
* @param cmd
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCrossJoinExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCrossJoinExpr.java
index 8e7095da..25c3f4c8 100644
---
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCrossJoinExpr.java
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCrossJoinExpr.java
@@ -133,6 +133,16 @@ public class DBCrossJoinExpr extends DBJoinExpr
right = swap;
}
+ /**
+ * prepareCommand
+ * Nothing to do here
+ */
+ @Override
+ public void prepareCommand(DBCommand cmd)
+ {
+ // not applicable
+ }
+
/**
* Copy Command
* @param cmd
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
index eb47bb02..6111935b 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBJoinExpr.java
@@ -102,6 +102,12 @@ public abstract class DBJoinExpr extends DBExpr
* This function swaps the left and the right statements of the join
expression.
*/
public abstract void reverse();
+
+ /**
+ * internally used for preapred statement generation
+ * @param cmd
+ */
+ public abstract void prepareCommand(DBCommand cmd);
/**
* Copy Command
