用于调用存储过程的 Spring JDBC 模板 [英] Spring JDBC Template for calling Stored Procedures

问题描述

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

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

比如说，我有一个声明 IN 和 OUT 参数的存储过程，如下所示:

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
)

我遇到过基于 CallableStatementCreator 的方法，我们必须显式注册 IN 和 OUT 参数.考虑 JdbcTemplate 类中的以下方法:

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

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);

当我已经在我的 csc 实现中注册 declaredParameters 时，它们的目的是什么?换句话说，当 spring 可以在内部简单地执行 con.prepareCall(sql) 时，为什么我需要传入 csc ?基本上，我不能传入其中一个而不是两个吗?

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?

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

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

注意:您可能会发现许多标题看似相似但与本题不同的问题.

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

推荐答案

Spring 中有多种调用存储过程的方法.

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

如果你使用CallableStatementCreator来声明参数，你将使用Java的标准CallableStatement接口，即注册出参数并单独设置它们.使用 SqlParameter 抽象将使您的代码更简洁.

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.

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

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

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

对于简单的程序，您可以使用 jdbcTemplate 的 update 方法:

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

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

这篇关于用于调用存储过程的 Spring JDBC 模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！