spring中JdbcTemplate操作oracle的存储过程实例代码
目录
- 场景:
- 功能:
- 1.JdbcTemplate调用存储过程(Procedure)不带返回值
- 1.1存储过程
- 1.2代码
- 2.JdbcTemplate调用存储过程(Procedure)带返回值但值不是集合类型
- 2.1存储过程
- 2.2代码
- 3.JdbcTemplate调用存储过程(Procedure)带返回值且值集合类型
- 3.1存储过程
- 3.2代码
- 4.附本例使用建表语句
- 5.附本例使用完整测试代码
- 总结
场景:
使用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的存储过程的文章就介绍到这了,更多相关JdbcTemplate操作oracle存储过程内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
赞 (0)