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 2009/06/16 20:56:58 UTC
svn commit: r785341 [1/3] - in /openjpa/trunk:
openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/
openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/
openjpa-persistence/src/main/java/org/apache/openjpa/persiste...
Author: ppoddar
Date: Tue Jun 16 18:56:57 2009
New Revision: 785341
URL: http://svn.apache.org/viewvc?rev=785341&view=rev
Log:
OPENJPA-1013: Joins, key/value, parameters, embedded
Added:
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java (with props)
Modified:
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java
openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestTypeSafeCondExpression.java
openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestTypesafeCriteria.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaBuilder.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaExpressionBuilder.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaQueryImpl.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/Expressions.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/Joins.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/ParameterImpl.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/PathImpl.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/SubqueryImpl.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/Members.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/MetamodelImpl.java
openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/Types.java
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java Tue Jun 16 18:56:57 2009
@@ -95,8 +95,7 @@
SQLBuffer buf, int index) {
CoalesceExpState cstate = (CoalesceExpState) state;
- buf.append(" COALESCE ");
- buf.append("(");
+ buf.append(" COALESCE("); // MySQL does not like space before bracket
for (int i = 0; i < _vals.length; i++) {
if (i > 0)
Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java Tue Jun 16 18:56:57 2009
@@ -82,8 +82,7 @@
SQLBuffer buf, int index) {
BinaryOpExpState bstate = (BinaryOpExpState) state;
- buf.append(" NULLIF ");
- buf.append("(");
+ buf.append(" NULLIF("); // MySQL does not like space before bracket
_val1.appendTo(sel, ctx, bstate.state1, buf, 0);
buf.append(",");
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java Tue Jun 16 18:56:57 2009
@@ -66,7 +66,7 @@
return balance;
}
- public void setBalancey(int balance) {
+ public void setBalance(int balance) {
this.balance = balance;
}
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java Tue Jun 16 18:56:57 2009
@@ -24,7 +24,7 @@
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
-import javax.persistence.OrderBy;
+import javax.persistence.OrderColumn;
import javax.persistence.Table;
@Entity
@@ -38,7 +38,7 @@
private String name;
@OneToMany
- @OrderBy
+ @OrderColumn
private List<Student> studentWaitList;
public long getId() {
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java Tue Jun 16 18:56:57 2009
@@ -12,6 +12,5 @@
public class CreditCard_ {
public static volatile Attribute<CreditCard,Customer> customer;
public static volatile Attribute<CreditCard,Long> id;
- public static volatile List<CreditCard,TransactionHistory>
- transactionHistory;
+ public static volatile List<CreditCard,TransactionHistory> transactionHistory;
}
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java Tue Jun 16 18:56:57 2009
@@ -117,11 +117,14 @@
* configuration values in the form key,value,key,value...
*/
protected void createNamedEMF(Class<?>... types) {
- Map map = new HashMap();
+ Map<Object,Object> map = new HashMap<Object,Object>();
map.put("openjpa.jdbc.SynchronizeMappings",
"buildSchema(ForeignKeys=true,"
+ "SchemaAction='add,deleteTableContents')");
+ map.put("openjpa.jdbc.QuerySQLCache", "false");
map.put("openjpa.DynamicEnhancementAgent", "false");
+ map.put("openjpa.RuntimeUnenhancedClasses", "unsupported");
+ map.put("openjpa.Compatibility", "QuotedNumbersInQueries=true");
map.put("openjpa.jdbc.JDBCListeners",
new JDBCListener[] { new Listener() });
@@ -223,10 +226,10 @@
e.printStackTrace();
sqls[0] = new ArrayList<String>();
sqls[0].add(extractSQL(e));
- fail("JPQL :" + jpql + "\r\nSQL :" + sqls[0]);
+ fail("Wrong SQL for JPQL :" + jpql + "\r\nSQL :" + sqls[0]);
} catch (Exception e) {
e.printStackTrace();
- fail("JPQL :" + jpql);
+ fail("Wrong JPQL :" + jpql);
}
sqls[0] = new ArrayList<String>(sql);
@@ -244,6 +247,23 @@
return true;
}
+
+ List<String> executeJPQL(String jpql, Map<?,Object> params) {
+ sql.clear();
+ Query q = em.createQuery(jpql);
+ if (params != null) {
+ for (Object key : params.keySet()) {
+ if (key instanceof String)
+ q.setParameter(key.toString(), params.get(key));
+ else if (key instanceof String)
+ q.setParameter(key.toString(), params.get(key));
+ else
+ throw new RuntimeException("Bad Parameter key " + key);
+ }
+ }
+
+ return sql;
+ }
String extractSQL(PersistenceException e) {
Throwable t = e.getCause();
@@ -287,8 +307,12 @@
* Affirms if the test case or the test method is annotated with
* @AllowFailure. Method level annotation has higher precedence than Class
* level annotation.
+ *
+ * Set -DIgnoreAllowFailure=true to ignore this directive altogether.
*/
protected AllowFailure getAllowFailure() {
+ if (Boolean.getBoolean("IgnoreAllowFailure"))
+ return null;
try {
Method runMethod = getClass().getMethod(getName(), (Class[])null);
AllowFailure anno = runMethod.getAnnotation(AllowFailure.class);
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java Tue Jun 16 18:56:57 2009
@@ -12,8 +12,7 @@
public static volatile Attribute<Employee,Contact> contactInfo;
public static volatile Attribute<Employee,Department> department;
public static volatile Attribute<Employee,Integer> empId;
- public static volatile Attribute<Employee,FrequentFlierPlan>
- frequentFlierPlan;
+ public static volatile Attribute<Employee,FrequentFlierPlan> frequentFlierPlan;
public static volatile Attribute<Employee,Manager> manager;
public static volatile Attribute<Employee,String> name;
public static volatile Attribute<Employee,Integer> rating;
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java Tue Jun 16 18:56:57 2009
@@ -9,10 +9,10 @@
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.ListJoin;
+import javax.persistence.criteria.MapJoin;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.SetJoin;
-
-import org.apache.openjpa.persistence.test.AllowFailure;
+import javax.persistence.metamodel.Bindable;
/**
* Tests Criteria Queries that use Join.
@@ -21,13 +21,19 @@
*
*/
public class TestJoinCondition extends CriteriaTest {
-
+ protected Class[] getDomainClasses() {
+ return new Class[]{A.class,B.class,C.class,D.class};
+ }
+
public void testSingleAttributeJoinModel() {
CriteriaQuery cq = cb.create();
Root<A> a = cq.from(A.class);
Join<A,B> b = a.join(A_.b);
+ assertTrue(b.getModel() instanceof Bindable);
assertSame(B.class, b.getJavaType());
assertSame(B.class, b.getMember().getMemberJavaType());
+ assertEquals(Bindable.BindableType.MANAGED_TYPE,
+ b.getModel().getBindableType());
}
public void testCollectionJoinModel() {
@@ -71,7 +77,6 @@
assertEquivalence(cq, jpql);
}
- @AllowFailure(message="Missing where clause")
public void testCrossJoin() {
String jpql = "select a from A a, C c where a.name=c.name";
CriteriaQuery cq = cb.create();
@@ -143,4 +148,23 @@
assertEquivalence(c, jpql);
}
+ public void testKeyExpression() {
+ String jpql = "select c from C c JOIN c.map d where KEY(d)=33";
+ CriteriaQuery cq = cb.create();
+ Root<C> c = cq.from(C.class);
+ MapJoin<C,Integer,D> d = c.join(C_.map);
+ cq.where(cb.equal(d.key(),33));
+
+ assertEquivalence(cq, jpql);
+ }
+
+ public void testValueExpression() {
+ String jpql = "select c from C c JOIN c.map d where VALUE(d).name='xy'";
+ CriteriaQuery cq = cb.create();
+ Root<C> c = cq.from(C.class);
+ MapJoin<C,Integer,D> d = c.join(C_.map);
+ cq.where(cb.equal(d.value().get(D_.name),"xy"));
+
+ assertEquivalence(cq, jpql);
+ }
}
Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java Tue Jun 16 18:56:57 2009
@@ -22,17 +22,18 @@
import java.util.List;
import javax.persistence.Parameter;
-import javax.persistence.Query;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.ListJoin;
import javax.persistence.criteria.MapJoin;
import javax.persistence.criteria.Root;
+import javax.persistence.criteria.SetJoin;
import javax.persistence.criteria.Subquery;
import javax.persistence.metamodel.Embeddable;
import javax.persistence.metamodel.Entity;
import javax.persistence.metamodel.Metamodel;
+import javax.persistence.metamodel.Set;
import org.apache.openjpa.persistence.test.AllowFailure;
@@ -66,6 +67,7 @@
Metamodel mm = em.getMetamodel();
account_ = mm.entity(Account.class);
address_ = mm.embeddable(Address.class);
+ assertNotNull(address_);
contact_ = mm.embeddable(Contact.class);
course_ = mm.entity(Course.class);
creditCard_ = mm.entity(CreditCard.class);
@@ -88,50 +90,56 @@
videoStore_ = mm.entity(VideoStore.class);
}
- public void testCriteria() {
+ public void testStringEqualExpression() {
String jpql = "select c from Customer c "
+ "where c.name='Autowest Toyota'";
+
CriteriaQuery q = cb.create();
Root<Customer> customer = q.from(Customer.class);
- q.select(customer).where(
- cb.equal(customer.get(customer_.getAttribute("name",
- String.class)), "Autowest Toyota"));
+ q.select(customer)
+ .where(cb.equal(
+ customer.get(customer_.getAttribute("name", String.class)),
+ "Autowest Toyota"));
assertEquivalence(q, jpql);
}
- @AllowFailure
- public void testJoins() {
- String jpql = "SELECT c.name FROM Customer c JOIN c.orders o "
- + "JOIN o.lineItems i WHERE i.product.productType = 'printer'";
+ public void testSetAndListJoins() {
+ String jpql = "SELECT c.name FROM Customer c "
+ + "JOIN c.orders o JOIN o.lineItems i "
+ + "WHERE i.product.productType = 'printer'";
+
CriteriaQuery q = cb.create();
- Root<Customer> cust = q.from(Customer.class);
- Join<Customer, Order> order = cust.join(customer_.getSet("orders",
+ Root<Customer> c = q.from(Customer.class);
+ SetJoin<Customer, Order> o = c.join(customer_.getSet("orders",
Order.class));
- Join<Order, LineItem> item = order.join(order_.getList("lineItems",
+ ListJoin<Order, LineItem> i = o.join(order_.getList("lineItems",
LineItem.class));
- q.select(cust.get(Customer_.name)).where(
- cb.equal(item.get(
- lineItem_.getAttribute("product", Product.class)).get(
- product_.getAttribute("productType", String.class)),
- "printer"));
+ q.select(c.get(Customer_.name)).where(
+ cb.equal(i.get(lineItem_.getAttribute("product", Product.class))
+ .get(product_.getAttribute("productType", String.class)),
+ "printer"));
assertEquivalence(q, jpql);
-
- jpql = "SELECT c FROM Customer c LEFT JOIN c.orders o WHERE "
- + "c.status = 1";
- q = cb.create();
- Root<Customer> cust1 = q.from(Customer.class);
- Join<Customer, Order> order1 = cust1.join(customer_.getSet("orders",
+ }
+
+ public void testLeftSetJoin() {
+ String jpql = "SELECT c FROM Customer c "
+ + "LEFT JOIN c.orders o "
+ + "WHERE c.status = 1";
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ SetJoin<Customer, Order> o = c.join(customer_.getSet("orders",
Order.class), JoinType.LEFT);
- q.where(
- cb.equal(cust1.get(customer_.getAttribute("status",
- Integer.class)), 1)).select(cust1);
+ q.where(cb.equal(
+ c.get(customer_.getAttribute("status", Integer.class)),
+ 1));
assertEquivalence(q, jpql);
}
- @AllowFailure
+ @AllowFailure(message="FetchJoin not implemented")
public void testFetchJoins() {
String jpql = "SELECT d FROM Department LEFT JOIN FETCH d.employees "
+ "WHERE d.deptNo = 1";
@@ -145,11 +153,11 @@
assertEquivalence(q, jpql);
}
- @AllowFailure
public void testPathNavigation() {
String jpql = "SELECT p.vendor FROM Employee e "
- + "JOIN e.contactInfo.phones p "
- + "WHERE e.contactInfo.address.zipCode = '95054'";
+ + "JOIN e.contactInfo.phones p "
+ + "WHERE e.contactInfo.address.zipCode = '95054'";
+
CriteriaQuery q = cb.create();
Root<Employee> emp = q.from(Employee.class);
Join<Contact, Phone> phone = emp.join(
@@ -162,22 +170,23 @@
q.select(phone.get(phone_.getAttribute("vendor", String.class)));
assertEquivalence(q, jpql);
+ }
+
+ public void testKeyPathNavigation() {
+ String jpql = "SELECT i.name, p FROM Item i JOIN i.photos p "
+ + "WHERE KEY(p) LIKE '%egret%'";
- jpql = "SELECT i.name, p FROM Item i JOIN i.photos p WHERE KEY(p) "
- + "LIKE '%egret%'";
-
- q = cb.create();
+ CriteriaQuery q = cb.create();
Root<Item> item = q.from(Item.class);
- MapJoin<Item, String, Photo> photo = item.join(item_.getMap("photos",
- String.class, Photo.class));
+ MapJoin<Item, String, Photo> photo = item.join(
+ item_.getMap("photos", String.class, Photo.class));
q.select(item.get(item_.getAttribute("name", String.class)), photo)
.where(cb.like(photo.key(), "%egret%"));
assertEquivalence(q, jpql);
}
- @AllowFailure
- public void testRestrictQueryResult() {
+ public void testIndexExpression() {
String jpql = "SELECT t FROM CreditCard c JOIN c.transactionHistory t "
+ "WHERE c.customer.accountNum = 321987 AND INDEX(t) BETWEEN 0 "
+ "AND 9";
@@ -193,24 +202,25 @@
321987), cb.between(t.index(), 0, 9));
assertEquivalence(cq, jpql);
-
- /*
- *
- * jpql = "SELECT o FROM Order o WHERE o.lineItems IS EMPTY"; q =
- * cb.create(); Root<Order> order = q.from(Order.class);
- * Join<Order,LineItem> lineItems =
- * order.join(order_.getList("lineItems", LineItem.class));
- * q.where(cb.isEmpty(lineItems)); q.select(order);
- *
- * assertEquivalence(q, jpql);
- */
+ }
+
+ @AllowFailure(message="as() not implemented")
+ public void testIsEmptyExpressionOnJoin() {
+ String jpql = "SELECT o FROM Order o WHERE o.lineItems IS EMPTY";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ ListJoin<Order,LineItem> lineItems =
+ o.join(order_.getList("lineItems", LineItem.class));
+ q.where(cb.isEmpty(lineItems.as(List.class)));
+ q.select(o);
+ assertEquivalence(q, jpql);
}
- @AllowFailure
- public void testExpressions() {
+ public void testFunctionalExpressionInProjection() {
String jpql = "SELECT o.quantity, o.totalCost*1.08 AS taxedCost, "
+ "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
+ "WHERE a.state = 'CA' AND a.county = 'Santa Clara'";
+
CriteriaQuery q = cb.create();
Root<Customer> cust = q.from(Customer.class);
Join<Customer, Order> order = cust.join(customer_.getSet("orders",
@@ -226,17 +236,22 @@
address.get(address_.getAttribute("zipCode", String.class)));
assertEquivalence(q, jpql);
-
- jpql = "SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt";
- q = cb.create();
+ }
+
+ public void testTypeExpression() {
+ String jpql = "SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt";
+ CriteriaQuery q = cb.create();
Root<Employee> emp = q.from(Employee.class);
q.select(emp.type()).where(cb.notEqual(emp.type(), Exempt.class));
assertEquivalence(q, jpql);
-
- jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
+ }
+
+ public void testJoinAndIndexExpression() {
+ String jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
+ "WHERE c.name = 'Calculus' AND INDEX(w) = 0";
- q = cb.create();
+
+ CriteriaQuery q = cb.create();
Root<Course> course = q.from(Course.class);
ListJoin<Course, Student> w = course.join(course_.getList(
"studentWaitList", Student.class));
@@ -247,11 +262,13 @@
w.get(student_.getAttribute("name", String.class)));
assertEquivalence(q, jpql);
-
- jpql = "SELECT SUM(i.price) "
+ }
+
+ public void testAggregateExpressionInProjection() {
+ String jpql = "SELECT SUM(i.price) "
+ "FROM Order o JOIN o.lineItems i JOIN o.customer c "
+ "WHERE c.lastName = 'Smith' AND c.firstName = 'John'";
- q = cb.create();
+ CriteriaQuery q = cb.create();
Root<Order> o = q.from(Order.class);
Join<Order, LineItem> i = o.join(order_.getList("lineItems",
LineItem.class));
@@ -264,24 +281,34 @@
q.select(cb.sum(i.get(lineItem_.getAttribute("price", Double.class))));
assertEquivalence(q, jpql);
- /*
- * jpql = "SELECT SIZE(d.employees) FROM Department d " +
- * "WHERE d.name = 'Sales'"; q = cb.create(); Root<Department> d =
- * q.from(Department.class);
- * q.where(cb.equal(d.get(department_.getAttribute("name",
- * String.class)), "Sales"));
- * q.select(cb.size(d.get(department_.getSet("employees",
- * Employee.class))));
- *
- * assertEquivalence(q, jpql);
- */
- jpql = "SELECT e.name, CASE WHEN e.rating = 1 THEN e.salary * 1.1 "
+ }
+
+ public void testSizeExpressionInProjection() {
+ String jpql = "SELECT SIZE(d.employees) FROM Department d "
+ + "WHERE d.name = 'Sales'";
+
+ CriteriaQuery q = cb.create();
+ Root<Department> d = q.from(Department.class);
+ q.where(cb.equal(
+ d.get(department_.getAttribute("name", String.class)),
+ "Sales"));
+ Set<Department, Employee> employees =
+ department_.getDeclaredSet("employees", Employee.class);
+ q.select(cb.size(d.get(employees)));
+
+ assertEquivalence(q, jpql);
+
+ }
+
+ public void testCaseExpression() {
+ String jpql = "SELECT e.name, "
+ + "CASE WHEN e.rating = 1 THEN e.salary * 1.1 "
+ "WHEN e.rating = 2 THEN e.salary * 1.2 ELSE e.salary * 1.01 END "
+ "FROM Employee e WHERE e.department.name = 'Engineering'";
- q = cb.create();
+
+ CriteriaQuery q = cb.create();
Root<Employee> e = q.from(Employee.class);
- q
- .where(cb.equal(e.get(
+ q.where(cb.equal(e.get(
employee_.getAttribute("department", Department.class))
.get(department_.getAttribute("name", String.class)),
"Engineering"));
@@ -301,32 +328,30 @@
assertEquivalence(q, jpql);
}
- /*
- * @AllowFailure public void testLiterals() { String jpql =
- * "SELECT p FROM Person p where 'Joe' MEMBER OF " + "p.nickNames";
- * CriteriaQuery q = cb.create(); Root<Person> p = q.from(Person.class);
- * q.select(p).where(cb.isMember(cb.literal("Joe"), p.get(person_.
- * getSet("nickNames", String.class))));
- *
- * assertEquivalence(q, jpql); }
- */
+ public void testMemberOfExpression() {
+ String jpql = "SELECT p FROM Person p where 'Joe' MEMBER OF p.nickNames";
+
+ CriteriaQuery q = cb.create();
+ Root<Person> p = q.from(Person.class);
+ q.select(p).where(cb.isMember(cb.literal("Joe"),
+ p.get(person_.getDeclaredSet("nickNames", String.class))));
+
+ assertEquivalence(q, jpql);
+ }
- @AllowFailure
public void testParameters() {
String jpql = "SELECT c FROM Customer c Where c.status = :stat";
CriteriaQuery q = cb.create();
Root<Customer> c = q.from(Customer.class);
- Parameter<Integer> param = cb.parameter(Integer.class);
- q.select(c).where(
- cb.equal(
- c.get(customer_.getAttribute("status", Integer.class)),
- param));
+ Parameter<Integer> param = cb.parameter(Integer.class, "stat");
+ q.select(c).where(cb.equal(
+ c.get(customer_.getAttribute("status", Integer.class)), param));
assertEquivalence(q, jpql, new String[] { "stat" }, new Object[] { 1 });
}
- @AllowFailure
- public void testSelectList() {
+ @AllowFailure(message="Generates invalid SQL")
+ public void testKeyExpressionInSelectList() {
String jpql = "SELECT v.location.street, KEY(i).title, VALUE(i) FROM "
+ "VideoStore v JOIN v.videoInventory i "
+ "WHERE v.location.zipCode = " + "'94301' AND VALUE(i) > 0";
@@ -343,25 +368,27 @@
.get(movie_.getAttribute("title", String.class)), inv.value());
assertEquivalence(q, jpql);
-
- jpql = "SELECT NEW CustomerDetails(c.id, c.status, o.quantity) FROM "
- + "Customer c JOIN c.orders o WHERE o.quantity > 100";
- q = cb.create();
+ }
+
+ public void testConstructorInSelectList() {
+ String jpql = "SELECT NEW CustomerDetails(c.id, c.status, o.quantity) "
+ + "FROM Customer c JOIN c.orders o WHERE o.quantity > 100";
+
+ CriteriaQuery q = cb.create();
Root<Customer> c = q.from(Customer.class);
- Join<Customer, Order> o = c.join(customer_
- .getSet("orders", Order.class));
+ SetJoin<Customer, Order> o = c.join(
+ customer_.getSet("orders", Order.class));
q.where(cb.gt(o.get(order_.getAttribute("quantity", Integer.class)),
100));
- q.select(cb.select(CustomerDetails.class, c.get(customer_.getAttribute(
- "id", Integer.class)), c.get(customer_.getAttribute("status",
- Integer.class)), o.get(order_.getAttribute("quantity",
- Integer.class))));
+ q.select(cb.select(CustomerDetails.class,
+ c.get(customer_.getAttribute("id", Long.class)),
+ c.get(customer_.getAttribute("status", Integer.class)),
+ o.get(order_.getAttribute("quantity", Integer.class))));
assertEquivalence(q, jpql);
}
- @AllowFailure
- public void testSubqueries() {
+ public void testUncorrelatedSubqueryWithAggregateProjection() {
String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE "
+ "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM "
+ "Customer c)";
@@ -375,42 +402,49 @@
q.select(goodCustomer);
assertEquivalence(q, jpql);
-
- jpql = "SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ("
+ }
+
+ public void testSubqueryWithExistsClause() {
+ String jpql = "SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ("
+ "SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = "
+ "emp.spouse)";
- q = cb.create();
+ CriteriaQuery q = cb.create();
Root<Employee> emp = q.from(Employee.class);
- Subquery<Employee> sq1 = q.subquery(Employee.class);
- Root<Employee> spouseEmp = sq1.from(Employee.class);
- sq1.select(spouseEmp);
- sq1.where(cb.equal(spouseEmp, emp.get(employee_.getAttribute("spouse",
+ Subquery<Employee> sq = q.subquery(Employee.class);
+ Root<Employee> spouseEmp = sq.from(Employee.class);
+ sq.select(spouseEmp);
+ sq.where(cb.equal(spouseEmp, emp.get(employee_.getAttribute("spouse",
Employee.class))));
q.where(cb.exists(sq));
q.select(emp).distinct(true);
assertEquivalence(q, jpql);
+ }
- jpql = "SELECT emp FROM Employee emp WHERE emp.salary > ALL ("
+ public void testSubqueryWithAllClause() {
+ String jpql = "SELECT emp FROM Employee emp WHERE emp.salary > ALL ("
+ "SELECT m.salary FROM Manager m WHERE m.department ="
+ " emp.department)";
- q = cb.create();
- Root<Employee> emp1 = q.from(Employee.class);
- q.select(emp1);
- Subquery<BigDecimal> sq2 = q.subquery(BigDecimal.class);
- Root<Manager> m = sq2.from(Manager.class);
- sq2.select(m.get(manager_.getAttribute("salary", BigDecimal.class)));
- sq2.where(cb.equal(m.get(manager_.getAttribute("department",
- Department.class)), emp1.get(employee_.getAttribute(
+
+ CriteriaQuery q = cb.create();
+ Root<Employee> emp = q.from(Employee.class);
+ q.select(emp);
+ Subquery<BigDecimal> sq = q.subquery(BigDecimal.class);
+ Root<Manager> m = sq.from(Manager.class);
+ sq.select(m.get(manager_.getAttribute("salary", BigDecimal.class)));
+ sq.where(cb.equal(m.get(manager_.getAttribute("department",
+ Department.class)), emp.get(employee_.getAttribute(
"department", Department.class))));
q.where(cb.gt(emp.get(employee_.getAttribute("salary", Long.class)), cb
.all(sq)));
assertEquivalence(q, jpql);
-
- jpql = "SELECT c FROM Customer c WHERE "
+ }
+
+ public void testCorrelatedSubqueryWithCount() {
+ String jpql = "SELECT c FROM Customer c WHERE "
+ "(SELECT COUNT(o) FROM c.orders o) > 10";
- q = cb.create();
+ CriteriaQuery q = cb.create();
Root<Customer> c1 = q.from(Customer.class);
q.select(c1);
Subquery<Long> sq3 = q.subquery(Long.class);
@@ -420,43 +454,50 @@
q.where(cb.gt(sq3.select(cb.count(o)), 10));
assertEquivalence(q, jpql);
-
- jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
+ }
+
+ public void testCorrelatedSubqueryWithJoin() {
+ String jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
+ "SELECT a.balance FROM o.customer c JOIN c.accounts a)";
- q = cb.create();
- Root<Order> o1 = q.from(Order.class);
- q.select(o1);
- Subquery<Integer> sq4 = q.subquery(Integer.class);
- Root<Order> o2 = sq4.correlate(o1);
- Join<Order, Customer> c3 = o2.join(order_.getAttribute("customer",
+
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o);
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Order> o2 = sq.correlate(o);
+ Join<Order, Customer> c = o2.join(order_.getAttribute("customer",
Customer.class));
- Join<Customer, Account> a = c3.join(customer_.getList("accounts",
+ Join<Customer, Account> a = c.join(customer_.getList("accounts",
Account.class));
- sq4.select(a.get(account_.getAttribute("balance", Integer.class)));
- q.where(cb.lt(cb.literal(10000), cb.all(sq4)));
+ sq.select(a.get(account_.getAttribute("balance", Integer.class)));
+ q.where(cb.lt(cb.literal(10000), cb.all(sq)));
assertEquivalence(q, jpql);
-
- jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
- + "ALL (SELECT a.balance FROM c.accounts a)";
- q = cb.create();
- Root<Order> o3 = q.from(Order.class);
- q.select(o3);
- Join<Order, Customer> c4 = o3.join(Order_.customer);
- Subquery<Integer> sq5 = q.subquery(Integer.class);
- Join<Order, Customer> c5 = sq5.correlate(c4);
- Join<Customer, Account> a2 = c5.join(customer_.getList("accounts",
+ }
+
+ @AllowFailure(message="Root of the subquery._delegate not set")
+ public void testCorrelatedSubqueryWithAllClause() {
+ String jpql = "SELECT o FROM Order o JOIN o.customer c "
+ + "WHERE 10000 < ALL (SELECT a.balance FROM c.accounts a)";
+
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o);
+ Join<Order, Customer> c = o.join(Order_.customer);
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Join<Order, Customer> csq = sq.correlate(c);
+ Join<Customer, Account> a = csq.join(customer_.getList("accounts",
Account.class));
- sq5.select(a.get(account_.getAttribute("balance", Integer.class)));
- q.where(cb.lt(cb.literal(10000), cb.all(sq5)));
+ sq.select(a.get(account_.getAttribute("balance", Integer.class)));
+ q.where(cb.lt(cb.literal(10000), cb.all(sq)));
assertEquivalence(q, jpql);
}
- @AllowFailure
public void testGroupByAndHaving() {
String jpql = "SELECT c.status, AVG(c.filledOrderCount), COUNT(c) FROM "
+ "Customer c GROUP BY c.status HAVING c.status IN (1, 2)";
+
CriteriaQuery q = cb.create();
Root<Customer> c = q.from(Customer.class);
q.groupBy(c.get(customer_.getAttribute("status", Integer.class)));
@@ -469,54 +510,65 @@
assertEquivalence(q, jpql);
}
- @AllowFailure
- public void testOrdering() {
- String jpql = "SELECT o FROM Customer c JOIN c.orders o "
- + "JOIN c.address a WHERE a.state = 'CA' "
- + "ORDER BY o.quantity DESC, o.totalCost";
+ public void testOrderingByExpressionNotIncludedInSelection() {
+ String jpql = "SELECT o FROM Customer c "
+ + "JOIN c.orders o JOIN c.address a "
+ + "WHERE a.state = 'CA' "
+ + "ORDER BY o.quantity DESC, o.totalCost";
+
CriteriaQuery q = cb.create();
Root<Customer> c = q.from(Customer.class);
- Join<Customer, Order> o = c.join(customer_
- .getSet("orders", Order.class));
+ SetJoin<Customer, Order> o = c.join(customer_.getSet("orders",
+ Order.class));
Join<Customer, Address> a = c.join(customer_.getAttribute("address",
Address.class));
- q.where(cb.equal(a.get(address_.getAttribute("state", String.class)),
+ q.where(cb.equal(
+ a.get(address_.getAttribute("state", String.class)),
"CA"));
- q.orderBy(cb
- .desc(o.get(order_.getAttribute("quantity", Integer.class))),
- cb.asc(o.get(order_.getAttribute("totalCost", Double.class))));
+ q.orderBy(
+ cb.desc(o.get(order_.getAttribute("quantity", Integer.class))),
+ cb.asc(o.get(order_.getAttribute("totalCost", Double.class))));
q.select(o);
assertEquivalence(q, jpql);
-
- jpql = "SELECT o.quantity, a.zipCode FROM Customer c JOIN c.orders "
- + "JOIN c.address a WHERE a.state = 'CA' ORDER BY o.quantity, "
- + "a.zipCode";
- q = cb.create();
- Root<Customer> c1 = q.from(Customer.class);
- Join<Customer, Order> o1 = c1.join(customer_.getSet("orders",
+ }
+
+ public void testOrderingByExpressionIncludedInSelection() {
+ String jpql = "SELECT o.quantity, a.zipCode FROM Customer c "
+ + "JOIN c.orders o JOIN c.address a "
+ + "WHERE a.state = 'CA' "
+ + "ORDER BY o.quantity, a.zipCode";
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ Join<Customer, Order> o = c.join(customer_.getSet("orders",
Order.class));
- Join<Customer, Address> a1 = c1.join(customer_.getAttribute("address",
+ Join<Customer, Address> a = c.join(customer_.getAttribute("address",
Address.class));
- q.where(cb.equal(a1.get(address_.getAttribute("state", String.class)),
+ q.where(cb.equal(
+ a.get(address_.getAttribute("state", String.class)),
"CA"));
q.orderBy(cb
- .asc(o1.get(order_.getAttribute("quantity", Integer.class))),
- cb.asc(a1.get(address_.getAttribute("zipCode", String.class))));
- q.select(o1.get(order_.getAttribute("quantity", Integer.class)), a1
- .get(address_.getAttribute("zipCode", String.class)));
+ .asc(o.get(order_.getAttribute("quantity", Integer.class))),
+ cb.asc(a.get(address_.getAttribute("zipCode", String.class))));
+ q.select(o.get(order_.getAttribute("quantity", Integer.class)),
+ a.get(address_.getAttribute("zipCode", String.class)));
assertEquivalence(q, jpql);
-
- jpql = "SELECT o.quantity, o.cost * 1.08 AS taxedCost, a.zipCode "
+ }
+
+ public void testOrderingWithNumericalExpressionInSelection() {
+ String jpql = "SELECT o.quantity, o.totalCost * 1.08 AS taxedCost, "
+ + "a.zipCode "
+ "FROM Customer c JOIN c.orders o JOIN c.address a "
+ "WHERE a.state = 'CA' AND a.county = 'Santa Clara' "
+ "ORDER BY o.quantity, taxedCost, a.zipCode";
- q = cb.create();
- Root<Customer> c2 = q.from(Customer.class);
- Join<Customer, Order> o2 = c2.join(customer_.getSet("orders",
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ Join<Customer, Order> o = c.join(customer_.getSet("orders",
Order.class));
- Join<Customer, Address> a2 = c2.join(customer_.getAttribute("address",
+ Join<Customer, Address> a = c.join(customer_.getAttribute("address",
Address.class));
q.where(cb.equal(a.get(address_.getAttribute("state", String.class)),
"CA"), cb.equal(a.get(address_.getAttribute("county",
@@ -530,7 +582,6 @@
.prod(o.get(order_.getAttribute("totalCost", Double.class)),
1.08), a.get(address_.getAttribute("zipCode",
String.class)));
-
assertEquivalence(q, jpql);
}
}
Added: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java?rev=785341&view=auto
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java (added)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java Tue Jun 16 18:56:57 2009
@@ -0,0 +1,604 @@
+package org.apache.openjpa.persistence.criteria;
+
+import java.sql.Timestamp;
+
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Expression;
+import javax.persistence.criteria.Join;
+import javax.persistence.criteria.JoinType;
+import javax.persistence.criteria.ListJoin;
+import javax.persistence.criteria.Path;
+import javax.persistence.criteria.Root;
+import javax.persistence.criteria.SetJoin;
+import javax.persistence.criteria.Subquery;
+import javax.persistence.criteria.QueryBuilder.Coalesce;
+
+import org.apache.openjpa.persistence.test.AllowFailure;
+
+public class TestSubqueries extends CriteriaTest {
+ public void testExist() {
+ String query = "SELECT DISTINCT o.name FROM CompUser o WHERE EXISTS"
+ + " (SELECT c FROM Address c WHERE c = o.address )";
+
+ CriteriaQuery q = cb.create();
+ Root<CompUser> o = q.from(CompUser.class);
+ Subquery<Address> sq = q.subquery(Address.class);
+ sq.correlate(o);
+ Root<Address> c = sq.from(Address.class);
+ sq.select(c);
+ sq.where(cb.equal(c, o.get(CompUser_.address)));
+ q.where(cb.exists(sq));
+ q.select(o.get(CompUser_.name)).distinct(true);
+ assertEquivalence(q, query);
+ }
+
+
+ public void testNotExist() {
+ String query = "SELECT DISTINCT o.name FROM CompUser o WHERE NOT EXISTS"
+ + " (SELECT s FROM CompUser s WHERE s.address.country = "
+ + "o.address.country)";
+
+ CriteriaQuery q = cb.create();
+ Root<CompUser> o = q.from(CompUser.class);
+ Subquery<CompUser> sq = q.subquery(CompUser.class);
+ sq.correlate(o);
+ Root<CompUser> s = sq.from(CompUser.class);
+ sq.select(s);
+ sq.where(cb.equal(s.get(CompUser_.address).get(Address_.country), o
+ .get(CompUser_.address).get(Address_.country)));
+ q.where(cb.exists(sq).negate());
+ q.select(o.get(CompUser_.name)).distinct(true);
+
+ assertEquivalence(q, query);
+ }
+
+ public void testAny() {
+ String query = "SELECT o.name FROM CompUser o "
+ + "WHERE o.address.zipCode = "
+ + " ANY (SELECT s.computerName "
+ + " FROM CompUser s WHERE s.address.country IS NOT NULL)";
+
+ CriteriaQuery q = cb.create();
+ Root<CompUser> o = q.from(CompUser.class);
+ q.select(o.get(CompUser_.name));
+ Subquery<String> sq = q.subquery(String.class);
+ Root<CompUser> s = sq.from(CompUser.class);
+ sq.select(s.get(CompUser_.computerName));
+ sq.where(cb.notEqual(s.get(CompUser_.address).get(Address_.country),
+ null));
+ q.where(cb.equal(o.get(CompUser_.address).get(Address_.zipCode), cb
+ .any(sq)));
+
+ assertEquivalence(q, query);
+ }
+
+ public void testSubquery01() {
+ String query = "select o1.id from Order o1 where o1.id in "
+ + " (select distinct o.id from LineItem i, Order o"
+ + " where i.quantity > 10 and o.count > 1000 and i.id = o.id)";
+
+ CriteriaQuery q = cb.create();
+ Root<Order> o1 = q.from(Order.class);
+ q.select(o1.get(Order_.id));
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<LineItem> i = sq.from(LineItem.class);
+ Join<LineItem, Order> o = i.join(LineItem_.order);
+ sq.where(cb.and(cb.and(cb.gt(i.get(LineItem_.quantity), 10), cb.gt(o
+ .get(Order_.count), 1000)), cb.equal(i.get(LineItem_.id), o
+ .get(Order_.id))));
+ sq.select(o.get(Order_.id)).distinct(true);
+ q.where(cb.in(o1.get(Order_.id)).value(
+ sq.select(o.get(Order_.id)).distinct(true)));
+
+ assertEquivalence(q, query);
+ }
+
+ public void testSubquery02() {
+ String query = "select o.id from Order o where o.customer.balanceOwed ="
+ + " (select max(o2.customer.balanceOwed) from Order o2"
+ + " where o.customer.id = o2.customer.id)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+ Order_.customer).get(Customer_.id)));
+ q.where(cb.equal(o.get(Order_.customer).get(Customer_.balanceOwed), sq
+ .select(cb.max(o2.get(Order_.customer).get(
+ Customer_.balanceOwed)))));
+
+ assertEquivalence(q, query);
+ }
+
+ public void testSubquery03() {
+ String query = "select o from Order o where o.customer.balanceOwed ="
+ + " (select max(o2.customer.balanceOwed) from Order o2"
+ + " where o.customer.id = o2.customer.id)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o);
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+ Order_.customer).get(Customer_.id)));
+ q.where(cb.equal(o.get(Order_.customer).get(Customer_.balanceOwed), sq
+ .select(cb.max(o2.get(Order_.customer).get(
+ Customer_.balanceOwed)))));
+
+ assertEquivalence(q, query);
+ }
+
+ public void testSubquery04() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select count(i) from o.lineItems i)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+ Subquery<Long> sq = q.subquery(Long.class);
+ Root<Order> osq = sq.correlate(o);
+ Join<Order, LineItem> i = osq.join(Order_.lineItems);
+ q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(i))));
+ assertEquivalence(q, query);
+ }
+
+ public void testSubquery05() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select count(o.quantity) from Order o)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+ Subquery<Long> sq = q.subquery(Long.class);
+ Root<Order> o2 = sq.from(Order.class);
+ q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(o2
+ .get(Order_.quantity)))));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery06() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select count(o.id) from Order o)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+ Subquery<Long> sq = q.subquery(Long.class);
+ Root<Order> o2 = sq.from(Order.class);
+ q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(o2
+ .get(Order_.id)))));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery07() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select avg(o.quantity) from Order o)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+ Subquery<Double> sq = q.subquery(Double.class);
+ Root<Order> o2 = sq.from(Order.class);
+ q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.avg(o2
+ .get(Order_.quantity)))));
+
+ assertEquivalence(q, query);
+ }
+
+ @AllowFailure(message="JPQL generates invalid SQL")
+ public void testSubquery08() {
+ String query = "select c.name from Customer c "
+ + "where exists (select o from c.orders o where o.id = 1) "
+ + "or exists (select o from c.orders o where o.id = 2)";
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ q.select(c.get(Customer_.name));
+ Subquery<Order> sq1 = q.subquery(Order.class);
+ Root<Customer> c1 = sq1.correlate(c);
+ SetJoin<Customer, Order> o1 = c1.join(Customer_.orders);
+ sq1.where(cb.equal(o1.get(Order_.id), 1)).select(o1);
+
+ Subquery<Order> sq2 = q.subquery(Order.class);
+ Root<Customer> c2 = sq2.correlate(c);
+ SetJoin<Customer, Order> o2 = c2.join(Customer_.orders);
+ sq2.where(cb.equal(o2.get(Order_.id), 2)).select(o2);
+
+ q.where(cb.or(cb.exists(sq1), cb.exists(sq2)));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery09() {
+ String query = "select c.name from Customer c, in(c.orders) o "
+ + "where o.quantity between "
+ + "(select max(o.quantity) from Order o) and "
+ + "(select avg(o.quantity) from Order o) ";
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ q.select(c.get(Customer_.name));
+
+ Subquery<Integer> sq1 = q.subquery(Integer.class);
+ Root<Order> o1 = sq1.from(Order.class);
+ sq1.select(cb.max(o1.get(Order_.quantity)));
+
+ Subquery<Double> sq2 = q.subquery(Double.class);
+ Root<Order> o2 = sq2.from(Order.class);
+ sq2.select(cb.avg(o2.get(Order_.quantity)));
+
+ SetJoin<Customer, Order> o = c.join(Customer_.orders);
+ // not sure how to do call between of integer(quantity)
+ // between integer (max quantity) and double (avg quantity)
+ // q.where(cb.between(o.get(Order_.quantity), sq1, sq2));
+
+ assertEquivalence(q, query);
+ }
+
+ @AllowFailure(message="")
+ public void testSubquery10() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select sum(o2.quantity) from Customer c, "
+ + "in(c.orders) o2) ";
+
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Customer> c = sq.from(Customer.class);
+ SetJoin<Customer, Order> o2 = c.join(Customer_.orders);
+ sq.select(cb.sum(o2.get(Order_.quantity)));
+
+ q.where(cb.gt(o2.get(Order_.quantity), sq));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery11() {
+ String query = "select o.id from Order o where o.quantity between"
+ + " (select avg(o2.quantity) from Customer c, in(c.orders) o2)"
+ + " and (select min(o2.quantity) from Customer c, in(c.orders)"
+ + " o2)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<Double> sq1 = q.subquery(Double.class);
+ Root<Customer> c = sq1.from(Customer.class);
+ SetJoin<Customer, Order> o2 = c.join(Customer_.orders);
+ sq1.select(cb.avg(o2.get(Order_.quantity)));
+
+ Subquery<Integer> sq2 = q.subquery(Integer.class);
+ Root<Customer> c2 = sq2.from(Customer.class);
+ SetJoin<Customer, Order> o3 = c2.join(Customer_.orders);
+ sq2.select(cb.min(o3.get(Order_.quantity)));
+
+ // do not know how to call between for double and integer
+ // q.where(cb.between(o2.get(Order_.quantity), sq1, sq2));
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery12() {
+ String query =
+ "select o.id from Customer c, in(c.orders)o "
+ + "where o.quantity > (select sum(o2.quantity)"
+ + " from c.orders o2)";
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ SetJoin<Customer, Order> o = c.join(Customer_.orders);
+ q.select(o.get(Order_.id));
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Customer> sqc = sq.correlate(c);
+ SetJoin<Customer, Order> o2 = sqc.join(Customer_.orders);
+ sq.select(cb.sum(o2.get(Order_.quantity)));
+ q.where(cb.gt(o.get(Order_.quantity), sq));
+
+ assertEquivalence(q, query);
+ }
+
+ @AllowFailure
+ public void testSubquery13() {
+ String query = "select o1.id, c.name from Order o1, Customer c"
+ + " where o1.quantity = "
+ + " any(select o2.quantity from in(c.orders) o2)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o1 = q.from(Order.class);
+ Join<Order, Customer> c = o1.join(Order_.customer);
+ q.select(o1.get(Order_.id), c.get(Customer_.name));
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Join<Order, Customer> sqc = sq.correlate(c);
+ SetJoin<Customer, Order> o2 = sqc.join(Customer_.orders);
+ sq.select(o2.get(Order_.quantity));
+
+ q.where(cb.equal(o1.get(Order_.quantity), cb.any(sq)));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery14() {
+ String query = "SELECT p, m FROM Publisher p "
+ + "LEFT OUTER JOIN p.magazineCollection m "
+ + "WHERE m.id = (SELECT MAX(m2.id) FROM Magazine m2 "
+ + "WHERE m2.idPublisher.id = p.id AND m2.id = "
+ + "(SELECT MAX(m3.id) FROM Magazine m3 "
+ + "WHERE m3.idPublisher.id = p.id)) ";
+
+ CriteriaQuery q = cb.create();
+ Root<Publisher> p = q.from(Publisher.class);
+ Join<Publisher, Magazine> m = p.join(Publisher_.magazineCollection,
+ JoinType.LEFT);
+ q.select(p, m);
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<Magazine> m2 = sq.from(Magazine.class);
+ q.where(cb.equal(
+ m.get(Magazine_.id),
+ sq.select(cb.max(m2.get(Magazine_.id)))));
+
+ Subquery<Integer> sq2 = q.subquery(Integer.class);
+ Root<Magazine> m3 = sq2.from(Magazine.class);
+
+ sq2.where(cb.equal(
+ m3.get(Magazine_.idPublisher).get(Publisher_.id),
+ p.get(Publisher_.id)));
+
+ sq.where(cb.and(cb.equal(
+ m2.get(Magazine_.idPublisher).get(Publisher_.id),
+ p.get(Publisher_.id)),
+ cb.equal(
+ m2.get(Magazine_.id),
+ sq2.select(cb.max(m3.get(Magazine_.id))))));
+
+ assertEquivalence(q, query);
+ }
+
+ // outstanding problem subqueries:
+ // "select o from Order o where o.amount > (select count(o) from Order o)",
+ // "select o from Order o where o.amount > (select count(o2) from Order o2)
+ //",
+ // "select c from Customer c left join c.orders p where not exists"
+ // + " (select o2 from c.orders o2 where o2 = o",
+
+ // not sure how to write CriteriaQuery for
+ // Subquery.select(SimpleCase/GeneralCase)
+
+ public void testSubquery15() {
+ String query = "select o.id from Order o where o.delivered =(select "
+ + " CASE WHEN o2.quantity > 10 THEN true"
+ + " WHEN o2.quantity = 10 THEN false "
+ + " ELSE false END from Order o2"
+ + " where o.customer.id = o2.customer.id)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<Boolean> sq = q.subquery(Boolean.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+ Order_.customer).get(Customer_.id)));
+
+ q.where(cb.equal(o.get(Order_.delivered),
+ // sq.select(
+ cb.selectCase().when(cb.gt(o2.get(Order_.quantity), 10), true)
+ .when(cb.equal(o2.get(Order_.quantity), 10), false)
+ .otherwise(false)
+ // )
+ ));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery16() {
+ String query = "select o1.id from Order o1 where o1.quantity > "
+ + " (select o.quantity*2 from LineItem i, Order o"
+ + " where i.quantity > 10 and o.quantity > 1000 and i.id = " +
+ "o.id)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o1 = q.from(Order.class);
+ q.select(o1.get(Order_.id));
+
+ Subquery<Integer> sq = q.subquery(Integer.class);
+ Root<LineItem> i = sq.from(LineItem.class);
+ Join<LineItem, Order> o = i.join(LineItem_.order);
+ sq.where(cb.and(cb.and(cb.gt(i.get(LineItem_.quantity), 10), cb.gt(o
+ .get(Order_.quantity), 1000)), cb.equal(i.get(LineItem_.id), o
+ .get(Order_.id))));
+
+ q.where(cb.gt(o1.get(Order_.quantity), sq.select(cb.prod(o
+ .get(Order_.quantity), 2))));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery17() {
+ String query = "select o.id from Order o where o.customer.name ="
+ + " (select substring(o2.customer.name, 3) from Order o2"
+ + " where o.customer.id = o2.customer.id)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.customer).get(Customer_.name));
+
+ Subquery<String> sq = q.subquery(String.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+ Order_.customer).get(Customer_.id)));
+
+ q.where(cb.equal(o.get(Order_.customer).get(Customer_.name), sq
+ .select(cb.substring(o2.get(Order_.customer)
+ .get(Customer_.name), 3))));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery18() {
+ String query = "select o.id from Order o where o.orderTs >"
+ + " (select CURRENT_TIMESTAMP from o.lineItems i)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<Timestamp> sq = q.subquery(Timestamp.class);
+ Root<Order> o2 = sq.correlate(o);
+ ListJoin<Order, LineItem> i = o2.join(Order_.lineItems);
+
+ // q.where(cb.gt(
+ // o.get(Order_.orderTs),
+ // sq.select(cb.currentTimestamp())));
+
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery19() {
+ String query = "select o.id from Order o where o.quantity >"
+ + " (select SQRT(o.quantity) from Order o where o.delivered" +
+ " = true)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<Double> sq = q.subquery(Double.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(o2.get(Order_.delivered), true));
+
+ q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.sqrt(o2
+ .get(Order_.quantity)))));
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery20() {
+ String query = "select o.id from Order o where o.customer.name in"
+ + " (select CONCAT(o.customer.name, 'XX') from Order o"
+ + " where o.quantity > 10)";
+ CriteriaQuery q = cb.create();
+ Root<Order> o = q.from(Order.class);
+ q.select(o.get(Order_.id));
+
+ Subquery<String> sq = q.subquery(String.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.gt(o2.get(Order_.quantity), 10));
+
+ q.where(cb.in(o.get(Order_.customer).get(Customer_.name)).value(
+ sq.select(cb.concat(
+ o2.get(Order_.customer).get(Customer_.name), "XX"))));
+ assertEquivalence(q, query);
+ }
+
+ @AllowFailure
+ public void testSubquery21() {
+ String query = "select c from Customer c where c.creditRating ="
+ + " (select "
+ + " CASE WHEN o2.quantity > 10 THEN "
+ + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.POOR "
+ + "WHEN o2.quantity = 10 THEN "
+ + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.GOOD "
+ + " ELSE "
+ + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.EXCELLENT "
+ + " END from Order o2"
+ + " where c.id = o2.customer.id)";
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ q.select(c);
+
+ Subquery<String> sq = q.subquery(String.class);
+ Root<Order> o2 = sq.from(Order.class);
+ sq.where(cb.equal(
+ c.get(Customer_.id),
+ o2.get(Order_.customer).get(Customer_.id)));
+
+ q.where(cb.equal(c.get(Customer_.creditRating),
+ cb.selectCase()
+ .when(cb.gt(o2.get(Order_.quantity), 10),
+ Customer.CreditRating.POOR)
+ .when(cb.equal(o2.get(Order_.quantity), 10),
+ Customer.CreditRating.GOOD)
+ .otherwise(Customer.CreditRating.EXCELLENT)
+ ));
+ assertEquivalence(q, query);
+ }
+
+ // Coalesce for Enum type
+ @AllowFailure(message="Wrong JPQL due to enum oridinal/identifier " +
+ "type mismatch")
+ public void testSubquery22() {
+ String query = "select c from Customer c "
+ + "where c.creditRating = (select COALESCE (c1.creditRating, "
+ + "org.apache.openjpa.persistence.criteria.Customer$" +
+ "CreditRating.POOR) "
+ + "from Customer c1 where c1.name = 'Famzy') order by c.name " +
+ "DESC";
+
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ q.select(c);
+ q.orderBy(cb.desc(c.get(Customer_.name)));
+
+ Subquery<Customer.CreditRating> sq =
+ q.subquery(Customer.CreditRating.class);
+ Root<Customer> c1 = sq.from(Customer.class);
+ sq.where(cb.equal(c1.get(Customer_.name), "Famzy"));
+
+ Expression<Customer.CreditRating> coalesce = cb.coalesce(
+ c1.get(Customer_.creditRating),
+ Customer.CreditRating.POOR);
+ sq.select(coalesce);
+ q.where(cb.equal(c.get(Customer_.creditRating),sq));
+ assertEquivalence(q, query);
+ }
+
+
+ public void testSubquery23() {
+ String query =
+ "select c from Customer c "
+ + "where c.creditRating = (select NULLIF (c1.creditRating, "
+ + "org.apache.openjpa.persistence.criteria."
+ + "Customer$CreditRating.POOR) "
+ + "from Customer c1 where c1.name = 'Famzy') "
+ + "order by c.name DESC";
+ CriteriaQuery q = cb.create();
+ Root<Customer> c = q.from(Customer.class);
+ q.select(c);
+ q.orderBy(cb.desc(c.get(Customer_.name)));
+
+ Subquery<Customer.CreditRating> sq =
+ q.subquery(Customer.CreditRating.class);
+ Root<Customer> c1 = sq.from(Customer.class);
+ sq.where(cb.equal(c1.get(Customer_.name), "Famzy"));
+
+ q.where(cb.equal(c.get(Customer_.creditRating),
+ sq.select(cb.nullif(c1.get(Customer_.creditRating),
+ Customer.CreditRating.POOR))));
+ assertEquivalence(q, query);
+ }
+
+ /**
+ * Verify a sub query can contain MAX and additional date comparisons
+ * without losing the correct alias information. This sort of query
+ * originally caused problems for DBDictionaries which used DATABASE syntax.
+ */
+ // Not sure how to do Cartesian join when Employee can not
+ // navigate to Dependent
+
+ public void testSubSelectMaxDateRange() {
+ String query = "SELECT e,d from Employee e, Dependent d "
+ + "WHERE e.empId = :empid "
+ + "AND d.id.empid = (SELECT MAX (e2.empId) FROM Employee e2) "
+ + "AND d.id.effDate > :minDate "
+ + "AND d.id.effDate < :maxDate ";
+ }
+
+
+}
Propchange: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java
------------------------------------------------------------------------------
svn:eol-style = native