KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > springframework > jdbc > object > StoredProcedureTests


1 /*
2  * Copyright 2002-2005 the original author or authors.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */

16
17 package org.springframework.jdbc.object;
18
19 import java.sql.CallableStatement JavaDoc;
20 import java.sql.Connection JavaDoc;
21 import java.sql.ResultSet JavaDoc;
22 import java.sql.SQLException JavaDoc;
23 import java.sql.Types JavaDoc;
24 import java.util.HashMap JavaDoc;
25 import java.util.List JavaDoc;
26 import java.util.Map JavaDoc;
27
28 import javax.sql.DataSource JavaDoc;
29
30 import org.easymock.MockControl;
31
32 import org.springframework.dao.DataAccessException;
33 import org.springframework.dao.InvalidDataAccessApiUsageException;
34 import org.springframework.jdbc.AbstractJdbcTests;
35 import org.springframework.jdbc.BadSqlGrammarException;
36 import org.springframework.jdbc.core.CallableStatementCreator;
37 import org.springframework.jdbc.core.JdbcTemplate;
38 import org.springframework.jdbc.core.ParameterMapper;
39 import org.springframework.jdbc.core.RowCallbackHandler;
40 import org.springframework.jdbc.core.RowMapper;
41 import org.springframework.jdbc.core.SqlOutParameter;
42 import org.springframework.jdbc.core.SqlParameter;
43 import org.springframework.jdbc.core.SqlReturnResultSet;
44 import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
45 import org.springframework.jdbc.datasource.ConnectionHolder;
46 import org.springframework.jdbc.support.SQLExceptionTranslator;
47 import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
48 import org.springframework.transaction.support.TransactionSynchronizationManager;
49
50 /**
51  * @author Thomas Risberg
52  * @author Trevor Cook
53  * @author Rod Johnson
54  */

