|
1 package org.hibernate.test.sql;
3
4 import java.io.Serializable  ;
5 import java.sql.SQLException ;
6 import java.sql.Statement ;
7 import java.util.Date ;
8 import java.util.Iterator ;
9 import java.util.List ;
10
11 import junit.framework.Test;
12 import junit.framework.TestSuite;
13
14 import org.hibernate.Hibernate;
15 import org.hibernate.HibernateException;
16 import org.hibernate.LockMode;
17 import org.hibernate.Query;
18 import org.hibernate.Session;
19 import org.hibernate.Transaction;
20 import org.hibernate.dialect.DB2Dialect;
21 import org.hibernate.dialect.Oracle9Dialect;
22 import org.hibernate.test.TestCase;
23
24
27 public class SQLTest extends TestCase {
28
29 public SQLTest(String str) {
30 super(str);
31 }
32
33 public void testHandSQL() {
34
35 if ( getDialect() instanceof DB2Dialect ) return;
36
37 Session s = openSession();
38 Transaction t = s.beginTransaction();
39 Organization ifa = new Organization("IFA");
40 Organization jboss = new Organization("JBoss");
41 Person gavin = new Person("Gavin");
42 Employment emp = new Employment(gavin, jboss, "AU");
43 Serializable orgId = s.save(jboss);
44 Serializable orgId2 = s.save(ifa);
45 s.save(gavin);
46 s.save(emp);
47 t.commit();
48
49 t = s.beginTransaction();
50 Person christian = new Person("Christian");
51 s.save(christian);
52 Employment emp2 = new Employment(christian, jboss, "EU");
53 s.save(emp2);
54 t.commit();
55 s.close();
56
57 getSessions().evict(Organization.class);
58 getSessions().evict(Person.class);
59 getSessions().evict(Employment.class);
60
61 s = openSession();
62 t = s.beginTransaction();
63 jboss = (Organization) s.get(Organization.class, orgId);
64 assertEquals( jboss.getEmployments().size(), 2 );
65 emp = (Employment) jboss.getEmployments().iterator().next();
66 gavin = emp.getEmployee();
67 assertEquals( gavin.getName(), "GAVIN" );
68 assertEquals( s.getCurrentLockMode(gavin), LockMode.UPGRADE );
69 emp.setEndDate( new Date () );
70 Employment emp3 = new Employment(gavin, jboss, "US");
71 s.save(emp3);
72 t.commit();
73 s.close();
74
75 s = openSession();
76 t = s.beginTransaction();
77 Iterator iter = s.getNamedQuery("allOrganizationsWithEmployees").list().iterator();
78 assertTrue ( iter.hasNext() );
79 Organization o = (Organization) iter.next();
80 assertEquals( o.getEmployments().size(), 3 );
81 Iterator iter2 = o.getEmployments().iterator();
82 while ( iter2.hasNext() ) {
83 Employment e = (Employment) iter2.next();
84 s.delete(e);
85 }
86 iter2 = o.getEmployments().iterator();
87 while ( iter2.hasNext() ) {
88 Employment e = (Employment) iter2.next();
89 s.delete( e.getEmployee() );
90 }
91 s.delete(o);
92 assertFalse ( iter.hasNext() );
93 s.delete(ifa);
94 t.commit();
95 s.close();
96 }
97
98 public void testSQLQueryInterface() {
99
100 if ( getDialect() instanceof DB2Dialect ) return;
102 Session s = openSession();
103 Transaction t = s.beginTransaction();
104 Organization ifa = new Organization("IFA");
105 Organization jboss = new Organization("JBoss");
106 Person gavin = new Person("Gavin");
107 Employment emp = new Employment(gavin, jboss, "AU");
108
109 s.persist(ifa);
110 s.persist(jboss);
111 s.persist(gavin);
112 s.persist(emp);
113
114
115
116 List l = s.createSQLQuery("select {org.*}, {emp.*}, emp.regionCode from organization org left outer join employment emp on org.orgid = emp.employer")
117 .addEntity("org", Organization.class)
118 .addJoin("emp", "org.employments")
119 .addScalar("regionCode", Hibernate.STRING)
120 .list();
121
122 assertEquals( l.size(), 2 );
123
124 l = s.createSQLQuery("select {org.*}, {emp.*}, {pers.*} from organization org join employment emp on org.orgid = emp.employer join person pers on pers.perid = emp.employee")
125 .addEntity("org", Organization.class)
126 .addJoin("emp", "org.employments")
127 .addJoin("pers", "emp.employee")
128 .list();
129
130 assertEquals( l.size(), 1 );
131
132 s.delete(emp);
133 s.delete(gavin);
134 s.delete(ifa);
135 s.delete(jboss);
136
137 t.commit();
138 s.close();
139
140 }
141
142 public void testResultSetMappingDefinition() {
143
144 if ( getDialect() instanceof DB2Dialect ) return;
146 Session s = openSession();
147 Transaction t = s.beginTransaction();
148 Organization ifa = new Organization("IFA");
149 Organization jboss = new Organization("JBoss");
150 Person gavin = new Person("Gavin");
151 Employment emp = new Employment(gavin, jboss, "AU");
152
153 s.persist(ifa);
154 s.persist(jboss);
155 s.persist(gavin);
156 s.persist(emp);
157
158 List l = s.createSQLQuery("select {org.*}, {emp.*}, emp.regionCode from organization org left outer join employment emp on org.orgid = emp.employer")
159 .setResultSetMapping( "org-emp-regionCode")
160 .list();
161
162 assertEquals( l.size(), 2 );
163
164 l = s.createSQLQuery("select {org.*}, {emp.*}, {pers.*} from organization org join employment emp on org.orgid = emp.employer join person pers on pers.perid = emp.employee")
165 .setResultSetMapping( "org-emp-person" )
166 .list();
167
168 assertEquals( l.size(), 1 );
169
170 s.delete(emp);
171 s.delete(gavin);
172 s.delete(ifa);
173 s.delete(jboss);
174
175 t.commit();
176 s.close();
177
178 }
179
180 public void testScalarValues() throws Exception {
181
182 if ( getDialect() instanceof DB2Dialect ) return;
184 Session s = openSession();
185 Transaction t = s.beginTransaction();
186
187 Organization ifa = new Organization("IFA");
188 Organization jboss = new Organization("JBoss");
189
190 Serializable idIfa = s.save(ifa);
191 Serializable idJBoss = s.save(jboss);
192
193 s.flush();
194
195 List result = s.getNamedQuery("orgNamesOnly").list();
196 assertTrue(result.contains("IFA"));
197 assertTrue(result.contains("JBOSS"));
198
199 t.commit();
200 s.close();
201
202 s = openSession();
203 t = s.beginTransaction();
204
205 Iterator iter = s.getNamedQuery("orgNamesAndOrgs").list().iterator();
206 Object [] o = (Object []) iter.next();
207 assertEquals(o[0], "IFA");
208 assertEquals(((Organization)o[1]).getName(), "IFA");
209 o = (Object []) iter.next();
210 assertEquals(o[0], "JBOSS");
211 assertEquals(((Organization)o[1]).getName(), "JBOSS");
212
213 t.commit();
214 s.close();
215
216 s = openSession();
217 t = s.beginTransaction();
218
219 iter = s.getNamedQuery("orgsAndOrgNames").list().iterator();
220 o = (Object []) iter.next();
221 assertEquals(o[0], "IFA");
222 assertEquals(((Organization)o[1]).getName(), "IFA");
223 o = (Object []) iter.next();
224 assertEquals(o[0], "JBOSS");
225 assertEquals(((Organization)o[1]).getName(), "JBOSS");
226
227 t.commit();
228 s.close();
229
230 s = openSession();
231 t = s.beginTransaction();
232
233 iter = s.getNamedQuery("orgIdsAndOrgNames").list().iterator();
234 o = (Object []) iter.next();
235 assertEquals(o[1], "IFA");
236 assertEquals(o[0], idIfa);
237 o = (Object []) iter.next();
238 assertEquals(o[1], "JBOSS");
239 assertEquals(o[0], idJBoss);
240
241 s.delete(ifa);
242 s.delete(jboss);
243 t.commit();
244 s.close();
245
246 }
247
248 public void testFailOnNoAddEntityOrScalar() {
249 Session s = openSession();
250 try {
251 s.createSQLQuery("select {org.*}, {emp.*}, emp.regionCode from organization org left outer join employment emp on org.orgid = emp.employer").list();
252 fail("Should throw an exception since no addEntity nor addScalar has been performed.");
253 } catch(HibernateException he) {
254
255 }
256 s.close();
257
258 }
259 public void testScalarStoredProcedure() throws HibernateException, SQLException {
260
261 if( !(getDialect() instanceof Oracle9Dialect)) return;
262
263 Session s = openSession();
264
265 Statement statement = s.connection().createStatement();
266 statement.execute("CREATE OR REPLACE FUNCTION simpleScalar (j number) " +
267 " RETURN SYS_REFCURSOR " +
268 "AS " +
269 " st_cursor SYS_REFCURSOR; " +
270 "BEGIN " +
271 " OPEN st_cursor FOR " +
272 " SELECT j as value, \'getAll\' as name from dual; " +
273 " RETURN st_cursor; " +
274 "END;");
275 statement.close();
276 Query namedQuery = s.getNamedQuery("simpleScalar_SP");
277 namedQuery.setLong("number", 43);
278 List list = namedQuery.list();
279 Object o[] = (Object []) list.get(0);
280 assertEquals(o[0], "getAll");
281 assertEquals(o[1], new Long (43));
282 s.close();
283 }
284
285 public void testEntityStoredProcedure() throws HibernateException, SQLException {
286
287 if( !(getDialect() instanceof Oracle9Dialect)) return;
288
289 Session s = openSession();
290
291 Transaction t = s.beginTransaction();
292 Statement statement = s.connection().createStatement();
293 statement.execute("CREATE OR REPLACE FUNCTION allEmployments \n" +
294 " RETURN SYS_REFCURSOR \n" +
295 "AS \n" +
296 " st_cursor SYS_REFCURSOR; \n" +
297 "BEGIN \n" +
298 " OPEN st_cursor FOR \n" +
299 " SELECT EMPLOYEE, EMPLOYER, \n" +
300 " STARTDATE, ENDDATE, \n" +
301 " REGIONCODE, EID, VALUE, CURRENCY \n" +
302 " FROM EMPLOYMENT; \n" +
303 " RETURN st_cursor; \n " +
304 "END;\n");
305 statement.close();
306 t.commit();
307
308 t = s.beginTransaction();
309 Organization ifa = new Organization("IFA");
310 Organization jboss = new Organization("JBoss");
311 Person gavin = new Person("Gavin");
312 Employment emp = new Employment(gavin, jboss, "AU");
313 s.persist(ifa);
314 s.persist(jboss);
315 s.persist(gavin);
316 s.persist(emp);
317 Query namedQuery = s.getNamedQuery("allEmployments_SP");
318 List list = namedQuery.list();
319 assertTrue(list.get(0) instanceof Employment);
320 s.delete(ifa);
321 s.delete(jboss);
322 s.delete(gavin);
323 s.delete(emp);
324 t.commit();
325
326 s.close();
327 }
328
329 public void testParameterHandling() throws HibernateException, SQLException {
330 if( !(getDialect() instanceof Oracle9Dialect)) return;
331
332 Session s = openSession();
333
334 Statement statement = s.connection().createStatement();
335 statement.execute("CREATE OR REPLACE FUNCTION testParamHandling (j number, i number) " +
336 " RETURN SYS_REFCURSOR " +
337 "AS " +
338 " st_cursor SYS_REFCURSOR; " +
339 "BEGIN " +
340 " OPEN st_cursor FOR " +
341 " SELECT j as value, i as value2 from dual; " +
342 " RETURN st_cursor; " +
343 "END;");
344 statement.close();
345 Query namedQuery = s.getNamedQuery("paramhandling");
346 namedQuery.setLong(0, 10);
347 namedQuery.setLong(1, 20);
348 List list = namedQuery.list();
349 Object o[] = (Object []) list.get(0);
350 assertEquals(o[0], new Long (10));
351 assertEquals(o[1], new Long (20));
352 s.close();
353 }
354
355 public void testMappedAliasStrategy() {
356 if ( getDialect() instanceof DB2Dialect ) return;
358 Session s = openSession();
359
360 Transaction t = s.beginTransaction();
361 Organization ifa = new Organization("IFA");
362 Organization jboss = new Organization("JBoss");
363 Person gavin = new Person("Gavin");
364 Employment emp = new Employment(gavin, jboss, "AU");
365 Serializable orgId = s.save(jboss);
366 Serializable orgId2 = s.save(ifa);
367 s.save(gavin);
368 s.save(emp);
369 t.commit();
370 s.close();
371
372 s = openSession();
373 t = s.beginTransaction();
374 Query namedQuery = s.getNamedQuery("AllEmploymentAsMapped");
375
376 List list = namedQuery.list();
377 assertEquals(1,list.size());
378
379 Employment emp2 = (Employment) list.get(0);
380 assertEquals(emp2.getEmploymentId(), emp.getEmploymentId() );
381 assertEquals(emp2.getStartDate().getDate(), emp.getStartDate().getDate() );
382 assertEquals(emp2.getEndDate(), emp.getEndDate() );
383 t.commit();
384 s.close();
385
386 s = openSession();
387 t = s.beginTransaction();
388 namedQuery = s.getNamedQuery("EmploymentAndPerson");
389
390 list = namedQuery.list();
391 assertEquals(1,list.size() );
392 Object [] objs = (Object []) list.get(0);
393 assertEquals(2, objs.length);
394 emp2 = (Employment) objs[0];
395 gavin = (Person) objs[1];
396 s.delete(emp2);
397 s.delete(jboss);
398 s.delete(gavin);
399 s.delete(ifa);
400 t.commit();
401 s.close();
402 }
403
404 public void testAutoDetectAliasing() {
405 if ( getDialect() instanceof DB2Dialect ) return;
407 Session s = openSession();
408
409 Transaction t = s.beginTransaction();
410 Organization ifa = new Organization("IFA");
411 Organization jboss = new Organization("JBoss");
412 Person gavin = new Person("Gavin");
413 Employment emp = new Employment(gavin, jboss, "AU");
414 Serializable orgId = s.save(jboss);
415 Serializable orgId2 = s.save(ifa);
416 s.save(gavin);
417 s.save(emp);
418 t.commit();
419 s.close();
420
421 s = openSession();
422 t = s.beginTransaction();
423 Query namedQuery = s.createSQLQuery("SELECT * FROM EMPLOYMENT").addEntity( Employment.class.getName() );
424
425 List list = namedQuery.list();
426 assertEquals( 1,list.size() );
427
428 Employment emp2 = (Employment) list.get(0);
429 assertEquals(emp2.getEmploymentId(), emp.getEmploymentId() );
430 assertEquals(emp2.getStartDate().getDate(), emp.getStartDate().getDate() );
431 assertEquals(emp2.getEndDate(), emp.getEndDate() );
432
433 s.clear();
434
435 Query queryWithCollection = s.getNamedQuery("organizationEmploymentsExplicitAliases");
436 queryWithCollection.setLong("id", jboss.getId() );
437 list = queryWithCollection.list();
438 assertEquals(list.size(),1);
439
440 s.clear();
441
442 Query queryWithJoin = s.createSQLQuery(
443 "SELECT org.orgid as {org.id}, org.name as {org.name}, {emp.*} FROM ORGANIZATION org LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER ")
444 .addEntity("org", Organization.class).addJoin("emp", "org.employments");
445
446 queryWithJoin.list();
447 list = queryWithJoin.list();
448 assertEquals( 2,list.size() );
449
450 s.clear();
451
452 queryWithJoin = s.createSQLQuery(
453 "SELECT org.orgid as {org.id}, org.name as {org.name}, emp.employer as {emp.key}, emp.empid as {emp.element}, {emp.element.*} FROM ORGANIZATION org LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER ")
454 .addEntity("org", Organization.class).addJoin("emp", "org.employments");
455
456 list = queryWithJoin.list();
457 assertEquals( 2,list.size() );
458
459 s.clear();
460
461 queryWithJoin = s.createSQLQuery(
462 "SELECT org.orgid as {org.id}, org.name as {org.name}, emp.employer as {emp.key}, emp.empid as {emp.element}, {emp.element.*} FROM ORGANIZATION org LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER ")
463 .addEntity("org", Organization.class).addJoin("emp", "org.employments");
464
465 list = queryWithJoin.list();
466 assertEquals( 2,list.size() );
467
468 s.clear();
469
470 queryWithJoin = s.getNamedQuery("organizationreturnproperty");
471 queryWithJoin.list();
472
473 list = queryWithJoin.list();
474 assertEquals( 2,list.size() );
475
476 t.commit();
477 s.close();
478
479 s = openSession();
480 t = s.beginTransaction();
481 s.delete(emp2);
482
483 s.delete(jboss);
484 s.delete(gavin);
485 s.delete(ifa);
486 t.commit();
487 s.close();
488
489 }
490
491 protected String [] getMappings() {
492 return new String [] { "sql/OracleEmployment.hbm.xml" };
493 }
494
495 public static Test suite() {
496 return new TestSuite(SQLTest.class);
497 }
498
499 public String getCacheConcurrencyStrategy() {
500 return null;
501 }
502 }
503
504 |
Popular Tags |
Java API By Example, From Geeks To Geeks.