JDBC -访问存储过程

来源：互联网发布：力度exp5000编程方法编辑：程序博客网时间：2024/06/05 19:50

存储过程：

create  procedure queryempinform (eno in  emp.empno%type ,                                  pename out  emp.ename%type,                                  psal out  emp.sal%type,                                  pjob out  emp.job%type ) asbegin     select ename,sal,job into pename ,psal,pjob from emp where empno = eno;end;

JDBCUtils类

package Procedure;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {    private static String driver = "oracle.jdbc.driver.OracleDriver";    private static String  url ="jdbc:oracle:thin:@127.0.0.1:1521:orcl";//127.0.0.1是本机地址，1521是oracle数据库的默认监听端口。    private static String user="scott";    private static String password ="tiger";    //注册数据库的驱动    static {        try {            Class.forName(driver);//加载oracle驱动程序            //DriverManager.registerDriver(driver);        } catch (ClassNotFoundException e) {            throw new ExceptionInInitializerError(e);        }    }    //获取连接    public static Connection getConnection() {        try {            return DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }    //释放资源    public static void release (Connection conn,Statement st ,ResultSet rs) {        if(rs!=null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                rs=null;            }        }        if(st!=null) {            try {                st.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                st=null;            }        }        if(conn!=null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }finally{                conn=null;            }        }    }}

测试类

package Procedure;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.SQLException;import oracle.jdbc.OracleTypes;public class TestProcedure {    public static void main(String[] args) throws SQLException {        //{call <procedure-name> [(<arg1>,<arg2>,...)]}        String sql = "{call queryempinform(?,?,?,?)}";//预编译语句        Connection conn = null;        CallableStatement call = null;        try {            // 得到一个连接            conn = JDBCUtils.getConnection();            // 通过连接创建出statement            call = conn.prepareCall(sql);//实例化预编译语句            // 对于in输入参数，需要赋值            call.setInt(1, 7839);//1表示参数的索引,而非列中索引            // 对于out输出参数，需要申明                       call.registerOutParameter(2, OracleTypes.VARCHAR);            call.registerOutParameter(3, OracleTypes.NUMBER);            call.registerOutParameter(4, OracleTypes.VARCHAR);            // 执行调用            call.execute();            // 取出结果            String name = call.getString(2);            double sal = call.getDouble(3);            String job = call.getString(4);            System.out.println(name + '\t' + sal + '\t' + job);        } catch (SQLException e) {            e.printStackTrace();        } finally {            //释放连接            JDBCUtils.release(conn, call, null);        }    }}

0 0