Spring calls the Oracle Stored Procedure

1. Create a table

Create Table test_users <br/> (<br/> user_id varchar2 (10) Not null, <br/> name varchar2 (10) Not null, <br/> password varchar2 (20) not null <br/>)

2. Create a stored procedure

Create or replace package display_users_package is <br/> type search_results is ref cursor; <br/> procedure display_users_proc (results_out search_results, userid in test_users.user_id % type ); <br/> end display_users_package; </P> <p> Create or replace package body display_users_package is <br/> procedure display_users_proc (results_out search_results, userid in test_users.user_id % type) <br/> is <br/> begin <br/> If userid is not null then <br/> open results_out for select * From test_users where user_id like userid | '%'; <br/> else <br/> open results_out for select * From test_users; <br/> end if; <br/> end display_users_proc; <br/> end display_users_package; <br/>

3. Complete implementation code:

Import Java. SQL. callablestatement; <br/> Import Java. SQL. connection; <br/> Import Java. SQL. resultset; <br/> Import Java. SQL. sqlexception; <br/> Import Java. util. arraylist; <br/> Import Java. util. hashmap; <br/> Import Java. util. list; <br/> Import Java. util. map; </P> <p> Import javax. SQL. datasource; </P> <p> Import oracle. JDBC. oracletypes; </P> <p> Import Org. springframework. dao. dataaccessexception; <br/> Import o RG. springframework. JDBC. core. callablestatementcallback; <br/> Import Org. springframework. JDBC. core. callablestatementcreator; <br/> Import Org. springframework. JDBC. core. jdbctemplate; </P> <p> Import COM. spring. stored. procedure. util. datacontextutil; </P> <p>/** <br/> * @ author Jane Jiao <br/> */<br/> public class springstoredproce {</ p> <p> public list <map> execute (string storedproc, string Param S) {<br/> List <map> resultlist = NULL; <br/> try {<br/> final datasource DS = datacontextutil. getinstance (). getdatasource (); <br/> final jdbctemplate template = new jdbctemplate (DS); <br/> resultlist = (list <map> Custom template.exe cute (New proccallablestatementcreator (storedproc, Params ), <br/> New proccallablestatementcallback (); <br/>} catch (dataaccessexception e) {<br/> throw new runtimeexception ("e Xecute method error: dataaccessexception "+ E. getmessage (); <br/>}< br/> return resultlist; <br/>}</P> <p>/** <br/> * Create a callable statement in this connection. <br/> */<br/> private class proccallablestatementcreator implements callablestatementcreator {<br/> private string storedproc; <br/> private string Params; </P> <p>/** <br/> * constructs a callable statement. <br/> * @ Param St Oredproc the stored procedure's name. <br/> * @ Param Params input parameters. <br/> * @ Param outresultcount count of output result set. <br/> */<br/> Public proccallablestatementcreator (string storedproc, string Params) {<br/> This. params = Params; <br/> This. storedproc = storedproc; <br/>}</P> <p>/** <br/> * returns a callable statement <br/> * @ Param conn connection to use to create statemen T <br/> * @ return CS a callable statement <br/> */<br/> Public callablestatement createcallablestatement (connection conn) {<br/> stringbuffer storedprocname = new stringbuffer ("call"); <br/> storedprocname. append (storedproc + "("); <br/> // set output parameters <br/> storedprocname. append ("? "); <Br/> storedprocname. append (", "); </P> <p> // set input parameters <br/> storedprocname. append ("? "); <Br/> storedprocname. append (")"); </P> <p> callablestatement cs = NULL; <br/> try {<br/> // set the first parameter is oracletyep. cursor for each El Stored Procedure <br/> cs = Conn. preparecall (storedprocname. tostring (); <br/> CS. registeroutparameter (1, oracletypes. cursor); <br/> // set the sencond paramter <br/> CS. setobject (2, Params); <br/>} catch (sqlexception e) {<br/> throw new runtimeexception ("createcallablestatement method error: sqlexception" + E. getmessage (); <br/>}< br/> return Cs; <br/>}</P> <p>/** <br/> * The proccallablestatementcallback return a result object, <br/> * For example a collection of domain objects. <br/> */<br/> private class proccallablestatementcallback implements callablestatementcallback {</P> <p>/** <br/> * constructs a proccallablestatementcallback. <br/> */<br/> Public proccallablestatementcallback () {<br/>}</P> <p>/** <br/> * returns a list (MAP) collection. <br/> * @ Param CS object that can create a callablestatement given a connection <br/> * @ return resultslist a result object returned by the action, or null <br/> */<br/> Public object doincallablestatement (callablestatement CS) {<br/> List <map> resultsmap = new arraylist <map> (); <br/> try {<br/> cs.exe cute (); <br/> resultset rs = (resultset) Cs. getObject (1); <br/> while (RS. next () {<br/> Map <string, string> rowmap = new hashmap <string, string> (); <br/> rowmap. put ("userid", Rs. getstring ("user_id"); <br/> rowmap. put ("name", Rs. getstring ("name"); <br/> rowmap. put ("password", Rs. getstring ("password"); <br/> resultsmap. add (rowmap); <br/>}< br/> Rs. close (); <br/>}catch (sqlexception e) {<br/> throw new runtimeexception ("doincallablestatement method error: sqlexception" + E. getmessage (); <br/>}< br/> return resultsmap; <br/>}< br/>

4. Test code. Here the junit4 test is used:
Import static Org. JUnit. assert. assertnotnull; <br/> Import static Org. JUnit. assert. asserttrue; </P> <p> Import Java. util. list; <br/> Import Java. util. map; </P> <p> Import Org. JUnit. after; <br/> Import Org. JUnit. before; <br/> Import Org. JUnit. test; </P> <p>/** <br/> * @ author Jane Jiao <br/> */<br/> public class springstoredprocetest {</ p> <p> private springstoredproce; </P> <p>/** <br/> * @ throws Java. lang. exception <br/> */<br/> @ before <br/> Public void setup () throws exception {<br/> springstoredproce = new springstoredproce (); <br/>}</P> <p>/** <br/> * @ throws Java. lang. exception <br/> */<br/> @ After <br/> Public void teardown () throws exception {<br/> springstoredproce = NULL; <br/>}</P> <p>/** <br/> * Test Method for {@ link COM. hactl. listingframework. dao. springstoredproce # execute (Java. lang. string, Java. lang. string )}. <br/> */<br/> @ test <br/> Public void testexecute () {<br/> final string storedprocname = "display_users_package.display_users_proc "; <br/> final string Param = "test"; <br/> List <map> resultlist = springstoredproce.exe cute (storedprocname, Param); <br/> assertnotnull (resultlist ); <br/> asserttrue (resultlist. size ()> 0); <br/> for (INT I = 0; I <resultlist. size (); I ++) {<br/> map rowmap = resultlist. get (I); <br/> final string userid = rowmap. get ("userid "). tostring (); <br/> final string name = rowmap. get ("name "). tostring (); <br/> final string Password = rowmap. get ("password "). tostring (); <br/> system. out. println ("user_id =" + userid + "/T name =" + name + "/T Password =" + password ); <br/>}</P> <p >}< br/>

5. Test output:

User_id = test1 name = AA Password = AA <br/> user_id = Test2 name = BB Password = BB <br/> user_id = test3 name = Cc Password = cc