openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ppod...@apache.org
Subject 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...
Date Fri, 22 Mar 2013 18:22:38 GMT
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>



Mime
View raw message