一 概述
本篇介绍用java访问存储过程和访问存储函数
二 开发环境搭建
1、安装oracle时候，会带有jdbc的驱动程序，存放路径如下：
D:\app\lenovo\product\11.2.0\dbhome_1\jdbc\lib
2、拷贝ojdbc6.jar到eclipse的工程目录的lib目录下。
F:\java\workspace\TestOracle\lib
3、在eclipse工程中选中ojdbc6.jar，右键Build Path，将该jar包加到开发环境中。
4、环境搭建后截图如下：
三 创建JDBC工具类
代码如下：
@H_404_56@package demo.utils;@H_404_56@import java.sql.Connection;@H_404_56@import java.sql.DriverManager;@H_404_56@import java.sql.ResultSet;@H_404_56@import java.sql.sqlException;@H_404_56@import java.sql.Statement;@H_404_56@public@H_404_56@classJDBCUtils{@H_404_56@private@H_404_56@staticString driver ="oracle.jdbc.OracleDriver";@H_404_56@private@H_404_56@staticString url ="jdbc:oracle:thin:@127.0.0.1:1521:orcl";@H_404_56@private@H_404_56@staticString user ="scott";@H_404_56@private@H_404_56@staticString password ="tiger";//注册数据库的驱动@H_404_56@static{@H_404_56@try{Class.forName(driver);//DriverManager.registerDriver(driver);}@H_404_56@catch(ClassNotFoundException e){@H_404_56@throw@H_404_56@newExceptionInInitializerError(e);}}//获得数据库连接@H_404_56@public@H_404_56@staticConnection getConnection(){@H_404_56@try{@H_404_56@returnDriverManager.getConnection(url, user, password);}@H_404_56@catch(sqlException e){// TODO Auto-generated catch blocke.printStackTrace();}@H_404_56@return@H_404_56@null;//释放数据库的资源@H_404_56@public@H_404_56@static@H_404_56@void release(Connection conn,Statement st,ResultSet rs){@H_404_56@if(rs!=@H_404_56@null){rs.close();}@H_404_56@catch(sqlException e){// TODO Auto-generated catch blocke.printStackTrace();}@H_404_56@finally{rs =@H_404_56@null;}}@H_404_56@if(st!=@H_404_56@null){st.close();st=@H_404_56@null;@H_404_56@if(conn !=@H_404_56@null){conn.close();conn =@H_404_56@null;}}
四 在应用程序中如何访问存储过程
1、构建测试类：
TestProcedure
@H_404_56@package demo.oracle;//import static org.junit.Assert.*;@H_404_56@import java.sql.CallableStatement;@H_404_56@import java.sql.Connection;@H_404_56@import oracle.jdbc.OracleTypes;@H_404_56@import org.junit.Test;@H_404_56@import demo.utils.JDBCUtils;@H_404_56@public@H_404_56@classTestProcedure{/*create or replace procedure queryempinformation(eno in number,pename out varchar2,psal out number,pjob out varchar2)* */@Test@H_404_56@public@H_404_56@void testProdedure(){String sql ="{call queryempinformation(?,?,?)}";Connection conn =@H_404_56@null;CallableStatement call=@H_404_56@null;@H_404_56@try{//得到一个连接conn =JDBCUtils.getConnection();call = conn.prepareCall(sql);//对于输入参数，赋值call.setInt(1,7839);//对于输出参数，申明call.registerOutParameter(2,OracleTypes.VARCHAR);call.registerOutParameter(3,OracleTypes.NUMBER);call.registerOutParameter(4,OracleTypes.VARCHAR);//执行调用call.execute();//取出结果String name = call.getString(2);@H_404_56@double sal =call.getDouble(3);String job = call.getString(4);System.out.println(name+"\t"+sal+"\t"+job);}@H_404_56@catch(Exception e){e.printStackTrace();}@H_404_56@finally{JDBCUtils.release(conn, call,@H_404_56@null);}}}
2、测试结果：
KING10100.0PRESIDENT
五 在应用程序中如何访问存储函数
1、构建测试类TestFunction
@H_404_56@import java.sql.CallableStatement;@H_404_56@import java.sql.Connection;@H_404_56@import oracle.jdbc.OracleTypes;@H_404_56@import org.junit.Test;@H_404_56@import demo.utils.JDBCUtils;@H_404_56@public@H_404_56@classTestFunction{/*create or replace function queryempincoming(eno in number)return number* */@Test@H_404_56@public@H_404_56@void testFunction(){String sql="{call queryempincoming(?)}";Connection conn =@H_404_56@null;CallableStatement call =@H_404_56@null;@H_404_56@try{//得到数据库连接conn =JDBCUtils.getConnection();call = conn.prepareCall(sql);//对于输出函数，申明call.registerOutParameter(1,OracleTypes.NUMBER);//对于输入参数，赋值call.setInt(2,7839);//执行调用call.execute();//取出年收入结构@H_404_56@double income = call.getDouble(1);System.out.println("该员工的年收入是："+income);}@H_404_56@catch(Exception e){e.printStackTrace();}@H_404_56@finally{404_56@null);}}
2、测试结果
KING10100.0PRESIDENT
