java调用存储过程

1.申明包（数据库）   

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS   

 TYPE Test_CURSOR IS REF CURSOR;   

 TYPE Test_CURSOR1 IS REF CURSOR;   

end TESTPACKAGE;   

2.存储过程（数据库）   

CREATE OR REPLACE PROCEDURE TESTC(a1 in NUMBER,a2 in VARCHAR2,bb out VARCHAR2, cc out NUMBER ,dd out Date, p_CURSOR out TESTPACKAGE.Test_CURSOR,p_CURSOR1 out TESTPACKAGE.Test_CURSOR1) IS   

BEGIN   

    OPEN p_CURSOR FOR SELECT * FROM MBOXSTATTEAMBLOGUSER;   

    OPEN p_CURSOR1 FOR SELECT * FROM MBOXMESSAGEREADER;   
    bb:=a2;   
    cc:=a1;   
    dd:=sysdate;   

END TESTC;   

3.调用存储过程的方法（java）   

// 返回记录集信息   

 public List callProcedureGetList(MyQuery query){   
  Session session = getHibernateSession();   
  Connection conn = session.connection();   
  CallableStatement cstmt=null;   
  List rtls=new ArrayList();    
  try {   
   cstmt=conn.prepareCall(query.getQueryString());   

   List ls=query.getParalist();       //输入参数集合   
   List outls=query.getOutlist();    //输出参数集合   
   List outcursorls=query.getOutCursorlist(); //输出游标参数集合   


   for (int i=0; i < ls.size(); i++) {   //输入参数   
    cstmt.setObject(i+1,ls.get(i));   
   }    

   int outpos=ls.size()+1;      
   for (int k = 0; k < outls.size(); k++) { //输出参数   

    if (((Integer)outls.get(k)).intValue()==1)      //String    
    {   
     cstmt.registerOutParameter(outpos,Types.VARCHAR);   
     outpos++;   
    }   
    else if (((Integer)outls.get(k)).intValue()==2)  //NUMBER   
    {   
     cstmt.registerOutParameter(outpos,Types.INTEGER);   
     outpos++;   

    }else if (((Integer)outls.get(k)).intValue()==3) //DATE   
    {   
     cstmt.registerOutParameter(outpos,Types.DATE);   
     outpos++;   
    }   
    else  
    {   
     cstmt.registerOutParameter(outpos,Types.OTHER);   
     outpos++;   
    }   

   }   

   int outcursorpos=ls.size()+outls.size()+1;   
   for (int k = 0; k < outcursorls.size(); k++) {         //输出集合   

    cstmt.registerOutParameter(outcursorpos,oracle.jdbc.OracleTypes.CURSOR);   
    outcursorpos++;   
   }   

   cstmt.executeUpdate();   

   int begin=ls.size()+1;   
   for (int k = 0; k < outls.size()+outcursorls.size(); k++) {   

    Object object=cstmt.getObject(begin);   

    if ( object instanceof ResultSet)   
    {   
      object=resultSet2Map((ResultSet)object);   
    }   
    rtls.add(object);   
    begin++;   
   }   

   return rtls;   
  }catch(Exception ex){   
   ex.printStackTrace();   
   return rtls;   

  }finally{   
   try{   
    cstmt.close();   
    cstmt=null;   

   }catch(Exception ex){   
    ex.printStackTrace();   

   }   

  }   
 }    


 private   List resultSet2Map(ResultSet rs) throws Exception {   
  if(rs==null)return null;   
     ResultSetMetaData meta = rs.getMetaData();   
     int count = meta.getColumnCount();          

     List list=new ArrayList();   
         while(rs.next())   
         {      
          Map map = new HashMap(count);   
          for (int i = 1; i <= count; i++) {        
              map.put(meta.getColumnName(i).toLowerCase(),rs.getObject(i));   
          }   
          list.add(map);   
         }   
        return list;   
 }   

4.调用存储过程(java)   

  MyQuery query=new MyQuery();   
  query.setQueryString("{ call TESTC(?,?,?,?,?,?,?) }");   

  query.addPara(new Integer(8888));   
  query.addPara(new String("this is test4444!!!"));   

  query.addParaOut(new Integer(1));   
  query.addParaOut(new Integer(2));   
  query.addParaOut(new Integer(3));   

  query.addParaCursorOut(new Integer(1));   
  query.addParaCursorOut(new Integer(2));   

  return super.callProcedureGetList(query);   

5.解析返回结果(java)   


 private void showDataInfo(List list)   
   {       
       if (list!=null && list.size()>0)   
       {   
         for (int k=0;k<list.size();k++)   
         {   
          if (list.get(k) instanceof String  )   
          {   
          String str=(String)list.get(k);   
       System.out.println(str);   
          }   
         else if (list.get(k) instanceof Integer  )   
          {   
         Integer intvalue=(Integer)list.get(k);   
       System.out.println(intvalue);   
          }   
      else if (list.get(k) instanceof Date  )   
          {   
       Date datevalue=(Date)list.get(k);   
        System.out.println(datevalue);   
          }   
      else if (list.get(k) instanceof ArrayList  )   
      {   
        List rs=(ArrayList)list.get(k);   
        if (rs!=null && rs.size()>0)   
        {      
         for (int i=0;i<rs.size();i++)   
         {   
            Map tsetMap=(HashMap)rs.get(i);   

            if (tsetMap!=null && tsetMap.size()>0)   
            {   
              Iterator it= tsetMap.keySet().iterator();   
              System.out.println("----begin-----");   
              while (it.hasNext())   
             {   
               String keyValue= (String)it.next();   
               Object value=(Object)tsetMap.get(keyValue);   
               System.out.println("name :"+keyValue+"--------"+"value :"+value);                          
             }   
             System.out.println("----end------");   
            }                           
         }   
        }   
      }   
         }   
      }          
   }   

6.MyQuery 结构（java）   

public class MyQuery {   

    /** 参数集合对象 */  
    private List paralist = new ArrayList();   

    /** 输出参数 */  
    private List outlist =new ArrayList();   


    /** 输出参数 */  
    private List outCursorlist =new ArrayList();   

....   
}   



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yang8210/archive/2006/04/12/660742.aspx