|
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.SybaseDialect;
22 import org.hibernate.test.TestCase;
23
24
27 public class MSSQLTest extends TestCase {
28
29 public MSSQLTest(String str) {
30 super(str);
31 }
32
33 public void testHandSQL() {
34
35 if ( ! ( getDialect() instanceof SybaseDialect ) ) 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 SybaseDialect ) ) return;
101
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 List l = s.createSQLQuery("select {org.*}, {emp.*}, emp.regionCode from organization org left outer join employment emp on org.orgid = emp.employer")
115 .addEntity("org", Organization.class)
116 .addJoin("emp", "org.employments")
117 .addScalar("regionCode", Hibernate.STRING)
118 .list();
119
120 assertEquals( l.size(), 2 );
121
122 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")
123 .addEntity("org", Organization.class)
124 .addJoin("emp", "org.employments")
125 .addJoin("pers", "emp.employee")
126 .list();
127
128 assertEquals( l.size(), 1 );
129
130 s.delete(emp);
131 s.delete(gavin);
132 s.delete(ifa);
133 s.delete(jboss);
134
135 t.commit();
136 s.close();
137
138 }
139
140 public void testScalarValues() throws Exception {
141
142 if ( getDialect() instanceof DB2Dialect ) return;
144 Session s = openSession();
145 Transaction t = s.beginTransaction();
146
147 Organization ifa = new Organization("IFA");
148 Organization jboss = new Organization("JBoss");
149
150 Serializable idIfa = s.save(ifa);
151 Serializable idJBoss = s.save(jboss);
152
153 s.flush();
154
155 List result = s.getNamedQuery("orgNamesOnly").list();
156 assertTrue(result.contains("IFA"));
157 assertTrue(result.contains("JBOSS"));
158
159 t.commit();
160 s.close();
161
162 s = openSession();
163 t = s.beginTransaction();
164
165 Iterator iter = s.getNamedQuery("orgNamesAndOrgs").list().iterator();
166 Object [] o = (Object []) iter.next();
167 assertEquals(o[0], "IFA");
168 assertEquals(((Organization)o[1]).getName(), "IFA");
169 o = (Object []) iter.next();
170 assertEquals(o[0], "JBOSS");
171 assertEquals(((Organization)o[1]).getName(), "JBOSS");
172
173 t.commit();
174 s.close();
175
176 s = openSession();
177 t = s.beginTransaction();
178
179 iter = s.getNamedQuery("orgsAndOrgNames").list().iterator();
180 o = (Object []) iter.next();
181 assertEquals(o[0], "IFA");
182 assertEquals(((Organization)o[1]).getName(), "IFA");
183 o = (Object []) iter.next();
184 assertEquals(o[0], "JBOSS");
185 assertEquals(((Organization)o[1]).getName(), "JBOSS");
186
187 t.commit();
188 s.close();
189
190 s = openSession();
191 t = s.beginTransaction();
192
193 iter = s.getNamedQuery("orgIdsAndOrgNames").list().iterator();
194 o = (Object []) iter.next();
195 assertEquals(o[1], "IFA");
196 assertEquals(o[0], idIfa);
197 o = (Object []) iter.next();
198 assertEquals(o[1], "JBOSS");
199 assertEquals(o[0], idJBoss);
200
201 t.commit();
202 s.close();
203
204 }
205
206 public void testScalarStoredProcedure() throws HibernateException, SQLException {
207
208 if ( !(getDialect() instanceof SybaseDialect) ) return;
209
210 Session s = openSession();
211
212 Statement statement = s.connection().createStatement();
213 statement.execute("CREATE PROCEDURE simpleScalar @j int\r\n" +
214 "AS\r\n" +
215 "SELECT @j as value, \'getAll\' as name\r\n");
216 statement.close();
217 Query namedQuery = s.getNamedQuery("simpleScalar_MSSP");
218 namedQuery.setLong("number", 43);
219 List list = namedQuery.list();
220 Object o[] = (Object []) list.get(0);
221 assertEquals(o[0], "getAll");
222 assertEquals(o[1], new Long (43));
223 s.close();
224
225 }
226
227 public void testParameterHandling() throws HibernateException, SQLException {
228
229 if ( !(getDialect() instanceof SybaseDialect) ) return;
230 Session s = openSession();
231
232 Statement statement = s.connection().createStatement();
233 statement.execute("CREATE PROCEDURE paramHandling @j int, @i int\r\n" +
234 "AS\r\n" +
235 "SELECT @j as value, @i as value2\r\n");
236 statement.close();
237 Query namedQuery = s.getNamedQuery("paramhandling");
238 namedQuery.setLong(0, 10);
239 namedQuery.setLong(1, 20);
240 List list = namedQuery.list();
241 Object o[] = (Object []) list.get(0);
242 assertEquals(o[0], new Long (10));
243 assertEquals(o[1], new Long (20));
244 s.close();
245 }
246
247
248 public void testEntityStoredProcedure() throws HibernateException, SQLException {
249
250 if ( !(getDialect() instanceof SybaseDialect) ) return;
251 Session s = openSession();
252 Transaction t = s.beginTransaction();
253 Organization ifa = new Organization("IFA");
254 Organization jboss = new Organization("JBoss");
255 Person gavin = new Person("Gavin");
256 Employment emp = new Employment(gavin, jboss, "AU");
257 s.persist(ifa);
258 s.persist(jboss);
259 s.persist(gavin);
260 s.persist(emp);
261
262 Statement statement = s.connection().createStatement();
263 statement.execute("CREATE PROCEDURE selectAllEmployments AS\n" +
264 " SELECT EMPLOYEE, EMPLOYER, \n" +
265 " STARTDATE, ENDDATE, \n" +
266 " REGIONCODE, EID, VALUE, CURRENCY \n" +
267 " FROM EMPLOYMENT;");
268 statement.close();
269 Query namedQuery = s.getNamedQuery("selectAllEmployees_MSSP");
270 List list = namedQuery.list();
271 assertTrue(list.get(0) instanceof Employment);
272
273 t.commit();
274
275 s.close();
276 }
277
278
279 protected String [] getMappings() {
280 return new String [] { "sql/Employment.hbm.xml" };
281 }
282
283 public static Test suite() {
284 return new TestSuite(MSSQLTest.class);
285 }
286
287 public String getCacheConcurrencyStrategy() {
288 return null;
289 }
290 }
291
292 |
Popular Tags |
Java API By Example, From Geeks To Geeks.