|
1 package org.hibernate.test.criteria;
3
4 import java.util.List  ;
5 import java.util.Map ;
6
7 import junit.framework.Test;
8 import junit.framework.TestSuite;
9
10 import org.hibernate.Criteria;
11 import org.hibernate.FetchMode;
12 import org.hibernate.Hibernate;
13 import org.hibernate.ScrollableResults;
14 import org.hibernate.Session;
15 import org.hibernate.Transaction;
16 import org.hibernate.criterion.DetachedCriteria;
17 import org.hibernate.criterion.Expression;
18 import org.hibernate.criterion.MatchMode;
19 import org.hibernate.criterion.Order;
20 import org.hibernate.criterion.Projection;
21 import org.hibernate.criterion.Projections;
22 import org.hibernate.criterion.Property;
23 import org.hibernate.criterion.Restrictions;
24 import org.hibernate.criterion.Subqueries;
25 import org.hibernate.dialect.HSQLDialect;
26 import org.hibernate.test.TestCase;
27 import org.hibernate.test.hql.Reptile;
28 import org.hibernate.transform.AliasToBeanResultTransformer;
29 import org.hibernate.type.Type;
30 import org.hibernate.util.SerializationHelper;
31
32
35 public class CriteriaQueryTest extends TestCase {
36
37 public CriteriaQueryTest(String str) {
38 super(str);
39 }
40
41 public void testScrollCriteria() {
42 Session session = openSession();
43 Transaction t = session.beginTransaction();
44
45 Course course = new Course();
46 course.setCourseCode("HIB");
47 course.setDescription("Hibernate Training");
48 session.persist(course);
49 session.flush();
50 session.clear();
51 ScrollableResults sr = session.createCriteria(Course.class).scroll();
52 assertTrue( sr.next() );
53 course = (Course) sr.get(0);
54 assertNotNull(course);
55 sr.close();
56 session.delete(course);
57
58 t.commit();
59 session.close();
60
61 }
62
63 public void testSubselect() {
64
65 if ( getDialect() instanceof HSQLDialect ) return;
66
67 Session session = openSession();
68 Transaction t = session.beginTransaction();
69
70 Course course = new Course();
71 course.setCourseCode("HIB");
72 course.setDescription("Hibernate Training");
73 session.persist(course);
74
75 Student gavin = new Student();
76 gavin.setName("Gavin King");
77 gavin.setStudentNumber(232);
78 session.persist(gavin);
79
80 Enrolment enrolment2 = new Enrolment();
81 enrolment2.setCourse(course);
82 enrolment2.setCourseCode(course.getCourseCode());
83 enrolment2.setSemester((short) 3);
84 enrolment2.setYear((short) 1998);
85 enrolment2.setStudent(gavin);
86 enrolment2.setStudentNumber(gavin.getStudentNumber());
87 gavin.getEnrolments().add(enrolment2);
88 session.persist(enrolment2);
89
90 DetachedCriteria dc = DetachedCriteria.forClass(Student.class)
91 .add( Property.forName("studentNumber").eq( new Long (232) ) )
92 .setProjection( Property.forName("name") );
93
94 session.createCriteria(Student.class)
95 .add( Subqueries.propertyEqAll("name", dc) )
96 .list();
97
98 session.createCriteria(Student.class)
99 .add( Subqueries.exists(dc) )
100 .list();
101
102 session.createCriteria(Student.class)
103 .add( Property.forName("name").eqAll(dc) )
104 .list();
105
106 session.createCriteria(Student.class)
107 .add( Subqueries.in("Gavin King", dc) )
108 .list();
109
110 DetachedCriteria dc2 = DetachedCriteria.forClass(Student.class, "st")
111 .add( Property.forName("st.studentNumber").eqProperty("e.studentNumber") )
112 .setProjection( Property.forName("name") );
113
114 session.createCriteria(Enrolment.class, "e")
115 .add( Subqueries.eq("Gavin King", dc2) )
116 .list();
117
118
128
129 session.delete(enrolment2);
130 session.delete(gavin);
131 session.delete(course);
132 t.commit();
133 session.close();
134
135 }
136
137 public void testDetachedCriteria() {
138
139 DetachedCriteria dc = DetachedCriteria.forClass(Student.class)
140 .add( Property.forName("name").eq("Gavin King") )
141 .addOrder( Order.asc("studentNumber") )
142 .setProjection( Property.forName("studentNumber") );
143
144 byte[] bytes = SerializationHelper.serialize(dc);
145
146 dc = (DetachedCriteria) SerializationHelper.deserialize(bytes);
147
148 Session session = openSession();
149 Transaction t = session.beginTransaction();
150
151 Student gavin = new Student();
152 gavin.setName("Gavin King");
153 gavin.setStudentNumber(232);
154 Student bizarroGavin = new Student();
155 bizarroGavin.setName("Gavin King");
156 bizarroGavin.setStudentNumber(666);
157 session.persist(bizarroGavin);
158 session.persist(gavin);
159
160 List result = dc.getExecutableCriteria(session)
161 .setMaxResults(3)
162 .list();
163
164 assertEquals( result.size(), 2 );
165 assertEquals( result.get(0), new Long (232) );
166 assertEquals( result.get(1), new Long (666) );
167
168 session.delete(gavin);
169 session.delete(bizarroGavin);
170 t.commit();
171 session.close();
172 }
173
174 public void testProjectionCache() {
175 Session s = openSession();
176 Transaction t = s.beginTransaction();
177
178 Course course = new Course();
179 course.setCourseCode("HIB");
180 course.setDescription("Hibernate Training");
181 s.save(course);
182
183 Student gavin = new Student();
184 gavin.setName("Gavin King");
185 gavin.setStudentNumber(666);
186 s.save(gavin);
187
188 Student xam = new Student();
189 xam.setName("Max Rydahl Andersen");
190 xam.setStudentNumber(101);
191 s.save(xam);
192
193 Enrolment enrolment1 = new Enrolment();
194 enrolment1.setCourse(course);
195 enrolment1.setCourseCode(course.getCourseCode());
196 enrolment1.setSemester((short) 1);
197 enrolment1.setYear((short) 1999);
198 enrolment1.setStudent(xam);
199 enrolment1.setStudentNumber(xam.getStudentNumber());
200 xam.getEnrolments().add(enrolment1);
201 s.save(enrolment1);
202
203 Enrolment enrolment2 = new Enrolment();
204 enrolment2.setCourse(course);
205 enrolment2.setCourseCode(course.getCourseCode());
206 enrolment2.setSemester((short) 3);
207 enrolment2.setYear((short) 1998);
208 enrolment2.setStudent(gavin);
209 enrolment2.setStudentNumber(gavin.getStudentNumber());
210 gavin.getEnrolments().add(enrolment2);
211 s.save(enrolment2);
212
213 List list = s.createCriteria(Enrolment.class)
214 .createAlias("student", "s")
215 .createAlias("course", "c")
216 .add( Restrictions.isNotEmpty("s.enrolments") )
217 .setProjection( Projections.projectionList()
218 .add( Projections.property("s.name") )
219 .add( Projections.property("c.description") )
220 )
221 .setCacheable(true)
222 .list();
223
224 assertEquals( list.size(), 2 );
225 assertEquals( ( (Object []) list.get(0) ).length, 2 );
226 assertEquals( ( (Object []) list.get(1) ).length, 2 );
227
228 t.commit();
229 s.close();
230
231 s = openSession();
232 t = s.beginTransaction();
233
234 s.createCriteria(Enrolment.class)
235 .createAlias("student", "s")
236 .createAlias("course", "c")
237 .add( Restrictions.isNotEmpty("s.enrolments") )
238 .setProjection( Projections.projectionList()
239 .add( Projections.property("s.name") )
240 .add( Projections.property("c.description") )
241 )
242 .setCacheable(true)
243 .list();
244
245 assertEquals( list.size(), 2 );
246 assertEquals( ( (Object []) list.get(0) ).length, 2 );
247 assertEquals( ( (Object []) list.get(1) ).length, 2 );
248
249 t.commit();
250 s.close();
251
252 s = openSession();
253 t = s.beginTransaction();
254
255 s.createCriteria(Enrolment.class)
256 .createAlias("student", "s")
257 .createAlias("course", "c")
258 .add( Restrictions.isNotEmpty("s.enrolments") )
259 .setProjection( Projections.projectionList()
260 .add( Projections.property("s.name") )
261 .add( Projections.property("c.description") )
262 )
263 .setCacheable(true)
264 .list();
265
266 assertEquals( list.size(), 2 );
267 assertEquals( ( (Object []) list.get(0) ).length, 2 );
268 assertEquals( ( (Object []) list.get(1) ).length, 2 );
269
270 s.delete(enrolment1);
271 s.delete(enrolment2);
272 s.delete(course);
273 s.delete(gavin);
274 s.delete(xam);
275
276 t.commit();
277 s.close();
278 }
279
280 public void testProjections() {
281 Session s = openSession();
282 Transaction t = s.beginTransaction();
283
284 Course course = new Course();
285 course.setCourseCode("HIB");
286 course.setDescription("Hibernate Training");
287 s.save(course);
288
289 Student gavin = new Student();
290 gavin.setName("Gavin King");
291 gavin.setStudentNumber(667);
292 s.save(gavin);
293
294 Student xam = new Student();
295 xam.setName("Max Rydahl Andersen");
296 xam.setStudentNumber(101);
297 s.save(xam);
298
299 Enrolment enrolment = new Enrolment();
300 enrolment.setCourse(course);
301 enrolment.setCourseCode(course.getCourseCode());
302 enrolment.setSemester((short) 1);
303 enrolment.setYear((short) 1999);
304 enrolment.setStudent(xam);
305 enrolment.setStudentNumber(xam.getStudentNumber());
306 xam.getEnrolments().add(enrolment);
307 s.save(enrolment);
308
309 enrolment = new Enrolment();
310 enrolment.setCourse(course);
311 enrolment.setCourseCode(course.getCourseCode());
312 enrolment.setSemester((short) 3);
313 enrolment.setYear((short) 1998);
314 enrolment.setStudent(gavin);
315 enrolment.setStudentNumber(gavin.getStudentNumber());
316 gavin.getEnrolments().add(enrolment);
317 s.save(enrolment);
318
319
321 Integer count = (Integer ) s.createCriteria(Enrolment.class)
322 .setProjection( Projections.count("studentNumber").setDistinct() )
323 .uniqueResult();
324 assertEquals(count, new Integer (2));
325
326 Object object = s.createCriteria(Enrolment.class)
327 .setProjection( Projections.projectionList()
328 .add( Projections.count("studentNumber") )
329 .add( Projections.max("studentNumber") )
330 .add( Projections.min("studentNumber") )
331 .add( Projections.avg("studentNumber") )
332 )
333 .uniqueResult();
334 Object [] result = (Object [])object;
335
336 assertEquals(new Integer (2),result[0]);
337 assertEquals(new Long (667),result[1]);
338 assertEquals(new Long (101),result[2]);
339 assertEquals( 384.0, ( (Double ) result[3] ).doubleValue(), 0.01 );
340
341
342 List resultWithMaps = s.createCriteria(Enrolment.class)
343 .setProjection( Projections.distinct( Projections.projectionList()
344 .add( Projections.property("studentNumber"), "stNumber" )
345 .add( Projections.property("courseCode"), "cCode" ) )
346 )
347 .add( Expression.gt( "studentNumber", new Long (665) ) )
348 .add( Expression.lt( "studentNumber", new Long (668) ) )
349 .addOrder( Order.asc("stNumber") )
350 .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
351 .list();
352
353 assertEquals(1, resultWithMaps.size());
354 Map m1 = (Map ) resultWithMaps.get(0);
355
356 assertEquals(new Long (667), m1.get("stNumber"));
357 assertEquals(course.getCourseCode(), m1.get("cCode"));
358
359 resultWithMaps = s.createCriteria(Enrolment.class)
360 .setProjection( Projections.property("studentNumber").as("stNumber") )
361 .addOrder( Order.desc("stNumber") )
362 .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
363 .list();
364
365 assertEquals(2, resultWithMaps.size());
366 Map m0 = (Map ) resultWithMaps.get(0);
367 m1 = (Map ) resultWithMaps.get(1);
368
369 assertEquals(new Long (101), m1.get("stNumber"));
370 assertEquals(new Long (667), m0.get("stNumber"));
371
372
373 List resultWithAliasedBean = s.createCriteria(Enrolment.class)
374 .createAlias("student", "st")
375 .createAlias("course", "co")
376 .setProjection( Projections.projectionList()
377 .add( Projections.property("st.name"), "studentName" )
378 .add( Projections.property("co.description"), "courseDescription" )
379 )
380 .addOrder( Order.desc("studentName") )
381 .setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
382 .list();
383
384 assertEquals(2, resultWithAliasedBean.size());
385
386 StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);
387 assertNotNull(dto.getDescription());
388 assertNotNull(dto.getName());
389
390 s.createCriteria(Student.class)
391 .add( Restrictions.like("name", "Gavin", MatchMode.START) )
392 .addOrder( Order.asc("name") )
393 .createCriteria("enrolments", "e")
394 .addOrder( Order.desc("year") )
395 .addOrder( Order.desc("semester") )
396 .createCriteria("course","c")
397 .addOrder( Order.asc("description") )
398 .setProjection( Projections.projectionList()
399 .add( Projections.property("this.name") )
400 .add( Projections.property("e.year") )
401 .add( Projections.property("e.semester") )
402 .add( Projections.property("c.courseCode") )
403 .add( Projections.property("c.description") )
404 )
405 .uniqueResult();
406
407 Projection p1 = Projections.projectionList()
408 .add( Projections.count("studentNumber") )
409 .add( Projections.max("studentNumber") )
410 .add( Projections.rowCount() );
411
412 Projection p2 = Projections.projectionList()
413 .add( Projections.min("studentNumber") )
414 .add( Projections.avg("studentNumber") )
415 .add( Projections.sqlProjection(
416 "1 as constOne, count(*) as countStar",
417 new String [] { "constOne", "countStar" },
418 new Type[] { Hibernate.INTEGER, Hibernate.INTEGER }
419 ) );
420
421 Object [] array = (Object []) s.createCriteria(Enrolment.class)
422 .setProjection( Projections.projectionList().add(p1).add(p2) )
423 .uniqueResult();
424
425 assertEquals( array.length, 7 );
426
427 List list = s.createCriteria(Enrolment.class)
428 .createAlias("student", "st")
429 .createAlias("course", "co")
430 .setProjection( Projections.projectionList()
431 .add( Projections.groupProperty("co.courseCode") )
432 .add( Projections.count("st.studentNumber").setDistinct() )
433 .add( Projections.groupProperty("year") )
434 )
435 .list();
436
437 assertEquals( list.size(), 2 );
438
439 Object g = s.createCriteria(Student.class)
440 .add( Restrictions.idEq( new Long (667) ) )
441 .setFetchMode("enrolments", FetchMode.JOIN)
442 .uniqueResult();
444 assertSame(g, gavin);
445
446 s.delete(gavin);
447 s.delete(xam);
448 s.delete(course);
449
450 t.commit();
451 s.close();
452 }
453
454 public void testProjectionsUsingProperty() {
455 Session s = openSession();
456 Transaction t = s.beginTransaction();
457
458 Course course = new Course();
459 course.setCourseCode("HIB");
460 course.setDescription("Hibernate Training");
461 s.save(course);
462
463 Student gavin = new Student();
464 gavin.setName("Gavin King");
465 gavin.setStudentNumber(667);
466 s.save(gavin);
467
468 Student xam = new Student();
469 xam.setName("Max Rydahl Andersen");
470 xam.setStudentNumber(101);
471 s.save(xam);
472
473 Enrolment enrolment = new Enrolment();
474 enrolment.setCourse(course);
475 enrolment.setCourseCode(course.getCourseCode());
476 enrolment.setSemester((short) 1);
477 enrolment.setYear((short) 1999);
478 enrolment.setStudent(xam);
479 enrolment.setStudentNumber(xam.getStudentNumber());
480 xam.getEnrolments().add(enrolment);
481 s.save(enrolment);
482
483 enrolment = new Enrolment();
484 enrolment.setCourse(course);
485 enrolment.setCourseCode(course.getCourseCode());
486 enrolment.setSemester((short) 3);
487 enrolment.setYear((short) 1998);
488 enrolment.setStudent(gavin);
489 enrolment.setStudentNumber(gavin.getStudentNumber());
490 gavin.getEnrolments().add(enrolment);
491 s.save(enrolment);
492
493 s.flush();
494
495 Integer count = (Integer ) s.createCriteria(Enrolment.class)
496 .setProjection( Property.forName("studentNumber").count().setDistinct() )
497 .uniqueResult();
498 assertEquals(count, new Integer (2));
499
500 Object object = s.createCriteria(Enrolment.class)
501 .setProjection( Projections.projectionList()
502 .add( Property.forName("studentNumber").count() )
503 .add( Property.forName("studentNumber").max() )
504 .add( Property.forName("studentNumber").min() )
505 .add( Property.forName("studentNumber").avg() )
506 )
507 .uniqueResult();
508 Object [] result = (Object [])object;
509
510 assertEquals(new Integer (2),result[0]);
511 assertEquals(new Long (667),result[1]);
512 assertEquals(new Long (101),result[2]);
513 assertEquals(384.0, ( (Double ) result[3] ).doubleValue(), 0.01);
514
515
516 s.createCriteria(Enrolment.class)
517 .add( Property.forName("studentNumber").gt( new Long (665) ) )
518 .add( Property.forName("studentNumber").lt( new Long (668) ) )
519 .add( Property.forName("courseCode").like("HIB", MatchMode.START) )
520 .add( Property.forName("year").eq( new Short ( (short) 1999 ) ) )
521 .addOrder( Property.forName("studentNumber").asc() )
522 .uniqueResult();
523
524 List resultWithMaps = s.createCriteria(Enrolment.class)
525 .setProjection( Projections.projectionList()
526 .add( Property.forName("studentNumber").as("stNumber") )
527 .add( Property.forName("courseCode").as("cCode") )
528 )
529 .add( Property.forName("studentNumber").gt( new Long (665) ) )
530 .add( Property.forName("studentNumber").lt( new Long (668) ) )
531 .addOrder( Property.forName("studentNumber").asc() )
532 .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
533 .list();
534
535 assertEquals(1, resultWithMaps.size());
536 Map m1 = (Map ) resultWithMaps.get(0);
537
538 assertEquals(new Long (667), m1.get("stNumber"));
539 assertEquals(course.getCourseCode(), m1.get("cCode"));
540
541 resultWithMaps = s.createCriteria(Enrolment.class)
542 .setProjection( Property.forName("studentNumber").as("stNumber") )
543 .addOrder( Order.desc("stNumber") )
544 .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
545 .list();
546
547 assertEquals(2, resultWithMaps.size());
548 Map m0 = (Map ) resultWithMaps.get(0);
549 m1 = (Map ) resultWithMaps.get(1);
550
551 assertEquals(new Long (101), m1.get("stNumber"));
552 assertEquals(new Long (667), m0.get("stNumber"));
553
554
555 List resultWithAliasedBean = s.createCriteria(Enrolment.class)
556 .createAlias("student", "st")
557 .createAlias("course", "co")
558 .setProjection( Projections.projectionList()
559 .add( Property.forName("st.name").as("studentName") )
560 .add( Property.forName("co.description").as("courseDescription") )
561 )
562 .addOrder( Order.desc("studentName") )
563 .setResultTransformer( new AliasToBeanResultTransformer(StudentDTO.class) )
564 .list();
565
566 assertEquals(2, resultWithAliasedBean.size());
567
568 StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);
569 assertNotNull(dto.getDescription());
570 assertNotNull(dto.getName());
571
572 s.createCriteria(Student.class)
573 .add( Restrictions.like("name", "Gavin", MatchMode.START) )
574 .addOrder( Order.asc("name") )
575 .createCriteria("enrolments", "e")
576 .addOrder( Order.desc("year") )
577 .addOrder( Order.desc("semester") )
578 .createCriteria("course","c")
579 .addOrder( Order.asc("description") )
580 .setProjection( Projections.projectionList()
581 .add( Property.forName("this.name") )
582 .add( Property.forName("e.year") )
583 .add( Property.forName("e.semester") )
584 .add( Property.forName("c.courseCode") )
585 .add( Property.forName("c.description") )
586 )
587 .uniqueResult();
588
589 Projection p1 = Projections.projectionList()
590 .add( Property.forName("studentNumber").count() )
591 .add( Property.forName("studentNumber").max() )
592 .add( Projections.rowCount() );
593
594 Projection p2 = Projections.projectionList()
595 .add( Property.forName("studentNumber").min() )
596 .add( Property.forName("studentNumber").avg() )
597 .add( Projections.sqlProjection(
598 "1 as constOne, count(*) as countStar",
599 new String [] { "constOne", "countStar" },
600 new Type[] { Hibernate.INTEGER, Hibernate.INTEGER }
601 ) );
602
603 Object [] array = (Object []) s.createCriteria(Enrolment.class)
604 .setProjection( Projections.projectionList().add(p1).add(p2) )
605 .uniqueResult();
606
607 assertEquals( array.length, 7 );
608
609 List list = s.createCriteria(Enrolment.class)
610 .createAlias("student", "st")
611 .createAlias("course", "co")
612 .setProjection( Projections.projectionList()
613 .add( Property.forName("co.courseCode").group() )
614 .add( Property.forName("st.studentNumber").count().setDistinct() )
615 .add( Property.forName("year").group() )
616 )
617 .list();
618
619 assertEquals( list.size(), 2 );
620
621 s.delete(gavin);
622 s.delete(xam);
623 s.delete(course);
624
625 t.commit();
626 s.close();
627 }
628
629 public void testRestrictionOnSubclassCollection() {
630 Session s = openSession();
631 Transaction t = s.beginTransaction();
632
633 s.createCriteria( Reptile.class )
634 .add( Restrictions.isEmpty( "offspring" ) )
635 .list();
636
637 s.createCriteria( Reptile.class )
638 .add( Restrictions.isNotEmpty( "offspring" ) )
639 .list();
640
641 t.rollback();
642 s.close();
643 }
644
645 public void testProjectedId() {
646 Session s = openSession();
647 Transaction t = s.beginTransaction();
648 s.createCriteria(Course.class).setProjection( Projections.property("courseCode") ).list();
649 s.createCriteria(Course.class).setProjection( Projections.id() ).list();
650 t.rollback();
651 s.close();
652 }
653
654 protected String [] getMappings() {
655 return new String [] { "criteria/Enrolment.hbm.xml", "hql/Animal.hbm.xml" };
656 }
657
658 public static Test suite() {
659 return new TestSuite(CriteriaQueryTest.class);
660 }
661
662 }
663
664
|
Popular Tags
|
Java API By Example, From Geeks To Geeks.