I am using Spring persistence framework for my project. I want to call oracle function or stored procedure from this framework.
我正在为我的项目使用Spring持久性框架。我想从这个框架调用oracle函数或存储过程。
Can anybody suggest how can I achieve this.
任何人都可以建议我如何实现这一目标。
Please give solution for both * oracle function and *stored procedure.
请为* oracle函数和*存储过程提供解决方案。
Thanks.
3 个解决方案
#1
Assuming you are referring to JdbcTemplate:
假设您指的是JdbcTemplate:
jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException{
CallableStatement cs = con.prepareCall("{call MY_STORED_PROCEDURE(?, ?, ?)}");
cs.setInt(1, ...); // first argument
cs.setInt(2, ...); // second argument
cs.setInt(3, ...); // third argument
return cs;
}
},
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException{
cs.execute();
return null; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
}
);
Calling a function is almost identical:
调用函数几乎完全相同:
jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) {
CallableStatement cs = con.prepareCall("{? = call MY_FUNCTION(?, ?, ?)}");
cs.registerOutParameter(1, Types.INTEGER); // or whatever type your function returns.
// Set your arguments
cs.setInt(2, ...); // first argument
cs.setInt(3, ...); // second argument
cs.setInt(4, ...); // third argument
return cs;
}
},
new CallableStatementCallback {
public Object doInCallableStatement(CallableStatement cs) {
cs.execute();
int result = cs.getInt(1);
return result; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
}
);
#2
Simpler way of calling a Oracle function in Spring is subclassing StoredProcedure like below
在Spring中调用Oracle函数的简单方法是继承StoredProcedure,如下所示
public class MyStoredProcedure extends StoredProcedure{
private static final String SQL = "package.function";
public MyStoredProcedure(DataSource ds){
super(ds,SQL);
declareParameter(new SqlOutParameter("param_out",Types.NUMERIC));
declareParameter(new SqlParameter("param_in",Types.NUMERIC));
setFunction(true);//you must set this as it distinguishes it from a sproc
compile();
}
public String execute(Long rdsId){
Map in = new HashMap();
in.put("param_in",rdsId);
Map out = execute(in);
if(!out.isEmpty())
return out.get("param_out").toString();
else
return null;
}
}
And call it like this
并称之为这样
@Autowired DataSource ds;
MyStoredProcedure sp = new MyStoredProcedure(ds);
String i = sp.execute(1l);
The Oracle function used here just takes in a numeric parameter and returns a numeric paramter.
这里使用的Oracle函数只接受一个数字参数并返回一个数字参数。
#3
In my opinion this is one of the easiest approaches:
在我看来,这是最简单的方法之一:
public class ServRepository {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall functionGetServerErrors;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.functionGetServerErrors = new SimpleJdbcCall(jdbcTemplate).withFunctionName("THIS_IS_YOUR_DB_FUNCTION_NAME").withSchemaName("OPTIONAL_SCHEMA_NAME");
}
public String callYourFunction(int parameterOne, int parameterTwo) {
SqlParameterSource in = new MapSqlParameterSource().addValue("DB_FUNCTION_INCOMING_PARAMETER_ONE", parameterOne).addValue("DB_FUNCTION_INCOMING_PARAMETER_TWO", parameterTwo);
return functionGetServerErrors.executeFunction(String.class, in);
}
}
#1
Assuming you are referring to JdbcTemplate:
假设您指的是JdbcTemplate:
jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException{
CallableStatement cs = con.prepareCall("{call MY_STORED_PROCEDURE(?, ?, ?)}");
cs.setInt(1, ...); // first argument
cs.setInt(2, ...); // second argument
cs.setInt(3, ...); // third argument
return cs;
}
},
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException{
cs.execute();
return null; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
}
);
Calling a function is almost identical:
调用函数几乎完全相同:
jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) {
CallableStatement cs = con.prepareCall("{? = call MY_FUNCTION(?, ?, ?)}");
cs.registerOutParameter(1, Types.INTEGER); // or whatever type your function returns.
// Set your arguments
cs.setInt(2, ...); // first argument
cs.setInt(3, ...); // second argument
cs.setInt(4, ...); // third argument
return cs;
}
},
new CallableStatementCallback {
public Object doInCallableStatement(CallableStatement cs) {
cs.execute();
int result = cs.getInt(1);
return result; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
}
);
#2
Simpler way of calling a Oracle function in Spring is subclassing StoredProcedure like below
在Spring中调用Oracle函数的简单方法是继承StoredProcedure,如下所示
public class MyStoredProcedure extends StoredProcedure{
private static final String SQL = "package.function";
public MyStoredProcedure(DataSource ds){
super(ds,SQL);
declareParameter(new SqlOutParameter("param_out",Types.NUMERIC));
declareParameter(new SqlParameter("param_in",Types.NUMERIC));
setFunction(true);//you must set this as it distinguishes it from a sproc
compile();
}
public String execute(Long rdsId){
Map in = new HashMap();
in.put("param_in",rdsId);
Map out = execute(in);
if(!out.isEmpty())
return out.get("param_out").toString();
else
return null;
}
}
And call it like this
并称之为这样
@Autowired DataSource ds;
MyStoredProcedure sp = new MyStoredProcedure(ds);
String i = sp.execute(1l);
The Oracle function used here just takes in a numeric parameter and returns a numeric paramter.
这里使用的Oracle函数只接受一个数字参数并返回一个数字参数。
#3
In my opinion this is one of the easiest approaches:
在我看来,这是最简单的方法之一:
public class ServRepository {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall functionGetServerErrors;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.functionGetServerErrors = new SimpleJdbcCall(jdbcTemplate).withFunctionName("THIS_IS_YOUR_DB_FUNCTION_NAME").withSchemaName("OPTIONAL_SCHEMA_NAME");
}
public String callYourFunction(int parameterOne, int parameterTwo) {
SqlParameterSource in = new MapSqlParameterSource().addValue("DB_FUNCTION_INCOMING_PARAMETER_ONE", parameterOne).addValue("DB_FUNCTION_INCOMING_PARAMETER_TWO", parameterTwo);
return functionGetServerErrors.executeFunction(String.class, in);
}
}