oracle java调用存储过程_java调用存储过程(oracle)

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

{

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

{

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();

....}