spring的JdbcTemplate操作oracle的存储过程
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了spring的JdbcTemplate操作oracle的存储过程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7227字，纯文字阅读大概需要11分钟。
内容图文
场景:
使用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, "代码调用");
/** 注册输出参数的类型-此处集合为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;
}
});
}
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);
/** 注册输出参数的类型-此处集合为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;
}
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, "代码调用");
/** 注册输出参数的类型-此处集合为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的存储过程全部内容，希望文章能够帮你解决spring的JdbcTemplate操作oracle的存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】