Use spring jdbctemplate to call a stored procedure

Original address http://alex007.iteye.com/blog/869544

Spring simplejdbctemplate encapsulates the call of the stored procedure, but unfortunately it can only be used in the jdk1.5 environment and cannot be used in the jdk1.4 environment. jdbctemplate is fully applicable to the jdk1.4 environment, the following describes how to use jdbctemplate to call an oracle stored procedure:

1) stored procedure calls without return values

Stored Procedure:

CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2) ASBEGIN    INSERT INTO TESTTABLE (ID,NAME) VALUES (PARAM1, PARAM2);END TESTPRO;

Java code:

package com.dragon.test;import org.springframework.jdbc.core.JdbcTemplate;public class JdbcTemplateTest {  private JdbcTemplate jdbcTemplate;  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  this.jdbcTemplate = jdbcTemplate;  }  public void test(){     this.jdbcTemplate.execute("call testpro('p1','p2')");  }}

Note: The testtable (ID, name) is used in the Stored Procedure testpro, which must be created in advance.

2) stored procedures with returned values (non-result sets)

Stored Procedure:

CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,PARAM2 OUT VARCHAR2) AS  BEGIN       SELECT INTO PARAM2 FROM TESTTABLE WHERE ID= PARAM1;   END TESTPRO;

Java code:

Public void test () {string param2value = (string) jdbctemplate.exe cute (New callablestatementcreator () {public callablestatement createcallablestatement (connection con) throws sqlexception {string storedproc = "{call testpro (?,?)} "; // The called SQL callablestatement cs = con. preparecall (storedproc); CS. setstring (1, "p1"); // sets the value of the input parameter CS. registeroutparameter (2, oracletypes. varchar); // register the type of the output parameter return Cs; }}, new callablestatementcallback () {public object doincallablestatement (callablestatement CS) throws sqlexception, dataaccessexception {cs.exe cute (); return CS. getstring (2); // get the output parameter value }});}

Note: The value 2 in CS. getstring (2) is the index value corresponding to the out column in the stored procedure (the first parameter index is 1, and so on)

3) stored procedures with returned values (result set)
Because all return values in Oracle stored procedures are returned through the out parameter, the list is no exception. However, because it is a set, general parameters cannot be used. pagkage must be used, which consists of two parts:
1. Create a package as follows:

CREATE OR REPLACE PACKAGE TESTPACKAGE AS  TYPE TEST_CURSOR IS REF CURSOR;END TESTPACKAGE; 

2. Create a stored procedure as follows:

CREATE OR REPLACE PROCEDURE TESTPRO(PARAM1 IN VARCHAR2,test_cursor out TESTPACKAGE.TEST_CURSOR) ISBEGIN     OPEN test_cursor FOR SELECT * FROM TESTTABLE;END TESTPRO; 

The list is returned by using the cursor as an out parameter.

Java code:

Public void test () {list resultlist = (list) jdbctemplate.exe cute (New callablestatementcreator () {public callablestatement createcallablestatement (connection con) throws sqlexception {string storedproc = "{call testpro (?,?)} "; // The called SQL callablestatement cs = con. preparecall (storedproc); CS. setstring (1, "p1"); // sets the value of the input parameter CS. registeroutparameter (2, oracletypes. cursor); // register the type of the output parameter return Cs; }}, new callablestatementcallback () {public object doincallablestatement (callablestatement CS) throws sqlexception, dataaccessexception {list resultsmap = new arraylist (); cs.exe cute (); resultset rs = (resultset) Cs. getObject (2); // get the value of a row of the cursor while (RS. next () {// convert the returned values of each row to map. Map rowmap = new hashmap (); rowmap. put ("ID", Rs. getstring ("ID"); rowmap. put ("name", Rs. getstring ("name"); resultsmap. add (rowmap);} Rs. close (); Return resultsmap;}); For (INT I = 0; I <resultlist. size (); I ++) {map rowmap = (MAP) resultlist. get (I); string id = rowmap. get ("ID "). tostring (); string name = rowmap. get ("name "). tostring (); system. out. println ("ID =" + ID + "; name =" + name );}}