55 public class StoredProcedureTests extends AbstractJdbcTests {
56
57     private MockControl ctrlCallable;
58     private CallableStatement JavaDoc mockCallable;
59
60     protected void setUp() throws Exception JavaDoc {
61         super.setUp();
62
63         ctrlCallable = MockControl.createControl(CallableStatement JavaDoc.class);
64         mockCallable = (CallableStatement JavaDoc) ctrlCallable.getMock();
65     }
66
67     protected void tearDown() throws Exception JavaDoc {
68         super.tearDown();
69         if (shouldVerify()) {
70             ctrlCallable.verify();
71         }
72     }
73
74     protected void replay() {
75         super.replay();
76         ctrlCallable.replay();
77     }
78
79     public void testNoSuchStoredProcedure() throws Exception JavaDoc {
80         SQLException JavaDoc sex =
81             new SQLException JavaDoc(
82                 "Syntax error or access violation exception",
83                 "42000");
84         mockCallable.execute();
85         ctrlCallable.setThrowable(sex);
86         mockCallable.close();
87         ctrlCallable.setVoidCallable();
88
89         mockConnection.prepareCall(
90             "{call " + NoSuchStoredProcedure.SQL + "()}");
91         ctrlConnection.setReturnValue(mockCallable);
92
93         replay();
94
95         NoSuchStoredProcedure sproc = new NoSuchStoredProcedure(mockDataSource);
96         try {
97             sproc.execute();
98             fail("Shouldn't succeed in running stored procedure which doesn't exist");
99         } catch (BadSqlGrammarException ex) {
100             // OK
101
}
102     }
103
104     private void testAddInvoice(final int amount, final int custid)
105         throws Exception JavaDoc {
106         AddInvoice adder = new AddInvoice(mockDataSource);
107         int id = adder.execute(amount, custid);
108         assertEquals(4, id);
109     }
110
111     public void testAddInvoices() throws Exception JavaDoc {
112         mockCallable.setObject(1, new Integer JavaDoc(1106), Types.INTEGER);
113         ctrlCallable.setVoidCallable();
114         mockCallable.setObject(2, new Integer JavaDoc(3), Types.INTEGER);
115         ctrlCallable.setVoidCallable();
116         mockCallable.registerOutParameter(3, Types.INTEGER);
117         ctrlCallable.setVoidCallable();
118         mockCallable.execute();
119         ctrlCallable.setReturnValue(false);
120         mockCallable.getUpdateCount();
121         ctrlCallable.setReturnValue(-1);
122         mockCallable.getObject(3);
123         ctrlCallable.setReturnValue(new Integer JavaDoc(4));
124         mockCallable.getWarnings();
125         ctrlCallable.setReturnValue(null);
126         mockCallable.close();
127         ctrlCallable.setVoidCallable();
128
129         mockConnection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}");
130         ctrlConnection.setReturnValue(mockCallable);
131
132         replay();
133
134         testAddInvoice(1106, 3);
135     }
136
137     public void testAddInvoicesWithinTransaction() throws Exception JavaDoc {
138         mockCallable.setObject(1, new Integer JavaDoc(1106), Types.INTEGER);
139         ctrlCallable.setVoidCallable();
140         mockCallable.setObject(2, new Integer JavaDoc(3), Types.INTEGER);
141         ctrlCallable.setVoidCallable();
142         mockCallable.registerOutParameter(3, Types.INTEGER);
143         ctrlCallable.setVoidCallable();
144         mockCallable.execute();
145         ctrlCallable.setReturnValue(false);
146         mockCallable.getUpdateCount();
147         ctrlCallable.setReturnValue(-1);
148         mockCallable.getObject(3);
149         ctrlCallable.setReturnValue(new Integer JavaDoc(4));
150         mockCallable.getWarnings();
151         ctrlCallable.setReturnValue(null);
152         mockCallable.close();
153         ctrlCallable.setVoidCallable();
154
155         mockConnection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}");
156         ctrlConnection.setReturnValue(mockCallable);
157
158         replay();
159
160         TransactionSynchronizationManager.bindResource(
161             mockDataSource,
162             new ConnectionHolder(mockConnection));
163
164         try {
165             testAddInvoice(1106, 3);
166         }
167         finally {
168             TransactionSynchronizationManager.unbindResource(mockDataSource);
169         }
170     }
171
172         
173     /**
174      * Confirm no connection was used to get metadata.
175      * Does not use superclass replay mechanism.
176      * @throws Exception
177      */

178     public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator() throws Exception JavaDoc {
179         mockCallable.setObject(1, new Integer JavaDoc(11), Types.INTEGER);
180         ctrlCallable.setVoidCallable(1);
181         mockCallable.registerOutParameter(2, Types.INTEGER);
182         ctrlCallable.setVoidCallable(1);
183         mockCallable.execute();
184         ctrlCallable.setReturnValue(false, 1);
185         mockCallable.getUpdateCount();
186         ctrlCallable.setReturnValue(-1);
187         mockCallable.getObject(2);
188         ctrlCallable.setReturnValue(new Integer JavaDoc(5), 1);
189         mockCallable.getWarnings();
190         ctrlCallable.setReturnValue(null);
191         mockCallable.close();
192         ctrlCallable.setVoidCallable(1);
193         // Must call this here as we're not using setUp()/tearDown() mechanism
194
ctrlCallable.replay();
195
196         ctrlConnection = MockControl.createControl(Connection JavaDoc.class);
197         mockConnection = (Connection JavaDoc) ctrlConnection.getMock();
198         mockConnection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}");
199         ctrlConnection.setReturnValue(mockCallable, 1);
200         mockConnection.close();
201         ctrlConnection.setVoidCallable(1);
202         ctrlConnection.replay();
203         
204         MockControl dsControl = MockControl.createControl(DataSource JavaDoc.class);
205         DataSource JavaDoc localDs = (DataSource JavaDoc) dsControl.getMock();
206         localDs.getConnection();
207         dsControl.setReturnValue(mockConnection, 1);
208         dsControl.replay();
209
210         class TestJdbcTemplate extends JdbcTemplate {
211             int calls;
212             public Map JavaDoc call(CallableStatementCreator csc, List JavaDoc declaredParameters) throws DataAccessException {
213                 calls++;
214                 return super.call(csc, declaredParameters);
215             }
216
217         }
218         TestJdbcTemplate t = new TestJdbcTemplate();
219         t.setDataSource(localDs);
220         // Will fail without the following, because we're not able to get a connection from the
221
// DataSource here if we need to to create an ExceptionTranslator
222
t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
223         StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);
224         
225         assertEquals(sp.execute(11), 5);
226         assertEquals(1, t.calls);
227         
228         dsControl.verify();
229         ctrlCallable.verify();
230         ctrlConnection.verify();
231     }
232     
233     /**
234      * Confirm our JdbcTemplate is used
235      * @throws Exception
236      */

