|
1 package org.hibernate.test.legacy;
3
4 import java.util.ArrayList  ;
5 import java.util.Date ;
6 import java.util.HashSet ;
7 import java.util.Iterator ;
8 import java.util.List ;
9 import java.util.Map ;
10
11 import junit.framework.Test;
12 import junit.framework.TestSuite;
13 import junit.textui.TestRunner;
14
15 import org.apache.commons.logging.Log;
16 import org.apache.commons.logging.LogFactory;
17 import org.hibernate.Hibernate;
18 import org.hibernate.Query;
19 import org.hibernate.ScrollableResults;
20 import org.hibernate.Transaction;
21 import org.hibernate.classic.Session;
22 import org.hibernate.dialect.DB2Dialect;
23 import org.hibernate.dialect.HSQLDialect;
24 import org.hibernate.dialect.InterbaseDialect;
25 import org.hibernate.dialect.MckoiDialect;
26 import org.hibernate.dialect.MySQLDialect;
27 import org.hibernate.dialect.Oracle9Dialect;
28 import org.hibernate.dialect.OracleDialect;
29 import org.hibernate.dialect.SybaseDialect;
30 import org.hibernate.dialect.TimesTenDialect;
31 import org.hibernate.dialect.function.SQLFunction;
32 import org.hibernate.test.TestCase;
33
34 public class SQLFunctionsTest extends TestCase {
35
36 private static final Log log = LogFactory.getLog(SQLFunctionsTest.class);
37
38 public SQLFunctionsTest(String name) {
39 super(name);
40 }
41
42 public void testDialectSQLFunctions() throws Exception {
43
44 Session s = openSession();
45 Transaction t = s.beginTransaction();
46
47 Iterator iter = s.iterate("select max(s.count) from Simple s");
48
49 if ( getDialect() instanceof MySQLDialect ) assertTrue( iter.hasNext() && iter.next()==null );
50
51 Simple simple = new Simple();
52 simple.setName("Simple Dialect Function Test");
53 simple.setAddress("Simple Address");
54 simple.setPay(new Float (45.8));
55 simple.setCount(2);
56 s.save(simple, new Long (10) );
57
58 assertTrue(
60 s.find("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").size() == 1
61 );
62
63 assertTrue(
65 s.find("select max(s.count) from Simple s").size() == 1
66 );
67 assertTrue(
68 s.find("select count(*) from Simple s").size() == 1
69 );
70
71 if ( getDialect() instanceof OracleDialect) {
72 java.util.List rset = s.find("select s.name, sysdate(), trunc(s.pay), round(s.pay) from Simple s");
74 assertNotNull("Name string should have been returned",(((Object [])rset.get(0))[0]));
75 assertNotNull("Todays Date should have been returned",(((Object [])rset.get(0))[1]));
76 assertEquals("trunc(45.8) result was incorrect ", new Float (45), ( (Object []) rset.get(0) )[2] );
77 assertEquals("round(45.8) result was incorrect ", new Float (46), ( (Object []) rset.get(0) )[3] );
78
79 simple.setPay(new Float (-45.8));
80 s.update(simple);
81
82 rset = s.find("select abs(round(s.pay)) from Simple s");
84 assertEquals("abs(round(-45.8)) result was incorrect ", new Float (46), rset.get(0));
85
86 assertTrue(
88 s.find("select trunc(round(sysdate())) from Simple s").size() == 1
89 );
90
91 simple.setPay(null);
93 s.update(simple);
94 Integer value = (Integer ) s.find("select MOD( NVL(s.pay, 5000), 2 ) from Simple as s where s.id = 10").get(0);
95 assertTrue( 0 == value.intValue() );
96 }
97
98 if ( (getDialect() instanceof HSQLDialect) ) {
99 Integer value = (Integer ) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10" ).get(0);
101 assertTrue( 0 == value.intValue() );
102 }
103
104 s.delete(simple);
105 t.commit();
106 s.close();
107 }
108
109 public void testSetProperties() throws Exception {
110 Session s = openSession();
111 Transaction t = s.beginTransaction();
112 Simple simple = new Simple();
113 simple.setName("Simple 1");
114 s.save(simple, new Long (10) );
115 Query q = s.createQuery("from Simple s where s.name=:name and s.count=:count");
116 q.setProperties(simple);
117 assertTrue( q.list().get(0)==simple );
118 Single single = new Single() { String [] getStuff() { return (String []) getSeveral().toArray(new String [getSeveral().size()]); }
121 };
122
123 List l = new ArrayList ();
124 l.add("Simple 1");
125 l.add("Slimeball");
126 single.setSeveral(l);
127 q = s.createQuery("from Simple s where s.name in (:several)");
128 q.setProperties(single);
129 assertTrue( q.list().get(0)==simple );
130
131
132 q = s.createQuery("from Simple s where s.name in (:stuff)");
133 q.setProperties(single);
134 assertTrue( q.list().get(0)==simple );
135 s.delete(simple);
136 t.commit();
137 s.close();
138 }
139
140 public void testBroken() throws Exception {
141 if (getDialect() instanceof Oracle9Dialect) return;
142 Session s = openSession();
143 Transaction t = s.beginTransaction();
144 Broken b = new Fixed();
145 b.setId( new Long (123));
146 b.setOtherId("foobar");
147 s.save(b);
148 s.flush();
149 b.setTimestamp( new Date () );
150 t.commit();
151 s.close();
152
153 s = openSession();
154 t = s.beginTransaction();
155 s.update(b);
156 t.commit();
157 s.close();
158
159 s = openSession();
160 t = s.beginTransaction();
161 b = (Broken) s.load( Broken.class, b );
162 t.commit();
163 s.close();
164
165 s = openSession();
166 t = s.beginTransaction();
167 s.delete(b);
168 t.commit();
169 s.close();
170 }
171
172 public void testNothinToUpdate() throws Exception {
173 Session s = openSession();
174 Transaction t = s.beginTransaction();
175 Simple simple = new Simple();
176 simple.setName("Simple 1");
177 s.save( simple, new Long (10) );
178 t.commit();
179 s.close();
180
181 s = openSession();
182 t = s.beginTransaction();
183 s.update( simple, new Long (10) );
184 t.commit();
185 s.close();
186
187 s = openSession();
188 t = s.beginTransaction();
189 s.update( simple, new Long (10) );
190 s.delete(simple);
191 t.commit();
192 s.close();
193 }
194
195 public void testCachedQuery() throws Exception {
196 Session s = openSession();
197 Transaction t = s.beginTransaction();
198 Simple simple = new Simple();
199 simple.setName("Simple 1");
200 s.save( simple, new Long (10) );
201 t.commit();
202 s.close();
203
204 s = openSession();
205 t = s.beginTransaction();
206 Query q = s.createQuery("from Simple s where s.name=?");
207 q.setCacheable(true);
208 q.setString(0, "Simple 1");
209 assertTrue( q.list().size()==1 );
210 assertTrue( q.list().size()==1 );
211 assertTrue( q.list().size()==1 );
212 q = s.createQuery("from Simple s where s.name=:name");
213 q.setCacheable(true);
214 q.setString("name", "Simple 1");
215 assertTrue( q.list().size()==1 );
216 simple = (Simple) q.list().get(0);
217
218 q.setString("name", "Simple 2");
219 assertTrue( q.list().size()==0 );
220 assertTrue( q.list().size()==0 );
221 simple.setName("Simple 2");
222 assertTrue( q.list().size()==1 );
223 assertTrue( q.list().size()==1 );
224 t.commit();
225 s.close();
226
227 s = openSession();
228 t = s.beginTransaction();
229 q = s.createQuery("from Simple s where s.name=:name");
230 q.setString("name", "Simple 2");
231 q.setCacheable(true);
232 assertTrue( q.list().size()==1 );
233 assertTrue( q.list().size()==1 );
234 t.commit();
235 s.close();
236
237 s = openSession();
238 t = s.beginTransaction();
239 s.update( simple, new Long (10) );
240 s.delete(simple);
241 t.commit();
242 s.close();
243
244 s = openSession();
245 t = s.beginTransaction();
246 q = s.createQuery("from Simple s where s.name=?");
247 q.setCacheable(true);
248 q.setString(0, "Simple 1");
249 assertTrue( q.list().size()==0 );
250 assertTrue( q.list().size()==0 );
251 t.commit();
252 s.close();
253 }
254
255 public void testCachedQueryRegion() throws Exception {
256 Session s = openSession();
257 Transaction t = s.beginTransaction();
258 Simple simple = new Simple();
259 simple.setName("Simple 1");
260 s.save( simple, new Long (10) );
261 t.commit();
262 s.close();
263
264 s = openSession();
265 t = s.beginTransaction();
266 Query q = s.createQuery("from Simple s where s.name=?");
267 q.setCacheRegion("foo");
268 q.setCacheable(true);
269 q.setString(0, "Simple 1");
270 assertTrue( q.list().size()==1 );
271 assertTrue( q.list().size()==1 );
272 assertTrue( q.list().size()==1 );
273 q = s.createQuery("from Simple s where s.name=:name");
274 q.setCacheRegion("foo");
275 q.setCacheable(true);
276 q.setString("name", "Simple 1");
277 assertTrue( q.list().size()==1 );
278 simple = (Simple) q.list().get(0);
279
280 q.setString("name", "Simple 2");
281 assertTrue( q.list().size()==0 );
282 assertTrue( q.list().size()==0 );
283 simple.setName("Simple 2");
284 assertTrue( q.list().size()==1 );
285 assertTrue( q.list().size()==1 );
286 t.commit();
287 s.close();
288
289 s = openSession();
290 t = s.beginTransaction();
291 s.update( simple, new Long (10) );
292 s.delete(simple);
293 t.commit();
294 s.close();
295
296 s = openSession();
297 t = s.beginTransaction();
298 q = s.createQuery("from Simple s where s.name=?");
299 q.setCacheRegion("foo");
300 q.setCacheable(true);
301 q.setString(0, "Simple 1");
302 assertTrue( q.list().size()==0 );
303 assertTrue( q.list().size()==0 );
304 t.commit();
305 s.close();
306 }
307
308 public void testSQLFunctions() throws Exception {
309 Session s = openSession();
310 Transaction t = s.beginTransaction();
311 Simple simple = new Simple();
312 simple.setName("Simple 1");
313 s.save(simple, new Long (10) );
314
315 if ( getDialect() instanceof DB2Dialect) {
316 s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
317 s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
318 s.find("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'");
319 }
320
321 assertTrue(
322 s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size()==1
323 );
324 if ( !(getDialect() instanceof HSQLDialect) ) {
325 assertTrue(
326 s.find("from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )").size()==1
327 );
328 }
329 if ( !(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) && !(getDialect() instanceof TimesTenDialect) ) { assertTrue(
331 s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size()==1
332 );
333 }
334 if ( (getDialect() instanceof SybaseDialect) ) {
335 assertTrue(
336 s.find("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").size()==1
337 );
338 }
339 if ( (getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
340 assertTrue(
341 s.find("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()==1
342 );
343 }
344
345 Simple other = new Simple();
346 other.setName("Simple 2");
347 other.setCount(12);
348 simple.setOther(other);
349 s.save( other, new Long (20) );
350 assertTrue(
352 s.find("from Simple s where upper( s.other.name ) ='SIMPLE 2'").size()==1
353 );
354 assertTrue(
355 s.find("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").size()==0
356 );
357 assertTrue(
358 s.find("select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2").size()==1
359 );
360 assertTrue(
361 s.find("select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count").size()==1
362 );
363 Simple min = new Simple();
364 min.setCount(-1);
365 s.save(min, new Long (30) );
366 if ( ! (getDialect() instanceof MySQLDialect) && ! (getDialect() instanceof HSQLDialect) ) { assertTrue(
368 s.find("from Simple s where s.count > ( select min(sim.count) from Simple sim )").size()==2
369 );
370 t.commit();
371 t = s.beginTransaction();
372 assertTrue(
373 s.find("from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0").size()==2
374 );
375 assertTrue(
376 s.find("from Simple s where s = some( select sim from Simple sim where sim.other.count=s.other.count ) and s.other.count > 0").size()==1
377 );
378 }
379
380 Iterator iter = s.iterate("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10");
381 assertTrue( iter.hasNext() );
382 assertTrue( new Integer (12).equals( iter.next() ) );
383 assertTrue( !iter.hasNext() );
384 if ( ! (getDialect() instanceof MySQLDialect) ) {
385 iter = s.iterate("select s.count from Simple s group by s.count having s.count = 12");
386 assertTrue( iter.hasNext() );
387 }
388
389 s.iterate("select s.id, s.count, count(t), max(t.date) from Simple s, Simple t where s.count = t.count group by s.id, s.count order by s.count");
390
391 Query q = s.createQuery("from Simple s");
392 q.setMaxResults(10);
393 assertTrue( q.list().size()==3 );
394 q = s.createQuery("from Simple s");
395 q.setMaxResults(1);
396 assertTrue( q.list().size()==1 );
397 q = s.createQuery("from Simple s");
398 assertTrue( q.list().size()==3 );
399 q = s.createQuery("from Simple s where s.name = ?");
400 q.setString(0, "Simple 1");
401 assertTrue( q.list().size()==1 );
402 q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
403 q.setString(1, "SIMPLE 1");
404 q.setString(0, "Simple 1");
405 q.setFirstResult(0);
406 assertTrue( q.iterate().hasNext() );
407 q = s.createQuery("from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
408 q.setParameter("bar", "SIMPLE 1");
409 q.setString("foo", "Simple 1");
410 q.setInteger("count", 69);
411 q.setFirstResult(0);
412 assertTrue( q.iterate().hasNext() );
413 q = s.createQuery("select s.id from Simple s");
414 q.setFirstResult(1);
415 q.setMaxResults(2);
416 iter = q.iterate();
417 int i=0;
418 while ( iter.hasNext() ) {
419 assertTrue( iter.next() instanceof Long );
420 i++;
421 }
422 assertTrue(i==2);
423 q = s.createQuery("select all s, s.other from Simple s where s = :s");
424 q.setParameter("s", simple);
425 assertTrue( q.list().size()==1 );
426
427
428 q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
429 HashSet set = new HashSet ();
430 set.add("Simple 1"); set.add("foo");
431 q.setParameterList( "name_list", set );
432 q.setParameter("count", new Integer (-1) );
433 assertTrue( q.list().size()==1 );
434
435 ScrollableResults sr = s.createQuery("from Simple s").scroll();
436 sr.next();
437 sr.get(0);
438 sr.close();
439
440 s.delete(other);
441 s.delete(simple);
442 s.delete(min);
443 t.commit();
444 s.close();
445
446 }
447
448 public void testBlobClob() throws Exception {
449
450 Session s = openSession();
451 Blobber b = new Blobber();
452 b.setBlob( Hibernate.createBlob( "foo/bar/baz".getBytes() ) );
453 b.setClob( Hibernate.createClob("foo/bar/baz") );
454 s.save(b);
455 s.flush();
458 s.refresh(b);
459 b.getClob().getSubString(2, 3);
461 s.flush();
463 s.connection().commit();
464 s.close();
465
466 s = openSession();
467 b = (Blobber) s.load( Blobber.class, new Integer ( b.getId() ) );
468 Blobber b2 = new Blobber();
469 s.save(b2);
470 b2.setBlob( b.getBlob() );
471 b.setBlob(null);
472 b.getClob().getSubString(1, 6);
474 s.flush();
476 s.connection().commit();
477 s.close();
478
479 s = openSession();
480 b = (Blobber) s.load( Blobber.class, new Integer ( b.getId() ) );
481 b.setClob( Hibernate.createClob("xcvfxvc xcvbx cvbx cvbx cvbxcvbxcvbxcvb") );
482 s.flush();
483 s.connection().commit();
484 s.close();
485
486 s = openSession();
487 b = (Blobber) s.load( Blobber.class, new Integer ( b.getId() ) );
488 assertTrue( b.getClob().getSubString(1, 7).equals("xcvfxvc") );
489 s.flush();
491 s.connection().commit();
492 s.close();
493
494
495
521
522 }
523
524 public void testSqlFunctionAsAlias() throws Exception {
525 String functionName = locateAppropriateDialectFunctionNameForAliasTest();
526 if (functionName == null) {
527 log.info("Dialect does not list any no-arg functions");
528 return;
529 }
530
531 log.info("Using function named [" + functionName + "] for 'function as alias' test");
532 String query = "select " + functionName + " from Simple as " + functionName + " where " + functionName + ".id = 10";
533
534 Session s = openSession();
535 Transaction t = s.beginTransaction();
536 Simple simple = new Simple();
537 simple.setName("Simple 1");
538 s.save( simple, new Long (10) );
539 t.commit();
540 s.close();
541
542 s = openSession();
543 t = s.beginTransaction();
544 List result = s.find(query);
545 assertTrue( result.size() == 1 );
546 assertTrue(result.get(0) instanceof Simple);
547 s.delete( result.get(0) );
548 t.commit();
549 s.close();
550 }
551
552 private String locateAppropriateDialectFunctionNameForAliasTest() {
553 for (Iterator itr = getDialect().getFunctions().entrySet().iterator(); itr.hasNext(); ) {
554 final Map.Entry entry = (Map.Entry ) itr.next();
555 final SQLFunction function = (SQLFunction) entry.getValue();
556 if ( !function.hasArguments() && !function.hasParenthesesIfNoArguments() ) {
557 return (String ) entry.getKey();
558 }
559 }
560 return null;
561 }
562
563 public void testCachedQueryOnInsert() throws Exception {
564 Session s = openSession();
565 Transaction t = s.beginTransaction();
566 Simple simple = new Simple();
567 simple.setName("Simple 1");
568 s.save( simple, new Long (10) );
569 t.commit();
570 s.close();
571
572 s = openSession();
573 t = s.beginTransaction();
574 Query q = s.createQuery("from Simple s");
575 List list = q.setCacheable(true).list();
576 assertTrue( list.size()==1 );
577 t.commit();
578 s.close();
579
580 s = openSession();
581 t = s.beginTransaction();
582 q = s.createQuery("from Simple s");
583 list = q.setCacheable(true).list();
584 assertTrue( list.size()==1 );
585 t.commit();
586 s.close();
587
588 s = openSession();
589 t = s.beginTransaction();
590 Simple simple2 = new Simple();
591 simple2.setCount(133);
592 s.save( simple2, new Long (12) );
593 t.commit();
594 s.close();
595
596 s = openSession();
597 t = s.beginTransaction();
598 q = s.createQuery("from Simple s");
599 list = q.setCacheable(true).list();
600 assertTrue( list.size()==2 );
601 t.commit();
602 s.close();
603
604 s = openSession();
605 t = s.beginTransaction();
606 q = s.createQuery("from Simple s");
607 list = q.setCacheable(true).list();
608 assertTrue( list.size()==2 );
609 Iterator i = list.iterator();
610 while ( i.hasNext() ) s.delete( i.next() );
611 t.commit();
612 s.close();
613
614 }
615
616
617 public String [] getMappings() {
618 return new String [] {
619 "legacy/AltSimple.hbm.xml",
620 "legacy/Broken.hbm.xml",
621 "legacy/Blobber.hbm.xml"
622 };
623 }
624
625 public static Test suite() {
626 return new TestSuite(SQLFunctionsTest.class);
627 }
628
629 public static void main(String [] args) throws Exception {
630 TestRunner.run( suite() );
631 }
632
633 }
634
635
636
637
638
639
640
641 |
Popular Tags |
Java API By Example, From Geeks To Geeks.