用于调用存储过程的Spring JDBC模板

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template?

使用现代(大约2012年)Spring JDBC模板调用存储过程的正确方法是什么?

Say, I have a stored procedure that declares both IN and OUT parameters, something like this:

比方说，我有一个存储过程，它同时声明输入和输出参数，就像这样:

mypkg.doSomething(
    id OUT int,
    name IN String,
    date IN Date
)

I have come across CallableStatementCreator based approaches where we have to explicitly register IN and OUT parameters. Consider the following method in JdbcTemplate class:

我遇到过基于CallableStatementCreator的方法，在这些方法中，我们必须显式地注册输入和输出参数。请考虑JdbcTemplate类中的以下方法:

public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)

Of course, I do know that I can use it like so:

当然，我知道我可以这样使用:

List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();

declaredParameters.add(new SqlOutParameter("id", Types.INTEGER));
declaredParameters.add(new SqlParameter("name", Types.VARCHAR));
declaredParameters.add(new SqlParameter("date", Types.DATE));

this.jdbcTemplate.call(new CallableStatementCreator() {

    @Override
    CallableStatement createCallableStatement(Connection con) throws SQLException {
        CallableStatement stmnt = con.createCall("{mypkg.doSomething(?, ?, ?)}");

        stmnt.registerOutParameter("id", Types.INTEGER);
        stmnt.setString("name", "<name>");
        stmnt.setDate("date", <date>);

        return stmnt;
    }
}, declaredParameters);

What is the purpose of declaredParameters when I am already registering them in my csc implementation? In other words, why would I need to pass in a csc when spring can simply do con.prepareCall(sql) internally? Basically, can't I pass in either one of them instead of both of them?

当我已经在csc实现中注册它们时，声明参数的目的是什么?换句话说，为什么我需要在spring可以简单地在内部执行con.prepareCall(sql)时通过csc呢?基本上，我不能把它们中的任何一个都传进去而不是两个都传进去吗?

Or, is there a much better way to call stored procedures (using Spring JDBC Template) than what I have come across so far?

或者，有比我到目前为止遇到的更好的方法调用存储过程(使用Spring JDBC模板)吗?

Note: You may find many questions that appear to have a similar title but they are not the same as this one.

注意:你可能会发现很多问题都有相似的标题，但是它们和这个题目不一样。

4 个解决方案

#1

There are a number of ways to call stored procedures in Spring.

在Spring中有许多方法可以调用存储过程。

If you use CallableStatementCreator to declare parameters, you will be using Java's standard interface of CallableStatement, i.e register out parameters and set them separately. Using SqlParameter abstraction will make your code cleaner.

如果您使用CallableStatementCreator来声明参数，您将使用Java的CallableStatement标准接口i。登记参数并分别设置。使用SqlParameter抽象将使您的代码更清晰。

I recommend you looking at SimpleJdbcCall. It may be used like this:

我建议您查看SimpleJdbcCall。它可以这样使用:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName(schema)
    .withCatalogName(package)
    .withProcedureName(procedure)();
...
jdbcCall.addDeclaredParameter(new SqlParameter(paramName, OracleTypes.NUMBER));
...
jdbcCall.execute(callParams);

For simple procedures you may use jdbcTemplate's update method:

对于简单的过程，您可以使用jdbcTemplate的更新方法:

jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);

#2

I generally prefer to extend Spring based StoredProcedure class to execute stored procedures.

我通常更喜欢扩展基于Spring的存储过程类来执行存储过程。

You need to create your class constructor and need to call StoredProcedure class constructor in it. This super class constructor accepts DataSource and procedure name.

您需要创建类构造函数，并需要在其中调用StoredProcedure类构造函数。这个超类构造函数接受数据源和过程名。

Example code:

示例代码:

