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 4a192f0b EMPIREDB-454 DBCommand: getSelect with Skip options
4a192f0b is described below
commit 4a192f0bcb8ef6932fed23b6d07cad21116c4142
Author: Rainer Döbele <[email protected]>
AuthorDate: Fri Jan 10 09:23:52 2025 +0100
EMPIREDB-454
DBCommand: getSelect with Skip options
---
.../java/org/apache/empire/commons/ValueUtils.java | 12 ++---
.../java/org/apache/empire/db/DBCombinedCmd.java | 4 +-
.../main/java/org/apache/empire/db/DBCommand.java | 61 ++++++++++++++--------
.../java/org/apache/empire/db/DBCommandExpr.java | 30 +++++++++--
.../main/java/org/apache/empire/db/DBUtils.java | 19 ++-----
.../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 | 43 +++++++++------
.../empire/dbms/postgresql/DBCommandPostgres.java | 4 +-
11 files changed, 113 insertions(+), 78 deletions(-)
diff --git a/empire-db/src/main/java/org/apache/empire/commons/ValueUtils.java
b/empire-db/src/main/java/org/apache/empire/commons/ValueUtils.java
index 999a982c..bd00cc30 100644
--- a/empire-db/src/main/java/org/apache/empire/commons/ValueUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/commons/ValueUtils.java
@@ -91,12 +91,12 @@ public class ValueUtils
return true;
if (o==ObjectUtils.NO_VALUE)
throw new InvalidValueException(o);
- if ((o instanceof String) && ((String)o).length()==0)
- return true;
- if ((o instanceof Object[]) && ((Object[])o).length==0)
- return true;
- if ((o instanceof Collection<?>) && ((Collection<?>)o).isEmpty())
- return true;
+ if ((o instanceof String))
+ return ((String)o).length()==0;
+ if ((o instanceof Object[]))
+ return ((Object[])o).length==0;
+ if ((o instanceof Collection<?>))
+ return ((Collection<?>)o).isEmpty();
if (o instanceof DBValueExpr)
return isEmpty(((DBValueExpr)o).getValue());
if (o instanceof Enum)
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 92ade6ad..99355c9c 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, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{
cmdParams.clear(0);
// the left part
@@ -242,7 +242,7 @@ public class DBCombinedCmd extends DBCommandExpr
else
right.getSelect(sql, flags);
// Add optional Order by statement
- if (orderBy!=null && !orderBy.isEmpty() && (flags & SF_NO_ORDER)==0)
+ if (notEmpty(orderBy) && not(flags, SF_SKIP_ORDER))
{ // add ORDER BY
sql.append("\r\nORDER BY ");
addListExpr(sql, orderBy, CTX_DEFAULT, ", ");
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 15bf9ec2..b9c23fe1 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
@@ -45,6 +45,7 @@ import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.dbms.DBSqlPhrase;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ItemNotFoundException;
+import org.apache.empire.exceptions.NotSupportedException;
import org.apache.empire.exceptions.ObjectNotValidException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -179,6 +180,12 @@ public abstract class DBCommand extends DBCommandExpr
for (int i=0; (clone.having!=null && i<clone.having.size()); i++)
clone.having.set(i, clone.having.get(i).copy(clone));
}
+ // check params
+ if (clone.cmdParams.size()!=this.cmdParams.size())
+ { // Failed to copy all DBCmdParams
+ log.error("DBCommand.clone failed: Not all DBCmdParams could be
replaced.");
+ throw new NotSupportedException(this, "clone");
+ }
// done
return clone;
}
@@ -192,9 +199,9 @@ public abstract class DBCommand extends DBCommandExpr
if (hasSetExpr())
return this.set.get(0).getDatabase();
// two more chances (should we?)
- if (where!=null && !where.isEmpty())
+ if (notEmpty(where))
return where.get(0).getDatabase();
- if (orderBy!=null && !orderBy.isEmpty())
+ if (notEmpty(orderBy))
return orderBy.get(0).getDatabase();
// not valid yet
throw new ObjectNotValidException(this);
@@ -325,7 +332,7 @@ public abstract class DBCommand extends DBCommandExpr
@Override
public boolean hasSelectExpr()
{
- return (select!=null && !select.isEmpty());
+ return notEmpty(select);
}
/**
@@ -600,7 +607,7 @@ public abstract class DBCommand extends DBCommandExpr
*/
public boolean hasSetExpr()
{
- return (this.set!=null ? !this.set.isEmpty() : false);
+ return notEmpty(set);
}
/**
@@ -1210,7 +1217,7 @@ public abstract class DBCommand extends DBCommandExpr
*/
public boolean hasGroupBy()
{
- return (this.groupBy!=null ? !this.groupBy.isEmpty() : false);
+ return notEmpty(groupBy);
}
/**
@@ -1618,21 +1625,29 @@ public abstract class DBCommand extends DBCommandExpr
* Creates a select SQL-Statement
*/
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{
- resetParamUsage();
- if (select == null)
- throw new ObjectNotValidException(this); // invalid!
// Prepares statement
- addSelect(sql);
+ resetParamUsage();
+ // Select clause
+ if (not(flags, SF_SKIP_SELECT))
+ { // check
+ if (select == null)
+ throw new ObjectNotValidException(this); // invalid!
+ // add select
+ addSelect(sql);
+ }
// From clause
- addFrom(sql);
+ if (not(flags, SF_SKIP_FROM))
+ addFrom(sql);
// Add Where
- addWhere(sql);
+ if (not(flags, SF_SKIP_WHERE))
+ addWhere(sql);
// Add Grouping
- addGrouping(sql);
+ if (not(flags, SF_SKIP_GROUP))
+ addGrouping(sql);
// Add Order
- if ((flags & SF_NO_ORDER)==0)
+ if (not(flags, SF_SKIP_ORDER))
addOrder(sql);
// done
completeParamUsage();
@@ -1656,7 +1671,7 @@ public abstract class DBCommand extends DBCommandExpr
sql.append("( ");
// Set Expressions
ArrayList<DBCompareColExpr> compexpr = null;
- if (where!=null && !where.isEmpty())
+ if (notEmpty(where))
{ // Convert ColumnExpression List to Column List
compexpr = new ArrayList<DBCompareColExpr>(where.size());
for (DBCompareExpr expr : where)
@@ -1735,7 +1750,7 @@ public abstract class DBCommand extends DBCommandExpr
return null;
DBSQLBuilder sql = createSQLBuilder("UPDATE ");
DBRowSet table = set.get(0).getTable();
- if (joins!=null && !joins.isEmpty())
+ if (notEmpty(joins))
{ // Join Update
addUpdateWithJoins(sql, table);
}
@@ -1784,7 +1799,7 @@ public abstract class DBCommand extends DBCommandExpr
resetParamUsage();
DBSQLBuilder sql = createSQLBuilder("DELETE ");
// joins or simple
- if (joins!=null && !joins.isEmpty())
+ if (notEmpty(joins))
{ // delete with joins
addDeleteWithJoins(sql, table);
}
@@ -1900,7 +1915,7 @@ public abstract class DBCommand extends DBCommandExpr
protected void addWhere(DBSQLBuilder sql, long context)
{
- if (where!=null && !where.isEmpty())
+ if (notEmpty(where))
{
sql.append("\r\nWHERE ");
// add where expression
@@ -1915,13 +1930,13 @@ public abstract class DBCommand extends DBCommandExpr
protected void addGrouping(DBSQLBuilder sql)
{
- if (groupBy!=null && !groupBy.isEmpty())
- { // Group by
+ if (notEmpty(groupBy))
+ { // Group by
sql.append("\r\nGROUP BY ");
addListExpr(sql, groupBy, CTX_DEFAULT, ", ");
}
- if (having!=null && !having.isEmpty())
- { // Having
+ if (notEmpty(having))
+ { // Having
sql.append("\r\nHAVING ");
addListExpr(sql, having, CTX_DEFAULT, " AND ");
}
@@ -1929,7 +1944,7 @@ public abstract class DBCommand extends DBCommandExpr
protected void addOrder(DBSQLBuilder sql)
{
- if (orderBy!=null && !orderBy.isEmpty())
+ if (notEmpty(orderBy))
{ // order By
sql.append("\r\nORDER BY ");
addListExpr(sql, orderBy, CTX_DEFAULT, ", ");
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 12ab9cec..38886532 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
@@ -19,6 +19,7 @@
package org.apache.empire.db;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Set;
@@ -46,8 +47,29 @@ public abstract class DBCommandExpr extends DBExpr
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
+ public static final int SF_DEFAULT = 0; // Default
+ public static final int SF_SKIP_SELECT = 1; // No select
+ public static final int SF_SKIP_FROM = 2; // No from / joins
+ public static final int SF_SKIP_WHERE = 4; // No where
+ public static final int SF_SKIP_GROUP = 8; // No group by / having
+ public static final int SF_SKIP_ORDER = 16; // No order by
+ public static final int SF_SKIP_LIMIT = 32; // No limit
+
+ /**
+ * Checks if a flag is NOT set
+ */
+ protected final boolean not(int flags, int flag)
+ {
+ return ((flags & flag)==0);
+ }
+
+ /**
+ * Checks if a collection is not null or empty
+ */
+ protected final boolean notEmpty(Collection<?> list)
+ {
+ return (list!=null && !list.isEmpty());
+ }
// Internal Classes
protected static class DBCmdQuery extends DBRowSet
@@ -361,13 +383,13 @@ 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, short flags);
+ public abstract void getSelect(DBSQLBuilder sql, int flags);
/**
* returns an SQL select command for querying records.
* @return the SQL-Command
*/
- public final String getSelect(short flags)
+ public final String getSelect(int flags)
{
DBSQLBuilder sql = createSQLBuilder(null);
getSelect(sql, flags);
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 fe40aaee..7762626c 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,23 +488,12 @@ public class DBUtils implements DBContextAware
// check if aggregate
if (aggregate)
{ // For Aggregations: Wrap
- String sql = "SELECT COUNT(*) FROM
("+cmd.getSelect(DBCommandExpr.SF_NO_ORDER) + ") q";
+ String sql = "SELECT COUNT(*) FROM
("+cmd.getSelect(DBCommandExpr.SF_SKIP_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());
- // perform query
- return querySingleInt(countCmd);
+ // simple expression
+ String sql = "SELECT COUNT(*)
"+cmd.getSelect(DBCommandExpr.SF_SKIP_SELECT | DBCommandExpr.SF_SKIP_ORDER);
+ return querySingleInt(sql, cmd.getParamValues(), 0);
}
/**
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 7f0f187f..bb36f8ad 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, DBCommandExpr.SF_NO_ORDER);
+ cmd.getSelect(sql, DBCommandExpr.SF_SKIP_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 ae11ed3d..9abafc99 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,11 +94,11 @@ public class DBMSHandlerH2 extends DBMSHandlerBase
}
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{ // Prepares statement
super.getSelect(sql, flags);
// add limit and offset
- if (limitRows>=0)
+ if (limitRows>=0 && not(flags, SF_SKIP_LIMIT))
{ sql.append("\r\nLIMIT ");
sql.append(String.valueOf(limitRows));
// Offset
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 95efcbf4..77bd2db3 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,11 +75,11 @@ public class DBCommandHSql extends DBCommand
}
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{ // call base class
super.getSelect(sql, flags);
// add limit and offset
- if (limitRows>=0)
+ if (limitRows>=0 && not(flags, SF_SKIP_LIMIT))
{ sql.append("\r\nLIMIT ");
sql.append(String.valueOf(limitRows));
// Offset
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 300d2ebf..542093fe 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,11 +94,11 @@ public class DBMSHandlerMySQL extends DBMSHandlerBase
}
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{ // call base class
super.getSelect(sql, flags);
// add limit and offset
- if (limit>=0)
+ if (limit>=0 && not(flags, SF_SKIP_LIMIT))
{ sql.append("\r\nLIMIT ");
sql.append(String.valueOf(limit));
// Offset
@@ -981,11 +981,11 @@ public class DBMSHandlerMySQL extends DBMSHandlerBase
skip = -1;
}
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{ // Prepares statement
super.getSelect(sql, flags);
// add limit and offset
- if (limit>=0)
+ if (limit>=0 && not(flags, SF_SKIP_LIMIT))
{ sql.append("\r\nLIMIT ");
sql.append(String.valueOf(limit));
// Offset
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 e5483e70..cc383639 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,33 +157,40 @@ public class DBCommandOracle extends DBCommand
* @param sql the SQL statement
*/
@Override
- public synchronized void getSelect(DBSQLBuilder sql, short flags)
+ public synchronized void getSelect(DBSQLBuilder sql, int flags)
{
+ // Prepare
resetParamUsage();
- if (select == null)
- throw new ObjectNotValidException(this);
// limit rows
boolean usePreparedStatements = isPreparedStatementsEnabled();
- if (limitRows>=0)
+ if (limitRows>=0 && not(flags, SF_SKIP_LIMIT))
{ // add limitRows and skipRows wrapper
sql.append("SELECT * FROM (");
if (skipRows>0)
sql.append("SELECT row_.*, rownum rownum_ FROM (");
}
// Prepares statement
- sql.append("SELECT ");
- if (StringUtils.isNotEmpty(optimizerHint))
- { // Append an optimizer hint to the select statement e.g. SELECT
/*+ RULE */
- sql.append("/*+ ").append(optimizerHint).append(" */ ");
+ if (not(flags, SF_SKIP_SELECT))
+ { // check select
+ if (select == null)
+ throw new ObjectNotValidException(this);
+ // Select
+ sql.append("SELECT ");
+ if (StringUtils.isNotEmpty(optimizerHint))
+ { // Append an optimizer hint to the select statement e.g.
SELECT /*+ RULE */
+ sql.append("/*+ ").append(optimizerHint).append(" */ ");
+ }
+ if (selectDistinct)
+ sql.append("DISTINCT ");
+ // Add Select Expressions
+ addListExpr(sql, select, CTX_ALL, ", ");
}
- if (selectDistinct)
- sql.append("DISTINCT ");
- // Add Select Expressions
- addListExpr(sql, select, CTX_ALL, ", ");
// Join
- addFrom(sql);
+ if (not(flags, SF_SKIP_FROM))
+ addFrom(sql);
// Where
- addWhere(sql);
+ if (not(flags, SF_SKIP_WHERE))
+ addWhere(sql);
// Connect By
if (connectBy != null)
{ // Add 'Connect By Prior' Expression
@@ -197,9 +204,10 @@ public class DBCommandOracle extends DBCommand
}
}
// Grouping
- addGrouping(sql);
+ if (not(flags, SF_SKIP_GROUP))
+ addGrouping(sql);
// Order
- if (orderBy!=null && !orderBy.isEmpty() && (flags & SF_NO_ORDER)==0)
+ if (notEmpty(orderBy) && not(flags, SF_SKIP_ORDER))
{ // add ORDER BY
if (connectBy != null)
sql.append("\r\nORDER SIBLINGS BY ");
@@ -209,7 +217,7 @@ public class DBCommandOracle extends DBCommand
addListExpr(sql, orderBy, CTX_DEFAULT, ", ");
}
// limit rows end
- if (limitRows>=0)
+ if (limitRows>=0 && not(flags, SF_SKIP_LIMIT))
{ // add limitRows and skipRows constraints
sql.append(") row_ WHERE rownum<=");
sql.append(usePreparedStatements ? "?" :
String.valueOf(skipRows+limitRows));
@@ -219,6 +227,7 @@ public class DBCommandOracle extends DBCommand
sql.append(usePreparedStatements ? "?" :
String.valueOf(skipRows));
}
}
+ // done
completeParamUsage();
}
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 6355fc36..546d9056 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,11 +149,11 @@ public class DBCommandPostgres extends DBCommand
}
@Override
- public void getSelect(DBSQLBuilder sql, short flags)
+ public void getSelect(DBSQLBuilder sql, int flags)
{ // call base class
super.getSelect(sql, flags);
// add limit and offset
- if (limit>=0)
+ if (limit>=0 && not(flags, SF_SKIP_LIMIT))
{ sql.append("\r\nLIMIT ");
sql.append(String.valueOf(limit));
// Offset