237     public void testStoredProcedureConfiguredViaJdbcTemplate() throws Exception JavaDoc {
238         mockCallable.setObject(1, new Integer JavaDoc(1106), Types.INTEGER);
239         ctrlCallable.setVoidCallable();
240         mockCallable.registerOutParameter(2, Types.INTEGER);
241         ctrlCallable.setVoidCallable();
242         mockCallable.execute();
243         ctrlCallable.setReturnValue(false);
244         mockCallable.getUpdateCount();
245         ctrlCallable.setReturnValue(-1);
246         mockCallable.getObject(2);
247         ctrlCallable.setReturnValue(new Integer JavaDoc(4));
248         mockCallable.getWarnings();
249         ctrlCallable.setReturnValue(null);
250         mockCallable.close();
251         ctrlCallable.setVoidCallable();
252
253         mockConnection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}");
254         ctrlConnection.setReturnValue(mockCallable);
255
256         replay();
257         JdbcTemplate t = new JdbcTemplate();
258         t.setDataSource(mockDataSource);
259         StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);
260     
261         assertEquals(sp.execute(1106), 4);
262     }
263
264     public void testNullArg() throws Exception JavaDoc {
265         MockControl ctrlResultSet = MockControl.createControl(ResultSet JavaDoc.class);
266         ResultSet JavaDoc mockResultSet = (ResultSet JavaDoc) ctrlResultSet.getMock();
267         mockResultSet.next();
268         ctrlResultSet.setReturnValue(true);
269
270         mockCallable.setNull(1, Types.VARCHAR);
271         ctrlCallable.setVoidCallable();
272         mockCallable.execute();
273         ctrlCallable.setReturnValue(false);
274         mockCallable.getUpdateCount();
275         ctrlCallable.setReturnValue(-1);
276         mockCallable.getWarnings();
277         ctrlCallable.setReturnValue(null);
278         mockCallable.close();
279         ctrlCallable.setVoidCallable();
280
281         mockConnection.prepareCall("{call " + NullArg.SQL + "(?)}");
282         ctrlConnection.setReturnValue(mockCallable);
283
284         replay();
285         ctrlResultSet.replay();
286
287         NullArg na = new NullArg(mockDataSource);
288         na.execute((String JavaDoc) null);
289     }
290
291     public void testUnnamedParameter() throws Exception JavaDoc {
292         replay();
293         try {
294             UnnamedParameterStoredProcedure unp =
295                 new UnnamedParameterStoredProcedure(mockDataSource);
296             fail("Shouldn't succeed in creating stored procedure with unnamed parameter");
297         } catch (InvalidDataAccessApiUsageException idaauex) {
298             // OK
299
}
300     }
301
302     public void testMissingParameter() throws Exception JavaDoc {
303         replay();
304
305         try {
306             MissingParameterStoredProcedure mp =
307                 new MissingParameterStoredProcedure(mockDataSource);
308             mp.execute();
309             fail("Shouldn't succeed in running stored procedure with missing required parameter");
310         } catch (InvalidDataAccessApiUsageException idaauex) {
311             // OK
312
}
313     }
314
315     public void testStoredProcedureExceptionTranslator() throws Exception JavaDoc {
316         SQLException JavaDoc sex =
317             new SQLException JavaDoc(
318                 "Syntax error or access violation exception",
319                 "42000");
320         mockCallable.execute();
321         ctrlCallable.setThrowable(sex);
322         mockCallable.close();
323         ctrlCallable.setVoidCallable();
324
325         mockConnection.prepareCall(
326             "{call " + StoredProcedureExceptionTranslator.SQL + "()}");
327         ctrlConnection.setReturnValue(mockCallable);
328
329         replay();
330
331         StoredProcedureExceptionTranslator sproc =
332             new StoredProcedureExceptionTranslator(mockDataSource);
333         try {
334             sproc.execute();
335             fail("Custom exception should be thrown");
336         } catch (CustomDataException ex) {
337             // OK
338
}
339     }
340
341     public void testStoredProcedureWithResultSet() throws Exception JavaDoc {
342         MockControl ctrlResultSet = MockControl.createControl(ResultSet JavaDoc.class);
343         ResultSet JavaDoc mockResultSet = (ResultSet JavaDoc) ctrlResultSet.getMock();
344         mockResultSet.next();
345         ctrlResultSet.setReturnValue(true);
346         mockResultSet.next();
347         ctrlResultSet.setReturnValue(true);
348         mockResultSet.next();
349         ctrlResultSet.setReturnValue(false);
350         mockCallable.getWarnings();
351         ctrlCallable.setReturnValue(null);
352         mockResultSet.close();
353         ctrlResultSet.setVoidCallable();
354
355         mockCallable.execute();
356         ctrlCallable.setReturnValue(true);
357         mockCallable.getUpdateCount();
358         ctrlCallable.setReturnValue(-1);
359         mockCallable.getResultSet();
360         ctrlCallable.setReturnValue(mockResultSet);
361         mockCallable.getMoreResults();
362         ctrlCallable.setReturnValue(false);
363         mockCallable.getUpdateCount();
364         ctrlCallable.setReturnValue(-1);
365         mockCallable.close();
366         ctrlCallable.setVoidCallable();
367
368         mockConnection.prepareCall(
369             "{call " + StoredProcedureWithResultSet.SQL + "()}");
370         ctrlConnection.setReturnValue(mockCallable);
371
372         replay();
373         ctrlResultSet.replay();
374
375         StoredProcedureWithResultSet sproc =
376             new StoredProcedureWithResultSet(mockDataSource);
377         sproc.execute();
378
379         ctrlResultSet.verify();
380         assertEquals(2, sproc.getCount());
381         
382     }
383     
384     public void testStoredProcedureWithResultSetMapped() throws Exception JavaDoc {
385         MockControl ctrlResultSet = MockControl.createControl(ResultSet JavaDoc.class);
386         ResultSet JavaDoc mockResultSet = (ResultSet JavaDoc) ctrlResultSet.getMock();
387         mockResultSet.next();
388         ctrlResultSet.setReturnValue(true);
389         mockResultSet.getString(2);
390         ctrlResultSet.setReturnValue("Foo");
391         mockResultSet.next();
392         ctrlResultSet.setReturnValue(true);
393         mockResultSet.getString(2);
394         ctrlResultSet.setReturnValue("Bar");
395         mockResultSet.next();
396         ctrlResultSet.setReturnValue(false);
397         mockResultSet.close();
398         ctrlResultSet.setVoidCallable();
399
400         mockCallable.execute();
401         ctrlCallable.setReturnValue(true);
402         mockCallable.getUpdateCount();
403         ctrlCallable.setReturnValue(-1);
404         mockCallable.getResultSet();
405         ctrlCallable.setReturnValue(mockResultSet);
406         mockCallable.getMoreResults();
407         ctrlCallable.setReturnValue(false);
408         mockCallable.getUpdateCount();
409         ctrlCallable.setReturnValue(-1);
410         mockCallable.getWarnings();
411         ctrlCallable.setReturnValue(null);
412         mockCallable.close();
413         ctrlCallable.setVoidCallable();
414
415         mockConnection.prepareCall(
416             "{call " + StoredProcedureWithResultSetMapped.SQL + "()}");
417         ctrlConnection.setReturnValue(mockCallable);
418
419         replay();
420         ctrlResultSet.replay();
421
422         StoredProcedureWithResultSetMapped sproc =
423             new StoredProcedureWithResultSetMapped(mockDataSource);
424         Map JavaDoc res = sproc.execute();
425
426         ctrlResultSet.verify();
427         
428         List JavaDoc rs = (List JavaDoc) res.get("rs");
429         assertEquals(2, rs.size());
430         assertEquals("Foo", rs.get(0));
431         assertEquals("Bar", rs.get(1));
432
433     }
434
435     public void testParameterMapper() throws Exception JavaDoc {
436         mockCallable.setString(1, "EasyMock for interface java.sql.Connection");
437         ctrlCallable.setVoidCallable();
438         mockCallable.registerOutParameter(2, Types.VARCHAR);
439         ctrlCallable.setVoidCallable();
440         mockCallable.execute();
441         ctrlCallable.setReturnValue(false);
442         mockCallable.getUpdateCount();
443         ctrlCallable.setReturnValue(-1);
444         mockCallable.getObject(2);
445         ctrlCallable.setReturnValue("OK");
446         mockCallable.getWarnings();
447         ctrlCallable.setReturnValue(null);
448         mockCallable.close();
449         ctrlCallable.setVoidCallable();
450
451         mockConnection.prepareCall(
452             "{call " + ParameterMapperStoredProcedure.SQL + "(?, ?)}");
453         ctrlConnection.setReturnValue(mockCallable);
454
455         replay();
456         ParameterMapperStoredProcedure pmsp =
457             new ParameterMapperStoredProcedure(mockDataSource);
458         Map JavaDoc out = pmsp.executeTest();
459         assertEquals("OK", out.get("out"));
460     }
461
462     public void testSqlTypeValue() throws Exception JavaDoc {
463         int[] testVal = new int[] {1, 2};
464         mockCallable.getConnection();
465         ctrlCallable.setDefaultReturnValue(mockConnection);
466         mockCallable.setObject(1, testVal, Types.ARRAY);
467         ctrlCallable.setVoidCallable();
468         mockCallable.registerOutParameter(2, Types.VARCHAR);
469         ctrlCallable.setVoidCallable();
470         mockCallable.execute();
471         ctrlCallable.setReturnValue(false);
472         mockCallable.getUpdateCount();
473         ctrlCallable.setReturnValue(-1);
474         mockCallable.getObject(2);
475         ctrlCallable.setReturnValue("OK");
476         mockCallable.getWarnings();
477         ctrlCallable.setReturnValue(null);
478         mockCallable.close();
479         ctrlCallable.setVoidCallable();
480
481         mockConnection.prepareCall(
482             "{call " + SqlTypeValueStoredProcedure.SQL + "(?, ?)}");
483         ctrlConnection.setReturnValue(mockCallable);
484
485         replay();
486         SqlTypeValueStoredProcedure stvsp =
487             new SqlTypeValueStoredProcedure(mockDataSource);
488         Map JavaDoc out = stvsp.executeTest(testVal);
489         assertEquals("OK", out.get("out"));
490     }
491
492     private class StoredProcedureConfiguredViaJdbcTemplate extends StoredProcedure {
493         public static final String JavaDoc SQL = "configured_via_jt";
494         public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
495             setJdbcTemplate(t);
496             setSql(SQL);
497             declareParameter(new SqlParameter("intIn", Types.INTEGER));
498             declareParameter(new SqlOutParameter("intOut", Types.INTEGER));
499             compile();
500         }
501
502         public int execute(int intIn) {
503             Map JavaDoc in = new HashMap JavaDoc();
504             in.put("intIn", new Integer JavaDoc(intIn));
505             Map JavaDoc out = execute(in);
506             Number JavaDoc intOut = (Number JavaDoc) out.get("intOut");
507             return intOut.intValue();
508         }
509     }
510
511     private class AddInvoice extends StoredProcedure {
512         public static final String JavaDoc SQL = "add_invoice";
513         public AddInvoice(DataSource JavaDoc ds) {
514             setDataSource(ds);
515             setSql(SQL);
516             declareParameter(new SqlParameter("amount", Types.INTEGER));
517             declareParameter(new SqlParameter("custid", Types.INTEGER));
518             declareParameter(new SqlOutParameter("newid", Types.INTEGER));
519             compile();
520         }
521
522         public int execute(int amount, int custid) {
523             Map JavaDoc in = new HashMap JavaDoc();
524             in.put("amount", new Integer JavaDoc(amount));
525             in.put("custid", new Integer JavaDoc(custid));
526             Map JavaDoc out = execute(in);
527             Number JavaDoc id = (Number JavaDoc) out.get("newid");
528             return id.intValue();
529         }
530     }
531
532     private class NullArg extends StoredProcedure {
533
534         public static final String JavaDoc SQL = "takes_null";
535
536         public NullArg(DataSource JavaDoc ds) {
537             setDataSource(ds);
538             setSql(SQL);
539             declareParameter(new SqlParameter("ptest", Types.VARCHAR));
540             compile();
541         }
542
543         public void execute(String JavaDoc s) {
544             Map JavaDoc in = new HashMap JavaDoc();
545             in.put("ptest", s);
546             Map JavaDoc out = execute(in);
547         }
548     }
549
550     private class NoSuchStoredProcedure extends StoredProcedure {
551
552         public static final String JavaDoc SQL = "no_sproc_with_this_name";
553
554         public NoSuchStoredProcedure(DataSource JavaDoc ds) {
555             setDataSource(ds);
556             setSql(SQL);
557             compile();
558         }
559
560         public void execute() {
561             execute(new HashMap JavaDoc());
562         }
563     }
564
565     private class UncompiledStoredProcedure extends StoredProcedure {
566         public static final String JavaDoc SQL = "uncompile_sp";
567         public UncompiledStoredProcedure(DataSource JavaDoc ds) {
568             super(ds, SQL);
569         }
570
571         public void execute() {
572             execute(new HashMap JavaDoc());
573         }
574     }
575
576     private class UnnamedParameterStoredProcedure extends StoredProcedure {
577
578         public UnnamedParameterStoredProcedure(DataSource JavaDoc ds) {
579             super(ds, "unnamed_parameter_sp");
580             declareParameter(new SqlParameter(Types.INTEGER));
581             compile();
582         }
583
584         public void execute(int id) {
585             Map JavaDoc in = new HashMap JavaDoc();
586             in.put("id", new Integer JavaDoc(id));
587             Map JavaDoc out = execute(in);
588
589         }
590     }
591
592     private class MissingParameterStoredProcedure extends StoredProcedure {
593
594         public MissingParameterStoredProcedure(DataSource JavaDoc ds) {
595             setDataSource(ds);
596             setSql("takes_string");
597             declareParameter(new SqlParameter("mystring", Types.VARCHAR));
598             compile();
599         }
600
601         public void execute() {
602             execute(new HashMap JavaDoc());
603         }
604     }
605
606     private class StoredProcedureWithResultSet extends StoredProcedure {
607         public static final String JavaDoc SQL = "sproc_with_result_set";
608
609         private int count = 0;
610
611         public StoredProcedureWithResultSet(DataSource JavaDoc ds) {
612             setDataSource(ds);
613             setSql(SQL);
614             declareParameter(
615                 new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
616             compile();
617         }
618
619         public void execute() {
620             execute(new HashMap JavaDoc());
621         }
622
623         public int getCount() {
624             return count;
625         }
626
627         private class RowCallbackHandlerImpl implements RowCallbackHandler {
628             public void processRow(ResultSet JavaDoc rs) throws SQLException JavaDoc {
629                 count++;
630             }
631         }
632
633     }
634
635     private class StoredProcedureWithResultSetMapped extends StoredProcedure {
636         public static final String JavaDoc SQL = "sproc_with_result_set";
637
638         public StoredProcedureWithResultSetMapped(DataSource JavaDoc ds) {
639             setDataSource(ds);
640             setSql(SQL);
641             declareParameter(
642                 new SqlReturnResultSet("rs", new RowMapperImpl()));
643             compile();
644         }
645
646         public Map JavaDoc execute() {
647             Map JavaDoc out = execute(new HashMap JavaDoc());
648             return out;
649         }
650
651         private class RowMapperImpl implements RowMapper {
652             public Object JavaDoc mapRow(ResultSet JavaDoc rs, int rowNum) throws SQLException JavaDoc {
653                 return rs.getString(2);
654             }
655         }
656
657     }
658
659     private class ParameterMapperStoredProcedure extends StoredProcedure {
660
661         public static final String JavaDoc SQL = "parameter_mapper_sp";
662
663         public ParameterMapperStoredProcedure(DataSource JavaDoc ds) {
664             setDataSource(ds);
665             setSql(SQL);
666             declareParameter(new SqlParameter("in", Types.VARCHAR));
667             declareParameter(new SqlOutParameter("out", Types.VARCHAR));
668             compile();
669         }
670
671         public Map JavaDoc executeTest() {
672             Map JavaDoc out = null;
673             out = execute(new TestParameterMapper());
674             return out;
675         }
676         
677         private class TestParameterMapper implements ParameterMapper {
678             
679             private TestParameterMapper() {
680             }
681             
682             public Map JavaDoc createMap(Connection JavaDoc conn) throws SQLException JavaDoc {
683                 Map JavaDoc inParms = new HashMap JavaDoc();
684                 String JavaDoc testValue = conn.toString();
685                 inParms.put("in", testValue);
686                 return inParms;
687             }
688         
689         }
690
691         
692     }
693
694     private class SqlTypeValueStoredProcedure extends StoredProcedure {
695
696         public static final String JavaDoc SQL = "sql_type_value_sp";
697
698         public SqlTypeValueStoredProcedure(DataSource JavaDoc ds) {
699                 setDataSource(ds);
700                 setSql(SQL);
701                 declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
702                 declareParameter(new SqlOutParameter("out", Types.VARCHAR));
703                 compile();
704         }
705
706         public Map JavaDoc executeTest(final int[] inValue) {
707             Map JavaDoc in = new HashMap JavaDoc(1);
708             in.put("in", new AbstractSqlTypeValue() {
709                 public Object JavaDoc createTypeValue(Connection JavaDoc con, int type, String JavaDoc typeName) {
710                     //assertEquals(Connection.class, con.getClass());
711
//assertEquals(Types.ARRAY, type);
712
//assertEquals("NUMBER", typeName);
713
return inValue;
714                 }
715             });
716             Map JavaDoc out = null;
717             out = execute(in);
718             return out;
719         }
720     }
721
722     private class StoredProcedureExceptionTranslator extends StoredProcedure {
723         public static final String JavaDoc SQL = "no_sproc_with_this_name";
724         public StoredProcedureExceptionTranslator(DataSource JavaDoc ds) {
725             setDataSource(ds);
726             setSql(SQL);
727             getJdbcTemplate().setExceptionTranslator(new SQLExceptionTranslator() {
728                 public DataAccessException translate(
729                     String JavaDoc task,
730                     String JavaDoc sql,
731                     SQLException JavaDoc sqlex) {
732                     return new CustomDataException(sql, sqlex);
733                 }
734
735             });
736             compile();
737         }
738
739         public void execute() {
740             execute(new HashMap JavaDoc());
741         }
742     }
743
744     private class CustomDataException extends DataAccessException {
745
746         public CustomDataException(String JavaDoc s) {
747             super(s);
748         }
749
750         public CustomDataException(String JavaDoc s, Throwable JavaDoc ex) {
751             super(s, ex);
752         }
753     }
754
755 }
756
Popular Tags