Source code: org/hibernate/test/hql/ASTParserLoadingTest.java
1 // $Id: ASTParserLoadingTest.java,v 1.23 2005/04/27 06:43:14 oneovthafew Exp $
2 package org.hibernate.test.hql;
3
4 import java.math.BigDecimal;
5 import java.sql.Date;
6 import java.sql.Time;
7 import java.sql.Timestamp;
8 import java.util.ArrayList;
9 import java.util.Collection;
10 import java.util.Iterator;
11 import java.util.List;
12 import java.util.Map;
13
14 import junit.framework.Test;
15 import junit.framework.TestSuite;
16
17 import org.hibernate.Hibernate;
18 import org.hibernate.Session;
19 import org.hibernate.Transaction;
20 import org.hibernate.cfg.Environment;
21 import org.hibernate.dialect.DB2Dialect;
22 import org.hibernate.dialect.MySQLDialect;
23 import org.hibernate.test.TestCase;
24 import org.hibernate.test.cid.Customer;
25 import org.hibernate.test.cid.LineItem;
26 import org.hibernate.test.cid.Order;
27 import org.hibernate.test.cid.Product;
28
29 /**
30 * Tests the integration of the new AST parser into the loading of query results using
31 * the Hibernate persisters and loaders.
32 *
33 * @author Steve
34 */
35 public class ASTParserLoadingTest extends TestCase {
36
37 public ASTParserLoadingTest(String name) {
38 super( name );
39 }
40
41 private List createdAnimalIds = new ArrayList();
42
43 protected String[] getMappings() {
44 // Make sure we are using the new AST parser translator...
45 System.setProperty( Environment.QUERY_TRANSLATOR, "org.hibernate.hql.ast.ASTQueryTranslatorFactory" );
46 return new String[]{
47 "hql/Animal.hbm.xml",
48 "batchfetch/ProductLine.hbm.xml",
49 "cid/Customer.hbm.xml",
50 "cid/Order.hbm.xml",
51 "cid/LineItem.hbm.xml",
52 "cid/Product.hbm.xml"
53 };
54 }
55
56 public void testAliases() {
57 Session s = openSession();
58 Transaction t = s.beginTransaction();
59 Animal a = new Animal();
60 a.setBodyWeight(12.4f);
61 a.setDescription("an animal");
62 s.persist(a);
63 String[] aliases1 = s.createQuery("select a.bodyWeight as abw, a.description from Animal a").getReturnAliases();
64 assertEquals(aliases1[0], "abw");
65 assertEquals(aliases1[1], "1");
66 String[] aliases2 = s.createQuery("select count(*), avg(a.bodyWeight) as avg from Animal a").getReturnAliases();
67 assertEquals(aliases2[0], "0");
68 assertEquals(aliases2[1], "avg");
69 s.delete(a);
70 t.commit();
71 s.close();
72 }
73
74 public void testAggregation() {
75 Session s = openSession();
76 Transaction t = s.beginTransaction();
77 Human h = new Human();
78 h.setBodyWeight( (float) 74.0 );
79 h.setHeight(120.5);
80 h.setDescription("Me");
81 h.setName( new Name("Gavin", 'A', "King") );
82 h.setNickName("Oney");
83 s.persist(h);
84 Float sum = (Float) s.createQuery("select sum(h.bodyWeight) from Human h").uniqueResult();
85 Double avg = (Double) s.createQuery("select avg(h.height) from Human h").uniqueResult();
86 assertEquals(sum.floatValue(), 74.0, 0.01);
87 assertEquals(avg.doubleValue(), 120.5, 0.01);
88 s.delete(h);
89 t.commit();
90 s.close();
91 }
92
93 public void testImplicitPolymorphism() {
94 Session s = openSession();
95 Transaction t = s.beginTransaction();
96
97 Product product = new Product();
98 product.setDescription( "My Product" );
99 product.setNumberAvailable( 10 );
100 product.setPrice( new BigDecimal( 123 ) );
101 product.setProductId( "4321" );
102 s.save( product );
103
104 List list = s.createQuery("from java.lang.Comparable").list();
105 assertEquals( list.size(), 0 );
106
107 list = s.createQuery("from java.lang.Object").list();
108 assertEquals( list.size(), 1 );
109
110 s.delete(product);
111
112 list = s.createQuery("from java.lang.Object").list();
113 assertEquals( list.size(), 0 );
114
115 t.commit();
116 s.close();
117 }
118
119 public void testCast() {
120 if ( (getDialect() instanceof MySQLDialect) || (getDialect() instanceof DB2Dialect) ) return;
121 Session session = openSession();
122 Transaction txn = session.beginTransaction();
123 session.createQuery("from Human h where h.nickName like 'G%'").list();
124 session.createQuery("from Animal a where cast(a.bodyWeight as string) like '1.%'").list();
125 session.createQuery("from Animal a where cast(a.bodyWeight as integer) = 1").list();
126 txn.commit();
127 session.close();
128 }
129
130 public void testExtract() {
131 Session session = openSession();
132 Transaction txn = session.beginTransaction();
133 session.createQuery("select second(current_timestamp()), minute(current_timestamp()), hour(current_timestamp()) from Mammal m").list();
134 session.createQuery("select day(m.birthdate), month(m.birthdate), year(m.birthdate) from Mammal m").list();
135 if ( !(getDialect() instanceof DB2Dialect) ) { //no ANSI extract
136 session.createQuery("select extract(second from current_timestamp()), extract(minute from current_timestamp()), extract(hour from current_timestamp()) from Mammal m").list();
137 session.createQuery("select extract(day from m.birthdate), extract(month from m.birthdate), extract(year from m.birthdate) from Mammal m").list();
138 }
139 txn.commit();
140 session.close();
141 }
142
143 public void testOneToManyFilter() throws Throwable {
144 Session session = openSession();
145 Transaction txn = session.beginTransaction();
146
147 Product product = new Product();
148 product.setDescription( "My Product" );
149 product.setNumberAvailable( 10 );
150 product.setPrice( new BigDecimal( 123 ) );
151 product.setProductId( "4321" );
152 session.save( product );
153
154 Customer customer = new Customer();
155 customer.setCustomerId( "123456789" );
156 customer.setName( "My customer" );
157 customer.setAddress( "somewhere" );
158 session.save( customer );
159
160 Order order = customer.generateNewOrder( new BigDecimal( 1234 ) );
161 session.save( order );
162
163 LineItem li = order.generateLineItem( product, 5 );
164 session.save( li );
165
166 session.flush();
167
168 assertEquals( session.createFilter( customer.getOrders(), "" ).list().size(), 1 );
169
170 assertEquals( session.createFilter( order.getLineItems(), "" ).list().size(), 1 );
171 assertEquals( session.createFilter( order.getLineItems(), "where this.quantity > :quantity" ).setInteger( "quantity", 5 ).list().size(), 0 );
172
173 session.delete(li);
174 session.delete(order);
175 session.delete(product);
176 session.delete(customer);
177 txn.commit();
178 session.close();
179 }
180
181 public void testManyToManyFilter() throws Throwable {
182 Session session = openSession();
183 Transaction txn = session.beginTransaction();
184
185 Human human = new Human();
186 human.setName( new Name() );
187 human.getName().setFirst( "Steve" );
188 human.getName().setInitial( 'L' );
189 human.getName().setLast( "Ebersole" );
190 session.save( human );
191
192 Human friend = new Human();
193 friend.setName( new Name() );
194 friend.getName().setFirst( "John" );
195 friend.getName().setInitial( 'Q' );
196 friend.getName().setLast( "Doe" );
197 friend.setBodyWeight( 11.0f );
198 session.save( friend );
199
200 human.setFriends( new ArrayList() );
201 friend.setFriends( new ArrayList() );
202 human.getFriends().add( friend );
203 friend.getFriends().add( human );
204
205 session.flush();
206
207 assertEquals( session.createFilter( human.getFriends(), "" ).list().size(), 1 );
208 assertEquals( session.createFilter( human.getFriends(), "where this.bodyWeight > ?" ).setFloat( 0, 10f ).list().size(), 1 );
209 assertEquals( session.createFilter( human.getFriends(), "where this.bodyWeight < ?" ).setFloat( 0, 10f ).list().size(), 0 );
210
211 session.delete(human);
212 session.delete(friend);
213
214 txn.commit();
215 session.close();
216 }
217
218 public void testSelectExpressions() {
219 createTestBaseData();
220 Session session = openSession();
221 Transaction txn = session.beginTransaction();
222 Human h = new Human();
223 h.setName( new Name("Gavin", 'A', "King") );
224 h.setNickName("Oney");
225 h.setBodyWeight(1.0f);
226 session.persist(h);
227 List results = session.createQuery("select 'found', lower(h.name.first) from Human h where lower(h.name.first) = 'gavin'").list();
228 results = session.createQuery("select 'found', lower(h.name.first) from Human h where concat(h.name.first, ' ', h.name.initial, ' ', h.name.last) = 'Gavin A King'").list();
229 results = session.createQuery("select 'found', lower(h.name.first) from Human h where h.name.first||' '||h.name.initial||' '||h.name.last = 'Gavin A King'").list();
230 results = session.createQuery("select a.bodyWeight + m.bodyWeight from Animal a join a.mother m").list();
231 results = session.createQuery("select 2.0 * (a.bodyWeight + m.bodyWeight) from Animal a join a.mother m").list();
232 results = session.createQuery("select sum(a.bodyWeight + m.bodyWeight) from Animal a join a.mother m").list();
233 results = session.createQuery("select sum(a.mother.bodyWeight * 2.0) from Animal a").list();
234 results = session.createQuery("select concat(h.name.first, ' ', h.name.initial, ' ', h.name.last) from Human h").list();
235 results = session.createQuery("select h.name.first||' '||h.name.initial||' '||h.name.last from Human h").list();
236 results = session.createQuery("select nickName from Human").list();
237 results = session.createQuery("select lower(nickName) from Human").list();
238 results = session.createQuery("select abs(bodyWeight*-1) from Human").list();
239 results = session.createQuery("select upper(h.name.first||' ('||h.nickName||')') from Human h").list();
240 session.delete(h);
241 txn.commit();
242 session.close();
243 destroyTestBaseData();
244 }
245
246 private void createTestBaseData() {
247 Session session = openSession();
248 Transaction txn = session.beginTransaction();
249
250 Mammal m1 = new Mammal();
251 m1.setBodyWeight( 11f );
252 m1.setDescription( "Mammal #1" );
253
254 session.save( m1 );
255
256 Mammal m2 = new Mammal();
257 m2.setBodyWeight( 9f );
258 m2.setDescription( "Mammal #2" );
259 m2.setMother( m1 );
260
261 session.save( m2 );
262
263 txn.commit();
264 session.close();
265
266 createdAnimalIds.add( m1.getId() );
267 createdAnimalIds.add( m2.getId() );
268 }
269
270 private void destroyTestBaseData() {
271 Session session = openSession();
272 Transaction txn = session.beginTransaction();
273
274 for ( int i = 0; i < createdAnimalIds.size(); i++ ) {
275 Animal animal = ( Animal ) session.load( Animal.class, ( Long ) createdAnimalIds.get( i ) );
276 session.delete( animal );
277 }
278
279 txn.commit();
280 session.close();
281 }
282
283 public void testImplicitJoin() throws Exception {
284 Session session = openSession();
285 Transaction t = session.beginTransaction();
286 Animal a = new Animal();
287 a.setBodyWeight(0.5f);
288 a.setBodyWeight(1.5f);
289 Animal b = new Animal();
290 Animal mother = new Animal();
291 mother.setBodyWeight(10.0f);
292 mother.addOffspring(a);
293 mother.addOffspring(b);
294 session.persist(a);
295 session.persist(b);
296 session.persist(mother);
297 List list = session.createQuery("from Animal a where a.mother.bodyWeight < 2.0 or a.mother.bodyWeight > 9.0").list();
298 assertEquals( list.size(), 2 );
299 list = session.createQuery("from Animal a where a.mother.bodyWeight > 2.0 and a.mother.bodyWeight > 9.0").list();
300 assertEquals( list.size(), 2 );
301 session.delete(b);
302 session.delete(a);
303 session.delete(mother);
304 t.commit();
305 session.close();
306 }
307
308 public void testFromOnly() throws Exception {
309
310 createTestBaseData();
311
312 Session session = openSession();
313
314 List results = session.createQuery( "from Animal" ).list();
315 assertEquals( "Incorrect result size", 2, results.size() );
316 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
317
318 session.close();
319
320 destroyTestBaseData();
321 }
322
323 public void testSimpleSelect() throws Exception {
324
325 createTestBaseData();
326
327 Session session = openSession();
328
329 List results = session.createQuery( "select a from Animal as a" ).list();
330 assertEquals( "Incorrect result size", 2, results.size() );
331 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
332
333 session.close();
334
335 destroyTestBaseData();
336 }
337
338 public void testEntityPropertySelect() throws Exception {
339
340 createTestBaseData();
341
342 Session session = openSession();
343
344 List results = session.createQuery( "select a.mother from Animal as a" ).list();
345 // assertEquals("Incorrect result size", 2, results.size());
346 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
347
348 session.close();
349
350 destroyTestBaseData();
351 }
352
353 public void testWhere() throws Exception {
354
355 createTestBaseData();
356
357 Session session = openSession();
358 List results = null;
359
360 results = session.createQuery( "from Animal an where an.bodyWeight > 10" ).list();
361 assertEquals( "Incorrect result size", 1, results.size() );
362
363 results = session.createQuery( "from Animal an where not an.bodyWeight > 10" ).list();
364 assertEquals( "Incorrect result size", 1, results.size() );
365
366 results = session.createQuery( "from Animal an where an.bodyWeight between 0 and 10" ).list();
367 assertEquals( "Incorrect result size", 1, results.size() );
368
369 results = session.createQuery( "from Animal an where an.bodyWeight not between 0 and 10" ).list();
370 assertEquals( "Incorrect result size", 1, results.size() );
371
372 results = session.createQuery( "from Animal an where sqrt(an.bodyWeight)/2 > 10" ).list();
373 assertEquals( "Incorrect result size", 0, results.size() );
374
375 results = session.createQuery( "from Animal an where (an.bodyWeight > 10 and an.bodyWeight < 100) or an.bodyWeight is null" ).list();
376 assertEquals( "Incorrect result size", 1, results.size() );
377
378 session.close();
379
380 destroyTestBaseData();
381 }
382
383 public void testEntityFetching() throws Exception {
384
385 createTestBaseData();
386
387 Session session = openSession();
388
389 List results = session.createQuery( "from Animal an join fetch an.mother" ).list();
390 assertEquals( "Incorrect result size", 1, results.size() );
391 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
392 Animal mother = ( ( Animal ) results.get( 0 ) ).getMother();
393 assertTrue( "fetch uninitialized", mother != null && Hibernate.isInitialized( mother ) );
394
395 results = session.createQuery( "select an from Animal an join fetch an.mother" ).list();
396 assertEquals( "Incorrect result size", 1, results.size() );
397 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
398 mother = ( ( Animal ) results.get( 0 ) ).getMother();
399 assertTrue( "fetch uninitialized", mother != null && Hibernate.isInitialized( mother ) );
400
401 session.close();
402
403 destroyTestBaseData();
404 }
405
406 public void testCollectionFetching() throws Exception {
407
408 createTestBaseData();
409
410 Session session = openSession();
411 List results = session.createQuery( "from Animal an join fetch an.offspring" ).list();
412 assertEquals( "Incorrect result size", 1, results.size() );
413 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
414 Collection os = ( ( Animal ) results.get( 0 ) ).getOffspring();
415 assertTrue( "fetch uninitialized", os != null && Hibernate.isInitialized( os ) && os.size() == 1 );
416
417 results = session.createQuery( "select an from Animal an join fetch an.offspring" ).list();
418 assertEquals( "Incorrect result size", 1, results.size() );
419 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
420 os = ( ( Animal ) results.get( 0 ) ).getOffspring();
421 assertTrue( "fetch uninitialized", os != null && Hibernate.isInitialized( os ) && os.size() == 1 );
422
423 session.close();
424
425 destroyTestBaseData();
426 }
427
428 public void testProjectionQueries() throws Exception {
429
430 createTestBaseData();
431
432 Session session = openSession();
433
434 List results = session.createQuery( "select an.mother.id, max(an.bodyWeight) from Animal an group by an.mother.id" ).list();
435 // mysql returns nulls in this group by
436 assertEquals( "Incorrect result size", 2, results.size() );
437 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Object[] );
438 assertEquals( "Incorrect return dimensions", 2, ( ( Object[] ) results.get( 0 ) ).length );
439
440 session.close();
441
442 destroyTestBaseData();
443
444 }
445
446 public void testStandardFunctions() throws Exception {
447 Session session = openSession();
448 Product p = new Product();
449 p.setDescription("a product");
450 p.setPrice( new BigDecimal(1.0) );
451 p.setProductId("abc123");
452 session.persist(p);
453 Object[] result = (Object[]) session
454 .createQuery("select current_time(), current_date(), current_timestamp() from Product")
455 .uniqueResult();
456 assertTrue( result[0] instanceof Time );
457 assertTrue( result[1] instanceof Date );
458 assertTrue( result[2] instanceof Timestamp );
459 assertNotNull( result[0] );
460 assertNotNull( result[1] );
461 assertNotNull( result[2] );
462 session.delete(p);
463 session.close();
464
465 }
466
467 public void testDynamicInstantiationQueries() throws Exception {
468
469 createTestBaseData();
470
471 Session session = openSession();
472
473 List results = session.createQuery( "select new Animal(an.description, an.bodyWeight) from Animal an" ).list();
474 assertEquals( "Incorrect result size", 2, results.size() );
475 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Animal );
476
477 Iterator iter = session.createQuery( "select new Animal(an.description, an.bodyWeight) from Animal an" ).iterate();
478 assertTrue( "Incorrect result size", iter.hasNext() );
479 assertTrue( "Incorrect return type", iter.next() instanceof Animal );
480
481 results = session.createQuery( "select new list(an.description, an.bodyWeight) from Animal an" ).list();
482 assertEquals( "Incorrect result size", 2, results.size() );
483 assertTrue( "Incorrect return type", results.get( 0 ) instanceof List );
484 assertEquals( "Incorrect return type", ( (List) results.get( 0 ) ).size(), 2 );
485
486 iter = session.createQuery( "select new list(an.description, an.bodyWeight) from Animal an" ).iterate();
487 assertTrue( "Incorrect result size", iter.hasNext() );
488 Object obj = iter.next();
489 assertTrue( "Incorrect return type", obj instanceof List );
490 assertEquals( "Incorrect return type", ( (List) obj ).size(), 2 );
491
492 results = session.createQuery( "select new map(an.description, an.bodyWeight) from Animal an" ).list();
493 assertEquals( "Incorrect result size", 2, results.size() );
494 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Map );
495 assertEquals( "Incorrect return type", ( (Map) results.get( 0 ) ).size(), 2 );
496 assertTrue( ( (Map) results.get( 0 ) ).containsKey("0") );
497 assertTrue( ( (Map) results.get( 0 ) ).containsKey("1") );
498
499 results = session.createQuery( "select new map(an.description as descr, an.bodyWeight as bw) from Animal an" ).list();
500 assertEquals( "Incorrect result size", 2, results.size() );
501 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Map );
502 assertEquals( "Incorrect return type", ( (Map) results.get( 0 ) ).size(), 2 );
503 assertTrue( ( (Map) results.get( 0 ) ).containsKey("descr") );
504 assertTrue( ( (Map) results.get( 0 ) ).containsKey("bw") );
505
506 iter = session.createQuery( "select new map(an.description, an.bodyWeight) from Animal an" ).iterate();
507 assertTrue( "Incorrect result size", iter.hasNext() );
508 obj = iter.next();
509 assertTrue( "Incorrect return type", obj instanceof Map );
510 assertEquals( "Incorrect return type", ( (Map) obj ).size(), 2 );
511
512 session.close();
513
514 destroyTestBaseData();
515 }
516
517 public static Test suite() {
518 TestSuite suite = new TestSuite( ASTParserLoadingTest.class );
519 return suite;
520 }
521
522 }