|
1
25 package testsuite.simple;
26
27 import com.mysql.jdbc.SQLError;
28
29 import testsuite.BaseTestCase;
30
31 import java.sql.CallableStatement  ;
32 import java.sql.Connection ;
33 import java.sql.ResultSet ;
34 import java.sql.ResultSetMetaData ;
35 import java.sql.SQLException ;
36 import java.sql.Types ;
37
38 import java.util.Properties ;
39
40
47 public class CallableStatementTest extends BaseTestCase {
48
53 public CallableStatementTest(String name) {
54 super(name);
55
56 }
58
59
65
66 public void testInOutParams() throws Exception {
67 if (versionMeetsMinimum(5, 0)) {
68 CallableStatement storedProc = null;
69
70 try {
71 this.stmt
72 .executeUpdate("DROP PROCEDURE IF EXISTS testInOutParam");
73 this.stmt
74 .executeUpdate("create procedure testInOutParam(INOUT foo VARCHAR(15))\n"
75 + "begin\n"
76 + "select concat(foo, foo) INTO foo;\n"
77 + "end\n");
78
79 storedProc = this.conn.prepareCall("{call testInOutParam(?)}");
80
81 storedProc.setString(1, "abcd");
82 storedProc.registerOutParameter(1, Types.VARCHAR);
83
84 storedProc.execute();
85 String retrievedString = storedProc.getString(1);
86 assertTrue(retrievedString.equals("abcdabcd"));
87 } finally {
88 this.stmt.executeUpdate("DROP PROCEDURE testInOutParam");
89 }
90 }
91 }
92
93
99 public void testOutParams() throws Exception {
100 if (versionMeetsMinimum(5, 0)) {
101 CallableStatement storedProc = null;
102
103 try {
104 this.stmt
105 .executeUpdate("DROP PROCEDURE IF EXISTS testOutParam");
106 this.stmt
107 .executeUpdate("CREATE PROCEDURE testOutParam(x int, out y int)\n"
108 + "begin\n"
109 + "declare z int;\n"
110 + "set z = x+1, y = z;\n" + "end\n");
111
112 storedProc = this.conn.prepareCall("{call testOutParam(?, ?)}");
113
114 storedProc.setInt(1, 5);
115 storedProc.registerOutParameter(2, Types.INTEGER);
116
117 storedProc.execute();
118
119 System.out.println(storedProc);
120
121 int indexedOutParamToTest = storedProc.getInt(2);
122 int namedOutParamToTest = storedProc.getInt("y");
123
124 assertTrue("Named and indexed parameter are not the same",
125 indexedOutParamToTest == namedOutParamToTest);
126 assertTrue("Output value not returned correctly",
127 indexedOutParamToTest == 6);
128
129 storedProc.clearParameters();
131 storedProc.setInt("x", 32);
132 storedProc.registerOutParameter("y", Types.INTEGER);
133
134 storedProc.execute();
135
136 indexedOutParamToTest = storedProc.getInt(2);
137 namedOutParamToTest = storedProc.getInt("y");
138
139 assertTrue("Named and indexed parameter are not the same",
140 indexedOutParamToTest == namedOutParamToTest);
141 assertTrue("Output value not returned correctly",
142 indexedOutParamToTest == 33);
143
144 try {
145 storedProc.registerOutParameter("x", Types.INTEGER);
146 assertTrue(
147 "Should not be able to register an out parameter on a non-out parameter",
148 true);
149 } catch (SQLException sqlEx) {
150 if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
151 .getSQLState())) {
152 throw sqlEx;
153 }
154 }
155
156 try {
157 storedProc.registerOutParameter(1, Types.INTEGER);
158 assertTrue(
159 "Should not be able to register an out parameter on a non-out parameter",
160 true);
161 } catch (SQLException sqlEx) {
162 if (!SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
163 .getSQLState())) {
164 throw sqlEx;
165 }
166 }
167
168 try {
169 storedProc.getInt("x");
170 assertTrue(
171 "Should not be able to retreive an out parameter on a non-out parameter",
172 true);
173 } catch (SQLException sqlEx) {
174 if (!SQLError.SQL_STATE_COLUMN_NOT_FOUND.equals(sqlEx
175 .getSQLState())) {
176 throw sqlEx;
177 }
178 }
179 } finally {
180 this.stmt.executeUpdate("DROP PROCEDURE testOutParam");
181 }
182 }
183 }
184
185
191 public void testResultSet() throws Exception {
192 if (versionMeetsMinimum(5, 0)) {
193 CallableStatement storedProc = null;
194
195 try {
196 this.stmt
197 .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl1");
198 this.stmt
199 .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl2");
200 this.stmt
201 .executeUpdate("CREATE TABLE testSpResultTbl1 (field1 INT)");
202 this.stmt
203 .executeUpdate("INSERT INTO testSpResultTbl1 VALUES (1), (2)");
204 this.stmt
205 .executeUpdate("CREATE TABLE testSpResultTbl2 (field2 varchar(255))");
206 this.stmt
207 .executeUpdate("INSERT INTO testSpResultTbl2 VALUES ('abc'), ('def')");
208
209 this.stmt
210 .executeUpdate("DROP PROCEDURE IF EXISTS testSpResult");
211 this.stmt
212 .executeUpdate("CREATE PROCEDURE testSpResult()\n"
213 + "BEGIN\n"
214 + "SELECT field2 FROM testSpResultTbl2 WHERE field2='abc';\n"
215 + "UPDATE testSpResultTbl1 SET field1=2;\n"
216 + "SELECT field2 FROM testSpResultTbl2 WHERE field2='def';\n"
217 + "end\n");
218
219 storedProc = this.conn.prepareCall("{call testSpResult()}");
220
221 storedProc.execute();
222
223 this.rs = storedProc.getResultSet();
224
225 ResultSetMetaData rsmd = this.rs.getMetaData();
226
227 assertTrue(rsmd.getColumnCount() == 1);
228 assertTrue("field2".equals(rsmd.getColumnName(1)));
229 assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);
230
231 assertTrue(this.rs.next());
232
233 assertTrue("abc".equals(this.rs.getString(1)));
234
235 assertTrue(storedProc.getMoreResults());
239
240 ResultSet nextResultSet = storedProc.getResultSet();
241
242 rsmd = nextResultSet.getMetaData();
243
244 assertTrue(rsmd.getColumnCount() == 1);
245 assertTrue("field2".equals(rsmd.getColumnName(1)));
246 assertTrue(rsmd.getColumnType(1) == Types.VARCHAR);
247
248 assertTrue(nextResultSet.next());
249
250 assertTrue("def".equals(nextResultSet.getString(1)));
251
252 nextResultSet.close();
253
254 this.rs.close();
255
256 storedProc.execute();
257
258 } finally {
259 this.stmt
260 .executeUpdate("DROP PROCEDURE IF EXISTS testSpResult");
261 this.stmt
262 .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl1");
263 this.stmt
264 .executeUpdate("DROP TABLE IF EXISTS testSpResultTbl2");
265 }
266 }
267 }
268
269
275 public void testSPParse() throws Exception {
276
277 if (versionMeetsMinimum(5, 0)) {
278
279 CallableStatement storedProc = null;
280
281 try {
282
283 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
284 this.stmt
285 .executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
286 + "BEGIN\n" + "SELECT 1;\n" + "end\n");
287
288 storedProc = this.conn.prepareCall("{call testSpParse()}");
289
290 } finally {
291 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
292 }
293 }
294 }
295
296
302 public void testSPNoParams() throws Exception {
303
304 if (versionMeetsMinimum(5, 0)) {
305
306 CallableStatement storedProc = null;
307
308 try {
309
310 this.stmt
311 .executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
312 this.stmt.executeUpdate("CREATE PROCEDURE testSPNoParams()\n"
313 + "BEGIN\n" + "SELECT 1;\n" + "end\n");
314
315 storedProc = this.conn.prepareCall("{call testSPNoParams()}");
316 storedProc.execute();
317
318 } finally {
319 this.stmt
320 .executeUpdate("DROP PROCEDURE IF EXISTS testSPNoParams");
321 }
322 }
323 }
324
325
331 public void testSPCache() throws Exception {
332
333 if (versionMeetsMinimum(5, 0)) {
334
335 CallableStatement storedProc = null;
336
337 try {
338
339 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
340 this.stmt
341 .executeUpdate("CREATE PROCEDURE testSpParse(IN FOO VARCHAR(15))\n"
342 + "BEGIN\n" + "SELECT 1;\n" + "end\n");
343
344 int numIterations = 10000;
345
346 long startTime = System.currentTimeMillis();
347
348 for (int i = 0; i < numIterations; i++) {
349 storedProc = this.conn.prepareCall("{call testSpParse(?)}");
350 storedProc.close();
351 }
352
353 long elapsedTime = System.currentTimeMillis() - startTime;
354
355 System.out.println("Standard parsing/execution: " + elapsedTime
356 + " ms");
357
358 storedProc = this.conn.prepareCall("{call testSpParse(?)}");
359 storedProc.setString(1, "abc");
360 this.rs = storedProc.executeQuery();
361
362 assertTrue(this.rs.next());
363 assertTrue(this.rs.getInt(1) == 1);
364
365 Properties props = new Properties ();
366 props.setProperty("cacheCallableStmts", "true");
367
368 Connection cachedSpConn = getConnectionWithProps(props);
369
370 startTime = System.currentTimeMillis();
371
372 for (int i = 0; i < numIterations; i++) {
373 storedProc = cachedSpConn
374 .prepareCall("{call testSpParse(?)}");
375 storedProc.close();
376 }
377
378 elapsedTime = System.currentTimeMillis() - startTime;
379
380 System.out
381 .println("Cached parse stage: " + elapsedTime + " ms");
382
383 storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
384 storedProc.setString(1, "abc");
385 this.rs = storedProc.executeQuery();
386
387 assertTrue(this.rs.next());
388 assertTrue(this.rs.getInt(1) == 1);
389
390 } finally {
391 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testSpParse");
392 }
393 }
394 }
395
396
401 public static void main(String [] args) {
402 junit.textui.TestRunner.run(CallableStatementTest.class);
403 }
404 }
405 |
Popular Tags |
Java API By Example, From Geeks To Geeks.