SQL Stored Procedure in Spring:-
The StoredProcedure class is a superclass for object abstractions of RDBMS
stored procedures. This class is abstract, and its various execute(..) methods
have protected access, preventing use other than through a subclass.
The SimpleJdbcCall class can be used to call a stored procedure
with IN and OUT parameters. You can use this approach while
working with either of the RDBMS like Apache Derby, DB2, MySQL, Microsoft SQL
Server, Oracle, and Sybase.
The inherited sql property will be the name of the stored procedure in
the RDBMS.
To define a parameter for the StoredProcedure class, you use an SqlParameter
or one of its subclasses. You must specify the parameter name and SQL type in
the constructor like in the following code . The SQL type is specified using the
java.sql.Types constants.
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_name", Types.VARCHAR),
The first line with the SqlParameter declares an IN parameter. IN
parameters can be used for both stored procedure calls and for queries using the
SqlQuery and its subclasses covered in the following section.
The second line with the SqlOutParameter declares an out parameter
to be used in the stored procedure call. There is also an SqlInOutParameter
for InOut parameters, parameters that provide an in value to the procedure and
that also return a value.
Below is an example of a simple DAO that uses a
StoredProcedure to call a function, sysdate(),which comes with any
Oracle database. To use the stored procedure functionality you have to create a
class that extends StoredProcedure. In this example, the StoredProcedure class
is an inner class, but if you need to reuse the StoredProcedure you declare it
as a top-level class.
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class StoredProcedureDao {
private GetSysdateProcedure getSysdate;
@Autowired
public void init(DataSource dataSource) {
this.getSysdate = new GetSysdateProcedure(dataSource);}
public Date getSysdate() {
return getSysdate.execute();}
private class GetSysdateProcedure extends StoredProcedure {
private static final String SQL = "sysdate";
public GetSysdateProcedure(DataSource dataSource) {
setDataSource(dataSource);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("date", Types.DATE));
compile();}
public Date execute() {
// the sysdate sproc has no input parameters, so an empty Map is supplied...
Map<String, Object> results = execute(new HashMap<String, Object>());
Date sysdate = (Date) results.get("date");
return sysdate;
}}}