java程序调用存储过程和存储函数

来源：互联网发布：野火微博软件编辑：程序博客网时间：2024/05/16 12:00

java程序调用存储过程

jdbcUtil.java文件

package cn.itcast.oracle.utils;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.OracleDriver";private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";private static String user = "scott";private static String password = "tiger";static{try {Class.forName(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;}/* * 执行java程序 * java -Xms100m -Xmx200m HelloWorld *  * 技术方向 * 1. 性能调优 * 2. 故障诊断: ThreadDump */public static void release(Connection conn,Statement st,ResultSet rs){if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;   //----> Java GC}}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;}}}}

testOracle.java

package cn.itcast.oracle.demo;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;import org.junit.Test;import cn.itcast.oracle.utils.JDBCUtils;public class TestOracle {/*调用存储过程

 * create or replace procedure queryEmpInfo(eno in number,                                         pename out varchar2,                                         psal   out number,                                         pjob   out varchar2) */@Testpublic void testProcedure(){//{call <procedure-name>[(<arg1>,<arg2>, ...)]}String sql = "{call queryEmpInfo(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);//对于in参数，赋值call.setInt(1, 7839);//对于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 (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}/*调用存储函数

 * create or replace function queryEmpIncome(eno in number)return number */@Testpublic void testFunction(){//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}String sql = "{?=call queryEmpIncome(?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);//对于out参数，申明call.registerOutParameter(1, OracleTypes.NUMBER);//对于in参数，赋值call.setInt(2, 7839);//执行call.execute();//取出结果double income = call.getDouble(1);System.out.println(income);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}

在out参数中使用光标

@Testpublic void testCursor(){String sql = "{call MYPACKAGE.queryEmpList(?,?)}";Connection conn = null;CallableStatement call = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.setInt(1, 20);call.registerOutParameter(2, OracleTypes.CURSOR);call.execute();//取出集合rs = ((OracleCallableStatement)call).getCursor(2);while(rs.next()){String name = rs.getString("ename");double sal = rs.getDouble("sal");System.out.println(name+"\t"+sal);}} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}}

0 0