java 使用spring存储过程调用oracle存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12532256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-31 09:16:22 来源:igfitidea点击:
Calling oracle stored procedure using spring stored procedure
提问by Ololade enifeni
I have been trying to get a piece of code that uses spring and oracle stored procedure with parameters but have been finding it difficult to run. The stored procedure as specified is intended to expect three parameter but in the error it appears it expects four. The fourth parameter is a cursor to be returned.
我一直在尝试获取一段使用带有参数的spring和oracle存储过程的代码，但发现很难运行。指定的存储过程预期需要三个参数，但在错误中它出现它需要四个。第四个参数是要返回的游标。
Here is my Oracle(9i) package specification:
这是我的 Oracle(9i) 包规范：
CREATE OR REPLACE PACKAGE pkg_data_load AS
procedure sp_atm_withdrawal(p_catermid IN VARCHAR2,
p_start_date IN VARCHAR2,p_end_date IN VARCHAR2,p_out out sys_refcursor);
END;
Below is the package body:
下面是包体：
CREATE OR REPLACE PACKAGE BODY pkg_data_load
AS
procedure sp_atm_withdrawal
(
p_catermid IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_out out sys_refcursor
) as
v_start_date date := to_date(p_start_date,'yyyy/mm/dd');
v_end_date date := to_date(p_end_date,'yyyy/mm/dd');
begin
open p_out for select
b.nam_branch BRANCH_NAME
, a.bcode brn_Code
, a.acct_no Acct_no
from table a, table b where b.cod_Cc_brn= a.cod_org_brn
and a.cod_reply=0
and b.flg_mnt_status='A'
and a.cod_proc not in ( 312000, 382000, 311000, 381000)
and a.cod_txn_literal<>'SCD'
and a.ca_term_id in (
select ca_term_id from tablec where flg_mnt_status='A')
and a.dat_post_stl between v_start_date and v_end_date
and a.ca_term_id = p_catermid;
end sp_atm_withdrawal;
END pkg_data_load;
Here is a snippet of my Procedure Class
这是我的程序类的片段
public class AtmStoredProcedures extends StoredProcedure {
public AtmStoredProcedures(JdbcTemplate jdbcTemplate, String procedure)
{
super(jdbcTemplate,procedure);
AtmRowMapper rowMapper = new AtmRowMapper();
declareParameter(new SqlOutParameter("sys_refcursor",OracleTypes.CURSOR, rowMapper));
declareParameter(new SqlParameter("branch", Types.VARCHAR));
declareParameter(new SqlParameter("startDate", Types.VARCHAR));
declareParameter(new SqlParameter("endDate", Types.VARCHAR));
compile();
}
public Map getCashWithdrawals(String branch, String startDate, String endDate)
{
Map inParam = new HashMap();
inParam.put("branch", branch);
inParam.put("startDate", startDate);
inParam.put("endDate", endDate);
Map out = execute(inParam); // Call on parent class
return out;
}
}
And Below is my DAO Implementation method
下面是我的 DAO 实现方法
public List<Atm> loadWithdrawal(String branch, String startDate, String endDate) {
if (this.jdbcTemplate == null) {
System.out.print("JDBC TEMPLATE IS NULL");
}
List<Atm> withdrawals = null;
try
{
AtmStoredProcedures st = new AtmStoredProcedures(jdbcTemplate,"pkg_data_load.sp_atm_withdrawal");
Map results = st.getCashWithdrawals(branch, startDate, endDate);
withdrawals = (List<Atm>) results.get("sys_refcursor");
} catch (DataAccessException ex) {
System.out.print(ex.getMessage());
}
return withdrawals;
}
After successful compliation i get the error below at runtime
成功编译后，我在运行时收到以下错误
CallableStatementCallback; bad SQL grammar
[{call pkg_data_load.sp_atm_withdrawal(?, ?, ?, ?)}];
nested exception is java.sql.SQLException:
ORA-06550: line 1, column 7: PLS-00306: wrong number or
types of arguments in call to 'SP_ATM_WITHDRAWAL'ORA-06550:
line 1, column 7: PLS-00306: wrong number or types of arguments
in call to 'SP_ATM_WITHDRAWAL'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
回答by GriffeyDog
Your calls to declareParameter() must be in the same order as the Oracle stored procedure. Try this:
您对 declareParameter() 的调用必须与 Oracle 存储过程的顺序相同。试试这个：
declareParameter(new SqlParameter("branch", Types.VARCHAR));
declareParameter(new SqlParameter("startDate", Types.VARCHAR));
declareParameter(new SqlParameter("endDate", Types.VARCHAR));
declareParameter(new SqlOutParameter("sys_refcursor",OracleTypes.CURSOR, rowMapper));
回答by Ravi Kant
create a bean for datasource configuration:
为数据源配置创建一个 bean：
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations"><list>
<value>classpath:datasource.properties</value>
</list>
</property>
<property name="ignoreResourceNotFound" value="true" />
<property name="ignoreUnresolvablePlaceholders" value="true" />
</bean>
<bean id="defaultDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="accessToUnderlyingConnectionAllowed" value="true"/>
</bean>
Create a dao with reference to which you inject your datasource (applicationContext.xml):
创建一个引用您注入数据源 (applicationContext.xml) 的 dao：
<bean id="testDao" class="com.test.dao.testDao">
<property name="dataSource" ref="dataSource" />
</bean>
Then create a class for convert Oracle Array to java List
然后创建一个用于将 Oracle Array 转换为 java List 的类
public class OracleLists implements SqlReturnType {
public Object getTypeValue(CallableStatement cs, int paramIndex,int sqlType, String typeName) throws SQLException {
Object[] structs = (Object[]) ((ARRAY)cs.getObject(paramIndex)).getArray();
List<String> po_list = new ArrayList<String>();
for(int i=0;i<structs.length ;i++){
po_list.add(String.valueOf(structs[i]));
}
return po_list;
}
}
Then create TestDao to call procedure::
然后创建TestDao来调用procedure::
public class TestDao {
private SimpleJdbcCall simpleJdbcCall;
public void setSimpleJdbcCall(SimpleJdbcCall simpleJdbcCall;) {
this.simpleJdbcCall = simpleJdbcCall;
}
public List<String> getTestParameter(Object object) {
MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("IN_PARAM1", object.getInParam1);
in.addValue("OUT_PARAM1", null,OracleTypes.ARRAY, object.getOutParam1);
Map<String, Object> result = simpleJdbcCall.execute(in);
List<String> stringList = (List<String>) result.get("OUT_PARAM1");
return stringList ;
}
simpleJdbcCall= new SimpleJdbcCall(dataSource)
.withSchemaName("TESTSCHEMA")
.withCatalogName("TESTPACKAGE")
.withProcedureName("TESTPROC")
.declareParameters(
new SqlParameter("IN_PARAM",
OracleTypes.VARCHAR,
"IN_PARAM"),
new SqlInOutParameter(
"OUT_PARAM1",
OracleTypes.ARRAY,
TestOracleType,
new OracleLists()),
);
}
