Java代码获取oracle存储过程返回值

Java代码获取oracle存储过程返回值

 下面以student表为例

1、创建表student

create table student（

id   number，

       name  varchar2,

       age  number,

       sex    varchar2

）

2、创建存储过程：以id为条件查询student表信息

create or replace procedure stu_models
(
v_id student.id%type,
v_mycur out sys_refcursor
)
is
begin
  open v_mycur for select * from student where id=v_id; 
end;

解释：%type --》属性类型，sys_refcursor --》系统游标（oracle中的存储过程用来传递参数都是sys_refcursor）

3、下面为Java代码

实体类：

package cn.itcast.entity;


public class Student {
private Integer id;
private String name;
private Integer age;
private String sex;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}


}

Dao类

BaseDao类：

package cn.itcast.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class BaseDao {
//自动加载数据库驱动
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

//获取数据库连接
public static Connection getConnect(){
try {
return DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "testJava", "123456");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}

//释放资源
public static void releaseConnect(Statement sta,ResultSet re,Connection con){
if(sta!=null){
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(re!=null){
try {
re.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

StudentDao类：

package cn.itcast.dao;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;


import oracle.jdbc.OracleTypes;
import cn.itcast.entity.Student;


public class StudentDao extends BaseDao{

public static Student queryStu(int id){
Connection con = getConnect();
CallableStatement stat = null;
java.sql.ResultSet rs = null;
try {
stat = con.prepareCall("call stu_models(?,?)");
stat.setInt(1, id);
stat.registerOutParameter(2, OracleTypes.CURSOR);
stat.execute();
rs = (ResultSet) stat.getObject(2);
Student stu = new Student();
while(rs.next()){
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setSex(rs.getString("sex"));
}
return stu;
} catch (SQLException e) {
e.printStackTrace();
}finally{
releaseConnect(stat, rs, con);
}
return null;
}
}

测试类：

package cn.itcast.demo;


import cn.itcast.dao.StudentDao;
import cn.itcast.entity.Student;


public class Test1 {

public static void main(String[] args) {
Student stu = StudentDao.queryStu(9);
System.out.println(stu.getName());
}
}