这篇“spring中JdbcTemplate操作oracle的存储过程是什么”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“spring中JdbcTemplate操作oracle的存储过程是什么”文章吧。
使用java代码调用oracle的存储过程,本例使用JdbcTemplate模板类操作.
方便后续查阅.
计算
安全
数据库
网络和加速
企业服务
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;public static void doProcedures() {
String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";
jdbcTemplate.execute(procedures);
}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;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, "代码调用");
/** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
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;
}
});
}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;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);
/** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
statement.registerOutParameter(1, OracleTypes.VARCHAR);
/** 注册输出参数的类型-此处集合为oracle的游标类型 */
statement.registerOutParameter(2, OracleTypes.CURSOR);
return statement;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(
CallableStatement statement) throws SQLException,
DataAccessException {
List resultsMap = new ArrayList();
statement.execute();
/** 获取游标结果集-此处2是存储过程参数顺序 */
ResultSet resultSet = (ResultSet) statement
.getObject(2);
/** 转换每行的返回值到Map中 */
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;
}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);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, "代码调用");
/** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
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;
}
});
}
/**
* 调用存储过程-返回值是List集合
* */
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);
/** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
statement.registerOutParameter(1, OracleTypes.VARCHAR);
/** 注册输出参数的类型-此处集合为oracle的游标类型 */
statement.registerOutParameter(2, OracleTypes.CURSOR);
return statement;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(
CallableStatement statement) throws SQLException,
DataAccessException {
List resultsMap = new ArrayList();
statement.execute();
/** 获取游标结果集-此处2是存储过程参数顺序 */
ResultSet resultSet = (ResultSet) statement
.getObject(2);
/** 转换每行的返回值到Map中 */
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;
}
/** 获取JdbcTemplate数据源 */
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);
/** 获取spring的JdbcTemplate*/
JdbcTemplate jdbcTemplate = new JdbcTemplate();
/** 设置数据源 */
jdbcTemplate.setDataSource(dataSource);
return jdbcTemplate;
}
}以上就是关于“spring中JdbcTemplate操作oracle的存储过程是什么”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：https://blog.csdn.net/zhangbeizhen18/article/details/100127703
