- 这里为大家谈谈Java存储过程调用,我们可以利用Java存储过程简化数据库操作, 利用Java存储过程沟通SQL、XML、Java、J2EE和Web服务。
创建需要的测试表:create table Test(tid varchar2(10),tname varchar2(10));
第一种情况:无返回值.
- create or replace procedure test_a(param1 in varchar2,param2 in varchar2)as
- begin
- insert into test values(param1,param2);
- end;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcA
- {
- public Test ProcA(){
- }
- public static void main(String[]args)
- {
- ResultSet rs=null;
- Connection conn=null;
- CallableStatement proc=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{calltest_a(?,?)}");
- proc.setString(1,"1001");
- proc.setString(2,"TestA");
- proc.execute();
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- try{
- if(null!=rs){
- rs.close();
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }
- }catch(Exception ex){
- }
- }
- }
- }
第二种情况:有返回值的存储过程(返回值非列表).
存储过程为:
- create or replace procedure test_b(param1 in varchar2,param2 out varchar2)
- as
- begin
- select tname into param2 from test where tid=param1;
- end;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcB
- {
- public TestProcB(){
- }
- public static void main(String[]args)
- {
- Connection conn=null;
- CallableStatement proc=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{calltest_b(?,?)}");
- proc.setString(1,"1001");
- proc.registerOutParameter(2,Types.VARCHAR);
- proc.execute();
- System.out.println("Outputis:"+proc.getString(2));
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- try{
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }catch(Exceptionex){
- }
- }
- }
- }
第三种情况:返回列表.
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.要分两部分来写:
- create or replace package tpackage as
- type t_cursor is ref cursor;
- procedure test_c(c_ref out t_cursor);
- end;
- create or replace package body tpackage as
- procedure test_c(c_ref out t_cursor)is
- begin
- open c_ref for select*from test;
- end test_c;
- end tpackage;
Java调用代码:
- package com.test;
- import java.sql.*;
- import java.io.*;
- import java.sql.*;
- public class TestProcB
- {
- public TestProcB(){
- }
- public static void main(String[]args)
- {
- Connection conn=null;
- CallableStatement proc=null;
- ResultSet rs=null;
- try{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test");
- proc=conn.prepareCall("{?=call tpackage.test_b(?)}");
- proc.registerOutParameter(1,OracleTypes.CURSOR);
- proc.execute();
- while(rs.next()){
- System.out.println(rs.getObject(1)+"/t"+rs.getObject(2));
- }
- }catch(Exceptione){
- e.printStackTrace();
- }finally{
- try{
- if(null!=rs){
- rs.close();
- if(null!=proc){
- proc.close();
- }
- if(null!=conn){
- conn.close();
- }
- }
- }catch(Exceptionex){
- }
- }
- }
- }
Hibernate调用oracle存储过程
- this.pnumberManager.getHibernateTemplate().execute(
- newHibernateCallback()...{
- publicObjectdoInHibernate(Sessionsession)
- throwsHibernateException,SQLException...{
- CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");
- cs.setString(1,foundationid);
- cs.execute();
- returnnull;
- }
- });