Calling stored procedures in spring

Springframework.jdbc.object.StoredProcedure is the action object that corresponds to the stored procedure call, and it obtains the corresponding underlying API support through its parent class Org.springframework.jdbc.object.SqlCall (CallableStatementCreator), and builds on this basis the execution method that invokes the stored procedure.

StoredProcedure is an abstract class, so you need to implement the subclass to encapsulate calls to a particular stored procedure, remember when we explained the stored procedure defined when JdbcTemplate called the stored procedure?

CREATE PROCEDURE CountTable(IN tableName varchar(1000),OUT sqlStr varchar(1000) , INOUT v INT)
BEGIN
　　 set @flag = v;
　　 set @sql = CONCAT('select count(*) into @res from ' , tableName , ' where ACTIVE_FLAG=?');
　　 PREPARE stmt FROM @sql;
　　 EXECUTE stmt using @flag;
　　 DEALLOCATE PREPARE stmt;
　　 set v = @res;
　　 set sqlStr = @sql;
END

By inheriting StoredProcedure, we can provide a corresponding action object for the invocation of the stored procedure:

public class Counttablestoredprocedure extends StoredProcedure {

private static final String Procedur E_name = "Counttable"; 　　

public static final String in_parameter_name = "TableName";
public static final String out_parameter_name = "Sqlstr";
public static final String inout_parameter_name = "V";

Public counttablestoredprocedure (DataSource DataSource)
{
Super (datasource,procedure_name); //Setfunction (TRUE);
Declareparameter (New SqlParameter (In_parameter_name,types.varchar));
Declareparameter (New Sqloutparameter (Out_parameter_name,types.varchar));
Declareparameter (New Sqlinoutparameter (Inout_parameter_name,types.integer));
Compile ();
}

Public counttableresult docounttable (String Tablename,integer v)
{
Map paramap = new HashMap ();
Paramap.put (In_parameter_name, TableName);
Paramap.put (Inout_parameter_name, v);

Map Resultmap = execute (paramap); 　　　　

Counttableresult result = new Counttableresult ();
Result.setsql ((String) Resultmap.get (out_parameter_name));
Result.setcount ((Integer) Resultmap.get (inout_parameter_name));
return result;
}
}

About the stored procedure Action object, part of the details we need to focus on:

The SQL of the stored procedure Action object is the name of the stored procedure, not the SQL statement in the real sense, when we call the Compile method, StoredProcedure's parent class sqlcall a stored procedure call statement that conforms to the SQL92 standard in real sense according to the stored procedure name you provide, similar to "{Call counttable (?,?,?)}" The form.

Because our counttablestoredprocedure is called only for counttable stored procedures, the name of the stored procedure is declared as a constant at the beginning of the class:

private static final String PROCEDURE_NAME = "CountTable";If you have more than one stored procedure with the same parameter order, and the result processing is the same, you can also declare the name of the stored procedure as a variable, depending entirely on the specific application scenario.

In the construction method, we will "Setfunction" (true); The comment is dropped because the counttable we call is not a function, and if the type of stored procedure you want to invoke is a function, you need to set the value of "function" to True by this method. To tell StoredProcedure to treat the call differently.

It is almost shine practice to declare parameters by Declareparameter before Complie, but when you use Declareparameter in StoredProcedure you have to be aware of it: