Author: pcl
Date: Tue Jan 15 19:49:23 2008
New Revision: 612345
URL: http://svn.apache.org/viewvc?rev=612345&view=rev
Log:
OPENJPA-488, OPENJPA-489, OPENJPA-490, OPENJPA-491, OPENJPA-492
Added:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestJDBCGrouping.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/GroupingTestCase.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubstring.java
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractDB2Dictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractSQLServerDictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AccessDictionary.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/H2Dictionary.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/OracleDictionary.java
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/exps/ExpressionFactory.java
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/jpql/JPQLExpressionBuilder.java
openjpa/trunk/openjpa-kernel/src/main/jjtree/org/apache/openjpa/kernel/jpql/JPQL.jjt
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestQueryPagination.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/AllFieldTypes.java
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractDB2Dictionary.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractDB2Dictionary.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractDB2Dictionary.java
(original)
+++
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractDB2Dictionary.java
Tue Jan 15 19:49:23 2008
@@ -26,6 +26,8 @@
public abstract class AbstractDB2Dictionary
extends DBDictionary {
+ public int varcharCastLength = 1000;
+
public AbstractDB2Dictionary() {
numericTypeName = "DOUBLE";
bitTypeName = "SMALLINT";
@@ -36,11 +38,13 @@
// DB2-based databases have restrictions on having uncast parameters
// in string functions
- toUpperCaseFunction = "UPPER(CAST({0} AS VARCHAR(1000)))";
- toLowerCaseFunction = "LOWER(CAST({0} AS VARCHAR(1000)))";
+ toUpperCaseFunction = "UPPER(CAST({0} AS VARCHAR(" + varcharCastLength
+ + ")))";
+ toLowerCaseFunction = "LOWER(CAST({0} AS VARCHAR(" + varcharCastLength
+ + ")))";
stringLengthFunction = "LENGTH({0})";
- concatenateFunction = "(CAST({0} AS VARCHAR(1000)))||"
- + "(CAST({1} AS VARCHAR(1000)))";
+ concatenateFunction = "(CAST({0} AS VARCHAR(" + varcharCastLength
+ + ")))||(CAST({1} AS VARCHAR(1000)))";
trimLeadingFunction = "LTRIM({0})";
trimTrailingFunction = "RTRIM({0})";
@@ -74,9 +78,11 @@
FilterValue start) {
buf.append("(LOCATE(CAST((");
find.appendTo(buf);
- buf.append(") AS VARCHAR(1000)), CAST((");
+ buf.append(") AS VARCHAR(").append(Integer.toString(varcharCastLength))
+ .append(")), CAST((");
str.appendTo(buf);
- buf.append(") AS VARCHAR(1000))");
+ buf.append(") AS VARCHAR(").append(Integer.toString(varcharCastLength))
+ .append("))");
if (start != null) {
buf.append(", CAST((");
start.appendTo(buf);
@@ -89,15 +95,30 @@
FilterValue end) {
buf.append("SUBSTR(CAST((");
str.appendTo(buf);
- buf.append(") AS VARCHAR(1000)), CAST((");
- start.appendTo(buf);
- buf.append(") AS INTEGER) + 1");
- if (end != null) {
- buf.append(", CAST((");
- end.appendTo(buf);
- buf.append(") AS INTEGER) - CAST((");
+ buf.append(") AS VARCHAR(").append(Integer.toString(varcharCastLength))
+ .append(")), ");
+ if (start.getValue() instanceof Number) {
+ long startLong = toLong(start);
+ buf.append(Long.toString(startLong + 1));
+ } else {
+ buf.append("CAST((");
start.appendTo(buf);
- buf.append(") AS INTEGER)");
+ buf.append(") AS INTEGER) + 1");
+ }
+ if (end != null) {
+ buf.append(", ");
+ if (start.getValue() instanceof Number
+ && end.getValue() instanceof Number) {
+ long startLong = toLong(start);
+ long endLong = toLong(end);
+ buf.append(Long.toString(endLong - startLong));
+ } else {
+ buf.append("CAST((");
+ end.appendTo(buf);
+ buf.append(") AS INTEGER) - CAST((");
+ start.appendTo(buf);
+ buf.append(") AS INTEGER)");
+ }
}
buf.append(")");
}
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractSQLServerDictionary.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractSQLServerDictionary.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractSQLServerDictionary.java
(original)
+++
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractSQLServerDictionary.java
Tue Jan 15 19:49:23 2008
@@ -117,23 +117,23 @@
public void substring(SQLBuffer buf, FilterValue str, FilterValue start,
FilterValue end) {
- buf.append("SUBSTRING(");
- str.appendTo(buf);
- buf.append(", ");
- start.appendTo(buf);
- buf.append(" + 1, ");
- if (end != null) {
- buf.append("(");
- end.appendTo(buf);
- buf.append(")");
- } else {
+ if (end != null)
+ super.substring(buf, str, start, end);
+ else {
+ // ### it would be good to change this logic as in DBDictionary to
+ // ### simplify the generated SQL
+ buf.append("SUBSTRING(");
+ str.appendTo(buf);
+ buf.append(", ");
+ start.appendTo(buf);
+ buf.append(" + 1, ");
buf.append("LEN(");
str.appendTo(buf);
buf.append(")");
+ buf.append(" - (");
+ start.appendTo(buf);
+ buf.append("))");
}
- buf.append(" - (");
- start.appendTo(buf);
- buf.append("))");
}
public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find,
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AccessDictionary.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AccessDictionary.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AccessDictionary.java
(original)
+++
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AccessDictionary.java
Tue Jan 15 19:49:23 2008
@@ -63,6 +63,8 @@
supportsForeignKeys = false;
supportsDeferredConstraints = false;
maxIndexesPerTable = 32;
+
+ substringFunctionName = "MID";
}
public void setLong(PreparedStatement stmnt, int idx, long val, Column col)
@@ -74,23 +76,6 @@
stmnt.setInt(idx, (int) val);
else
stmnt.setDouble(idx, val);
- }
-
- public void substring(SQLBuffer buf, FilterValue str, FilterValue start,
- FilterValue end) {
- buf.append("MID(");
- str.appendTo(buf);
- buf.append(", (");
- start.appendTo(buf);
- buf.append(" + 1)");
- if (end != null) {
- buf.append(", (");
- end.appendTo(buf);
- buf.append(" - ");
- start.appendTo(buf);
- buf.append(")");
- }
- buf.append(")");
}
}
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=612345&r1=612344&r2=612345&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
Tue Jan 15 19:49:23 2008
@@ -86,6 +86,7 @@
import org.apache.openjpa.jdbc.schema.Unique;
import org.apache.openjpa.kernel.Filters;
import org.apache.openjpa.kernel.exps.Path;
+import org.apache.openjpa.kernel.exps.Literal;
import org.apache.openjpa.lib.conf.Configurable;
import org.apache.openjpa.lib.conf.Configuration;
import org.apache.openjpa.lib.jdbc.ConnectionDecorator;
@@ -2494,19 +2495,36 @@
*/
public void substring(SQLBuffer buf, FilterValue str, FilterValue start,
FilterValue end) {
- buf.append(substringFunctionName).append("((");
+ buf.append(substringFunctionName).append("(");
str.appendTo(buf);
- buf.append("), (");
- start.appendTo(buf);
- buf.append(" + 1)");
- if (end != null) {
- buf.append(", (");
- end.appendTo(buf);
- buf.append(" - (");
+ buf.append(", ");
+ if (start instanceof Number) {
+ long startLong = toLong(start);
+ buf.append(Long.toString(startLong + 1));
+ } else {
+ buf.append("(");
start.appendTo(buf);
- buf.append("))");
+ buf.append(" + 1)");
+ }
+ if (end != null) {
+ buf.append(", ");
+ if (start.getValue() instanceof Number
+ && end.getValue() instanceof Number) {
+ long startLong = toLong(start);
+ long endLong = toLong(end);
+ buf.append(Long.toString(endLong - startLong));
+ } else {
+ end.appendTo(buf);
+ buf.append(" - (");
+ start.appendTo(buf);
+ buf.append(")");
+ }
}
buf.append(")");
+ }
+
+ long toLong(FilterValue litValue) {
+ return ((Number) litValue.getValue()).longValue();
}
/**
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/H2Dictionary.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/H2Dictionary.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/H2Dictionary.java
(original)
+++
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/H2Dictionary.java
Tue Jan 15 19:49:23 2008
@@ -193,23 +193,6 @@
buf.append(" OFFSET ").appendValue(start);
}
- public void substring(SQLBuffer buf, FilterValue str, FilterValue start,
- FilterValue end) {
- buf.append("SUBSTR(");
- str.appendTo(buf);
- buf.append(", (");
- start.appendTo(buf);
- buf.append(" + 1)");
- if (end != null) {
- buf.append(", (");
- end.appendTo(buf);
- buf.append(" - ");
- start.appendTo(buf);
- buf.append(")");
- }
- buf.append(")");
- }
-
public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find,
FilterValue start) {
buf.append("(POSITION(");
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=612345&r1=612344&r2=612345&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
Tue Jan 15 19:49:23 2008
@@ -157,6 +157,8 @@
"LONG", "MAXEXTENTS", "MINUS", "MODE", "NOAUDIT", "NOCOMPRESS",
"NOWAIT", "OFFLINE", "ONLINE", "PCTFREE", "ROW",
}));
+
+ substringFunctionName = "SUBSTR";
}
public void endConfiguration() {
@@ -425,23 +427,6 @@
if (hint != null)
select += " " + hint;
return select;
- }
-
- public void substring(SQLBuffer buf, FilterValue str, FilterValue start,
- FilterValue end) {
- buf.append("SUBSTR(");
- str.appendTo(buf);
- buf.append(", (");
- start.appendTo(buf);
- buf.append(" + 1)");
- if (end != null) {
- buf.append(", (");
- end.appendTo(buf);
- buf.append(" - ");
- start.appendTo(buf);
- buf.append(")");
- }
- buf.append(")");
}
public void setString(PreparedStatement stmnt, int idx, String val,
Modified:
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/exps/ExpressionFactory.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/exps/ExpressionFactory.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/exps/ExpressionFactory.java
(original)
+++
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/exps/ExpressionFactory.java
Tue Jan 15 19:49:23 2008
@@ -334,7 +334,8 @@
/**
* Return a value representing the [EMAIL PROTECTED] String#substring}
function on
- * the given target with the given args.
+ * the given target with the given args. As with [EMAIL PROTECTED]
String#substring},
+ * the start index is zero-based, and the second argument is the end index.
*/
public Value substring(Value str, Value args);
Modified:
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/jpql/JPQLExpressionBuilder.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/jpql/JPQLExpressionBuilder.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/jpql/JPQLExpressionBuilder.java
(original)
+++
openjpa/trunk/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/jpql/JPQLExpressionBuilder.java
Tue Jan 15 19:49:23 2008
@@ -977,21 +977,28 @@
// arg2 is the end index): we perform the translation by
// adding one to the first argument, and then adding the
// first argument to the second argument to get the endIndex
- //
- // ### we could get rid of some messy expressions by checking
for
- // the common case where the arguments are specified as
- // a literal, in which case we could just do the calculations
- // in memory; otherwise we wind up with ugly looking SQL like:
- // SELECT ... FROM ... t1
- // (SUBSTRING(t1.ASTR, (? - ?) + 1, (? + (? - ?)) - ((? - ?)))
= ?)
- // [params=(long) 2, (int) 1, (long) 2, (long) 2, (int) 1,
- // (long) 2, (int) 1, (String) oo
- return factory.substring(val1, factory.newArgumentList
- (factory.subtract(val2, factory.newLiteral
- (Numbers.valueOf(1), Literal.TYPE_NUMBER)),
- (factory.add(val3,
- (factory.subtract(val2, factory.newLiteral
- (Numbers.valueOf(1),
Literal.TYPE_NUMBER)))))));
+ Value start;
+ Value end;
+ if (val2 instanceof Literal && val3 instanceof Literal) {
+ // optimize SQL for the common case of two literals
+ long jpqlStart = ((Number) ((Literal) val2).getValue())
+ .longValue();
+ long length = ((Number) ((Literal) val3).getValue())
+ .longValue();
+ start = factory.newLiteral(new Long(jpqlStart - 1),
+ Literal.TYPE_NUMBER);
+ long endIndex = length + (jpqlStart - 1);
+ end = factory.newLiteral(new Long(endIndex),
+ Literal.TYPE_NUMBER);
+ } else {
+ start = factory.subtract(val2, factory.newLiteral
+ (Numbers.valueOf(1), Literal.TYPE_NUMBER));
+ end = factory.add(val3,
+ (factory.subtract(val2, factory.newLiteral
+ (Numbers.valueOf(1), Literal.TYPE_NUMBER))));
+ }
+ return factory.substring(val1, factory.newArgumentList(
+ start, end));
case JJTLOCATE:
// as with SUBSTRING (above), the semantics for LOCATE differ
@@ -1067,6 +1074,15 @@
case JJTCURRENTTIMESTAMP:
return factory.getCurrentTimestamp();
+ case JJTSELECTEXTENSION:
+ return eval(node.children[0]);
+
+ case JJTGROUPBYEXTENSION:
+ return eval(node.children[0]);
+
+ case JJTORDERBYEXTENSION:
+ return eval(node.children[0]);
+
default:
throw parseException(EX_FATAL, "bad-tree",
new Object[]{ node }, null);
@@ -1669,9 +1685,6 @@
// parser may sometimes (unfortunately) throw
throw new UserException(_loc.get("parse-error",
new Object[]{ e.toString(), jpql }));
- } catch (ParseException e) {
- throw new UserException(_loc.get("parse-error",
- new Object[]{ e.toString(), jpql }), e);
}
}
Modified:
openjpa/trunk/openjpa-kernel/src/main/jjtree/org/apache/openjpa/kernel/jpql/JPQL.jjt
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-kernel/src/main/jjtree/org/apache/openjpa/kernel/jpql/JPQL.jjt?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-kernel/src/main/jjtree/org/apache/openjpa/kernel/jpql/JPQL.jjt
(original)
+++
openjpa/trunk/openjpa-kernel/src/main/jjtree/org/apache/openjpa/kernel/jpql/JPQL.jjt
Tue Jan 15 19:49:23 2008
@@ -64,6 +64,7 @@
public class JPQL
{
String jpql;
+ boolean extensionsEnabled = true;
public JPQL (String jpql)
@@ -498,6 +499,13 @@
| identification_variable()
| (<OBJECT> "(" identification_variable() ")")
| constructor_expression()
+ | select_extension()
+}
+
+
+void select_extension() #SELECTEXTENSION(extensionsEnabled) : { }
+{
+ scalar_function()
}
@@ -506,12 +514,12 @@
subselect_expression() (<COMMA> subselect_expression())*
}
+
void subselect_expression() #SELECTEXPRESSION : { }
{
LOOKAHEAD(path()) path()
| aggregate_select_expression()
| LOOKAHEAD(1) identification_variable()
-
}
@@ -615,7 +623,13 @@
void groupby_item() : { }
{
- LOOKAHEAD(path()) path() | identification_variable()
+ LOOKAHEAD(path()) path() | identification_variable() |
groupby_extension()
+}
+
+
+void groupby_extension() #GROUPBYEXTENSION(extensionsEnabled) : { }
+{
+ scalar_function()
}
@@ -841,6 +855,12 @@
)
}
+void scalar_function() : { }
+{
+ functions_returning_numerics()
+ | functions_returning_datetime()
+ | functions_returning_strings()
+}
void arithmetic_value() : { }
{
@@ -1073,7 +1093,14 @@
void orderby_item() #ORDERBYITEM : { }
{
- path() [ <ASC> #ASCENDING | <DESC> #DESCENDING ]
+ (LOOKAHEAD(path()) path() | orderby_extension())
+ [ <ASC> #ASCENDING | <DESC> #DESCENDING ]
+}
+
+
+void orderby_extension() #ORDERBYEXTENSION(extensionsEnabled) : { }
+{
+ aggregate_select_expression()
}
Added:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestJDBCGrouping.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestJDBCGrouping.java?rev=612345&view=auto
==============================================================================
---
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestJDBCGrouping.java
(added)
+++
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestJDBCGrouping.java
Tue Jan 15 19:49:23 2008
@@ -0,0 +1,30 @@
+/*
+ * 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.openjpa.persistence.jdbc.query;
+
+import javax.persistence.Query;
+
+import org.apache.openjpa.persistence.query.GroupingTestCase;
+
+public class TestJDBCGrouping extends GroupingTestCase {
+
+ protected void prepareQuery(Query q) {
+ // nothing to do for JDBC case
+ }
+}
\ No newline at end of file
Added:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/GroupingTestCase.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/GroupingTestCase.java?rev=612345&view=auto
==============================================================================
---
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/GroupingTestCase.java
(added)
+++
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/GroupingTestCase.java
Tue Jan 15 19:49:23 2008
@@ -0,0 +1,277 @@
+package org.apache.openjpa.persistence.query;
+
+import java.util.*;
+
+import javax.persistence.EntityManager;
+import javax.persistence.Query;
+
+import org.apache.openjpa.persistence.test.SingleEMTestCase;
+import org.apache.openjpa.persistence.simple.AllFieldTypes;
+import org.apache.openjpa.persistence.ArgumentException;
+
+/**
+ * <p>Tests grouping and having capabilities.</p>
+ *
+ * @author Abe White
+ */
+public abstract class GroupingTestCase
+ extends SingleEMTestCase {
+
+ protected abstract void prepareQuery(Query q);
+
+ public void setUp() {
+ super.setUp(AllFieldTypes.class, CLEAR_TABLES, "openjpa.Log",
"SQL=TRACE");
+
+ AllFieldTypes pc1 = new AllFieldTypes();
+ AllFieldTypes pc2 = new AllFieldTypes();
+ AllFieldTypes pc3 = new AllFieldTypes();
+ AllFieldTypes pc4 = new AllFieldTypes();
+
+ // pc1 and pc2, pc3 and pc4 grouped on intField, shortField
+ pc1.setIntField(1);
+ pc1.setShortField((short) -1);
+ pc2.setIntField(1);
+ pc2.setShortField((short) -1);
+ pc3.setIntField(2);
+ pc3.setShortField((short) -2);
+ pc4.setIntField(2);
+ pc4.setShortField((short) -2);
+
+ // pc1 and pc2 grouped on stringField
+ pc1.setStringField("abc");
+ pc2.setStringField("acd");
+ pc3.setStringField("def");
+ pc4.setStringField("efg");
+
+ // pc2 and pc3 grouped on byteField
+ pc2.setByteField((byte) 1);
+ pc3.setByteField((byte) 1);
+ pc1.setByteField((byte) 0);
+ pc4.setByteField((byte) 2);
+
+ // longField is unique id
+ pc1.setLongField(1L);
+ pc2.setLongField(2L);
+ pc3.setLongField(3L);
+ pc4.setLongField(4L);
+
+ // set up some relations
+ pc1.setSelfOneOne(pc4);
+ pc2.setSelfOneOne(pc3);
+ pc3.setSelfOneOne(pc2);
+ pc4.setSelfOneOne(pc1);
+
+ // if variable testing, set up some 1-Ms instead of the 1-1s above
+ if (getName().startsWith("testVariable")) {
+ pc1.setSelfOneOne(pc1);
+ pc2.setSelfOneOne(pc1);
+ pc1.getSelfOneMany().add(pc1);
+ pc1.getSelfOneMany().add(pc2);
+
+ pc3.setSelfOneOne(pc3);
+ pc4.setSelfOneOne(pc3);
+ pc3.getSelfOneMany().add(pc3);
+ pc3.getSelfOneMany().add(pc4);
+ }
+
+ EntityManager em = emf.createEntityManager();
+ em.getTransaction().begin();
+ em.persist(pc1);
+ em.persist(pc2);
+ em.persist(pc3);
+ em.persist(pc4);
+ em.getTransaction().commit();
+ em.close();
+ }
+
+ public void testSimpleGroup() {
+ Query q = em.createQuery("select o.intField from AllFieldTypes o " +
+ "group by o.intField order by o.intField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Integer(1), itr.next());
+ assertEquals(new Integer(2), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testOrderByAggregate() {
+ // this is an extension of JPQL
+ Query q = em.createQuery("select sum(o.shortField) " +
+ "from AllFieldTypes o"
+ + " group by o.intField order by sum(o.shortField) asc");
+ prepareQuery(q);
+ // this might fail in MySQL
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Long(-4), itr.next());
+ assertEquals(new Long(-2), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testCompoundGroupSame() {
+ Query q = em.createQuery("select o.intField from AllFieldTypes o " +
+ "group by o.intField, o.shortField order by o.shortField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Integer(2), itr.next());
+ assertEquals(new Integer(1), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testCompoundGroupDifferent() {
+ Query q = em.createQuery("select o.intField from AllFieldTypes o " +
+ "group by o.intField, o.byteField order by o.intField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(4, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Integer(1), itr.next());
+ assertEquals(new Integer(1), itr.next());
+ assertEquals(new Integer(2), itr.next());
+ assertEquals(new Integer(2), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testDifferentGroupLengths() {
+ Query q = em.createQuery("select o.byteField from AllFieldTypes o"
+ + " group by o.byteField order by o.byteField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(3, res.size());
+ Iterator itr = res.iterator();
+ assertEquals((byte) 0, itr.next());
+ assertEquals((byte) 1, itr.next());
+ assertEquals((byte) 2, itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testGroupRelationField() {
+ Query q = em.createQuery("select o.selfOneOne.intField " +
+ "from AllFieldTypes o group by o.selfOneOne.intField " +
+ "order by o.selfOneOne.intField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Integer(1), itr.next());
+ assertEquals(new Integer(2), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testSubstringInGroupBy() {
+ // this is an extension of JPQL
+ Query q = em.createQuery("select substring(o.stringField, 1, 1), " +
+ "count(o) from AllFieldTypes o " +
+ "group by substring(o.stringField, 1, 1)");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(3, res.size());
+
+ q = em.createQuery("select substring(o.stringField, 1, 2), count(o) " +
+ "from AllFieldTypes o group by substring(o.stringField, 1, 2)");
+ prepareQuery(q);
+ res = q.getResultList();
+ assertEquals(4, res.size());
+ }
+
+ public void testGroupedAggregate() {
+ Query q = em.createQuery("select count(o) from AllFieldTypes o " +
+ "group by o.byteField order by o.byteField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(3, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Long(1), itr.next());
+ assertEquals(new Long(2), itr.next());
+ assertEquals(new Long(1), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testGroupedRelationAggregate() {
+ Query q = em.createQuery("select count(o), max(o.selfOneOne.longField)"
+ + " from AllFieldTypes o group by o.intField"
+ + " order by o.intField asc");
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ Object[] o = (Object[]) itr.next();
+ assertEquals(new Long(2), o[0]);
+ assertEquals(new Long(4), o[1]);
+ o = (Object[]) itr.next();
+ assertEquals(new Long(2), o[0]);
+ assertEquals(new Long(2), o[1]);
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testGroupedMixedProjection() {
+ Query q = em.createQuery("select count(o), o.shortField " +
+ "from AllFieldTypes o group by o.intField, o.shortField " +
+ "order by o.intField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ Object[] o = (Object[]) itr.next();
+ assertEquals(new Long(2), o[0]);
+ assertEquals(new Short((short) -1), o[1]);
+ o = (Object[]) itr.next();
+ assertEquals(new Long(2), o[0]);
+ assertEquals(new Short((short) -2), o[1]);
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testSimpleHaving() {
+ Query q = em.createQuery("select o.intField from AllFieldTypes o " +
+ "group by o.intField having o.intField < 2");
+ prepareQuery(q);
+ assertEquals(new Integer(1), q.getSingleResult());
+ }
+
+ public void testAggregateHaving() {
+ Query q = em.createQuery("select o.byteField from AllFieldTypes o " +
+ "group by o.byteField having count(o) > 1");
+ prepareQuery(q);
+ assertEquals(new Byte((byte) 1), q.getSingleResult());
+ }
+
+ public void testMixedHaving() {
+ Query q = em.createQuery("select o.byteField from AllFieldTypes o " +
+ "group by o.byteField having count(o) > 1 or o.byteField = 0 " +
+ "order by o.byteField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Byte((byte) 0), itr.next());
+ assertEquals(new Byte((byte) 1), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testVariableGroup() {
+ Query q = em.createQuery("select max(other.longField) " +
+ "from AllFieldTypes o, AllFieldTypes other " +
+ "where other member of o.selfOneMany " +
+ "group by other.intField order by other.intField asc");
+ prepareQuery(q);
+ List res = q.getResultList();
+ assertEquals(2, res.size());
+ Iterator itr = res.iterator();
+ assertEquals(new Long(2), itr.next());
+ assertEquals(new Long(4), itr.next());
+ assertTrue(!itr.hasNext());
+ }
+
+ public void testVariableHaving() {
+ Query q = em.createQuery("select max(o.longField), other.byteField " +
+ "from AllFieldTypes o, AllFieldTypes other " +
+ "where other member of o.selfOneMany " +
+ "group by other.byteField having sum(other.intField) = 2");
+ prepareQuery(q);
+ assertEquals(new Long(3), ((Object[])q.getSingleResult())[0]);
+ }
+}
Modified:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestQueryPagination.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestQueryPagination.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestQueryPagination.java
(original)
+++
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestQueryPagination.java
Tue Jan 15 19:49:23 2008
@@ -34,7 +34,7 @@
extends SQLListenerTestCase {
public void setUp() {
- setUp(SimpleEntity.class, CLEAR_TABLES, "openjpa.Log", "SQL=TRACE");
+ setUp(SimpleEntity.class, CLEAR_TABLES);
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Added:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubstring.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubstring.java?rev=612345&view=auto
==============================================================================
---
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubstring.java
(added)
+++
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubstring.java
Tue Jan 15 19:49:23 2008
@@ -0,0 +1,61 @@
+/*
+ * 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.openjpa.persistence.query;
+
+import javax.persistence.EntityManager;
+
+import org.apache.openjpa.persistence.test.SingleEMTestCase;
+
+public class TestSubstring extends SingleEMTestCase {
+
+ public void setUp() {
+ super.setUp(SimpleEntity.class, CLEAR_TABLES, "openjpa.Log",
"SQL=TRACE");
+
+ EntityManager em = emf.createEntityManager();
+ em.getTransaction().begin();
+ em.persist(new SimpleEntity("foo", "bar"));
+ em.getTransaction().commit();
+ em.close();
+ }
+
+ public void testSingleCharacterSubstringInWhere() {
+ assertEquals((long) 1, em.createQuery("select count(o) from simple o "
+
+ "where substring(o.value, 1, 1) = 'b'").getSingleResult());
+ assertEquals((long) 1, em.createQuery("select count(o) from simple o "
+
+ "where substring(o.value, 2, 1) = 'a'").getSingleResult());
+ assertEquals((long) 1, em.createQuery("select count(o) from simple o "
+
+ "where substring(o.value, 3, 1) = 'r'").getSingleResult());
+ }
+
+ public void testMultiCharacterSubstringInWhere() {
+ assertEquals((long) 1, em.createQuery("select count(o) from simple o "
+
+ "where substring(o.value, 1, 2) = 'ba'").getSingleResult());
+ assertEquals((long) 1, em.createQuery("select count(o) from simple o "
+
+ "where substring(o.value, 2, 2) = 'ar'").getSingleResult());
+ }
+
+ public void testSubstringInSelect() {
+ assertEquals("b", em.createQuery("select substring(o.value, 1, 1) " +
+ "from simple o").getSingleResult());
+ assertEquals("a", em.createQuery("select substring(o.value, 2, 1) " +
+ "from simple o").getSingleResult());
+ assertEquals("r", em.createQuery("select substring(o.value, 3, 1) " +
+ "from simple o").getSingleResult());
+ }
+}
Modified:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/AllFieldTypes.java
URL:
http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/AllFieldTypes.java?rev=612345&r1=612344&r2=612345&view=diff
==============================================================================
---
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/AllFieldTypes.java
(original)
+++
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/AllFieldTypes.java
Tue Jan 15 19:49:23 2008
@@ -25,14 +25,18 @@
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
+import java.util.List;
+import java.util.ArrayList;
import javax.persistence.Entity;
import javax.persistence.Version;
+import javax.persistence.OneToOne;
+import javax.persistence.OneToMany;
import org.apache.openjpa.persistence.PersistentCollection;
@Entity
public class AllFieldTypes {
-
+
public static enum EnumType {Value1, Value2};
// @Basic types
@@ -74,6 +78,12 @@
@PersistentCollection
private int[] arrayOfInts;
+ // one-to-one and one-to-many relations to self
+ @OneToOne
+ private AllFieldTypes selfOneOne;
+ @OneToMany
+ private List<AllFieldTypes> selfOneMany = new ArrayList<AllFieldTypes>();
+
public void setShortField(short shortField) {
this.shortField = shortField;
}
@@ -338,5 +348,20 @@
wShortField = shortField;
}
+ public AllFieldTypes getSelfOneOne() {
+ return selfOneOne;
+ }
+
+ public void setSelfOneOne(AllFieldTypes selfOneOne) {
+ this.selfOneOne = selfOneOne;
+ }
+
+ public List<AllFieldTypes> getSelfOneMany() {
+ return selfOneMany;
+ }
+
+ public void setSelfOneMany(List<AllFieldTypes> selfOneMany) {
+ this.selfOneMany = selfOneMany;
+ }
}
