|
1 package org.hibernate.test.legacy;
3
4 import java.sql.SQLException  ;
5 import java.util.ArrayList ;
6 import java.util.Date ;
7 import java.util.List ;
8
9 import junit.framework.Test;
10 import junit.framework.TestSuite;
11 import junit.textui.TestRunner;
12
13 import org.hibernate.HibernateException;
14 import org.hibernate.Query;
15 import org.hibernate.classic.Session;
16 import org.hibernate.dialect.HSQLDialect;
17 import org.hibernate.dialect.MySQLDialect;
18 import org.hibernate.dialect.Oracle9Dialect;
19 import org.hibernate.dialect.PostgreSQLDialect;
20 import org.hibernate.dialect.SQLServerDialect;
21 import org.hibernate.dialect.TimesTenDialect;
22 import org.hibernate.dialect.OracleDialect;
23 import org.hibernate.test.TestCase;
24
25 public class SQLLoaderTest extends TestCase {
26
27 static int nextInt = 1;
28 static long nextLong = 1;
29 public SQLLoaderTest(String arg) {
30 super(arg);
31 }
32
33 public void testTS() throws Exception {
34 if (getDialect() instanceof Oracle9Dialect) return;
35 Session session = openSession();
36 Simple sim = new Simple();
37 sim.setDate( new Date () );
38 session.save( sim, new Long (1) );
39 Query q = session.createSQLQuery("select {sim.*} from Simple {sim} where {sim}.date_ = ?", "sim", Simple.class);
40 q.setTimestamp( 0, sim.getDate() );
41 assertTrue ( q.list().size()==1 );
42 session.delete(sim);
43 session.flush();
44 session.connection().commit();
45 session.close();
46 }
47
48
49 public void testFindBySQLStar() throws HibernateException, SQLException {
50 Session session = openSession();
51 session.delete("from Assignable");
52 session.delete("from Category");
53 session.delete("from Simple");
54 session.delete("from A");
55
56 Category s = new Category();
57 s.setName(String.valueOf(nextLong++));
58 session.save(s);
59
60 Simple simple = new Simple();
61 simple.init();
62 session.save(simple, new Long (nextLong++));
63
64 A a = new A();
65 session.save(a);
66
67 B b = new B();
68 session.save(b);
69 session.flush();
70
71 session.createSQLQuery("select {category.*} from Category {category}", "category", Category.class).list();
72 session.createSQLQuery("select {simple.*} from Simple {simple}", "simple", Simple.class).list();
73 session.createSQLQuery("select {a.*} from A {a}", "a", A.class).list();
74
75 session.connection().commit();
76 session.close();
77
78 }
79
80 public void testFindBySQLProperties() throws HibernateException, SQLException {
81 Session session = openSession();
82 session.delete("from Category");
83
84 Category s = new Category();
85 s.setName(String.valueOf(nextLong++));
86 session.save(s);
87
88 s = new Category();
89 s.setName("WannaBeFound");
90 session.flush();
91
92 Query query = session.createSQLQuery("select {category.*} from Category {category} where {category}.name = :name", "category", Category.class);
93
94 query.setProperties(s);
95
97 query.list();
98
99 query = session.createSQLQuery("select {category.*} from Category {category} where {category}.name in (:names)", "category", Category.class);
100 String [] str = new String [] { "WannaBeFound", "NotThere" };
101 query.setParameterList("names", str);
102
103 query.uniqueResult();
104
105 session.connection().commit();
106 session.close();
107
108
109
110 }
111
112 public void testFindBySQLAssociatedObjects() throws HibernateException, SQLException {
113 Session s = openSession();
114 s.delete("from Assignable");
115 s.delete("from Category");
116
117 Category c = new Category();
118 c.setName("NAME");
119 Assignable assn = new Assignable();
120 assn.setId("i.d.");
121 List l = new ArrayList ();
122 l.add(c);
123 assn.setCategories(l);
124 c.setAssignable(assn);
125 s.save(assn);
126 s.flush();
127 s.connection().commit();
128 s.close();
129
130 s = openSession();
131 List list = s.createSQLQuery("select {category.*} from Category {category}", "category", Category.class).list();
132 list.get(0);
133 s.connection().commit();
134 s.close();
135
136 if ( getDialect() instanceof MySQLDialect ) return;
137 if ( getDialect() instanceof OracleDialect ) return;
139 s = openSession();
140
141 Query query = s.getNamedQuery("namedsql");
142 assertNotNull(query);
143 list = query.list();
144 assertNotNull(list);
145
146 Object [] values = (Object []) list.get(0);
147 assertNotNull(values[0]);
148 assertNotNull(values[1]);
149 assertTrue("wrong type: " + values[0].getClass(), values[0] instanceof Category);
150 assertTrue("wrong type: " + values[1].getClass(), values[1] instanceof Assignable);
151
152 s.connection().commit();
153 s.close();
154
155 }
156
157 public void testPropertyResultSQL() throws HibernateException, SQLException {
158
159 if ( getDialect() instanceof MySQLDialect ) return;
160
161 Session s = openSession();
162 s.delete("from Assignable");
163 s.delete("from Category");
164
165 Category c = new Category();
166 c.setName("NAME");
167 Assignable assn = new Assignable();
168 assn.setId("i.d.");
169 List l = new ArrayList ();
170 l.add(c);
171 assn.setCategories(l);
172 c.setAssignable(assn);
173 s.save(assn);
174 s.flush();
175 s.connection().commit();
176 s.close();
177
178 s = openSession();
179
180 Query query = s.getNamedQuery("nonaliasedsql");
181 assertNotNull(query);
182 List list = query.list();
183 assertNotNull(list);
184
185 assertTrue(list.get(0) instanceof Category);
186
187 s.connection().commit();
188 s.close();
189
190 }
191
192 public void testFindBySQLMultipleObject() throws HibernateException, SQLException {
193 Session s = openSession();
194 s.delete("from Assignable");
195 s.delete("from Category");
196 s.flush();
197 s.connection().commit();
198 s.close();
199 s = openSession();
200 Category c = new Category();
201 c.setName("NAME");
202 Assignable assn = new Assignable();
203 assn.setId("i.d.");
204 List l = new ArrayList ();
205 l.add(c);
206 assn.setCategories(l);
207 c.setAssignable(assn);
208 s.save(assn);
209 s.flush();
210 c = new Category();
211 c.setName("NAME2");
212 assn = new Assignable();
213 assn.setId("i.d.2");
214 l = new ArrayList ();
215 l.add(c);
216 assn.setCategories(l);
217 c.setAssignable(assn);
218 s.save(assn);
219 s.flush();
220
221 assn = new Assignable();
222 assn.setId("i.d.3");
223 s.save(assn);
224 s.flush();
225 s.connection().commit();
226 s.close();
227
228 if ( getDialect() instanceof MySQLDialect ) return;
229
230 s = openSession();
231 List list = s.createSQLQuery("select {category.*}, {assignable.*} from Category {category}, \"assign-able\" {assignable}", new String [] { "category", "assignable" }, new Class [] { Category.class, Assignable.class }).list();
232
233 assertTrue(list.size() == 6); assertTrue(list.get(0) instanceof Object []);
235 s.connection().commit();
236 s.close();
237 }
238
239 public void testFindBySQLParameters() throws HibernateException, SQLException {
240 Session s = openSession();
241 s.delete("from Assignable");
242 s.delete("from Category");
243 s.flush();
244 s.connection().commit();
245 s.close();
246 s = openSession();
247 Category c = new Category();
248 c.setName("Good");
249 Assignable assn = new Assignable();
250 assn.setId("i.d.");
251 List l = new ArrayList ();
252 l.add(c);
253 assn.setCategories(l);
254 c.setAssignable(assn);
255 s.save(assn);
256 s.flush();
257 c = new Category();
258 c.setName("Best");
259 assn = new Assignable();
260 assn.setId("i.d.2");
261 l = new ArrayList ();
262 l.add(c);
263 assn.setCategories(l);
264 c.setAssignable(assn);
265 s.save(assn);
266 s.flush();
267 c = new Category();
268 c.setName("Better");
269 assn = new Assignable();
270 assn.setId("i.d.7");
271 l = new ArrayList ();
272 l.add(c);
273 assn.setCategories(l);
274 c.setAssignable(assn);
275 s.save(assn);
276 s.flush();
277
278 assn = new Assignable();
279 assn.setId("i.d.3");
280 s.save(assn);
281 s.flush();
282 s.connection().commit();
283 s.close();
284
285 s = openSession();
286 Query basicParam = s.createSQLQuery("select {category.*} from Category {category} where {category}.name = 'Best'", "category", Category.class);
287 List list = basicParam.list();
288 assertEquals(1, list.size());
289
290 Query unnamedParam = s.createSQLQuery("select {category.*} from Category {category} where {category}.name = ? or {category}.name = ?", "category", Category.class);
291 unnamedParam.setString(0, "Good");
292 unnamedParam.setString(1, "Best");
293 list = unnamedParam.list();
294 assertEquals(2, list.size());
295
296 Query namedParam = s.createSQLQuery("select {category.*} from Category {category} where ({category}.name=:firstCat or {category}.name=:secondCat)", "category", Category.class);
297 namedParam.setString("firstCat", "Better");
298 namedParam.setString("secondCat", "Best");
299 list = namedParam.list();
300 assertEquals(2, list.size());
301
302 s.connection().commit();
303 s.close();
304 }
305
306 public void testEscapedJDBC() throws HibernateException, SQLException {
307 if (
308 getDialect() instanceof HSQLDialect ||
309 getDialect() instanceof SQLServerDialect ||
310 getDialect() instanceof PostgreSQLDialect
311 ) return;
312
313 Session session = openSession();
314 session.delete("from A");
315 A savedA = new A();
316 savedA.setName("Max");
317 session.save(savedA);
318
319 B savedB = new B();
320 session.save(savedB);
321 session.flush();
322
323 int count = session.createQuery("from A").list().size();
324 session.close();
325
326 session = openSession();
327
328 Query query;
329 if ( getDialect() instanceof OracleDialect ) {
330 query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from A where upper(name) like upper('max')", "a", A.class);
332 }
333 else if( getDialect() instanceof TimesTenDialect) {
334 query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from A where {fn ucase(name)} like 'MAX'", "a", A.class);
337 } else {
338 query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from A where {fn ucase(name)} like {fn ucase('max')}", "a", A.class);
339 }
340 List list = query.list();
341
342 assertNotNull(list);
343 assertEquals(1, list.size());
344 session.connection().commit();
345 session.close();
346 }
347
348 public void testDoubleAliasing() throws HibernateException, SQLException {
349 if ( getDialect() instanceof HSQLDialect || getDialect() instanceof SQLServerDialect ) return;
350
351 Session session = openSession();
352 session.delete("from A");
353 A savedA = new A();
354 savedA.setName("Max");
355 session.save(savedA);
356
357 B savedB = new B();
358 session.save(savedB);
359 session.flush();
360
361 int count = session.createQuery("from A").list().size();
362 session.close();
363
364 session = openSession();
365
366 Query query = session.createSQLQuery("select a.identifier_column as {a1.id}, a.clazz_discriminata as {a1.class}, a.count_ as {a1.count}, a.name as {a1.name} " +
367 ", b.identifier_column as {a2.id}, b.clazz_discriminata as {a2.class}, b.count_ as {a2.count}, b.name as {a2.name} " +
368 " from A a, A b" +
369 " where a.identifier_column = b.identifier_column", new String [] {"a1", "a2" }, new Class [] {A.class, A.class});
370 List list = query.list();
371
372 assertNotNull(list);
373 assertEquals(2, list.size());
374 session.connection().commit();
375 session.close();
376 }
377
378 public void testEmbeddedCompositeProperties() throws HibernateException, SQLException {
380 Session session = openSession();
381
382 Single s = new Single();
383 s.setId("my id");
384 s.setString("string 1");
385 session.save(s);
386 session.flush();
387 session.connection().commit();
388
389 session.clear();
390
391 Query query = session.createSQLQuery("select {sing.*} from Single {sing}", "sing", Single.class);
392
393 List list = query.list();
394
395 assertTrue(list.size()==1);
396
397 session.clear();
398
399 query = session.createSQLQuery("select {sing.*} from Single {sing} where sing.id = ?", "sing", Single.class);
400 query.setString(0, "my id");
401 list = query.list();
402
403 assertTrue(list.size()==1);
404
405 session.clear();
406
407 query = session.createSQLQuery("select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?", "sing", Single.class);
408 query.setString(0, "my id");
409 list = query.list();
410
411 assertTrue(list.size()==1);
412
413 session.clear();
414
415 query = session.createSQLQuery("select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?", "sing", Single.class);
416 query.setString(0, "my id");
417 list = query.list();
418
419 assertTrue(list.size()==1);
420
421 session.connection().commit();
422 session.close();
423
424 }
425
426 public void testComponentStar() throws HibernateException, SQLException {
427 componentTest("select {comp.*} from Componentizable comp");
428 }
429
430 public void testComponentNoStar() throws HibernateException, SQLException {
431 componentTest("select comp.id as {comp.id}, comp.nickName as {comp.nickName}, comp.name as {comp.component.name}, comp.subName as {comp.component.subComponent.subName}, comp.subName1 as {comp.component.subComponent.subName1} from Componentizable comp");
432 }
433
434
435 private void componentTest(String sql) throws SQLException {
436 Session session = openSession();
437
438 Componentizable c = new Componentizable();
439 c.setNickName("Flacky");
440 Component component = new Component();
441 component.setName("flakky comp");
442 SubComponent subComponent = new SubComponent();
443 subComponent.setSubName("subway");
444 component.setSubComponent(subComponent);
445
446 c.setComponent(component);
447
448 session.save(c);
449
450 session.flush();
451 session.connection().commit();
452
453 session.clear();
454
455 Query q = session.createSQLQuery(sql, "comp", Componentizable.class);
456 List list = q.list();
457
458 assertEquals(list.size(),1);
459
460 Componentizable co = (Componentizable) list.get(0);
461
462 assertEquals(c.getNickName(), co.getNickName());
463 assertEquals(c.getComponent().getName(), co.getComponent().getName());
464 assertEquals(c.getComponent().getSubComponent().getSubName(), co.getComponent().getSubComponent().getSubName());
465
466 session.delete(co);
467 session.flush();
468 session.connection().commit();
469 session.close();
470 }
471
472 public void testFindSimpleBySQL() throws Exception {
473 if ( getDialect() instanceof MySQLDialect ) return;
474 Session session = openSession();
475 Category s = new Category();
476 s.setName(String.valueOf(nextLong++));
477 session.save(s);
478 session.flush();
479
480 Query query = session.createSQLQuery("select s.category_key_col as {category.id}, s.name as {category.name}, s.\"assign-able-id\" as {category.assignable} from {category} s", "category", Category.class);
481 List list = query.list();
482
483 assertNotNull(list);
484 assertTrue(list.size() > 0);
485 assertTrue(list.get(0) instanceof Category);
486 session.connection().commit();
487 session.close();
488 }
490
491 public void testFindBySQLSimpleByDiffSessions() throws Exception {
492 Session session = openSession();
493 Category s = new Category();
494 s.setName(String.valueOf(nextLong++));
495 session.save(s);
496 session.flush();
497 session.connection().commit();
498 session.close();
499
500 if ( getDialect() instanceof MySQLDialect ) return;
501
502 session = openSession();
503
504 Query query = session.createSQLQuery("select s.category_key_col as {category.id}, s.name as {category.name}, s.\"assign-able-id\" as {category.assignable} from {category} s", "category", Category.class);
505 List list = query.list();
506
507 assertNotNull(list);
508 assertTrue(list.size() > 0);
509 assertTrue(list.get(0) instanceof Category);
510
511 session.connection().commit();
514 session.close();
515 }
516
517 public void testFindBySQLDiscriminatedSameSession() throws Exception {
518 Session session = openSession();
519 session.delete("from A");
520 A savedA = new A();
521 session.save(savedA);
522
523 B savedB = new B();
524 session.save(savedB);
525 session.flush();
526
527 Query query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, name as {a.name}, count_ as {a.count} from {a} s", "a", A.class);
528 List list = query.list();
529
530 assertNotNull(list);
531 assertEquals(2, list.size());
532
533 A a1 = (A) list.get(0);
534 A a2 = (A) list.get(1);
535
536 assertTrue((a2 instanceof B) || (a1 instanceof B));
537 assertFalse(a1 instanceof B && a2 instanceof B);
538
539 if (a1 instanceof B) {
540 assertSame(a1, savedB);
541 assertSame(a2, savedA);
542 }
543 else {
544 assertSame(a2, savedB);
545 assertSame(a1, savedA);
546 }
547
548 session.clear();
549 List list2 = session.getNamedQuery("propertyResultDiscriminator").list();
550 assertEquals(2, list2.size());
551
552 session.connection().commit();
553 session.close();
554 }
555
556 public void testFindBySQLDiscriminatedDiffSession() throws Exception {
557 Session session = openSession();
558 session.delete("from A");
559 A savedA = new A();
560 session.save(savedA);
561
562 B savedB = new B();
563 session.save(savedB);
564 session.flush();
565
566 int count = session.createQuery("from A").list().size();
567 session.close();
568
569 session = openSession();
570
571 Query query = session.createSQLQuery("select identifier_column as {a.id}, clazz_discriminata as {a.class}, count_ as {a.count}, name as {a.name} from A", "a", A.class);
572 List list = query.list();
573
574 assertNotNull(list);
575 assertEquals(count, list.size());
576 session.connection().commit();
577 session.close();
578 }
579
580
581
582 public void testCompositeIdId() throws HibernateException, SQLException {
583 Session s = openSession();
584
585 CompositeIdId id = new CompositeIdId();
586 id.setName("Max");
587 id.setSystem("c64");
588 id.setId("games");
589
590 s.save(id);
591 s.flush();
592 s.connection().commit();
593 s.close();
594
595 s = openSession();
596 Query query = s.createSQLQuery("select system as {c.system}, id as {c.id}, name as {c.name}, foo as {c.composite.foo}, bar as {c.composite.bar} from CompositeIdId where system=? and id=?", "c", CompositeIdId.class);
598 query.setString(0, "c64");
599 query.setString(1, "games");
600
601 CompositeIdId id2 = (CompositeIdId) query.uniqueResult();
602 check(id, id2);
603
604 s.flush();
605 s.connection().commit();
606 s.close();
607
608 s = openSession();
609
610 CompositeIdId useForGet = new CompositeIdId();
611 useForGet.setSystem("c64");
612 useForGet.setId("games");
613 CompositeIdId getted = (CompositeIdId) s.get(CompositeIdId.class, useForGet);
615 check(id,getted);
616
617
618 s.connection().commit();
619 s.close();
620
621 }
622
623
624
628 private void check(CompositeIdId id, CompositeIdId id2) {
629 assertEquals(id,id2);
630 assertEquals(id.getName(), id2.getName());
631 assertEquals(id.getId(), id2.getId());
632 assertEquals(id.getSystem(), id2.getSystem());
633 }
634
635 public String [] getMappings() {
636 return new String [] {
637 "legacy/ABC.hbm.xml",
638 "legacy/Category.hbm.xml",
639 "legacy/Simple.hbm.xml",
640 "legacy/Fo.hbm.xml",
641 "legacy/SingleSeveral.hbm.xml",
642 "legacy/Componentizable.hbm.xml",
643 "legacy/CompositeIdId.hbm.xml"
644 };
645 }
646
647 public static Test suite() {
648 return new TestSuite(SQLLoaderTest.class);
649 }
650
651 public static void main(String [] args) throws Exception {
652 TestRunner.run( suite() );
653 }
654
655 }
656 |
Popular Tags |
Java API By Example, From Geeks To Geeks.