JdbcTemplate call example

[bchris22]

Hi,

I'm looking for a JdbcTemplate.call(CallableStatementCreator csc, java.util.List declaredParameters) example.

Thanks :)
Benno

[irbouho]

I am using oracle for this example
1. Stored procedure (very basic one ;))

CREATE OR REPLACE procedure procTest(pS OUT NUMBER,
pA IN NUMBER,
pB IN NUMBER)
is
begin
pS := pA + pB;
end;
/

2. JdbcTemplate.call

import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.commons.dbcp.BasicDataSource;

import org.springframework.jdbc.core.CallableStatementCre ator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;

public class OracleJdbc
{
private void execute() throws Exception {
//some DI will be welcome here
BasicDataSource ds = new BasicDataSource ();
ds.setDriverClassName ("oracle.jdbc.driver.OracleDriver");
ds.setUrl ("jdbc:oracle:thin:@localhost:1521:orcl");
ds.setUsername ("taha");
ds.setPassword ("manager");
JdbcTemplate template = new JdbcTemplate (ds);

List params = new ArrayList();
params.add (new SqlOutParameter("pS", Types.NUMERIC));

try {
Map results = template.call (new ProcCallableStatementCreator(10, 20), params);
System.out.println (results.get ("pS"));
} catch (Exception e) {
e.printStackTrace ();
}

ds.close ();
}

public static void main (String[] args) throws Exception {
new OracleJdbc().execute();
}

private class ProcCallableStatementCreator implements CallableStatementCreator {
private int a;
private int b;

public ProcCallableStatementCreator(int a, int b) {
this.a = a;
this.b = b;
}

public CallableStatement createCallableStatement(Connection conn) throws SQLException {
CallableStatement cs = conn.prepareCall("call procTest(?, ?, ?)");
cs.registerOutParameter (1, Types.NUMERIC);
cs.setInt (2, a);
cs.setInt (3, b);
return cs;
}
}
}

HTH

[bchris22]

Thanks for your example. It's clear now.
Cheers,
Benno

[priyanka_sinha]

Can anyone tell me how to invoke stored procedure using JDBCTemplate in Spring??
(My stored proc has first four input and last three output parameters)
CallableStatement callableStatement = conn.prepareCall("CALL ACRT1.SPBINVCO(?,?,?,?,?,?,?)");
callableStatement.setString(1,"LIVRTS");
callableStatement.setDate(2, java.sql.Date.valueOf("2006-09-12"));
callableStatement.setInt(3, 150551);
callableStatement.setString(4,"LETTER");
callableStatement.registerOutParameter(5, Types.CHAR);
callableStatement.registerOutParameter(6, Types.CHAR);
callableStatement.registerOutParameter(7, Types.CHAR);

Am getting the following error message -:
[Parameter 1 is not an OUTPUT parameter]; nested exception is java.sql.SQLExce
ption: Parameter 1 is not an OUTPUT parameter

[karldmoore]

Any reason your are using trying to use JdbcTemplate? Have you looked at the StoredProcedure class?
http://www.springframework.org/docs/reference/jdbc.html#jdbc-StoredProcedure

[priyanka_sinha]

My stored procedure is actually in DB2 so am trying to use JDBCTemplate .I just want to make a call to that from java.

[karldmoore]

My stored procedure is actually in DB2 so am trying to use JDBCTemplate .I just want to make a call to that from java.

I appreciate that, what I was trying to say is the Spring StoredProcedure class is the easiest way to do it. The link should help you.
http://www.springframework.org/docs/reference/jdbc.html#jdbc-StoredProcedure

[priyanka_sinha]

Thanx.I tried using StoredProcedure class and its working fine.

[priyanka_sinha]

Can anyone tell me how to extract ResultSet from Stored Procedure using Stored Procedure class of Spring??

[karldmoore]

If you search on the forums for SqlReturnResultSet there are lots of threads.

e.g.
http://forum.springframework.org/archive/index.php/t-9879.html
http://www.springframework.org/docs/api/org/springframework/jdbc/core/SqlReturnResultSet.html

[priyanka_sinha]

If we are going to map multiple actions as below in dispatcher servlet, how do we capture these events in Controller class.
Please provide the implementation.

<bean id="urlMapping" class="org.springframework.web.servlet.handler.Sim pleUrlHandlerMapping">
<property name="mappings">
<props>
<prop key="/index/welcome.html">myController</prop>
<prop key="/**/notwelcome.html">myController</prop>
<prop key="/*/user?.html">myController</prop>
</props>
</property>
</bean>
<bean id="myController" class="com.web.controller.MyController">
<property name="successView"><value>myView</value></property>
</bean>