You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openjpa.apache.org by pp...@apache.org on 2013/03/22 19:22:38 UTC
svn commit: r1459918 - in /openjpa/trunk:
openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/
openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/
openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/
openjpa-persistence-jdbc/src/test/jav...
Author: ppoddar
Date: Fri Mar 22 18:22:38 2013
New Revision: 1459918
URL: http://svn.apache.org/r1459918
Log:
OPENJPA-2356: Use FETCH OFFSET/NEXT and ROWNUM support for DB2 familiy of databases for Range queries
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
openjpa/trunk/openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/TestDB2Dictionary.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestLocators.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestRangeQuery.java
openjpa/trunk/openjpa-project/src/doc/manual/ref_guide_dbsetup.xml
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java Fri Mar 22 18:22:38 2013
@@ -89,14 +89,19 @@ public class DB2Dictionary
= "SELECT SEQSCHEMA AS SEQUENCE_SCHEMA, SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES";
static final String SYSDUMMY = "SYSIBM.SYSDUMMY1";
- protected String databaseProductName = "";
- protected String databaseProductVersion = "";
- protected int maj = 0;
- protected int min = 0;
private int defaultBatchLimit = 100;
public boolean appendExtendedExceptionText = true;
+ /**
+ * Affirms whether this dictionary uses {@code ROWNUM} feature.
+ * {@code ROWNUM} feature is used to construct {@code SQL SELECT} query
+ * that uses an offset or limits the number of resultant rows.
+ * <br>
+ * By default, this flag is set to {@code false}.
+ */
+ public boolean supportsRowNum = false;
+
public DB2Dictionary() {
platform = "DB2";
validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM SYSIBM.SYSTABLES";
@@ -279,14 +284,12 @@ public class DB2Dictionary
else
driverVendor = VENDOR_OTHER;
- databaseProductName = nullSafe(metaData.getDatabaseProductName());
- databaseProductVersion = nullSafe(metaData.getDatabaseProductVersion());
// Determine the type of DB2 database
// First check for AS/400
getProductVersionMajorMinorForISeries();
- if (maj > 0) {
+ if (versionLaterThan(0)) {
if (isDB2ISeriesV5R3OrEarlier())
db2ServerType = db2ISeriesV5R3OrEarlier;
else if (isDB2ISeriesV5R4OrLater())
@@ -295,8 +298,8 @@ public class DB2Dictionary
if (db2ServerType == 0) {
if (isJDBC3) {
- maj = metaData.getDatabaseMajorVersion();
- min = metaData.getDatabaseMinorVersion();
+ setMajorVersion(metaData.getDatabaseMajorVersion());
+ setMinorVersion(metaData.getDatabaseMinorVersion());
}
else
getProductVersionMajorMinor();
@@ -311,19 +314,17 @@ public class DB2Dictionary
}
// verify that database product is supported
- if (db2ServerType == 0 || maj == 0)
+ if (db2ServerType == 0 || getMajorVersion() < 0)
throw new UnsupportedException(_loc.get("db-not-supported",
new Object[] {databaseProductName, databaseProductVersion }));
-
- if (maj >= 9 || (maj == 8 && min >= 2)) {
+ if (versionEqualOrLaterThan(9, 2)) {
supportsLockingWithMultipleTables = true;
supportsLockingWithInnerJoin = true;
supportsLockingWithOuterJoin = true;
forUpdateClause = "WITH RR USE AND KEEP UPDATE LOCKS";
- if (maj >=9) {
- supportsXMLColumn = true;
- }
+ supportsXMLColumn = versionEqualOrLaterThan(9, 0);
+
}
// platform specific settings
@@ -346,7 +347,7 @@ public class DB2Dictionary
}
sequenceSchemaSQL = "SCHEMA = ?";
sequenceNameSQL = "NAME = ?";
- if (maj == 8) {
+ if (getMajorVersion() == 8) {
// DB2 Z/OS Version 8: no bigint support, hence map Java
// long to decimal
bigintTypeName = "DECIMAL(31,0)";
@@ -372,9 +373,11 @@ public class DB2Dictionary
if (isDB2ISeriesV5R4OrEarlier()) {
supportsGetGeneratedKeys = false;
}
+
break;
}
}
+
public boolean supportsIsolationForUpdate() {
return true;
@@ -449,34 +452,34 @@ public class DB2Dictionary
public boolean isDB2UDBV82OrLater() {
return (databaseProductVersion.indexOf("SQL") != -1
|| databaseProductName.indexOf("DB2/") != -1)
- && ((maj == 8 && min >= 2) || (maj >= 9));
+ && versionEqualOrLaterThan(8, 2);
}
public boolean isDB2ZOSV8xOrLater() {
return (databaseProductVersion.indexOf("DSN") != -1
|| databaseProductName.indexOf("DB2/") == -1)
- && maj >= 8;
+ && versionLaterThan(7);
}
public boolean isDB2ISeriesV5R3OrEarlier() {
- return (databaseProductName.indexOf("AS") != -1
- && ((maj == 5 && min <=3) || maj < 5));
+ return databaseProductName.indexOf("AS") != -1
+ && versionEqualOrEarlierThan(5, 3);
}
public boolean isDB2ISeriesV5R4OrLater() {
return databaseProductName.indexOf("AS") != -1
- && (maj >=6 || (maj == 5 && min >=4));
+ && versionEqualOrLaterThan(5, 4);
}
public boolean isDB2ISeriesV5R4OrEarlier() {
- return (databaseProductName.indexOf("AS") != -1
- && ((maj == 5 && min <=4) || maj < 5));
+ return databaseProductName.indexOf("AS") != -1
+ && versionEqualOrEarlierThan(5, 4);
}
public boolean isDB2UDBV81OrEarlier() {
return (databaseProductVersion.indexOf("SQL") != -1
|| databaseProductName.indexOf("DB2/") != -1)
- && ((maj == 8 && min <= 1) || maj < 8);
+ && versionEqualOrEarlierThan(8,1);
}
/** Get the version Major/Minor for the ISeries
@@ -489,8 +492,8 @@ public class DB2Dictionary
// new jcc DBProdVersion QSQ05040 or QSQ06010
if (databaseProductName.indexOf("AS") != -1) {
// default to V5R4
- maj = 5;
- min = 4;
+ setMajorVersion(5);
+ setMinorVersion(4);
int index = databaseProductVersion.indexOf('V');
if (index != -1) {
String s = databaseProductVersion.substring(index);
@@ -500,9 +503,9 @@ public class DB2Dictionary
, false);
if (stringtokenizer.countTokens() == 3) {
String s1 = stringtokenizer.nextToken();
- maj = Integer.parseInt(s1);
+ setMajorVersion(Integer.parseInt(s1));
String s2 = stringtokenizer.nextToken();
- min = Integer.parseInt(s2);
+ setMinorVersion(Integer.parseInt(s2));
}
} else {
index = databaseProductVersion.indexOf('0');
@@ -514,9 +517,9 @@ public class DB2Dictionary
, false);
if (stringtokenizer.countTokens() == 2) {
String s1 = stringtokenizer.nextToken();
- maj = Integer.parseInt(s1);
+ setMajorVersion(Integer.parseInt(s1));
String s2 = stringtokenizer.nextToken();
- min = Integer.parseInt(s2);
+ setMinorVersion(Integer.parseInt(s2));
}
}
}
@@ -538,15 +541,15 @@ public class DB2Dictionary
// Linux DB2/LINUX DB2/LINUX
// 09.01.0000 SQL0901
if (databaseProductVersion.indexOf("09") != -1) {
- maj = 9;
+ setMajorVersion(9);
if (databaseProductVersion.indexOf("01") != -1) {
- min = 1;
+ setMinorVersion(1);
}
} else if (databaseProductVersion.indexOf("08") != -1) {
- maj = 8;
- min = 2;
+ setMajorVersion(8);
+ setMinorVersion(2);
if (databaseProductVersion.indexOf("01") != -1) {
- min = 1;
+ setMinorVersion(1);
}
}
}
@@ -933,10 +936,6 @@ public class DB2Dictionary
}
}
- String nullSafe(String s) {
- return s == null ? "" : s;
- }
-
@Override
public boolean isFatalException(int subtype, SQLException ex) {
String errorState = ex.getSQLState();
@@ -1113,11 +1112,11 @@ public class DB2Dictionary
}
public int getDB2MajorVersion() {
- return maj;
+ return getMajorVersion();
}
public int getDB2MinorVersion() {
- return min;
+ return getMinorVersion();
}
public String getDefaultSchemaName() {
@@ -1202,4 +1201,67 @@ public class DB2Dictionary
}
return null;
}
+
+ @Override
+ protected SQLBuffer toSelect(SQLBuffer select, JDBCFetchConfiguration fetch,
+ SQLBuffer tables, SQLBuffer where, SQLBuffer group,
+ SQLBuffer having, SQLBuffer order,
+ boolean distinct, boolean forUpdate, long start, long end,
+ Select sel) {
+ if (!supportsRowNum) {
+ return super.toSelect(select, fetch, tables, where, group, having, order,
+ distinct, forUpdate, start, end, sel);
+ }
+ // if no range, use standard select
+ if (!isUsingRange(start, end)) {
+ return super.toSelect(select, fetch, tables, where, group, having,
+ order, distinct, forUpdate, 0, Long.MAX_VALUE, sel);
+ }
+
+ // if no skip, ordering, or distinct can use rownum directly
+ SQLBuffer buf = new SQLBuffer(this);
+ if (!requiresSubselectForRange(start, end, distinct, order)) {
+ if (where != null && !where.isEmpty())
+ buf.append(where).append(" AND ");
+ buf.append("ROWNUM <= ").appendValue(end);
+ return super.toSelect(select, fetch, tables, buf, group, having,
+ order, distinct, forUpdate, 0, Long.MAX_VALUE, sel);
+ }
+
+ // if there is ordering, skip, or distinct we have to use subselects
+ SQLBuffer newsel = super.toSelect(select, fetch, tables, where,
+ group, having, order, distinct, forUpdate, 0, Long.MAX_VALUE,
+ sel);
+
+ // if no skip, can use single nested subselect
+ if (!isUsingOffset(start)) {
+ buf.append(getSelectOperation(fetch) + " * FROM (");
+ buf.append(newsel);
+ buf.append(") WHERE ROWNUM <= ").appendValue(end);
+ return buf;
+ }
+
+ // with a skip, we have to use a double-nested subselect to put
+ // where conditions on the rownum
+ buf.append(getSelectOperation(fetch))
+ .append(" * FROM (SELECT r.*, ROWNUM RNUM FROM (");
+ buf.append(newsel);
+ buf.append(") r");
+ if (isUsingLimit(end))
+ buf.append(" WHERE ROWNUM <= ").appendValue(end);
+ buf.append(") WHERE RNUM > ").appendValue(start);
+ return buf;
+ }
+
+ /**
+ * Return true if the select with the given parameters needs a
+ * subselect to apply a range.
+ */
+ private boolean requiresSubselectForRange(long start, long end, boolean distinct, SQLBuffer order) {
+ if (!isUsingRange(start, end))
+ return false;
+ return isUsingOffset(start) || distinct || isUsingOrderBy(order);
+ }
+
+
}
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java Fri Mar 22 18:22:38 2013
@@ -138,50 +138,53 @@ public class DBDictionary
implements Configurable, ConnectionDecorator, JoinSyntaxes,
LoggingConnectionDecorator.SQLWarningHandler, IdentifierConfiguration {
- public static final String VENDOR_OTHER = "other";
+ public static final String VENDOR_OTHER = "other";
public static final String VENDOR_DATADIRECT = "datadirect";
- public static final String SCHEMA_CASE_UPPER = IdentifierUtil.CASE_UPPER;
- public static final String SCHEMA_CASE_LOWER = IdentifierUtil.CASE_LOWER;
- public static final String SCHEMA_CASE_PRESERVE = IdentifierUtil.CASE_PRESERVE;
+ public static final String SCHEMA_CASE_UPPER = IdentifierUtil.CASE_UPPER;
+ public static final String SCHEMA_CASE_LOWER = IdentifierUtil.CASE_LOWER;
+ public static final String SCHEMA_CASE_PRESERVE = IdentifierUtil.CASE_PRESERVE;
public static final String CONS_NAME_BEFORE = "before";
- public static final String CONS_NAME_MID = "mid";
- public static final String CONS_NAME_AFTER = "after";
+ public static final String CONS_NAME_MID = "mid";
+ public static final String CONS_NAME_AFTER = "after";
public int blobBufferSize = 50000;
public int clobBufferSize = 50000;
- protected static final int RANGE_POST_SELECT = 0;
- protected static final int RANGE_PRE_DISTINCT = 1;
+ protected static final int RANGE_POST_SELECT = 0;
+ protected static final int RANGE_PRE_DISTINCT = 1;
protected static final int RANGE_POST_DISTINCT = 2;
- protected static final int RANGE_POST_LOCK = 3;
+ protected static final int RANGE_POST_LOCK = 3;
- protected static final int NANO = 1;
+ protected static final int NANO = 1;
protected static final int MICRO = NANO * 1000;
protected static final int MILLI = MICRO * 1000;
protected static final int CENTI = MILLI * 10;
- protected static final int DECI = MILLI * 100;
- protected static final int SEC = MILLI * 1000;
+ protected static final int DECI = MILLI * 100;
+ protected static final int SEC = MILLI * 1000;
- protected static final int NAME_ANY = DBIdentifierUtil.ANY;
- protected static final int NAME_TABLE = DBIdentifierUtil.TABLE;
+ protected static final int NAME_ANY = DBIdentifierUtil.ANY;
+ protected static final int NAME_TABLE = DBIdentifierUtil.TABLE;
protected static final int NAME_SEQUENCE = DBIdentifierUtil.SEQUENCE;
protected static final int UNLIMITED = -1;
protected static final int NO_BATCH = 0;
- private static final String ZERO_DATE_STR =
- "'" + new java.sql.Date(0) + "'";
- private static final String ZERO_TIME_STR = "'" + new Time(0) + "'";
- private static final String ZERO_TIMESTAMP_STR =
- "'" + new Timestamp(0) + "'";
-
- private static final Localizer _loc = Localizer.forPackage
- (DBDictionary.class);
-
+ private static final String ZERO_DATE_STR = "'" + new java.sql.Date(0) + "'";
+ private static final String ZERO_TIME_STR = "'" + new Time(0) + "'";
+ private static final String ZERO_TIMESTAMP_STR = "'" + new Timestamp(0) + "'";
+
+ private static final Localizer _loc = Localizer.forPackage(DBDictionary.class);
+
+ // Database version info preferably set from Connection metadata
+ private int major;
+ private int minor;
+
// schema data
public String platform = "Generic";
+ public String databaseProductName = "";
+ public String databaseProductVersion = "";
public String driverVendor = null;
public boolean createPrimaryKeys = true;
public String constraintNameMode = CONS_NAME_BEFORE;
@@ -412,11 +415,11 @@ public class DBDictionary
* If a native query begins with any of the values found here then it will
* be treated as a select statement.
*/
- protected final Set selectWordSet = new HashSet();
+ protected final Set<String> selectWordSet = new HashSet<String>();
// when we store values that lose precision, track the types so that the
// first time it happens we can warn the user
- private Set _precisionWarnedTypes = null;
+ private Set<Class<?>> _precisionWarnedTypes = null;
// batchLimit value:
// -1 = unlimited
@@ -452,6 +455,11 @@ public class DBDictionary
DatabaseMetaData metaData = null;
try {
metaData = conn.getMetaData();
+
+ databaseProductName = nullSafe(metaData.getDatabaseProductName());
+ databaseProductVersion = nullSafe(metaData.getDatabaseProductVersion());
+ setMajorVersion(metaData.getDatabaseMajorVersion());
+ setMinorVersion(metaData.getDatabaseMinorVersion());
try {
// JDBC3-only method, so it might throw an
// AbstractMethodError
@@ -484,15 +492,14 @@ public class DBDictionary
if (supportsDelimitedIdentifiers == null) // not explicitly set
configureNamingUtil(metaData);
- // Auto-detect generated keys retrieval support
- // unless user specified it.
+ // Auto-detect generated keys retrieval support unless user specified it.
if (supportsGetGeneratedKeys == null) {
- if (isJDBC3) {
- supportsGetGeneratedKeys =
- metaData.supportsGetGeneratedKeys();
- } else {
- supportsGetGeneratedKeys = false;
- }
+ supportsGetGeneratedKeys = (isJDBC3) ? metaData.supportsGetGeneratedKeys() : false;
+ }
+ if (log.isInfoEnabled()) {
+ log.info(_loc.get("dict-info", new Object[] {
+ metaData.getDatabaseProductName(), getMajorVersion(), getMinorVersion(),
+ metaData.getDriverName(), metaData.getDriverVersion()}));
}
}
connected = true;
@@ -1539,7 +1546,7 @@ public class DBDictionary
boolean warn;
synchronized (this) {
if (_precisionWarnedTypes == null)
- _precisionWarnedTypes = new HashSet();
+ _precisionWarnedTypes = new HashSet<Class<?>>();
warn = _precisionWarnedTypes.add(orig.getClass());
}
@@ -1841,8 +1848,8 @@ public class DBDictionary
String s;
idx = typeName.length();
int curIdx = -1;
- for (Iterator i = typeModifierSet.iterator(); i.hasNext();) {
- s = (String) i.next();
+ for (Iterator<String> i = typeModifierSet.iterator(); i.hasNext();) {
+ s = i.next();
if (typeName.toUpperCase().indexOf(s) != -1) {
curIdx = typeName.toUpperCase().indexOf(s);
if (curIdx != -1 && curIdx < idx) {
@@ -5597,4 +5604,66 @@ public class DBDictionary
public String getIdentityColumnName() {
return null;
}
+
+ protected boolean isUsingRange(long start, long end) {
+ return isUsingOffset(start) || isUsingLimit(end);
+ }
+
+ protected boolean isUsingOffset(long start) {
+ return start != 0;
+ }
+
+ protected boolean isUsingLimit(long end) {
+ return end != Long.MAX_VALUE;
+ }
+
+ protected boolean isUsingOrderBy(SQLBuffer sql) {
+ return sql != null && !sql.isEmpty();
+ }
+
+ protected boolean versionEqualOrLaterThan(int maj, int min) {
+ return (major > maj) || (major == maj && minor >= min);
+ }
+
+ protected boolean versionEqualOrEarlierThan(int maj, int min) {
+ return (major < maj) || (major == maj && minor <= min);
+ }
+
+ protected boolean versionLaterThan(int maj) {
+ return (major > maj);
+ }
+
+ /**
+ * Gets major version of the database server.
+ */
+ public final int getMajorVersion() {
+ return major;
+ }
+
+ /**
+ * Sets major version of the database server.
+ */
+ public void setMajorVersion(int maj) {
+ major = maj;
+ }
+
+ /**
+ * Gets minor version of the database server.
+ */
+ public final int getMinorVersion() {
+ return major;
+ }
+
+ /**
+ * Sets minor version of the database server.
+ */
+ public void setMinorVersion(int min) {
+ minor = min;
+ }
+
+ String nullSafe(String s) {
+ return s == null ? "" : s;
+ }
+
+
}
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java Fri Mar 22 18:22:38 2013
@@ -18,6 +18,8 @@
*/
package org.apache.openjpa.jdbc.sql;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
@@ -118,6 +120,50 @@ public class DerbyDictionary
"WRITE", "XML", "XMLEXISTS", "XMLPARSE", "XMLQUERY", "XMLSERIALIZE", "YEAR",
}));
}
+
+ @Override
+ public void connectedConfiguration(Connection conn) throws SQLException {
+ super.connectedConfiguration(conn);
+ if (versionEqualOrLaterThan(10, 5)) {
+ supportsSelectStartIndex = true;
+ supportsSelectEndIndex = true;
+ }
+ }
+
+ /**
+ * Appends a range to the given buffer.
+ * <br>
+ * A range query is never appended to a subselct clause.
+ * <br>
+ * If this dictionary supports {@link DBDictionary#supportsSelectStartIndex offset}
+ * and {@link DBDictionary#supportsSelectEndIndex limit} on queries then the
+ * syntax is <pre>
+ * [ OFFSET {start} ROWS ]
+ * [ FETCH NEXT {end-start} ROWS ONLY ]
+ * </pre>
+ * Otherwise, the offset is not used and the syntax is <pre>
+ * [ FETCH FIRST {end} ROWS ONLY ]
+ * </pre>
+ * @param buf the SQL buffer to be appended
+ * @param start starting offset. {@code 0} means offset is not used.
+ * @param end number of rows to be fetched. {@code Long.MAX_VALUE} means no limit.
+ * @param subselect flags if the buffer represents a SQL Subquery clause
+ */
+ protected void appendSelectRange(SQLBuffer buf, long start, long end, boolean subselect) {
+ // do not generate FETCH FIRST clause for subselect
+ if (subselect)
+ return;
+ if (supportsSelectStartIndex && supportsSelectEndIndex) {
+ if (isUsingOffset(start))
+ buf.append(" OFFSET ").append(Long.toString(start)).append(" ROWS ");
+ if (isUsingLimit(end)) {
+ long rowCount = end - start;
+ buf.append(" FETCH NEXT ").append(Long.toString(rowCount)).append(" ROWS ONLY");
+ }
+ } else if (isUsingLimit(end)) {
+ buf.append(" FETCH FIRST ").append(Long.toString(end)).append(" ROWS ONLY");
+ }
+ }
public void closeDataSource(DataSource dataSource) {
super.closeDataSource(dataSource);
@@ -152,5 +198,5 @@ public class DerbyDictionary
}
return super.isFatalException(subtype, ex);
}
-
+
}
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java Fri Mar 22 18:22:38 2013
@@ -451,10 +451,11 @@ public class OracleDictionary
}
// if no range, use standard select
- if (start == 0 && end == Long.MAX_VALUE)
+ if (!isUsingRange(start, end)) {
return super.toSelect(select, fetch, tables, where, group, having,
order, distinct, forUpdate, 0, Long.MAX_VALUE, sel);
-
+ }
+
// if no skip, ordering, or distinct can use rownum directly
SQLBuffer buf = new SQLBuffer(this);
if (!requiresSubselectForRange(start, end, distinct, order)) {
@@ -471,7 +472,7 @@ public class OracleDictionary
sel);
// if no skip, can use single nested subselect
- if (start == 0) {
+ if (!isUsingOffset(start)) {
buf.append(getSelectOperation(fetch) + " * FROM (");
buf.append(newsel);
buf.append(") WHERE ROWNUM <= ").appendValue(end);
@@ -480,11 +481,11 @@ public class OracleDictionary
// with a skip, we have to use a double-nested subselect to put
// where conditions on the rownum
- buf.append(getSelectOperation(fetch)
- + " * FROM (SELECT r.*, ROWNUM RNUM FROM (");
+ buf.append(getSelectOperation(fetch))
+ .append(" * FROM (SELECT r.*, ROWNUM RNUM FROM (");
buf.append(newsel);
buf.append(") r");
- if (end != Long.MAX_VALUE)
+ if (isUsingLimit(end))
buf.append(" WHERE ROWNUM <= ").appendValue(end);
buf.append(") WHERE RNUM > ").appendValue(start);
return buf;
@@ -496,14 +497,14 @@ public class OracleDictionary
*/
private boolean requiresSubselectForRange(long start, long end,
boolean distinct, SQLBuffer order) {
- if (start == 0 && end == Long.MAX_VALUE)
- return false;
- return start != 0 || distinct || (order != null && !order.isEmpty());
+ if (!isUsingRange(start, end))
+ return false;
+ return isUsingOffset(start) || distinct || isUsingOrderBy(order);
}
/**
* Check to see if we have set the {@link #SELECT_HINT} in the
- * fetch configuraiton, and if so, append the Orache hint after the
+ * fetch configuration, and if so, append the Oracle hint after the
* "SELECT" part of the query.
*/
public String getSelectOperation(JDBCFetchConfiguration fetch) {
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java Fri Mar 22 18:22:38 2013
@@ -364,7 +364,9 @@ public class SelectImpl
_dict.setQueryTimeout(stmnt,
store.getFetchConfiguration().getQueryTimeout());
rs = executeQuery(conn, stmnt, sql, false, store);
- return getCount(rs);
+ int count = getCount(rs);
+
+ return applyRange(count);
} finally {
if (rs != null)
try { rs.close(); } catch (SQLException se) {}
@@ -374,6 +376,27 @@ public class SelectImpl
try { conn.close(); } catch (SQLException se) {}
}
}
+
+ /**
+ * Applies range calculation on the actual number of rows selected
+ * by a {@code COUNT(*)} query. A range query may use either only
+ * the limit or both offset and limit based on database dictionary support
+ * and accordingly the number of rows in the result set needs to be
+ * modified.
+ * @param count
+ * @return
+ */
+ int applyRange(int count) {
+ DBDictionary dict = getDictionary();
+ if (dict.supportsSelectStartIndex) {
+ if (getStartIndex() > 0) count -= getStartIndex();
+ if (getEndIndex() != Long.MAX_VALUE) {
+ long size = getEndIndex() - getStartIndex();
+ count = (int)Math.min(count,size);
+ }
+ }
+ return count;
+ }
public Result execute(JDBCStore store, JDBCFetchConfiguration fetch)
throws SQLException {
Modified: openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/resources/org/apache/openjpa/jdbc/sql/localizer.properties Fri Mar 22 18:22:38 2013
@@ -123,6 +123,7 @@ warn-generic: Your database configuratio
appropriate dictionary class in the "openjpa.jdbc.DBDictionary" \
property of the OpenJPA configuration.
using-dict: Using dictionary class "{0}"{1}.
+dict-info: Connected to {0} version {1}.{2} using JDBC driver {3} version {4}.
no-pessimistic: This dictionary ({0}) does not support locking, so \
operations may not be performed using a Broker that does not have \
optimistic locking enabled.
@@ -169,7 +170,7 @@ millis-query-timeout: JDBC locking does
timeouts. Use timeouts that are multiples of 1000 for even second values.
db-not-supported: The database product "{0}", version "{1}" is not officially supported.
stream-exception: Unexpected error recovering the row to stream the LOB.
-batch_unlimit: The batch limit was changed from unlimit (-1) to {0}.
+batch_unlimit: The batch limit was changed from unlimited (-1) to {0}.
function-not-supported: The database dictionary in use ("{0}") \
does not support "{1}" function.
batch-update-success-count: ExecuteBatch command returns update success count {0}
@@ -216,7 +217,7 @@ sequencesql-override: Going to override
DBDictionary.sequenceSQL string, which is: "{0}" \
with the value of: "{1}", which is the default sequenceSQL string for DB2 on the give operating system. \
If the user intends to use a value of: "{0}" by defining it as a DBDictionary property, \
- please change the case of at least one of the charaters of the string defined in \
+ please change the case of at least one of the characters of the string defined in \
the property. This will allow openJPA to detect a difference between the DB2 default \
string and the string set in the property and will further allow openJPA to use the \
string defined by the property rather than the default string for DB2.
Modified: openjpa/trunk/openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/TestDB2Dictionary.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/TestDB2Dictionary.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/TestDB2Dictionary.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/test/java/org/apache/openjpa/jdbc/sql/TestDB2Dictionary.java Fri Mar 22 18:22:38 2013
@@ -225,9 +225,8 @@ public class TestDB2Dictionary extends M
DB2Dictionary dict = new DB2Dictionary();
// skip all the meta data resolution code.
- dict.db2ServerType=DB2Dictionary.db2UDBV82OrLater;
- dict.maj=9;
-
+ dict.db2ServerType = DB2Dictionary.db2UDBV82OrLater;
+ dict.setMajorVersion(9);
dict.setConfiguration(mockConfiguration);
assertNotNull(dict);
dict.connectedConfiguration(mockConnection);
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestLocators.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestLocators.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestLocators.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestLocators.java Fri Mar 22 18:22:38 2013
@@ -29,7 +29,7 @@ package org.apache.openjpa.persistence.j
import java.util.*;
import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
-import org.apache.openjpa.jdbc.sql.OracleDictionary;
+import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.persistence.Extent;
import org.apache.openjpa.persistence.jdbc.common.apps.*;
@@ -84,7 +84,7 @@ public class TestLocators
(OpenJPAEntityManager)currentEntityManager();
JDBCConfiguration conf = (JDBCConfiguration)
((OpenJPAEntityManagerSPI) pm).getConfiguration();
- OracleDictionary dict = (OracleDictionary)
+ DBDictionary dict = (DBDictionary)
conf.getDBDictionaryInstance();
int t = dict.maxEmbeddedBlobSize;
@@ -159,7 +159,7 @@ public class TestLocators
(OpenJPAEntityManager)currentEntityManager();
JDBCConfiguration conf = (JDBCConfiguration)
((OpenJPAEntityManagerSPI) pm).getConfiguration();
- OracleDictionary dict = (OracleDictionary)
+ DBDictionary dict = (DBDictionary)
conf.getDBDictionaryInstance();
int t = dict.maxEmbeddedClobSize;
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestRangeQuery.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestRangeQuery.java?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestRangeQuery.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/meta/TestRangeQuery.java Fri Mar 22 18:22:38 2013
@@ -65,12 +65,13 @@ public class TestRangeQuery extends
q.setFirstResult(5).setMaxResults(15);
List results = (List) q.getResultList();
- assertEquals(5, results.size());
for (int i = 0; i < results.size(); i++) {
EagerOuterJoinPC pc = (EagerOuterJoinPC) results.get(i);
assertEquals(String.valueOf(i + 5), pc.getName());
+ System.err.println("Row " + i + " " + pc.getName());
}
+ assertEquals(5, results.size());
q.closeAll();
em.close();
}
Modified: openjpa/trunk/openjpa-project/src/doc/manual/ref_guide_dbsetup.xml
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-project/src/doc/manual/ref_guide_dbsetup.xml?rev=1459918&r1=1459917&r2=1459918&view=diff
==============================================================================
--- openjpa/trunk/openjpa-project/src/doc/manual/ref_guide_dbsetup.xml (original)
+++ openjpa/trunk/openjpa-project/src/doc/manual/ref_guide_dbsetup.xml Fri Mar 22 18:22:38 2013
@@ -4047,7 +4047,29 @@ properties:
get extended exception text.
</para>
</listitem>
+
+ <listitem id="DB2Dictionary.SupportsRowNum">
+ <para>
+ <indexterm>
+ <primary>
+ DB2
+ </primary>
+ <secondary>
+ SupportsRowNum
+ </secondary>
+ </indexterm>
+<literal>SupportsRowNum</literal>: If true, OpenJPA will use <literal>ROWNUM</literal> facility
+for range based queries that set an offset and/or limit via <literal>setFirstResult()</literal>
+and <literal>setMaxResult()</literal> query methods. This property must be set to <literal>true</literal>
+alongwith <lietral>SupportsSelectStartIndex</lietral> and <lietral>SupportsSelectEndIndex</lietral>.
+By default, <literal>SupportsRowNum</literal> is set to <literal>false</literal>.
+It is appropriate to set <literal>SupportsRowNum</literal> to <literal>true</literal> only when
+DB2 version being used is 9.7 or later.
+
+ </para>
+ </listitem>
</itemizedlist>
+
</section>
<section id="ref_guide_dbsetup_dbsupport_delim_id">
<title>