1
//$Id: SQLFunctionsTest.java 10976 2006-12-12 23:22:26Z steve.ebersole@jboss.com $
2
package org.hibernate.test.legacy;
4
import java.util.ArrayList;
6
import java.util.HashMap;
7
import java.util.HashSet;
8
import java.util.Iterator;
12
import junit.framework.Test;
13
import org.apache.commons.logging.Log;
14
import org.apache.commons.logging.LogFactory;
16
import org.hibernate.Hibernate;
17
import org.hibernate.Query;
18
import org.hibernate.ScrollableResults;
19
import org.hibernate.Transaction;
20
import org.hibernate.classic.Session;
21
import org.hibernate.dialect.DB2Dialect;
22
import org.hibernate.dialect.HSQLDialect;
23
import org.hibernate.dialect.InterbaseDialect;
24
import org.hibernate.dialect.MckoiDialect;
25
import org.hibernate.dialect.MySQLDialect;
26
import org.hibernate.dialect.Oracle9Dialect;
27
import org.hibernate.dialect.OracleDialect;
28
import org.hibernate.dialect.SybaseDialect;
29
import org.hibernate.dialect.TimesTenDialect;
30
import org.hibernate.dialect.function.SQLFunction;
31
import org.hibernate.junit.functional.FunctionalTestClassTestSuite;
34
public class SQLFunctionsTest extends LegacyTestCase {
36
private static final Log log = LogFactory.getLog(SQLFunctionsTest.class);
38
public SQLFunctionsTest(String name) {
42
public String[] getMappings() {
44
"legacy/AltSimple.hbm.xml",
45
"legacy/Broken.hbm.xml",
46
"legacy/Blobber.hbm.xml"
50
public static Test suite() {
51
return new FunctionalTestClassTestSuite( SQLFunctionsTest.class );
54
public void testDialectSQLFunctions() throws Exception {
56
Session s = openSession();
57
Transaction t = s.beginTransaction();
59
Iterator iter = s.iterate("select max(s.count) from Simple s");
61
if ( getDialect() instanceof MySQLDialect ) assertTrue( iter.hasNext() && iter.next()==null );
63
Simple simple = new Simple();
64
simple.setName("Simple Dialect Function Test");
65
simple.setAddress("Simple Address");
66
simple.setPay(new Float(45.8));
68
s.save(simple, new Long(10) );
70
// Test to make sure allocating an specified object operates correctly.
72
s.find("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").size() == 1
75
// Quick check the base dialect functions operate correctly
77
s.find("select max(s.count) from Simple s").size() == 1
80
s.find("select count(*) from Simple s").size() == 1
83
if ( getDialect() instanceof OracleDialect) {
84
// Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg functions
85
java.util.List rset = s.find("select s.name, sysdate(), trunc(s.pay), round(s.pay) from Simple s");
86
assertNotNull("Name string should have been returned",(((Object[])rset.get(0))[0]));
87
assertNotNull("Todays Date should have been returned",(((Object[])rset.get(0))[1]));
88
assertEquals("trunc(45.8) result was incorrect ", new Float(45), ( (Object[]) rset.get(0) )[2] );
89
assertEquals("round(45.8) result was incorrect ", new Float(46), ( (Object[]) rset.get(0) )[3] );
91
simple.setPay(new Float(-45.8));
94
// Test type conversions while using nested functions (Float to Int).
95
rset = s.find("select abs(round(s.pay)) from Simple s");
96
assertEquals("abs(round(-45.8)) result was incorrect ", new Float(46), rset.get(0));
98
// Test a larger depth 3 function example - Not a useful combo other than for testing
100
s.find("select trunc(round(sysdate())) from Simple s").size() == 1
103
// Test the oracle standard NVL funtion as a test of multi-param functions...
106
Integer value = (Integer) s.find("select MOD( NVL(s.pay, 5000), 2 ) from Simple as s where s.id = 10").get(0);
107
assertTrue( 0 == value.intValue() );
110
if ( (getDialect() instanceof HSQLDialect) ) {
111
// Test the hsql standard MOD funtion as a test of multi-param functions...
112
Integer value = (Integer) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10" ).get(0);
113
assertTrue( 0 == value.intValue() );
121
public void testSetProperties() throws Exception {
122
Session s = openSession();
123
Transaction t = s.beginTransaction();
124
Simple simple = new Simple();
125
simple.setName("Simple 1");
126
s.save(simple, new Long(10) );
127
Query q = s.createQuery("from Simple s where s.name=:name and s.count=:count");
128
q.setProperties(simple);
129
assertTrue( q.list().get(0)==simple );
130
//misuse of "Single" as a propertyobject, but it was the first testclass i found with a collection ;)
131
Single single = new Single() { // trivial hack to test properties with arrays.
132
String[] getStuff() { return (String[]) getSeveral().toArray(new String[getSeveral().size()]); }
135
List l = new ArrayList();
138
single.setSeveral(l);
139
q = s.createQuery("from Simple s where s.name in (:several)");
140
q.setProperties(single);
141
assertTrue( q.list().get(0)==simple );
144
q = s.createQuery("from Simple s where s.name in (:stuff)");
145
q.setProperties(single);
146
assertTrue( q.list().get(0)==simple );
152
public void testSetPropertiesMap() throws Exception {
153
Session s = openSession();
154
Transaction t = s.beginTransaction();
155
Simple simple = new Simple();
156
simple.setName("Simple 1");
157
s.save(simple, new Long(10) );
158
Map parameters = new HashMap();
159
parameters.put("name", simple.getName());
160
parameters.put("count", new Integer(simple.getCount()));
162
Query q = s.createQuery("from Simple s where s.name=:name and s.count=:count");
163
q.setProperties(((Map)parameters));
164
assertTrue( q.list().get(0)==simple );
166
List l = new ArrayList();
169
parameters.put("several", l);
170
q = s.createQuery("from Simple s where s.name in (:several)");
171
q.setProperties(parameters);
172
assertTrue( q.list().get(0)==simple );
175
parameters.put("stuff", l.toArray(new String[0]));
176
q = s.createQuery("from Simple s where s.name in (:stuff)");
177
q.setProperties(parameters);
178
assertTrue( q.list().get(0)==simple );
183
public void testBroken() throws Exception {
184
if (getDialect() instanceof Oracle9Dialect) return;
185
Session s = openSession();
186
Transaction t = s.beginTransaction();
187
Broken b = new Fixed();
188
b.setId( new Long(123));
189
b.setOtherId("foobar");
192
b.setTimestamp( new Date() );
197
t = s.beginTransaction();
203
t = s.beginTransaction();
204
b = (Broken) s.load( Broken.class, b );
209
t = s.beginTransaction();
215
public void testNothinToUpdate() throws Exception {
216
Session s = openSession();
217
Transaction t = s.beginTransaction();
218
Simple simple = new Simple();
219
simple.setName("Simple 1");
220
s.save( simple, new Long(10) );
225
t = s.beginTransaction();
226
s.update( simple, new Long(10) );
231
t = s.beginTransaction();
232
s.update( simple, new Long(10) );
238
public void testCachedQuery() throws Exception {
239
Session s = openSession();
240
Transaction t = s.beginTransaction();
241
Simple simple = new Simple();
242
simple.setName("Simple 1");
243
s.save( simple, new Long(10) );
248
t = s.beginTransaction();
249
Query q = s.createQuery("from Simple s where s.name=?");
250
q.setCacheable(true);
251
q.setString(0, "Simple 1");
252
assertTrue( q.list().size()==1 );
253
assertTrue( q.list().size()==1 );
254
assertTrue( q.list().size()==1 );
255
q = s.createQuery("from Simple s where s.name=:name");
256
q.setCacheable(true);
257
q.setString("name", "Simple 1");
258
assertTrue( q.list().size()==1 );
259
simple = (Simple) q.list().get(0);
261
q.setString("name", "Simple 2");
262
assertTrue( q.list().size()==0 );
263
assertTrue( q.list().size()==0 );
264
simple.setName("Simple 2");
265
assertTrue( q.list().size()==1 );
266
assertTrue( q.list().size()==1 );
271
t = s.beginTransaction();
272
q = s.createQuery("from Simple s where s.name=:name");
273
q.setString("name", "Simple 2");
274
q.setCacheable(true);
275
assertTrue( q.list().size()==1 );
276
assertTrue( q.list().size()==1 );
281
t = s.beginTransaction();
282
s.update( simple, new Long(10) );
288
t = s.beginTransaction();
289
q = s.createQuery("from Simple s where s.name=?");
290
q.setCacheable(true);
291
q.setString(0, "Simple 1");
292
assertTrue( q.list().size()==0 );
293
assertTrue( q.list().size()==0 );
298
public void testCachedQueryRegion() throws Exception {
299
Session s = openSession();
300
Transaction t = s.beginTransaction();
301
Simple simple = new Simple();
302
simple.setName("Simple 1");
303
s.save( simple, new Long(10) );
308
t = s.beginTransaction();
309
Query q = s.createQuery("from Simple s where s.name=?");
310
q.setCacheRegion("foo");
311
q.setCacheable(true);
312
q.setString(0, "Simple 1");
313
assertTrue( q.list().size()==1 );
314
assertTrue( q.list().size()==1 );
315
assertTrue( q.list().size()==1 );
316
q = s.createQuery("from Simple s where s.name=:name");
317
q.setCacheRegion("foo");
318
q.setCacheable(true);
319
q.setString("name", "Simple 1");
320
assertTrue( q.list().size()==1 );
321
simple = (Simple) q.list().get(0);
323
q.setString("name", "Simple 2");
324
assertTrue( q.list().size()==0 );
325
assertTrue( q.list().size()==0 );
326
simple.setName("Simple 2");
327
assertTrue( q.list().size()==1 );
328
assertTrue( q.list().size()==1 );
333
t = s.beginTransaction();
334
s.update( simple, new Long(10) );
340
t = s.beginTransaction();
341
q = s.createQuery("from Simple s where s.name=?");
342
q.setCacheRegion("foo");
343
q.setCacheable(true);
344
q.setString(0, "Simple 1");
345
assertTrue( q.list().size()==0 );
346
assertTrue( q.list().size()==0 );
351
public void testSQLFunctions() throws Exception {
352
Session s = openSession();
353
Transaction t = s.beginTransaction();
354
Simple simple = new Simple();
355
simple.setName("Simple 1");
356
s.save(simple, new Long(10) );
358
if ( getDialect() instanceof DB2Dialect) {
359
s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
360
s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
361
s.find("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'");
365
s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size()==1
367
if ( !(getDialect() instanceof HSQLDialect) ) {
369
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
372
if ( !(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) && !(getDialect() instanceof TimesTenDialect) ) { //My SQL has a funny concatenation operator
374
s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size()==1
377
if ( (getDialect() instanceof SybaseDialect) ) {
379
s.find("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").size()==1
382
if ( (getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
384
s.find("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()==1
388
Simple other = new Simple();
389
other.setName("Simple 2");
391
simple.setOther(other);
392
s.save( other, new Long(20) );
393
//s.find("from Simple s where s.name ## 'cat|rat|bag'");
395
s.find("from Simple s where upper( s.other.name ) ='SIMPLE 2'").size()==1
398
s.find("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").size()==0
401
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
404
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
406
Simple min = new Simple();
408
s.save(min, new Long(30) );
409
if ( ! (getDialect() instanceof MySQLDialect) && ! (getDialect() instanceof HSQLDialect) ) { //My SQL has no subqueries
411
s.find("from Simple s where s.count > ( select min(sim.count) from Simple sim )").size()==2
414
t = s.beginTransaction();
416
s.find("from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0").size()==2
419
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
423
Iterator iter = s.iterate("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10");
424
assertTrue( iter.hasNext() );
425
assertEquals( new Long(12), iter.next() );
426
assertTrue( !iter.hasNext() );
427
if ( ! (getDialect() instanceof MySQLDialect) ) {
428
iter = s.iterate("select s.count from Simple s group by s.count having s.count = 12");
429
assertTrue( iter.hasNext() );
432
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");
434
Query q = s.createQuery("from Simple s");
436
assertTrue( q.list().size()==3 );
437
q = s.createQuery("from Simple s");
439
assertTrue( q.list().size()==1 );
440
q = s.createQuery("from Simple s");
441
assertTrue( q.list().size()==3 );
442
q = s.createQuery("from Simple s where s.name = ?");
443
q.setString(0, "Simple 1");
444
assertTrue( q.list().size()==1 );
445
q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
446
q.setString(1, "SIMPLE 1");
447
q.setString(0, "Simple 1");
449
assertTrue( q.iterate().hasNext() );
450
q = s.createQuery("from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
451
q.setParameter("bar", "SIMPLE 1");
452
q.setString("foo", "Simple 1");
453
q.setInteger("count", 69);
455
assertTrue( q.iterate().hasNext() );
456
q = s.createQuery("select s.id from Simple s");
461
while ( iter.hasNext() ) {
462
assertTrue( iter.next() instanceof Long );
466
q = s.createQuery("select all s, s.other from Simple s where s = :s");
467
q.setParameter("s", simple);
468
assertTrue( q.list().size()==1 );
471
q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
472
HashSet set = new HashSet();
473
set.add("Simple 1"); set.add("foo");
474
q.setParameterList( "name_list", set );
475
q.setParameter("count", new Integer(-1) );
476
assertTrue( q.list().size()==1 );
478
ScrollableResults sr = s.createQuery("from Simple s").scroll();
491
public void testBlobClob() throws Exception {
493
Session s = openSession();
494
Blobber b = new Blobber();
495
b.setBlob( Hibernate.createBlob( "foo/bar/baz".getBytes() ) );
496
b.setClob( Hibernate.createClob("foo/bar/baz") );
499
//assertTrue( b.getClob() instanceof ClobImpl );
502
//b.getBlob().setBytes( 2, "abc".getBytes() );
503
b.getClob().getSubString(2, 3);
504
//b.getClob().setString(2, "abc");
506
s.connection().commit();
510
b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
511
Blobber b2 = new Blobber();
513
b2.setBlob( b.getBlob() );
515
//assertTrue( b.getClob().getSubString(1, 3).equals("fab") );
516
b.getClob().getSubString(1, 6);
517
//b.getClob().setString(1, "qwerty");
519
s.connection().commit();
523
b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
524
b.setClob( Hibernate.createClob("xcvfxvc xcvbx cvbx cvbx cvbxcvbxcvbxcvb") );
526
s.connection().commit();
530
b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
531
assertTrue( b.getClob().getSubString(1, 7).equals("xcvfxvc") );
532
//b.getClob().setString(5, "1234567890");
534
s.connection().commit();
538
/*InputStream is = getClass().getClassLoader().getResourceAsStream("jdbc20.pdf");
539
s = sessionsopenSession();
540
b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
541
System.out.println( is.available() );
542
int size = is.available();
543
b.setBlob( Hibernate.createBlob( is, is.available() ) );
545
s.connection().commit();
546
ResultSet rs = s.connection().createStatement().executeQuery("select datalength(blob_) from blobber where id=" + b.getId() );
548
assertTrue( size==rs.getInt(1) );
552
s = sessionsopenSession();
553
b = (Blobber) s.load( Blobber.class, new Integer( b.getId() ) );
554
File f = new File("C:/foo.pdf");
556
FileOutputStream fos = new FileOutputStream(f);
557
Blob blob = b.getBlob();
558
byte[] bytes = blob.getBytes( 1, (int) blob.length() );
559
System.out.println( bytes.length );
567
public void testSqlFunctionAsAlias() throws Exception {
568
String functionName = locateAppropriateDialectFunctionNameForAliasTest();
569
if (functionName == null) {
570
log.info("Dialect does not list any no-arg functions");
574
log.info("Using function named [" + functionName + "] for 'function as alias' test");
575
String query = "select " + functionName + " from Simple as " + functionName + " where " + functionName + ".id = 10";
577
Session s = openSession();
578
Transaction t = s.beginTransaction();
579
Simple simple = new Simple();
580
simple.setName("Simple 1");
581
s.save( simple, new Long(10) );
586
t = s.beginTransaction();
587
List result = s.find(query);
588
assertTrue( result.size() == 1 );
589
assertTrue(result.get(0) instanceof Simple);
590
s.delete( result.get(0) );
595
private String locateAppropriateDialectFunctionNameForAliasTest() {
596
for (Iterator itr = getDialect().getFunctions().entrySet().iterator(); itr.hasNext(); ) {
597
final Map.Entry entry = (Map.Entry) itr.next();
598
final SQLFunction function = (SQLFunction) entry.getValue();
599
if ( !function.hasArguments() && !function.hasParenthesesIfNoArguments() ) {
600
return (String) entry.getKey();
606
public void testCachedQueryOnInsert() throws Exception {
607
Session s = openSession();
608
Transaction t = s.beginTransaction();
609
Simple simple = new Simple();
610
simple.setName("Simple 1");
611
s.save( simple, new Long(10) );
616
t = s.beginTransaction();
617
Query q = s.createQuery("from Simple s");
618
List list = q.setCacheable(true).list();
619
assertTrue( list.size()==1 );
624
t = s.beginTransaction();
625
q = s.createQuery("from Simple s");
626
list = q.setCacheable(true).list();
627
assertTrue( list.size()==1 );
632
t = s.beginTransaction();
633
Simple simple2 = new Simple();
634
simple2.setCount(133);
635
s.save( simple2, new Long(12) );
640
t = s.beginTransaction();
641
q = s.createQuery("from Simple s");
642
list = q.setCacheable(true).list();
643
assertTrue( list.size()==2 );
648
t = s.beginTransaction();
649
q = s.createQuery("from Simple s");
650
list = q.setCacheable(true).list();
651
assertTrue( list.size()==2 );
652
Iterator i = list.iterator();
653
while ( i.hasNext() ) s.delete( i.next() );