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 2010/11/07 15:03:11 UTC
svn commit: r1032278 - in /incubator/empire-db/trunk:
empire-db-examples/empire-db-example-basic/
empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/
empire-db/src/main/java/org/apache/empire/db/ empire-db/src/main/ja...
Author: doebele
Date: Sun Nov 7 14:03:11 2010
New Revision: 1032278
URL: http://svn.apache.org/viewvc?rev=1032278&view=rev
Log:
EMPIREDB-62
Added limitRows() for Oracle
Added:
incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java (with props)
Modified:
incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml
incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml
incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml (original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml Sun Nov 7 14:03:11 2010
@@ -21,7 +21,7 @@
<properties>
<!-- provider name must match the property-section containing the connection data -->
- <databaseProvider>sqlserver</databaseProvider>
+ <databaseProvider>hsqldb</databaseProvider>
</properties>
<properties-hsqldb>
Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml (original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml Sun Nov 7 14:03:11 2010
@@ -60,11 +60,11 @@
<version>5.1.6</version>
</dependency>
- <!-- ojdbc
+ <!-- ojdbc
<dependency>
<groupId>com.oracle</groupId>
- <artifactId>ojdbc14</artifactId>
- <version>10.2.0.3.0</version>
+ <artifactId>ojdbc</artifactId>
+ <version>14</version>
</dependency>
-->
Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java (original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java Sun Nov 7 14:03:11 2010
@@ -36,6 +36,7 @@ import org.apache.empire.db.h2.DBDatabas
import org.apache.empire.db.hsql.DBDatabaseDriverHSql;
import org.apache.empire.db.mysql.DBDatabaseDriverMySQL;
import org.apache.empire.db.oracle.DBDatabaseDriverOracle;
+import org.apache.empire.db.oracle.OracleRowNumExpr;
import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL;
import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL;
import org.apache.empire.xml.XMLWriter;
@@ -417,9 +418,15 @@ public class SampleApp
cmd.where(EMP.LASTNAME.length().isGreaterThan(0));
cmd.orderBy(EMP.LASTNAME, EMP.FIRSTNAME);
- cmd.limitRows(20);
- if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
- cmd.skipRows(1);
+ /*
+ // Example for limitRows() and skipRows()
+ if (db.getDriver().isSupported(DBDriverFeature.QUERY_LIMIT_ROWS))
+ { // set maximum number of rows
+ cmd.limitRows(20);
+ if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
+ cmd.skipRows(1);
+ }
+ */
// Query Records and print output
DBReader reader = new DBReader();
Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java (original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java Sun Nov 7 14:03:11 2010
@@ -470,7 +470,7 @@ public abstract class DBCommand extends
{
if (where == null)
where = new ArrayList<DBCompareExpr>();
- setCompare(where, expr);
+ setConstraint(where, expr);
}
/**
@@ -496,6 +496,17 @@ public abstract class DBCommand extends
}
return null;
}
+
+ /**
+ * removes a constraint on a particular column from the where clause
+ * @param col the column expression for which to remove the constraint
+ */
+ public void removeWhereConstraintOn(DBColumnExpr col)
+ {
+ if (where == null)
+ return;
+ removeConstraintOn(where, col);
+ }
/**
* Returns a copy of the defined joins.
@@ -513,7 +524,6 @@ public abstract class DBCommand extends
/**
* Adds a list of constraints to the command.
- *
* @param constraints list of constraints
*/
public void addWhereConstraints(List<DBCompareExpr> constraints)
@@ -526,16 +536,25 @@ public abstract class DBCommand extends
}
/**
- * Sets a having contraint.
- *
+ * adds a constraint to the having clause.
* @param expr the DBCompareExpr object
*/
- // having
public void having(DBCompareExpr expr)
{
if (having == null)
having = new ArrayList<DBCompareExpr>();
- setCompare(having, expr);
+ setConstraint(having, expr);
+ }
+
+ /**
+ * removes a constraint on a particular column from the where clause
+ * @param col the column expression for which to remove the constraint
+ */
+ public void removeHavingConstraintOn(DBColumnExpr col)
+ {
+ if (having == null)
+ return;
+ removeConstraintOn(having, col);
}
/**
@@ -701,13 +720,11 @@ public abstract class DBCommand extends
}
/**
- * Compares the DBCompareExpr object with the Elements
- * of the Vector 'where' or 'having'.
- *
- * @param list the Vector 'where' or 'having'
+ * adds a constraint to the 'where' or 'having' collections
+ * @param list the 'where' or 'having' list
* @param expr the DBCompareExpr object
*/
- protected void setCompare(List<DBCompareExpr> list, DBCompareExpr expr)
+ protected void setConstraint(List<DBCompareExpr> list, DBCompareExpr expr)
{ // adds a comparison to the where or having list
for (int i = 0; i < list.size(); i++)
{ // check expression
@@ -718,10 +735,33 @@ public abstract class DBCommand extends
list.set(i, expr);
return;
}
- // neue expression, or possible another expression
- // for the same column when allowMultiple == true
+ // add expression
list.add(expr);
}
+
+ /**
+ * removes a constraint on a particular column to the 'where' or 'having' collections
+ * @param list the 'where' or 'having' list
+ * @param col the column expression for which to remove the constraint
+ */
+ protected void removeConstraintOn(List<DBCompareExpr> list, DBColumnExpr col)
+ {
+ if (list == null)
+ return;
+ for(DBCompareExpr cmp : list)
+ { // Check whether it is a compare column expr.
+ if (!(cmp instanceof DBCompareColExpr))
+ continue;
+ // Compare columns
+ DBColumnExpr c = ((DBCompareColExpr)cmp).getColumnExpr();
+ DBColumn udc = c.getUpdateColumn();
+ if (c.equals(col) || (udc!=null && udc.equals(col.getUpdateColumn())))
+ { // found the column
+ list.remove(cmp);
+ return;
+ }
+ }
+ }
/**
* Gets a list of all tables referenced by the query.
Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java (original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java Sun Nov 7 14:03:11 2010
@@ -454,7 +454,7 @@ public class DBQuery extends DBRowSet
{ // Check whether
DBCompareColExpr cmpExpr = (DBCompareColExpr) cmp;
DBColumn col = cmpExpr.getColumnExpr().getUpdateColumn();
- if (col.getRowSet() == table)
+ if (col!=null && col.getRowSet() == table)
upd.where(cmp);
}
else
Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java (original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java Sun Nov 7 14:03:11 2010
@@ -37,6 +37,7 @@ public class DBCommandOracle extends DBC
protected DBCompareExpr startWith = null;
// optimizerHint
protected String optimizerHint = null;
+ protected OracleRowNumExpr rowNumExpr = null;
/**
* Constructs an oracle command object.
@@ -89,6 +90,25 @@ public class DBCommandOracle extends DBC
{
this.startWith = expr;
}
+
+ @Override
+ public boolean limitRows(int numRows)
+ {
+ if (rowNumExpr==null)
+ rowNumExpr = new OracleRowNumExpr(getDatabase());
+ // Add the constraint
+ where(rowNumExpr.isLessOrEqual(numRows));
+ return success();
+ }
+
+ @Override
+ public void clearLimit()
+ {
+ if (rowNumExpr!=null)
+ removeWhereConstraintOn(rowNumExpr);
+ // constraint removed
+ rowNumExpr = null;
+ }
/**
* Creates the SQL statement the special characteristics of
Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java (original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java Sun Nov 7 14:03:11 2010
@@ -47,9 +47,6 @@ import org.apache.empire.db.DBView;
/**
* This class provides support for the Oracle database system.<br>
* Oracle Version 9 or higher is required.
- *
- *
- *
*/
public class DBDatabaseDriverOracle extends DBDatabaseDriver
{
@@ -105,8 +102,10 @@ public class DBDatabaseDriverOracle exte
{
switch (type)
{ // return support info
- case CREATE_SCHEMA: return false;
- case SEQUENCES: return true;
+ case CREATE_SCHEMA: return false;
+ case SEQUENCES: return true;
+ case QUERY_LIMIT_ROWS: return true;
+ case QUERY_SKIP_ROWS: return false;
default:
// All other features are not supported by default
return false;
Added: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java?rev=1032278&view=auto
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java (added)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java Sun Nov 7 14:03:11 2010
@@ -0,0 +1,146 @@
+/*
+ * 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.oracle;
+
+// Java
+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.xml.XMLUtil;
+import org.w3c.dom.Element;
+
+
+/**
+ * implements a column expression for the Oracle rownum function
+ */
+public class OracleRowNumExpr extends DBColumnExpr
+{
+ public final DBDatabase db;
+
+ /**
+ * Constructs a new OracleRowNumExpr object.
+ *
+ * @param db the database
+ */
+ public OracleRowNumExpr(DBDatabase db)
+ {
+ this.db = db;
+ }
+
+ /**
+ * Returns the current DBDatabase object.
+ * @return the current DBDatabase object
+ */
+ @Override
+ public DBDatabase getDatabase()
+ {
+ return db;
+ }
+
+ /**
+ * Returns the data type of the DBColumnExpr object.
+ *
+ * @return the data type
+ */
+ @Override
+ public DataType getDataType()
+ {
+ return DataType.INTEGER;
+ }
+
+ /**
+ * Returns the column name.
+ *
+ * @return the column name
+ */
+ @Override
+ public String getName()
+ {
+ return "rownum";
+ }
+
+ /** this helper function calls the DBColumnExpr.addXML(Element, long) method */
+ @Override
+ public Element addXml(Element parent, long flags)
+ {
+ Element elem = XMLUtil.addElement(parent, "column");
+ elem.setAttribute("name", getName());
+ elem.setAttribute("function", "rownum");
+ return elem;
+ }
+
+ /**
+ * Returns null.
+ *
+ * @return null
+ */
+ @Override
+ public DBColumn getUpdateColumn()
+ {
+ return null;
+ }
+
+ /**
+ * Always returns false
+ * @return false
+ */
+ @Override
+ public boolean isAggregate()
+ {
+ return false;
+ }
+
+ /**
+ * Creates the SQL-Command.
+ *
+ * @param buf the SQL-Command
+ * @param context the current SQL-Command context
+ */
+ @Override
+ public void addSQL(StringBuilder buf, long context)
+ {
+ if (!(db.getDriver() instanceof DBDatabaseDriverOracle))
+ {
+ log.warn("Oracle RowNumExpression can be used with Oracle databases only!");
+ }
+ buf.append("rownum");
+ }
+
+ /**
+ * @see org.apache.empire.db.DBExpr#addReferencedColumns(Set)
+ */
+ @Override
+ public void addReferencedColumns(Set<DBColumn> list)
+ { // nothing to do!
+ return;
+ }
+
+ /**
+ * @see java.lang.Object#equals(Object)
+ */
+ @Override
+ public boolean equals(Object other)
+ {
+ return (other instanceof OracleRowNumExpr);
+ }
+
+}
Propchange: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
------------------------------------------------------------------------------
svn:mime-type = text/plain