|
1
22 package org.jboss.test.cmp2.commerce;
23
24 import java.util.Collection  ;
25 import java.util.Set ;
26 import java.lang.reflect.Method ;
27 import javax.management.MBeanServer ;
28 import javax.management.MBeanServerFactory ;
29 import javax.management.ObjectName ;
30
31 import junit.framework.Test;
32 import org.jboss.ejb.EntityContainer;
33 import org.jboss.ejb.plugins.cmp.ejbql.Catalog;
34 import org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler;
35 import org.jboss.ejb.plugins.cmp.jdbc.metadata.JDBCReadAheadMetaData;
36 import org.jboss.ejb.plugins.cmp.jdbc.metadata.JDBCQueryMetaData;
37 import org.jboss.mx.server.ServerConstants;
38 import org.jboss.mx.server.registry.MBeanEntry;
39 import org.jboss.mx.server.registry.MBeanRegistry;
40 import org.jboss.test.JBossTestCase;
41 import org.jboss.mx.util.MBeanProxyExt;
42 import org.jboss.util.UnreachableStatementException;
43
44 public class QueryTest extends net.sourceforge.junitejb.EJBTestCase
45 {
46 private JDBCEJBQLCompiler compiler;
47 private static final String javaVersion = System.getProperty("java.specification.version");
48 private static final Class [] NO_PARAMS = new Class []{};
49
50 public static Test suite() throws Exception
51 {
52 return JBossTestCase.getDeploySetup(QueryTest.class, "cmp2-commerce.jar");
53 }
54
55 public QueryTest(String name)
56 {
57 super(name);
58 }
59
60 public void setUpEJB() throws Exception
61 {
62 MBeanServer server = (MBeanServer ) MBeanServerFactory.findMBeanServer(null).get(0);
63 ObjectName name = new ObjectName ("jboss.j2ee:jndiName=commerce/Order,service=EJB");
64 MBeanRegistry registry = (MBeanRegistry) MBeanProxyExt.create(MBeanRegistry.class, ServerConstants.MBEAN_REGISTRY, server);
65 MBeanEntry entry = registry.get(name);
66 EntityContainer container = (EntityContainer) entry.getResourceInstance();
67 Catalog catalog = (Catalog) container.getEjbModule().getModuleData("CATALOG");
68 compiler = new JDBCEJBQLCompiler(catalog);
69 }
70
71 private String compileEJBQL(String ejbql)
72 {
73 return compileEJBQL(ejbql, java.util.Collection .class, NO_PARAMS);
74 }
75
76 private String compileEJBQL(String ejbql, Class returnType, Class [] paramClasses)
77 {
78 try {
79 compiler.compileEJBQL(ejbql, returnType, paramClasses,
80 new JDBCQueryMetaData()
81 {
82 public Method getMethod()
83 {
84 throw new UnsupportedOperationException ();
85 }
86
87 public boolean isResultTypeMappingLocal()
88 {
89 return true;
90 }
91
92 public JDBCReadAheadMetaData getReadAhead()
93 {
94 return new JDBCReadAheadMetaData("on-load", 100, "*");
95 }
96
97 public Class getQLCompilerClass()
98 {
99 throw new UnsupportedOperationException ();
100 }
101
102 public boolean isLazyResultSetLoading()
103 {
104 return false;
105 }
106 });
107 return compiler.getSQL().trim();
108 } catch (Throwable t) {
109 fail(t.getMessage());
110 throw new UnreachableStatementException();
111 }
112 }
113
114 private String compileJBossQL(String ejbql, Class returnType, Class [] paramClasses)
115 {
116 return compileJBossQL(ejbql, returnType, paramClasses,
117 new JDBCQueryMetaData()
118 {
119 public Method getMethod()
120 {
121 throw new UnsupportedOperationException ();
122 }
123
124 public boolean isResultTypeMappingLocal()
125 {
126 return true;
127 }
128
129 public JDBCReadAheadMetaData getReadAhead()
130 {
131 return new JDBCReadAheadMetaData("on-load", 100, "*");
132 }
133
134 public Class getQLCompilerClass()
135 {
136 throw new UnsupportedOperationException ();
137 }
138
139 public boolean isLazyResultSetLoading()
140 {
141 return false;
142 }
143 });
144 }
145
146 private String compileJBossQL(String ejbql, Class returnType, Class [] paramClasses, JDBCQueryMetaData metadata)
147 {
148 try {
149 compiler.compileJBossQL(ejbql, returnType, paramClasses, metadata);
150 return compiler.getSQL();
151 } catch (Throwable t) {
152 fail(t.getMessage());
153 throw new UnreachableStatementException();
154 }
155 }
156
157 public void testJBossQL() throws Exception
158 {
159 assertEquals("SELECT t0_u.USER_ID FROM USER_DATA t0_u WHERE (ucase(t0_u.USER_NAME) = ?)",
160 compileJBossQL("SELECT OBJECT(u) FROM user u WHERE UCASE(u.userName) = ?1",
161 Collection .class, new Class []{String .class}));
162
163 assertEquals("SELECT t0_u.USER_ID FROM USER_DATA t0_u WHERE (lcase(t0_u.USER_NAME) = ?)",
164 compileJBossQL("SELECT OBJECT(u) FROM user u WHERE LCASE(u.userName) = ?1",
165 Collection .class, new Class []{String .class}));
166
167 String expected = "1.4".equals(javaVersion) ?
168 "SELECT t0_o1.ORDER_NUMBER FROM ORDER_DATA t0_o1, ORDER_DATA t3_o2, CUSTOMEREJB t2_o2_customer, CUSTOMEREJB t1_o1_customer WHERE (( NOT (t1_o1_customer.id=t2_o2_customer.id)) AND (t0_o1.CC_TYPE=t3_o2.CC_TYPE AND t0_o1.CC_FIRST_NAME=t3_o2.CC_FIRST_NAME AND t0_o1.CC_MI=t3_o2.CC_MI AND t0_o1.CC_LAST_NAME=t3_o2.CC_LAST_NAME AND t0_o1.CC_BILLING_ZIP=t3_o2.CC_BILLING_ZIP AND t0_o1.CC_CARD_NUMBER=t3_o2.CC_CARD_NUMBER) AND t3_o2.customer=t2_o2_customer.id AND t0_o1.customer=t1_o1_customer.id)" :
169 "SELECT t0_o1.ORDER_NUMBER FROM ORDER_DATA t0_o1, ORDER_DATA t3_o2, CUSTOMEREJB t1_o1_customer, CUSTOMEREJB t2_o2_customer WHERE (( NOT (t1_o1_customer.id=t2_o2_customer.id)) AND (t0_o1.CC_TYPE=t3_o2.CC_TYPE AND t0_o1.CC_FIRST_NAME=t3_o2.CC_FIRST_NAME AND t0_o1.CC_MI=t3_o2.CC_MI AND t0_o1.CC_LAST_NAME=t3_o2.CC_LAST_NAME AND t0_o1.CC_BILLING_ZIP=t3_o2.CC_BILLING_ZIP AND t0_o1.CC_CARD_NUMBER=t3_o2.CC_CARD_NUMBER) AND t0_o1.customer=t1_o1_customer.id AND t3_o2.customer=t2_o2_customer.id)";
170 String compiled = compileJBossQL(
171 "SELECT OBJECT(o1) FROM OrderX o1, OrderX o2 WHERE o1.customer <> o2.customer AND o1.creditCard = o2.creditCard",
172 Collection .class, NO_PARAMS);
173 assertTrue("Expected: " + expected + " but got: " + compiled, expected.equals(compiled));
174
175 assertEquals("SELECT t0_o.ORDER_NUMBER " +
176 "FROM ORDER_DATA t0_o " +
177 "WHERE ((t0_o.CC_TYPE=? " +
178 "AND t0_o.CC_FIRST_NAME=? " +
179 "AND t0_o.CC_MI=? " +
180 "AND t0_o.CC_LAST_NAME=? " +
181 "AND t0_o.CC_BILLING_ZIP=? " +
182 "AND t0_o.CC_CARD_NUMBER=?))",
183 compileJBossQL("SELECT OBJECT(o) FROM OrderX o WHERE o.creditCard = ?1",
184 Collection .class, new Class []{Card.class}));
185
186 assertEquals("SELECT t0_o.ORDER_NUMBER " +
187 "FROM ORDER_DATA t0_o " +
188 "WHERE (( NOT (t0_o.CC_TYPE=? " +
189 "AND t0_o.CC_FIRST_NAME=? " +
190 "AND t0_o.CC_MI=? " +
191 "AND t0_o.CC_LAST_NAME=? " +
192 "AND t0_o.CC_BILLING_ZIP=? " +
193 "AND t0_o.CC_CARD_NUMBER=?)))",
194 compileJBossQL("SELECT OBJECT(o) FROM OrderX o WHERE o.creditCard <> ?1",
195 Collection .class, new Class []{Card.class}));
196
197 assertEquals(
198 "SELECT DISTINCT t0_u.USER_ID, t0_u.USER_NAME FROM USER_DATA t0_u ORDER BY t0_u.USER_NAME ASC",
199 compileJBossQL("SELECT DISTINCT OBJECT(u) FROM user u ORDER BY u.userName", Collection .class, NO_PARAMS)
200 );
201 assertEquals(
202 "SELECT DISTINCT t0_u.USER_ID FROM USER_DATA t0_u ORDER BY t0_u.USER_ID ASC",
203 compileJBossQL("SELECT DISTINCT OBJECT(u) FROM user u ORDER BY u.userId", Collection .class, NO_PARAMS)
204 );
205 assertEquals(
206 "SELECT DISTINCT t0_u.USER_NAME FROM USER_DATA t0_u ORDER BY t0_u.USER_NAME ASC",
207 compileJBossQL("SELECT DISTINCT u.userName FROM user u ORDER BY u.userName", Collection .class, NO_PARAMS)
208 );
209 assertEquals(
210 "SELECT DISTINCT ucase(t0_u.USER_NAME) FROM USER_DATA t0_u ORDER BY t0_u.USER_NAME ASC",
211 compileJBossQL("SELECT DISTINCT UCASE(u.userName) FROM user u ORDER BY u.userName",
212 Collection .class, new Class []{String .class})
213 );
214 assertEquals(
215 "SELECT DISTINCT t0_u.USER_NAME, t0_u.USER_ID FROM USER_DATA t0_u ORDER BY t0_u.USER_ID ASC",
216 compileJBossQL("SELECT DISTINCT u.userName FROM user u ORDER BY u.userId",
217 Collection .class, new Class []{String .class})
218 );
219
220 assertEquals(
221 "SELECT t0_o.ORDER_NUMBER FROM ORDER_DATA t0_o, ADDRESSEJB t1_o_shippingAddress WHERE (t1_o_shippingAddress.city = ? AND t0_o.SHIPPING_ADDRESS=t1_o_shippingAddress.id) OR (t1_o_shippingAddress.state = ? AND t0_o.SHIPPING_ADDRESS=t1_o_shippingAddress.id)",
222 compileJBossQL(
223 "SELECT OBJECT(o) FROM OrderX o WHERE o.shippingAddress.city=?1 OR o.shippingAddress.state=?2",
224 Collection .class,
225 new Class []{String .class, String .class}
226 )
227 );
228
229 assertEquals(
230 "SELECT t0_o.ORDER_NUMBER, t1_o_shippingAddress.state FROM ORDER_DATA t0_o, ADDRESSEJB t1_o_shippingAddress WHERE t0_o.SHIPPING_ADDRESS=t1_o_shippingAddress.id ORDER BY t1_o_shippingAddress.state ASC",
231 compileJBossQL(
232 "SELECT OBJECT(o) FROM OrderX o ORDER BY o.shippingAddress.state",
233 Collection .class,
234 new Class []{String .class, String .class}
235 )
236 );
237
238 JDBCQueryMetaData lazyMD = new JDBCQueryMetaData()
239 {
240 public Method getMethod()
241 {
242 throw new UnsupportedOperationException ();
243 }
244
245 public boolean isResultTypeMappingLocal()
246 {
247 return true;
248 }
249
250 public JDBCReadAheadMetaData getReadAhead()
251 {
252 return new JDBCReadAheadMetaData("on-load", 4, "*");
253 }
254
255 public Class getQLCompilerClass()
256 {
257 throw new UnsupportedOperationException ();
258 }
259
260 public boolean isLazyResultSetLoading()
261 {
262 return true;
263 }
264
265 };
266
267 assertEquals(
268 "SELECT (SELECT count(t0_o.id) FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')), t0_o.id " +
269 "FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')",
270 compileJBossQL("select object(o) from Address o where o.state='CA'", Collection .class, NO_PARAMS, lazyMD)
271 );
272
273 assertEquals(
274 "SELECT DISTINCT (SELECT count(DISTINCT t0_o.id) FROM LINEITEMEJB t0_o WHERE (t0_o.quantity > 1000)), t0_o.id " +
275 "FROM LINEITEMEJB t0_o WHERE (t0_o.quantity > 1000)",
276 compileJBossQL("select object(o) from LineItem o where o.quantity > 1000 offset 1 limit 2", Set .class, NO_PARAMS, lazyMD)
277 );
278
279 assertEquals(
280 "SELECT (SELECT count(t0_o.city) FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')), t0_o.city " +
281 "FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')",
282 compileJBossQL("select o.city from Address o where o.state='CA'", Collection .class, NO_PARAMS, lazyMD)
283 );
284
285 assertEquals(
286 "SELECT DISTINCT (SELECT count(DISTINCT t0_o.city) FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')), t0_o.city " +
287 "FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')",
288 compileJBossQL("select distinct o.city from Address o where o.state='CA'", Collection .class, NO_PARAMS, lazyMD)
289 );
290 }
291
292 public void testEJBQL() throws Exception
293 {
294 assertEquals("SELECT t0_o.ORDER_NUMBER FROM ORDER_DATA t0_o",
295 compileEJBQL("SELECT OBJECT(o) FROM OrderX o"));
296
297 assertEquals(
298 "SELECT t0_o.ORDER_NUMBER FROM ORDER_DATA t0_o, ADDRESSEJB t1_o_shippingAddress WHERE (t1_o_shippingAddress.city = ? AND t0_o.SHIPPING_ADDRESS=t1_o_shippingAddress.id) OR (t1_o_shippingAddress.state = ? AND t0_o.SHIPPING_ADDRESS=t1_o_shippingAddress.id)",
299 compileEJBQL(
300 "SELECT OBJECT(o) FROM OrderX o WHERE o.shippingAddress.city=?1 OR o.shippingAddress.state=?2",
301 Collection .class,
302 new Class []{String .class, String .class}
303 )
304 );
305
306 String expected = "1.4".equals(javaVersion) ?
307 "SELECT t0_o.ORDER_NUMBER " +
308 "FROM ORDER_DATA t0_o, LINEITEMEJB t4_l, PRODUCTCATEGORYEJB t1_pc, PRODUCT_PRODUCT_CATEGORY t5_l_product_productCategories_R, PRODUCT t6_l_product " +
309 "WHERE (((t0_o.ORDER_NUMBER = ? AND t1_pc.name = ?))) " +
310 "AND t6_l_product.id=t5_l_product_productCategories_R.PRODUCT_ID " +
311 "AND t1_pc.id=t5_l_product_productCategories_R.PRODUCT_CATEGORY_ID " +
312 "AND t1_pc.subId=t5_l_product_productCategories_R.PRODUCT_CATEGORY_SUBID " +
313 "AND t4_l.product=t6_l_product.id AND t0_o.ORDER_NUMBER=t4_l.ORDER_NUMBER" :
314 "SELECT t0_o.ORDER_NUMBER " +
315 "FROM ORDER_DATA t0_o, LINEITEMEJB t4_l, PRODUCTCATEGORYEJB t1_pc, PRODUCT_PRODUCT_CATEGORY t5_l_product_productCategories_R, PRODUCT t6_l_product " +
316 "WHERE (((t0_o.ORDER_NUMBER = ? AND t1_pc.name = ?))) " +
317 "AND t0_o.ORDER_NUMBER=t4_l.ORDER_NUMBER " +
318 "AND t6_l_product.id=t5_l_product_productCategories_R.PRODUCT_ID " +
319 "AND t1_pc.id=t5_l_product_productCategories_R.PRODUCT_CATEGORY_ID " +
320 "AND t1_pc.subId=t5_l_product_productCategories_R.PRODUCT_CATEGORY_SUBID " +
321 "AND t4_l.product=t6_l_product.id";
322
323 String compiled = compileEJBQL("SELECT OBJECT(o) FROM OrderX o, " +
324 "IN(o.lineItems) l, " +
325 "IN(l.product.productCategories) pc " +
326 "WHERE (o.ordernumber = ?1 and pc.name=?2)",
327 Collection .class, new Class []{Long .class, String .class});
328 assertEquals(expected, compiled);
329
330 expected = "SELECT DISTINCT t0_o.ORDER_NUMBER " +
331 "FROM ORDER_DATA t0_o, LINEITEMEJB t3_l " +
332 "WHERE (t0_o.ORDER_NUMBER = ?) OR (EXISTS (SELECT t2_o_lineItems.id FROM LINEITEMEJB t2_o_lineItems " +
333 "WHERE t0_o.ORDER_NUMBER=t2_o_lineItems.ORDER_NUMBER AND t2_o_lineItems.id=t3_l.id))";
334 compiled = compileEJBQL("SELECT OBJECT(o) FROM OrderX o, LineItem l WHERE o.ordernumber = ?1 OR l MEMBER o.lineItems",
335 Set .class, new Class []{Long .class});
336 assertTrue("Expected: " + expected + " but got: " + compiled, expected.equals(compiled));
337
338 assertEquals("SELECT DISTINCT t0_o.ORDER_NUMBER " +
339 "FROM ORDER_DATA t0_o, LINEITEMEJB t3_l " +
340 "WHERE (t0_o.ORDER_NUMBER = ?) OR ( NOT EXISTS (SELECT t2_o_lineItems.id FROM LINEITEMEJB t2_o_lineItems " +
341 "WHERE t0_o.ORDER_NUMBER=t2_o_lineItems.ORDER_NUMBER AND t2_o_lineItems.id=t3_l.id))",
342 compileEJBQL("SELECT OBJECT(o) FROM OrderX o, LineItem l WHERE o.ordernumber = ?1 OR l NOT MEMBER o.lineItems",
343 Set .class, new Class []{Long .class}));
344
345 assertEquals("SELECT DISTINCT t0_p.id " +
346 "FROM PRODUCT t0_p, PRODUCTCATEGORYEJB t4_pc " +
347 "WHERE (t0_p.id = ?) OR (EXISTS (" +
348 "SELECT t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_ID, t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_SUBID " +
349 "FROM PRODUCT_PRODUCT_CATEGORY t3_p_productCategories_RELATION_ " +
350 "WHERE t0_p.id=t3_p_productCategories_RELATION_.PRODUCT_ID " +
351 "AND t4_pc.id=t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_ID " +
352 "AND t4_pc.subId=t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_SUBID))",
353 compileEJBQL("SELECT OBJECT(p) FROM Product p, ProductCategory pc WHERE p.id = ?1 OR pc MEMBER p.productCategories",
354 Set .class, new Class []{Long .class}));
355
356 assertEquals("SELECT DISTINCT t0_p.id " +
357 "FROM PRODUCT t0_p, PRODUCTCATEGORYEJB t4_pc " +
358 "WHERE (t0_p.id = ?) OR ( NOT EXISTS (" +
359 "SELECT t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_ID, t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_SUBID " +
360 "FROM PRODUCT_PRODUCT_CATEGORY t3_p_productCategories_RELATION_ " +
361 "WHERE t0_p.id=t3_p_productCategories_RELATION_.PRODUCT_ID " +
362 "AND t4_pc.id=t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_ID " +
363 "AND t4_pc.subId=t3_p_productCategories_RELATION_.PRODUCT_CATEGORY_SUBID))",
364 compileEJBQL("SELECT OBJECT(p) FROM Product p, ProductCategory pc WHERE p.id = ?1 OR pc NOT MEMBER p.productCategories",
365 Set .class, new Class []{Long .class}));
366
367 assertEquals("SELECT DISTINCT t0_o.ORDER_NUMBER " +
368 "FROM ORDER_DATA t0_o " +
369 "WHERE (t0_o.ORDER_NUMBER = ?) OR (EXISTS (SELECT t2_o_lineItems.id " +
370 "FROM LINEITEMEJB t2_o_lineItems " +
371 "WHERE t0_o.ORDER_NUMBER=t2_o_lineItems.ORDER_NUMBER))",
372 compileEJBQL("SELECT OBJECT(o) FROM OrderX o WHERE o.ordernumber = ?1 OR o.lineItems IS NOT EMPTY",
373 Set .class, new Class []{Long .class}));
374
375 assertEquals("SELECT t0_l.id FROM CUSTOMEREJB t1_c, ORDER_DATA t3_o, LINEITEMEJB t0_l WHERE ((t1_c.id = 1)) AND t1_c.id=t3_o.customer AND t3_o.ORDER_NUMBER=t0_l.ORDER_NUMBER",
376 compileEJBQL("SELECT OBJECT(l) FROM Customer c, IN(c.orders) o, IN(o.lineItems) l WHERE c.id=1"));
377
378 assertEquals("SELECT t0_l.id FROM LINEITEMEJB t0_l, CUSTOMEREJB t1_c, ORDER_DATA t3_l_order WHERE (t1_c.id = 1 AND EXISTS (SELECT t2_c_orders.ORDER_NUMBER FROM ORDER_DATA t2_c_orders WHERE t1_c.id=t2_c_orders.customer AND t2_c_orders.ORDER_NUMBER=t3_l_order.ORDER_NUMBER) AND t0_l.ORDER_NUMBER=t3_l_order.ORDER_NUMBER)",
380 compileEJBQL("SELECT OBJECT(l) FROM LineItem l, Customer c WHERE c.id=1 AND l.order MEMBER OF c.orders"));
381
382 StringBuffer sql = new StringBuffer (200);
383 sql.append("SELECT DISTINCT t0_li.id ")
384 .append("FROM LINEITEMEJB t0_li, ORDER_DATA t1_li_order, ADDRESSEJB t2_li_order_billingAddress ")
385 .append("WHERE (t1_li_order.BILLING_ADDRESS IS NOT NULL AND t0_li.ORDER_NUMBER=t1_li_order.ORDER_NUMBER AND t1_li_order.BILLING_ADDRESS=t2_li_order_billingAddress.id)");
386 assertEquals(
387 sql.toString(),
388 compileEJBQL("SELECT DISTINCT OBJECT(li) FROM LineItem AS li WHERE li.order.billingAddress IS NOT NULL")
389 );
390 }
391 }
392 |
Popular Tags |
Java API By Example, From Geeks To Geeks.