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 369e1669 EMPIREDB-454: DBCommand: allow to generate Select statement
without OrderBy
369e1669 is described below
commit 369e166979d5bdf4310543254c8e5ffb5404728e
Author: Rainer Döbele <[email protected]>
AuthorDate: Thu Jan 9 17:15:43 2025 +0100
EMPIREDB-454:
DBCommand: allow to generate Select statement without OrderBy
---
.../java/org/apache/empire/db/DBCombinedCmd.java | 12 ++++++------
.../main/java/org/apache/empire/db/DBCommand.java | 5 +++--
.../java/org/apache/empire/db/DBCommandExpr.java | 21 +++++++++++++++++----
.../src/main/java/org/apache/empire/db/DBUtils.java | 11 +++++++----
.../apache/empire/db/expr/compare/DBExistsExpr.java | 2 +-
.../org/apache/empire/dbms/h2/DBMSHandlerH2.java | 4 ++--
.../org/apache/empire/dbms/hsql/DBCommandHSql.java | 4 ++--
.../apache/empire/dbms/mysql/DBMSHandlerMySQL.java | 8 ++++----
.../apache/empire/dbms/oracle/DBCommandOracle.java | 6 +++---
.../empire/dbms/postgresql/DBCommandPostgres.java | 4 ++--
10 files changed, 47 insertions(+), 30 deletions(-)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCombinedCmd.java
b/empire-db/src/main/java/org/apache/empire/db/DBCombinedCmd.java
index 21b32e40..92ade6ad 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCombinedCmd.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCombinedCmd.java
@@ -214,7 +214,7 @@ public class DBCombinedCmd extends DBCommandExpr
* @param sql the SQL-Command
*/
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{
cmdParams.clear(0);
// the left part
@@ -226,7 +226,7 @@ public class DBCombinedCmd extends DBCommandExpr
sql.append(")");
}
else
- left.getSelect(sql);
+ left.getSelect(sql, flags);
// concat keyword
sql.append("\r\n");
sql.append(keyWord);
@@ -240,14 +240,14 @@ public class DBCombinedCmd extends DBCommandExpr
sql.append(")");
}
else
- right.getSelect(sql);
- // done
+ right.getSelect(sql, flags);
// Add optional Order by statement
- if (orderBy != null)
- { // Having
+ if (orderBy!=null && !orderBy.isEmpty() && (flags & SF_NO_ORDER)==0)
+ { // add ORDER BY
sql.append("\r\nORDER BY ");
addListExpr(sql, orderBy, CTX_DEFAULT, ", ");
}
+ // done
}
@Override
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 0fd6b1c4..15bf9ec2 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
@@ -1618,7 +1618,7 @@ public abstract class DBCommand extends DBCommandExpr
* Creates a select SQL-Statement
*/
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{
resetParamUsage();
if (select == null)
@@ -1632,7 +1632,8 @@ public abstract class DBCommand extends DBCommandExpr
// Add Grouping
addGrouping(sql);
// Add Order
- addOrder(sql);
+ if ((flags & SF_NO_ORDER)==0)
+ addOrder(sql);
// done
completeParamUsage();
}
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
b/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
index 1fd013a8..12ab9cec 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
@@ -45,6 +45,10 @@ public abstract class DBCommandExpr extends DBExpr
// *Deprecated* private static final long serialVersionUID = 1L;
private static final Logger log =
LoggerFactory.getLogger(DBCommandExpr.class);
+ // Select Context Flags
+ public static final short SF_DEFAULT = 0; // Default
+ public static final short SF_NO_ORDER = 1; // No order by
+
// Internal Classes
protected static class DBCmdQuery extends DBRowSet
{
@@ -357,18 +361,27 @@ public abstract class DBCommandExpr extends DBExpr
* returns an SQL select command
* @param sql the sql builder to add the command to
*/
- public abstract void getSelect(DBSQLBuilder sql);
+ public abstract void getSelect(DBSQLBuilder sql, short flags);
/**
* returns an SQL select command for querying records.
* @return the SQL-Command
*/
- public final String getSelect()
+ public final String getSelect(short flags)
{
DBSQLBuilder sql = createSQLBuilder(null);
- getSelect(sql);
+ getSelect(sql, flags);
return sql.toString();
}
+
+ /**
+ * returns an SQL select command for querying records.
+ * @return the SQL-Command
+ */
+ public final String getSelect()
+ {
+ return getSelect(SF_DEFAULT);
+ }
public abstract DBCmdParams getParams();
@@ -701,7 +714,7 @@ public abstract class DBCommandExpr extends DBExpr
}
// append select statement
sql.append("\r\n");
- getSelect(sql);
+ getSelect(sql, SF_DEFAULT);
// done
return sql.toString();
}
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
index 3c2c1a18..fe40aaee 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
@@ -488,15 +488,18 @@ public class DBUtils implements DBContextAware
// check if aggregate
if (aggregate)
{ // For Aggregations: Wrap
- DBCommand subCmd = cmd.clone();
- subCmd.clearOrderBy();
- String sql = "SELECT COUNT(*) FROM ("+subCmd.getSelect() + ") q";
- return querySingleInt(sql, subCmd.getParamValues(), 0);
+ String sql = "SELECT COUNT(*) FROM
("+cmd.getSelect(DBCommandExpr.SF_NO_ORDER) + ") q";
+ return querySingleInt(sql, cmd.getParamValues(), 0);
}
// find any rowset
DBRowSet rs = exprList[0].getRowSet();
// create the count command
DBCommand countCmd = cmd.clone();
+ if (countCmd.getParams().size()!=cmd.getParams().size())
+ { // Failed to copy all DBCmdParams
+ String sql = "SELECT COUNT(*) FROM
("+cmd.getSelect(DBCommandExpr.SF_NO_ORDER) + ") q";
+ return querySingleInt(sql, cmd.getParamValues(), 0);
+ }
countCmd.clearSelect();
countCmd.clearOrderBy();
countCmd.select(rs.count());
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBExistsExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBExistsExpr.java
index 7f609fa6..7f0f187f 100644
---
a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBExistsExpr.java
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBExistsExpr.java
@@ -142,7 +142,7 @@ public class DBExistsExpr extends DBCompareExpr
return;
}
sql.append(" exists (");
- cmd.getSelect(sql);
+ cmd.getSelect(sql, DBCommandExpr.SF_NO_ORDER);
if (compareExpr != null)
{
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
b/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
index 0f73347a..ae11ed3d 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
@@ -94,9 +94,9 @@ public class DBMSHandlerH2 extends DBMSHandlerBase
}
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{ // Prepares statement
- super.getSelect(sql);
+ super.getSelect(sql, flags);
// add limit and offset
if (limitRows>=0)
{ sql.append("\r\nLIMIT ");
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
index d0c64a72..95efcbf4 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
@@ -75,9 +75,9 @@ public class DBCommandHSql extends DBCommand
}
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{ // call base class
- super.getSelect(sql);
+ super.getSelect(sql, flags);
// add limit and offset
if (limitRows>=0)
{ sql.append("\r\nLIMIT ");
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
b/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
index daba146d..300d2ebf 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
@@ -94,9 +94,9 @@ public class DBMSHandlerMySQL extends DBMSHandlerBase
}
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{ // call base class
- super.getSelect(sql);
+ super.getSelect(sql, flags);
// add limit and offset
if (limit>=0)
{ sql.append("\r\nLIMIT ");
@@ -981,9 +981,9 @@ public class DBMSHandlerMySQL extends DBMSHandlerBase
skip = -1;
}
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{ // Prepares statement
- super.getSelect(sql);
+ super.getSelect(sql, flags);
// add limit and offset
if (limit>=0)
{ sql.append("\r\nLIMIT ");
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
index 3e4c6cab..e5483e70 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
@@ -157,7 +157,7 @@ public class DBCommandOracle extends DBCommand
* @param sql the SQL statement
*/
@Override
- public synchronized void getSelect(DBSQLBuilder sql)
+ public synchronized void getSelect(DBSQLBuilder sql, short flags)
{
resetParamUsage();
if (select == null)
@@ -199,8 +199,8 @@ public class DBCommandOracle extends DBCommand
// Grouping
addGrouping(sql);
// Order
- if (orderBy != null)
- { // Having
+ if (orderBy!=null && !orderBy.isEmpty() && (flags & SF_NO_ORDER)==0)
+ { // add ORDER BY
if (connectBy != null)
sql.append("\r\nORDER SIBLINGS BY ");
else
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
index 2d73f1bd..6355fc36 100644
---
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
+++
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
@@ -149,9 +149,9 @@ public class DBCommandPostgres extends DBCommand
}
@Override
- public void getSelect(DBSQLBuilder sql)
+ public void getSelect(DBSQLBuilder sql, short flags)
{ // call base class
- super.getSelect(sql);
+ super.getSelect(sql, flags);
// add limit and offset
if (limit>=0)
{ sql.append("\r\nLIMIT ");
