场景:
使用java代码调用oracle的存储过程,本例使用JdbcTemplate模板类操作.
功能:
方便后续查阅.
1.JdbcTemplate调用存储过程(Procedure)不带返回值
1.1存储过程
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS4(TASK_ID IN NUMBER) IS
BEGIN
INSERT INTO F_LOG_INFO
(TASK_ID,
BEGIN_TIME,
END_TIME,
FLAG,
FAIL_INFO,
DATA_COUNT,
TABLE_NAME)
VALUES
(TASK_ID, SYSDATE - 1, SYSDATE, '999', '999', 999, 'TABLE_NAME2019');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PRO_QUERY_INFO_ARGS4;1.2代码
public static void doProcedures() {
String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";
jdbcTemplate.execute(procedures);
}2.JdbcTemplate调用存储过程(Procedure)带返回值但值不是集合类型
2.1存储过程
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS3(ARGS IN VARCHAR2,
RTNINFO OUT VARCHAR2,
ERRORMSG OUT VARCHAR2,
FAILINFO OUT VARCHAR2) IS
BEGIN
ERRORMSG := '';
RTNINFO := '你输入的ARGS=' || ARGS;
SELECT FAIL_INFO INTO FAILINFO FROM F_LOG_INFO where TASK_ID = 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRORMSG := 'PRO_QUERY_INFO_ARG抛出异常: ' || SQLERRM;
END PRO_QUERY_INFO_ARGS3;2.2代码
public static void getProceduresResult() {
String tt2 = (String) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(
Connection con) throws SQLException {
String procedures = "{call PRO_QUERY_INFO_ARGS3 (?,?,?,?)}";
CallableStatement cs = con.prepareCall(procedures);
cs.setString(1, "代码调用");
cs.registerOutParameter(2, OracleTypes.VARCHAR);
cs.registerOutParameter(3, OracleTypes.VARCHAR);
cs.registerOutParameter(4, OracleTypes.VARCHAR);
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement st)
throws SQLException, DataAccessException {
st.execute();
Object tt2 = st.getObject(2);
Object tt3 = st.getObject(3);
Object tt4 = st.getObject(4);
return tt2;
}
});
}3.JdbcTemplate调用存储过程(Procedure)带返回值且值集合类型
3.1存储过程
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS2(ERRORMSG OUT VARCHAR2,
CURINFO OUT SYS_REFCURSOR) IS
BEGIN
ERRORMSG := '';
OPEN CURINFO FOR
SELECT FAIL_INFO, TABLE_NAME FROM F_LOG_INFO;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRORMSG := 'PRO_QUERY_INFO_ARG2抛出异常: ' || SQLERRM;
END PRO_QUERY_INFO_ARGS2;3.2代码
public static List getProceduresResultList() {
List resultList = (List) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(
Connection conn) throws SQLException {
String procedures = "{ CALL PRO_QUERY_INFO_ARGS2(?,?) }";
CallableStatement statement = conn
.prepareCall(procedures);
statement.registerOutParameter(1, OracleTypes.VARCHAR);
statement.registerOutParameter(2, OracleTypes.CURSOR);
return statement;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(
CallableStatement statement) throws SQLException,
DataAccessException {
List resultsMap = new ArrayList();
statement.execute();
ResultSet resultSet = (ResultSet) statement
.getObject(2);
while (resultSet.next()) {
Map rowMap = new HashMap();
rowMap.put("FAIL_INFO",
resultSet.getObject("FAIL_INFO"));
rowMap.put("TABLE_NAME",
resultSet.getObject("TABLE_NAME"));
resultsMap.add(rowMap);
}
resultSet.close();
return resultsMap;
}
});
return resultList;
}4.附本例使用建表语句
create table F_LOG_INFO
(
task_id NUMBER(16) not null,
begin_time DATE,
end_time DATE,
flag VARCHAR2(8),
fail_info VARCHAR2(512),
data_count NUMBER(16),
table_name VARCHAR2(256)
);
alter table F_LOG_INFO
add constraint PK_F_LOG_INFO primary key (TASK_ID);5.附本例使用完整测试代码
public class TestProcedures {
public static JdbcTemplate jdbcTemplate = getJdbcTemplate();
public static void main(String[] args) {
System.out.println("测试开始......");
// getProceduresResult();
doProcedures();
List result = getProceduresResultList();
for (int i = 0; i < result.size(); i++) {
Map rowMap = (Map) result.get(i);
String id = rowMap.get("FAIL_INFO").toString();
String name = rowMap.get("TABLE_NAME").toString();
System.out.println("FAIL_INFO=" + id + ";TABLE_NAME=" + name);
}
System.out.println("测试结束......");
}
public static void doProcedures() {
String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";
jdbcTemplate.execute(procedures);
}
public static void getProceduresResult() {
String tt2 = (String) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(
Connection con) throws SQLException {
String procedures = "{call PRO_QUERY_INFO_ARGS3 (?,?,?,?)}";
CallableStatement cs = con.prepareCall(procedures);
cs.setString(1, "代码调用");
cs.registerOutParameter(2, OracleTypes.VARCHAR);
cs.registerOutParameter(3, OracleTypes.VARCHAR);
cs.registerOutParameter(4, OracleTypes.VARCHAR);
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement st)
throws SQLException, DataAccessException {
st.execute();
Object tt2 = st.getObject(2);
Object tt3 = st.getObject(3);
Object tt4 = st.getObject(4);
return tt2;
}
});
}
public static List getProceduresResultList() {
List resultList = (List) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(
Connection conn) throws SQLException {
String procedures = "{ CALL PRO_QUERY_INFO_ARGS2(?,?) }";
CallableStatement statement = conn
.prepareCall(procedures);
statement.registerOutParameter(1, OracleTypes.VARCHAR);
statement.registerOutParameter(2, OracleTypes.CURSOR);
return statement;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(
CallableStatement statement) throws SQLException,
DataAccessException {
List resultsMap = new ArrayList();
statement.execute();
ResultSet resultSet = (ResultSet) statement
.getObject(2);
while (resultSet.next()) {
Map rowMap = new HashMap();
rowMap.put("FAIL_INFO",
resultSet.getObject("FAIL_INFO"));
rowMap.put("TABLE_NAME",
resultSet.getObject("TABLE_NAME"));
resultsMap.add(rowMap);
}
resultSet.close();
return resultsMap;
}
});
return resultList;
}
public static JdbcTemplate getJdbcTemplate() {
DruidDataSource dataSource = new DruidDataSource();
String username = "demodb";
String password = "123456";
String jdbcUrl = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
String driverName = "oracle.jdbc.OracleDriver";
dataSource.setPassword(password);
dataSource.setUrl(jdbcUrl);
dataSource.setUsername(username);
dataSource.setDriverClassName(driverName);
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
return jdbcTemplate;
}
}以上,感谢.
总结
到此这篇关于spring中JdbcTemplate操作oracle的存储过程的文章就介绍到这了,更多相关JdbcTemplate操作oracle存储过程内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
