|
1 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.HashMap ;
11 import java.util.Iterator ;
12 import java.util.List ;
13 import java.util.Map ;
14
15 import junit.framework.Test;
16 import junit.framework.TestSuite;
17
18 import org.hibernate.Hibernate;
19 import org.hibernate.Query;
20 import org.hibernate.ScrollableResults;
21 import org.hibernate.Session;
22 import org.hibernate.Transaction;
23 import org.hibernate.cfg.Environment;
24 import org.hibernate.dialect.DB2Dialect;
25 import org.hibernate.dialect.HSQLDialect;
26 import org.hibernate.dialect.MySQLDialect;
27 import org.hibernate.dialect.Oracle9Dialect;
28 import org.hibernate.test.TestCase;
29 import org.hibernate.test.cid.Customer;
30 import org.hibernate.test.cid.LineItem;
31 import org.hibernate.test.cid.Order;
32 import org.hibernate.test.cid.Product;
33 import org.hibernate.type.ManyToOneType;
34 import org.hibernate.type.Type;
35 import org.hibernate.util.StringHelper;
36
37
43 public class ASTParserLoadingTest extends TestCase {
44
45 public ASTParserLoadingTest(String name) {
46 super( name );
47 }
48
49 private List createdAnimalIds = new ArrayList ();
50
51 protected String [] getMappings() {
52 System.setProperty( Environment.QUERY_TRANSLATOR, "org.hibernate.hql.ast.ASTQueryTranslatorFactory" );
54 return new String []{
55 "hql/Animal.hbm.xml",
56 "batchfetch/ProductLine.hbm.xml",
57 "cid/Customer.hbm.xml",
58 "cid/Order.hbm.xml",
59 "cid/LineItem.hbm.xml",
60 "cid/Product.hbm.xml"
61 };
62 }
63
64 public void testSelectClauseSubselect() {
65 Session s = openSession();
66 Transaction t = s.beginTransaction();
67 Zoo zoo = new Zoo();
68 zoo.setName("Melbourne Zoo");
69 zoo.setMammals( new HashMap () );
70 zoo.setAnimals( new HashMap () );
71 Mammal plat = new Mammal();
72 plat.setBodyWeight( 11f );
73 plat.setDescription( "Platypus" );
74 plat.setZoo(zoo);
75 plat.setSerialNumber("plat123");
76 zoo.getMammals().put("Platypus", plat);
77 zoo.getAnimals().put("plat123", plat);
78 s.persist( plat );
79 s.persist(zoo);
80
81 s.createQuery("select (select max(z.id) from a.zoo z) from Animal a").list();
82 s.createQuery("select (select max(z.id) from a.zoo z where z.name=:name) from Animal a")
83 .setParameter("name", "Melbourne Zoo").list();
84
85 s.delete(plat);
86 s.delete(zoo);
87 t.commit();
88 s.close();
89 }
90
91 public void testInitProxy() {
92 Session s = openSession();
93 Transaction t = s.beginTransaction();
94 Mammal plat = new Mammal();
95 plat.setBodyWeight( 11f );
96 plat.setDescription( "Platypus" );
97 s.persist( plat );
98 s.flush();
99 s.clear();
100 plat = (Mammal) s.load(Mammal.class, plat.getId() );
101 assertFalse( Hibernate.isInitialized(plat) );
102 Object plat2 = s.createQuery("from Animal a").uniqueResult();
103 assertSame(plat, plat2);
104 assertTrue( Hibernate.isInitialized(plat) );
105 s.delete(plat);
106 t.commit();
107 s.close();
108 }
109
110 public void testSelectClauseImplicitJoin() {
111 Session s = openSession();
112 Transaction t = s.beginTransaction();
113 Zoo zoo = new Zoo();
114 zoo.setName("The Zoo");
115 zoo.setMammals( new HashMap () );
116 zoo.setAnimals( new HashMap () );
117 Mammal plat = new Mammal();
118 plat.setBodyWeight( 11f );
119 plat.setDescription( "Platypus" );
120 plat.setZoo(zoo);
121 plat.setSerialNumber("plat123");
122 zoo.getMammals().put("Platypus", plat);
123 zoo.getAnimals().put("plat123", plat);
124 s.persist( plat );
125 s.persist(zoo);
126 s.flush();
127 s.clear();
128 Query q = s.createQuery("select distinct a.zoo from Animal a where a.zoo is not null");
129 Type type = q.getReturnTypes()[0];
130 assertTrue( type instanceof ManyToOneType );
131 assertEquals( ( (ManyToOneType) type ).getAssociatedEntityName(), "org.hibernate.test.hql.Zoo" );
132 zoo = (Zoo) q.list().get(0);
133 assertEquals( zoo.getMammals().size(), 1 );
134 assertEquals( zoo.getAnimals().size(), 1 );
135 s.clear();
136 s.delete(plat);
137 s.delete(zoo);
138 t.commit();
139 s.close();
140 }
141
142 public void testSelectClauseImplicitJoinWithIterate() {
143 Session s = openSession();
144 Transaction t = s.beginTransaction();
145 Zoo zoo = new Zoo();
146 zoo.setName("The Zoo");
147 zoo.setMammals( new HashMap () );
148 zoo.setAnimals( new HashMap () );
149 Mammal plat = new Mammal();
150 plat.setBodyWeight( 11f );
151 plat.setDescription( "Platypus" );
152 plat.setZoo(zoo);
153 plat.setSerialNumber("plat123");
154 zoo.getMammals().put("Platypus", plat);
155 zoo.getAnimals().put("plat123", plat);
156 s.persist( plat );
157 s.persist(zoo);
158 s.flush();
159 s.clear();
160 Query q = s.createQuery("select distinct a.zoo from Animal a where a.zoo is not null");
161 Type type = q.getReturnTypes()[0];
162 assertTrue( type instanceof ManyToOneType );
163 assertEquals( ( (ManyToOneType) type ).getAssociatedEntityName(), "org.hibernate.test.hql.Zoo" );
164 zoo = (Zoo) q
165 .iterate().next();
166 assertEquals( zoo.getMammals().size(), 1 );
167 assertEquals( zoo.getAnimals().size(), 1 );
168 s.clear();
169 s.delete(plat);
170 s.delete(zoo);
171 t.commit();
172 s.close();
173 }
174
175 public void testComponentOrderBy() {
176 Session s = openSession();
177 Transaction t = s.beginTransaction();
178
179 Long id1 = ( Long ) s.save( genSimpleHuman( "John", "Jacob" ) );
180 Long id2 = ( Long ) s.save( genSimpleHuman( "Jingleheimer", "Schmidt" ) );
181
182 s.flush();
183
184 List results = s.createQuery( "from Human as h order by h.name" ).list();
186 assertEquals( "Incorrect return count", 2, results.size() );
187
188 Human h1 = ( Human ) results.get( 0 );
189 Human h2 = ( Human ) results.get( 1 );
190
191 assertEquals( "Incorrect ordering", id2, h1.getId() );
192 assertEquals( "Incorrect ordering", id1, h2.getId() );
193
194 s.delete( h1 );
195 s.delete( h2 );
196
197 t.commit();
198 s.close();
199 }
200
201 private Human genSimpleHuman(String fName, String lName) {
202 Human h = new Human();
203 h.setName( new Name() );
204 h.getName().setFirst( fName );
205 h.getName().setLast( lName );
206
207 return h;
208 }
209
210 public void testCastInSelect() {
211 Session s = openSession();
212 Transaction t = s.beginTransaction();
213 Animal a = new Animal();
214 a.setBodyWeight(12.4f);
215 a.setDescription("an animal");
216 s.persist(a);
217 Integer bw = (Integer ) s.createQuery("select cast(bodyWeight as integer) from Animal").uniqueResult();
218 bw = (Integer ) s.createQuery("select cast(a.bodyWeight as integer) from Animal a").uniqueResult();
219 bw.toString();
220 s.delete(a);
221 t.commit();
222 s.close();
223 }
224
225 public void testAliases() {
226 Session s = openSession();
227 Transaction t = s.beginTransaction();
228 Animal a = new Animal();
229 a.setBodyWeight(12.4f);
230 a.setDescription("an animal");
231 s.persist(a);
232 String [] aliases1 = s.createQuery("select a.bodyWeight as abw, a.description from Animal a").getReturnAliases();
233 assertEquals(aliases1[0], "abw");
234 assertEquals(aliases1[1], "1");
235 String [] aliases2 = s.createQuery("select count(*), avg(a.bodyWeight) as avg from Animal a").getReturnAliases();
236 assertEquals(aliases2[0], "0");
237 assertEquals(aliases2[1], "avg");
238 s.delete(a);
239 t.commit();
240 s.close();
241 }
242
243 public void testIndexParams() {
244 Session s = openSession();
245 Transaction t = s.beginTransaction();
246 s.createQuery("from Zoo zoo where zoo.mammals[:name] = :id")
247 .setParameter("name", "Walrus")
248 .setParameter("id", new Long (123))
249 .list();
250 s.createQuery("from Zoo zoo where zoo.mammals[:name].bodyWeight > :w")
251 .setParameter("name", "Walrus")
252 .setParameter("w", new Float (123.32))
253 .list();
254 s.createQuery("from Zoo zoo where zoo.animals[:sn].mother.bodyWeight < :mw")
255 .setParameter("sn", "ant-123")
256 .setParameter("mw", new Float (23.32))
257 .list();
258
264
269 t.commit();
270 s.close();
271 }
272
273 public void testAggregation() {
274 Session s = openSession();
275 Transaction t = s.beginTransaction();
276 Human h = new Human();
277 h.setBodyWeight( (float) 74.0 );
278 h.setHeight(120.5);
279 h.setDescription("Me");
280 h.setName( new Name("Gavin", 'A', "King") );
281 h.setNickName("Oney");
282 s.persist(h);
283 Float sum = (Float ) s.createQuery("select sum(h.bodyWeight) from Human h").uniqueResult();
284 Double avg = (Double ) s.createQuery("select avg(h.height) from Human h").uniqueResult();
285 assertEquals(sum.floatValue(), 74.0, 0.01);
286 assertEquals(avg.doubleValue(), 120.5, 0.01);
287 s.delete(h);
288 t.commit();
289 s.close();
290 }
291
292 public void testSelectClauseCase() {
293 Session s = openSession();
294 Transaction t = s.beginTransaction();
295 Human h = new Human();
296 h.setBodyWeight( (float) 74.0 );
297 h.setHeight(120.5);
298 h.setDescription("Me");
299 h.setName( new Name("Gavin", 'A', "King") );
300 h.setNickName("Oney");
301 s.persist(h);
302 String name = (String ) s.createQuery("select case nickName when 'Oney' then 'gavin' when 'Turin' then 'christian' else nickName end from Human").uniqueResult();
303 assertEquals(name, "gavin");
304 String result = (String ) s.createQuery("select case when bodyWeight > 100 then 'fat' else 'skinny' end from Human").uniqueResult();
305 assertEquals(result, "skinny");
306 s.delete(h);
307 t.commit();
308 s.close();
309 }
310
311 public void testImplicitPolymorphism() {
312 Session s = openSession();
313 Transaction t = s.beginTransaction();
314
315 Product product = new Product();
316 product.setDescription( "My Product" );
317 product.setNumberAvailable( 10 );
318 product.setPrice( new BigDecimal ( 123 ) );
319 product.setProductId( "4321" );
320 s.save( product );
321
322 List list = s.createQuery("from java.lang.Comparable").list();
323 assertEquals( list.size(), 0 );
324
325 list = s.createQuery("from java.lang.Object").list();
326 assertEquals( list.size(), 1 );
327
328 s.delete(product);
329
330 list = s.createQuery("from java.lang.Object").list();
331 assertEquals( list.size(), 0 );
332
333 t.commit();
334 s.close();
335 }
336
337 public void testCoalesce() {
338 Session session = openSession();
339 Transaction txn = session.beginTransaction();
340 session.createQuery("from Human h where coalesce(h.nickName, h.name.first, h.name.last) = 'max'").list();
341 session.createQuery("select nullif(nickName, '1e1') from Human").list();
342 txn.commit();
343 session.close();
344 }
345
346 public void testStr() {
347 Session session = openSession();
348 Transaction txn = session.beginTransaction();
349 Animal an = new Animal();
350 an.setBodyWeight(123.45f);
351 session.persist(an);
352 String str = (String ) session.createQuery("select str(an.bodyWeight) from Animal an where str(an.bodyWeight) like '123%' or str(an.bodyWeight) like '1.23%'").uniqueResult();
353 if ( getDialect() instanceof DB2Dialect ) {
354 assertTrue( str.startsWith("1.234") );
355 }
356 else {
357 assertTrue( str.startsWith("123.4") );
358 }
359 String dateStr1 = (String ) session.createQuery("select str(current_date) from Animal").uniqueResult();
360 String dateStr2 = (String ) session.createQuery("select str(year(current_date))||'-'||str(month(current_date))||'-'||str(day(current_date)) from Animal").uniqueResult();
361 System.out.println(dateStr1 + '=' + dateStr2);
362 if ( ! ( getDialect() instanceof Oracle9Dialect ) ) { String [] dp1 = StringHelper.split("-", dateStr1);
364 String [] dp2 = StringHelper.split("-", dateStr2);
365 for (int i=0; i<3; i++) {
366 if ( dp1[i].startsWith("0") ) dp1[i] = dp1[i].substring(1);
367 assertEquals( dp1[i], dp2[i] );
368 }
369 }
370 session.delete(an);
371 txn.commit();
372 session.close();
373 }
374
375 public void testCast() {
376 if ( (getDialect() instanceof MySQLDialect) || (getDialect() instanceof DB2Dialect) ) return;
377 Session session = openSession();
378 Transaction txn = session.beginTransaction();
379 session.createQuery("from Human h where h.nickName like 'G%'").list();
380 session.createQuery("from Animal a where cast(a.bodyWeight as string) like '1.%'").list();
381 session.createQuery("from Animal a where cast(a.bodyWeight as integer) = 1").list();
382 txn.commit();
383 session.close();
384 }
385
386 public void testExtract() {
387 Session session = openSession();
388 Transaction txn = session.beginTransaction();
389 session.createQuery("select second(current_timestamp()), minute(current_timestamp()), hour(current_timestamp()) from Mammal m").list();
390 session.createQuery("select day(m.birthdate), month(m.birthdate), year(m.birthdate) from Mammal m").list();
391 if ( !(getDialect() instanceof DB2Dialect) ) { session.createQuery("select extract(second from current_timestamp()), extract(minute from current_timestamp()), extract(hour from current_timestamp()) from Mammal m").list();
393 session.createQuery("select extract(day from m.birthdate), extract(month from m.birthdate), extract(year from m.birthdate) from Mammal m").list();
394 }
395 txn.commit();
396 session.close();
397 }
398
399 public void testOneToManyFilter() throws Throwable {
400 Session session = openSession();
401 Transaction txn = session.beginTransaction();
402
403 Product product = new Product();
404 product.setDescription( "My Product" );
405 product.setNumberAvailable( 10 );
406 product.setPrice( new BigDecimal ( 123 ) );
407 product.setProductId( "4321" );
408 session.save( product );
409
410 Customer customer = new Customer();
411 customer.setCustomerId( "123456789" );
412 customer.setName( "My customer" );
413 customer.setAddress( "somewhere" );
414 session.save( customer );
415
416 Order order = customer.generateNewOrder( new BigDecimal ( 1234 ) );
417 session.save( order );
418
419 LineItem li = order.generateLineItem( product, 5 );
420 session.save( li );
421
422 session.flush();
423
424 assertEquals( session.createFilter( customer.getOrders(), "" ).list().size(), 1 );
425
426 assertEquals( session.createFilter( order.getLineItems(), "" ).list().size(), 1 );
427 assertEquals( session.createFilter( order.getLineItems(), "where this.quantity > :quantity" ).setInteger( "quantity", 5 ).list().size(), 0 );
428
429 session.delete(li);
430 session.delete(order);
431 session.delete(product);
432 session.delete(customer);
433 txn.commit();
434 session.close();
435 }
436
437 public void testManyToManyFilter() throws Throwable {
438 Session session = openSession();
439 Transaction txn = session.beginTransaction();
440
441 Human human = new Human();
442 human.setName( new Name() );
443 human.getName().setFirst( "Steve" );
444 human.getName().setInitial( 'L' );
445 human.getName().setLast( "Ebersole" );
446 session.save( human );
447
448 Human friend = new Human();
449 friend.setName( new Name() );
450 friend.getName().setFirst( "John" );
451 friend.getName().setInitial( 'Q' );
452 friend.getName().setLast( "Doe" );
453 friend.setBodyWeight( 11.0f );
454 session.save( friend );
455
456 human.setFriends( new ArrayList () );
457 friend.setFriends( new ArrayList () );
458 human.getFriends().add( friend );
459 friend.getFriends().add( human );
460
461 session.flush();
462
463 assertEquals( session.createFilter( human.getFriends(), "" ).list().size(), 1 );
464 assertEquals( session.createFilter( human.getFriends(), "where this.bodyWeight > ?" ).setFloat( 0, 10f ).list().size(), 1 );
465 assertEquals( session.createFilter( human.getFriends(), "where this.bodyWeight < ?" ).setFloat( 0, 10f ).list().size(), 0 );
466
467 session.delete(human);
468 session.delete(friend);
469
470 txn.commit();
471 session.close();
472 }
473
474 public void testSelectExpressions() {
475 createTestBaseData();
476 Session session = openSession();
477 Transaction txn = session.beginTransaction();
478 Human h = new Human();
479 h.setName( new Name("Gavin", 'A', "King") );
480 h.setNickName("Oney");
481 h.setBodyWeight(1.0f);
482 session.persist(h);
483 List results = session.createQuery("select 'found', lower(h.name.first) from Human h where lower(h.name.first) = 'gavin'").list();
484 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();
485 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();
486 results = session.createQuery("select a.bodyWeight + m.bodyWeight from Animal a join a.mother m").list();
487 results = session.createQuery("select 2.0 * (a.bodyWeight + m.bodyWeight) from Animal a join a.mother m").list();
488 results = session.createQuery("select sum(a.bodyWeight + m.bodyWeight) from Animal a join a.mother m").list();
489 results = session.createQuery("select sum(a.mother.bodyWeight * 2.0) from Animal a").list();
490 results = session.createQuery("select concat(h.name.first, ' ', h.name.initial, ' ', h.name.last) from Human h").list();
491 results = session.createQuery("select h.name.first||' '||h.name.initial||' '||h.name.last from Human h").list();
492 results = session.createQuery("select nickName from Human").list();
493 results = session.createQuery("select lower(nickName) from Human").list();
494 results = session.createQuery("select abs(bodyWeight*-1) from Human").list();
495 results = session.createQuery("select upper(h.name.first||' ('||h.nickName||')') from Human h").list();
496 results = session.createQuery("select abs(a.bodyWeight-:param) from Animal a").setParameter("param", new Float (2.0)).list();
497 results = session.createQuery("select abs(:param - a.bodyWeight) from Animal a").setParameter("param", new Float (2.0)).list();
498 results = session.createQuery("select lower(upper('foo')) from Animal").list();
499 results = session.createQuery("select lower(upper('foo') || upper('bar')) from Animal").list();
500 results = session.createQuery("select sum(abs(bodyWeight - 1.0) * abs(length('ffobar')-3)) from Animal").list();
501 session.delete(h);
502 txn.commit();
503 session.close();
504 destroyTestBaseData();
505 }
506
507 private void createTestBaseData() {
508 Session session = openSession();
509 Transaction txn = session.beginTransaction();
510
511 Mammal m1 = new Mammal();
512 m1.setBodyWeight( 11f );
513 m1.setDescription( "Mammal #1" );
514
515 session.save( m1 );
516
517 Mammal m2 = new Mammal();
518 m2.setBodyWeight( 9f );
519 m2.setDescription( "Mammal #2" );
520 m2.setMother( m1 );
521
522 session.save( m2 );
523
524 txn.commit();
525 session.close();
526
527 createdAnimalIds.add( m1.getId() );
528 createdAnimalIds.add( m2.getId() );
529 }
530
531 private void destroyTestBaseData() {
532 Session session = openSession();
533 Transaction txn = session.beginTransaction();
534
535 for ( int i = 0; i < createdAnimalIds.size(); i++ ) {
536 Animal animal = ( Animal ) session.load( Animal.class, ( Long ) createdAnimalIds.get( i ) );
537 session.delete( animal );
538 }
539
540 txn.commit();
541 session.close();
542 }
543
544 public void testImplicitJoin() throws Exception {
545 Session session = openSession();
546 Transaction t = session.beginTransaction();
547 Animal a = new Animal();
548 a.setBodyWeight(0.5f);
549 a.setBodyWeight(1.5f);
550 Animal b = new Animal();
551 Animal mother = new Animal();
552 mother.setBodyWeight(10.0f);
553 mother.addOffspring(a);
554 mother.addOffspring(b);
555 session.persist(a);
556 session.persist(b);
557 session.persist(mother);
558 List list = session.createQuery("from Animal a where a.mother.bodyWeight < 2.0 or a.mother.bodyWeight > 9.0").list();
559 assertEquals( list.size(), 2 );
560 list = session.createQuery("from Animal a where a.mother.bodyWeight > 2.0 and a.mother.bodyWeight > 9.0").list();
561 assertEquals( list.size(), 2 );
562 session.delete(b);
563 session.delete(a);
564 session.delete(mother);
565 t.commit();
566 session.close();
567 }
568
569 public void testFromOnly() throws Exception {
570
571 createTestBaseData();
572
573 Session session = openSession();
574
575 List results = session.createQuery( "from Animal" ).list();
576 assertEquals( "Incorrect result size", 2, results.size() );
577 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
578
579 session.close();
580
581 destroyTestBaseData();
582 }
583
584 public void testSimpleSelect() throws Exception {
585
586 createTestBaseData();
587
588 Session session = openSession();
589
590 List results = session.createQuery( "select a from Animal as a" ).list();
591 assertEquals( "Incorrect result size", 2, results.size() );
592 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
593
594 session.close();
595
596 destroyTestBaseData();
597 }
598
599 public void testEntityPropertySelect() throws Exception {
600
601 createTestBaseData();
602
603 Session session = openSession();
604
605 List results = session.createQuery( "select a.mother from Animal as a" ).list();
606 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
608
609 session.close();
610
611 destroyTestBaseData();
612 }
613
614 public void testWhere() throws Exception {
615
616 createTestBaseData();
617
618 Session session = openSession();
619 List results = null;
620
621 results = session.createQuery( "from Animal an where an.bodyWeight > 10" ).list();
622 assertEquals( "Incorrect result size", 1, results.size() );
623
624 results = session.createQuery( "from Animal an where not an.bodyWeight > 10" ).list();
625 assertEquals( "Incorrect result size", 1, results.size() );
626
627 results = session.createQuery( "from Animal an where an.bodyWeight between 0 and 10" ).list();
628 assertEquals( "Incorrect result size", 1, results.size() );
629
630 results = session.createQuery( "from Animal an where an.bodyWeight not between 0 and 10" ).list();
631 assertEquals( "Incorrect result size", 1, results.size() );
632
633 results = session.createQuery( "from Animal an where sqrt(an.bodyWeight)/2 > 10" ).list();
634 assertEquals( "Incorrect result size", 0, results.size() );
635
636 results = session.createQuery( "from Animal an where (an.bodyWeight > 10 and an.bodyWeight < 100) or an.bodyWeight is null" ).list();
637 assertEquals( "Incorrect result size", 1, results.size() );
638
639 session.close();
640
641 destroyTestBaseData();
642 }
643
644 public void testEntityFetching() throws Exception {
645
646 createTestBaseData();
647
648 Session session = openSession();
649
650 List results = session.createQuery( "from Animal an join fetch an.mother" ).list();
651 assertEquals( "Incorrect result size", 1, results.size() );
652 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
653 Animal mother = ( ( Animal ) results.get( 0 ) ).getMother();
654 assertTrue( "fetch uninitialized", mother != null && Hibernate.isInitialized( mother ) );
655
656 results = session.createQuery( "select an from Animal an join fetch an.mother" ).list();
657 assertEquals( "Incorrect result size", 1, results.size() );
658 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
659 mother = ( ( Animal ) results.get( 0 ) ).getMother();
660 assertTrue( "fetch uninitialized", mother != null && Hibernate.isInitialized( mother ) );
661
662 session.close();
663
664 destroyTestBaseData();
665 }
666
667 public void testCollectionFetching() throws Exception {
668
669 createTestBaseData();
670
671 Session session = openSession();
672 List results = session.createQuery( "from Animal an join fetch an.offspring" ).list();
673 assertEquals( "Incorrect result size", 1, results.size() );
674 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
675 Collection os = ( ( Animal ) results.get( 0 ) ).getOffspring();
676 assertTrue( "fetch uninitialized", os != null && Hibernate.isInitialized( os ) && os.size() == 1 );
677
678 results = session.createQuery( "select an from Animal an join fetch an.offspring" ).list();
679 assertEquals( "Incorrect result size", 1, results.size() );
680 assertTrue( "Incorrect result return type", results.get( 0 ) instanceof Animal );
681 os = ( ( Animal ) results.get( 0 ) ).getOffspring();
682 assertTrue( "fetch uninitialized", os != null && Hibernate.isInitialized( os ) && os.size() == 1 );
683
684 session.close();
685
686 destroyTestBaseData();
687 }
688
689 public void testProjectionQueries() throws Exception {
690
691 createTestBaseData();
692
693 Session session = openSession();
694
695 List results = session.createQuery( "select an.mother.id, max(an.bodyWeight) from Animal an group by an.mother.id" ).list();
696 assertEquals( "Incorrect result size", 2, results.size() );
698 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Object [] );
699 assertEquals( "Incorrect return dimensions", 2, ( ( Object [] ) results.get( 0 ) ).length );
700
701 session.close();
702
703 destroyTestBaseData();
704
705 }
706
707 public void testStandardFunctions() throws Exception {
708 Session session = openSession();
709 Transaction t = session.beginTransaction();
710 Product p = new Product();
711 p.setDescription("a product");
712 p.setPrice( new BigDecimal (1.0) );
713 p.setProductId("abc123");
714 session.persist(p);
715 Object [] result = (Object []) session
716 .createQuery("select current_time(), current_date(), current_timestamp() from Product")
717 .uniqueResult();
718 assertTrue( result[0] instanceof Time );
719 assertTrue( result[1] instanceof Date );
720 assertTrue( result[2] instanceof Timestamp );
721 assertNotNull( result[0] );
722 assertNotNull( result[1] );
723 assertNotNull( result[2] );
724 session.delete(p);
725 t.commit();
726 session.close();
727
728 }
729
730 public void testDynamicInstantiationQueries() throws Exception {
731
732 createTestBaseData();
733
734 Session session = openSession();
735
736 List results = session.createQuery( "select new Animal(an.description, an.bodyWeight) from Animal an" ).list();
737 assertEquals( "Incorrect result size", 2, results.size() );
738 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Animal );
739
740 Iterator iter = session.createQuery( "select new Animal(an.description, an.bodyWeight) from Animal an" ).iterate();
741 assertTrue( "Incorrect result size", iter.hasNext() );
742 assertTrue( "Incorrect return type", iter.next() instanceof Animal );
743
744 results = session.createQuery( "select new list(an.description, an.bodyWeight) from Animal an" ).list();
745 assertEquals( "Incorrect result size", 2, results.size() );
746 assertTrue( "Incorrect return type", results.get( 0 ) instanceof List );
747 assertEquals( "Incorrect return type", ( (List ) results.get( 0 ) ).size(), 2 );
748
749 iter = session.createQuery( "select new list(an.description, an.bodyWeight) from Animal an" ).iterate();
750 assertTrue( "Incorrect result size", iter.hasNext() );
751 Object obj = iter.next();
752 assertTrue( "Incorrect return type", obj instanceof List );
753 assertEquals( "Incorrect return type", ( (List ) obj ).size(), 2 );
754
755 results = session.createQuery( "select new map(an.description, an.bodyWeight) from Animal an" ).list();
756 assertEquals( "Incorrect result size", 2, results.size() );
757 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Map );
758 assertEquals( "Incorrect return type", ( (Map ) results.get( 0 ) ).size(), 2 );
759 assertTrue( ( (Map ) results.get( 0 ) ).containsKey("0") );
760 assertTrue( ( (Map ) results.get( 0 ) ).containsKey("1") );
761
762 results = session.createQuery( "select new map(an.description as descr, an.bodyWeight as bw) from Animal an" ).list();
763 assertEquals( "Incorrect result size", 2, results.size() );
764 assertTrue( "Incorrect return type", results.get( 0 ) instanceof Map );
765 assertEquals( "Incorrect return type", ( (Map ) results.get( 0 ) ).size(), 2 );
766 assertTrue( ( (Map ) results.get( 0 ) ).containsKey("descr") );
767 assertTrue( ( (Map ) results.get( 0 ) ).containsKey("bw") );
768
769 iter = session.createQuery( "select new map(an.description, an.bodyWeight) from Animal an" ).iterate();
770 assertTrue( "Incorrect result size", iter.hasNext() );
771 obj = iter.next();
772 assertTrue( "Incorrect return type", obj instanceof Map );
773 assertEquals( "Incorrect return type", ( (Map ) obj ).size(), 2 );
774
775 ScrollableResults sr = session.createQuery( "select new map(an.description, an.bodyWeight) from Animal an" ).scroll();
776 assertTrue( "Incorrect result size", sr.next() );
777 obj = sr.get(0);
778 assertTrue( "Incorrect return type", obj instanceof Map );
779 assertEquals( "Incorrect return type", ( (Map ) obj ).size(), 2 );
780 sr.close();
781
782 sr = session.createQuery( "select new Animal(an.description, an.bodyWeight) from Animal an" ).scroll();
783 assertTrue( "Incorrect result size", sr.next() );
784 assertTrue( "Incorrect return type", sr.get(0) instanceof Animal );
785 sr.close();
786
787 session.close();
788
789 destroyTestBaseData();
790 }
791
792 public void testEJBQLFunctions() throws Exception {
793 Session session = openSession();
794
795 String hql = "from Animal a where a.description = concat('1', concat('2','3'), '4'||'5')||'0'";
796 session.createQuery(hql).list();
797
798 hql = "from Animal a where substring(a.description, 1, 3) = 'cat'";
799 session.createQuery(hql).list();
800
801 hql = "select substring(a.description, 1, 3) from Animal a";
802 session.createQuery(hql).list();
803
804 hql = "from Animal a where lower(a.description) = 'cat'";
805 session.createQuery(hql).list();
806
807 hql = "select lower(a.description) from Animal a";
808 session.createQuery(hql).list();
809
810 hql = "from Animal a where upper(a.description) = 'CAT'";
811 session.createQuery(hql).list();
812
813 hql = "select upper(a.description) from Animal a";
814 session.createQuery(hql).list();
815
816 hql = "from Animal a where length(a.description) = 5";
817 session.createQuery(hql).list();
818
819 hql = "select length(a.description) from Animal a";
820 session.createQuery(hql).list();
821
822 hql = "from Animal a where locate('abc', a.description, 2) = 2";
824 session.createQuery(hql).list();
825
826 hql = "from Animal a where locate('abc', a.description) = 2";
827 session.createQuery(hql).list();
828
829 hql = "select locate('cat', a.description, 2) from Animal a";
830 session.createQuery(hql).list();
831
832 if ( !( getDialect() instanceof DB2Dialect ) ) {
833 hql = "from Animal a where trim(trailing '_' from a.description) = 'cat'";
834 session.createQuery(hql).list();
835
836 hql = "select trim(trailing '_' from a.description) from Animal a";
837 session.createQuery(hql).list();
838
839 hql = "from Animal a where trim(leading '_' from a.description) = 'cat'";
840 session.createQuery(hql).list();
841
842 hql = "from Animal a where trim(both from a.description) = 'cat'";
843 session.createQuery(hql).list();
844 }
845
846 if ( !(getDialect() instanceof HSQLDialect) ) { hql = "from Animal a where trim(a.description) = 'cat'";
848 session.createQuery(hql).list();
849 }
850
851 hql = "from Animal a where abs(a.bodyWeight) = sqrt(a.bodyWeight)";
852 session.createQuery(hql).list();
853
854 hql = "from Animal a where mod(16, 4) = 4";
855 session.createQuery(hql).list();
856
857 hql = "from Animal a where bit_length(a.bodyWeight) = 24";
858 session.createQuery(hql).list();
859
860 hql = "select bit_length(a.bodyWeight) from Animal a";
861 session.createQuery(hql).list();
862
863
865
866
871 hql = "from Animal a where a.description like '%a%'";
872 session.createQuery(hql).list();
873
874 hql = "from Animal a where a.description not like '%a%'";
875 session.createQuery(hql).list();
876
877 hql = "from Animal a where a.description like 'x%ax%' escape 'x'";
878 session.createQuery(hql).list();
879
880 session.close();
881 }
882
883 public static Test suite() {
884 TestSuite suite = new TestSuite( ASTParserLoadingTest.class );
885 return suite;
886 }
887
888 }
889 |
Popular Tags |
Java API By Example, From Geeks To Geeks.