public class ProcedureExecutor extends StoredProcedure {
      public ProcedureExecutor(DataSource ds, String funcNameorSPName) {
        super(ds, funcNameorSPName);
        declareParameter(new SqlOutParameter("v_Return", Types.VARCHAR, null, new SqlReturnType() {
                public Object getTypeValue(CallableStatement cs,
                     int paramIndex, int sqlType, String typeName) throws SQLException {
                final String str = cs.getString(paramIndex);
                return str;
            }           
        }));    
        declareParameter(new SqlParameter("your parameter",
                Types.VARCHAR));
        //set below param true if you want to call database function 
        setFunction(true);
        compile();
        }

Override execute method of stored procedure call as below

重写存储过程调用的执行方法，如下所示

public Map<String, Object> execute(String someParams) {
             final Map<String, Object> inParams = new HashMap<String, Object>(8);
             inParams.put("my param", "some value");
             Map outMap = execute(inParams);
             System.out.println("outMap:" + outMap);
             return outMap;
         }

Hope this helps you.

希望这能帮助你。

#3

Here are the ways to call the stored procedures from java

以下是从java调用存储过程的方法

1. Using CallableStatement:

1。使用CallableStatement:

 connection = jdbcTemplate.getDataSource().getConnection();
  CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
  callableStatement.setString(1, "FirstName");
  callableStatement.setString(2, " LastName");
  callableStatement.registerOutParameter(3, Types.VARCHAR);
  callableStatement.executeUpdate();

Here we externally manage the resource closing

在这里，我们外部管理资源关闭

2. Using CallableStatementCreator

2。使用CallableStatementCreator

 List paramList = new ArrayList();
    paramList.add(new SqlParameter(Types.VARCHAR));
    paramList.add(new SqlParameter(Types.VARCHAR));
    paramList.add(new SqlOutParameter("msg", Types.VARCHAR));

    Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

    @Override
    public CallableStatement createCallableStatement(Connection connection)
    throws SQLException {

    CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
    callableStatement.setString(1, "FirstName");
            callableStatement.setString(2, " LastName");
            callableStatement.registerOutParameter(3, Types.VARCHAR);
    return callableStatement;

    }
    }, paramList);

3. Use SimpleJdbcCall:

3所示。使用SimpleJdbcCall:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)

.withProcedureName("STORED_PROCEDURE_NAME");

Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("firstName", "FirstNameValue");
inParamMap.put("lastName", "LastNameValue");
SqlParameterSource in = new MapSqlParameterSource(inParamMap);


Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);

4. Use StoredProcedure class of org.springframework.jdbc.object

4所示。使用storedprocess类的org.springframework.jdbc.object。

The Code:
First Create subclass of StoredProcedure: MyStoredProcedure

class MyStoredProcedure extends StoredProcedure {

public MyStoredProcedure(JdbcTemplate jdbcTemplate, String name) {

super(jdbcTemplate, name);
setFunction(false);

}

}

Use MyStoredProcedure to call database stored procedure:


//Pass jdbcTemlate and name of the stored Procedure.
MyStoredProcedure myStoredProcedure = new MyStoredProcedure(jdbcTemplate, "PROC_TEST");

//Sql parameter mapping
SqlParameter fNameParam = new SqlParameter("fName", Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter("lName", Types.VARCHAR);
SqlOutParameter msgParam = new SqlOutParameter("msg", Types.VARCHAR);
SqlParameter[] paramArray = {fNameParam, lNameParam, msgParam};


myStoredProcedure.setParameters(paramArray);
myStoredProcedure.compile();


//Call stored procedure
Map storedProcResult = myStoredProcedure.execute("FirstNameValue", " LastNameValue");

Reference

参考

#4

One more way to call stored procedure is:

另一种调用存储过程的方法是:

sql="execute Procedure_Name ?";
Object search[]={Id};
List<ClientInvestigateDTO> client=jdbcTemplateObject.query(sql,search,new 
   ClientInvestigateMapper());

In this example 'ClientInvestigateDTO' is the POJO class and 'ClientInvestigateMapper' is the mapper class.'client' stores all the result you get on calling the stored procedure.

在这个例子中，“clientinvestigative to”是POJO类，而“clientresearchatemapper”是映射器类。“客户端”存储调用存储过程的所有结果。

#1