在java应用程序下调用存储函数，存储过程，包

创建connection链接，释放关闭链接

package demo.untils;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:@127.0.0.1:1521:orcl";private static String user="scott";private static String password="tiger";//注册数据库的驱动static{try {Class.forName(driver);//采用java反射机制，必然会抛出异常//DriverManager.registerDriver(driver);//一般不采用java提供的这个方法注册驱动} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//获取数据库链接public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {// TODO Auto-generated catch blocke.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;//让rs迅速成为java垃圾回收的对象}}if(st!=null){try {st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{st=null;}}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{conn=null;}}}    }

调用存储过程

package demo.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.SQLException;import oracle.jdbc.OracleTypes;import org.junit.Test;import demo.untils.JDBCUtils;public class TestProcedure {/* *create or replace procedure queryempinform(eno in number,                                           pename out varchar2,                                           psal out number,                                           pjob out varchar2) */@Testpublic void testprocedure(){//{call <procedure-name>[(<arg1>,<arg2>, ...)]}        String sql = "{call queryempinform(?,?,?,?)}";        Connection conn =null;        CallableStatement call =null;                try {        //得到一个链接        conn=JDBCUtils.getConnection();        //通过链接创建出statementcall=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 salary = call.getDouble(3);String job = call.getString(4);System.out.println(name+"\t"+salary+"\t"+job);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtils.release(conn, call,null);}}}

调用存储函数

package demo.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.SQLException;import oracle.jdbc.OracleTypes;import org.junit.Test;import demo.untils.JDBCUtils;public class TestFunction {/* 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();        //基于链接创建statementcall = conn.prepareCall(sql);//对于输出参数，声明call.registerOutParameter(1, OracleTypes.NUMBER);//对于输入参数，赋值call.setInt(2, 7839);//执行调用call.execute();//取出年收入的结果（存储函数的结果）double income = call.getDouble(1);System.out.println("该员工的年收入是："+income);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}}

调用包，要注意下出out参数的强制类型转换

package demo.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;import org.junit.Test;import demo.untils.JDBCUtils;public class TestCursor {/* create or replace PACKAGE MYPACKAGE AS   type empcursor is ref cursor;  procedure queryEmpList(dno in number,empList out empcursor);END MYPACKAGE; */@Testpublic void testcursor(){//{call <procedure-name>[(<arg1>,<arg2>, ...)]}String sql = "{call MYPACKAGE.queryEmpList(?,?)}";Connection conn = null;CallableStatement call = null;ResultSet rs = null;try {//创建数据库链接conn = JDBCUtils.getConnection();//基于链接创建statementcall = conn.prepareCall(sql);//对于in参数， 赋值call.setInt(1, 20);//对于out，参数，声明call.registerOutParameter(2, OracleTypes.CURSOR);//执行调用call.execute();//取出该部门的所有员工信息//所有接口都有实现类，不同的是将sql java的接口转换成oracle数据库的接口rs = ((OracleCallableStatement)call).getCursor(2);while(rs.next()){//取出该员工的工号,姓名，薪水，职位int empno = rs.getInt("empno");String name = rs.getString("ename");double salary = rs.getDouble("sal");String job = rs.getString("empjob");System.out.println(empno+"\t"+name+"\t"+salary+"\t"+job);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}}}