Java执行Oracle存储过程并接收多个返回值(集合/数值)
Java代码:
package com.pure.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DBHelper {
public Connection getConnection(){
Connection conn=null;
try{
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//SqlServer
//conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test", "sa", "123");
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test","root","123");
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public void close(ResultSet rs,PreparedStatement ps,Connection conn){
try{
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(conn!=null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
DBHelper db=new DBHelper();
System.out.println(db.getConnection());
}
}
package test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import com.util.DBHelper;
public class Test {
/**
* jar运行Test类 -- cmd中运行test.jar
* java -jar test.jar 14
* @param args
*/
public static void main(String[] args) {
//System.out.println("数量="+testCall(parseInt(args[0]))); //jar运行
System.out.println("数量="+testCall(14));
}
public static int testCall(int param){
DBHelper db=new DBHelper();
Connection conn= db.getConnection();
int count = 0;
try{
CallableStatement call=conn.prepareCall("{call sp_test_1(?,?,?)}");//运行sp_test_1存储过程(注:存储过程接收参数和输出参数Java都有打?问号)
call.setInt(1, param);//设置参数
call.registerOutParameter(2, OracleTypes.INTEGER);//接收返回值
call.registerOutParameter(3,OracleTypes.CURSOR);//接收返回值
call.execute();//执行
count=call.getInt(2); //拿取存储过程返回值
List<Map> list=new ArrayList<Map>();
//ResultSet rs=call.getResultSet();
ResultSet rs=(ResultSet)call.getObject(3);//拿取存储过程返回的集合
while(rs.next()){
Map map=new HashMap();
map.put("id", rs.getInt("ID"));
map.put("user_name", rs.getString("USER_NAME"));
map.put("login_name", rs.getInt("LOGIN_NAME"));
list.add(map);
}
System.out.println("listSize="+list.size());
}catch(Exception e){
e.printStackTrace();
}finally{
}
return count;
}
public static int parseInt(Object obj){
try{
return Integer.parseInt(obj.toString());
}catch(Exception e){
return 0;
}
}
}
/*
* oracle存储过程测试
*/
CREATE OR REPLACE PROCEDURE sp_test_1(
param1 in int, --输入参数
out_int out int, --返回值int
out_list out SYS_REFCURSOR --返回集合
)
is
val_1 int; --全局变量
begin
select count(1) into val_1 from pd_system_user where id = param1;
out_int := val_1;
--设置结果集
open out_list for select * from pd_system_user where id <= param1;
--dbms_output.put_line(out_return); --打印
end sp_test_1;
-- 演示存储过程
/*
declare --声明变量
param1 varchar2(100);
val_1 int;
begin
param1 := 14; --变量赋值
--select val_1 := count(1) from pd_system_user where id = param1; --不行
select count(1) into val_1 from pd_system_user where id = param1;
--val_1 := param1;
dbms_output.put_line(val_1);--打印语句
end;
*/
运行结果: