Orcle 存储过程调用封装类-备忘

 

主调用：

import java.io.InputStream;

import java.net.URL;

import java.sql.Date;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Time;

import java.util.ArrayList;

import exception.OracleProException;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.jdbc.driver.OracleConnection;

import oracle.sql.STRUCT;

/**

 * Oracle 存储过程调用类

 */

public class OraProc {

/** 数据库链接器 */

public OracleConnection conn = null;

/** 存储过程名称 */

private String procName = null;

/** 参数个数值 */

private int paraN = 0;

private int binaryStreamSize=0;

/**获得二进制流数据大小*/

public int getBinaryStreamSize() {

return binaryStreamSize;

}

/**设置二进制数据流大小*/

public void setBinaryStreamSize(int binaryStreamSize) {

this.binaryStreamSize = binaryStreamSize;

}

public OracleCallableStatement proc = null;

private ArrayList<Para> pArray = null;

/**

* 存储过程调用对象构造方法

* 

* @param Connection

*            aConn 数据库链接对象。

* @param String

*            aProcName 存储过程名称。

* @param ArrayList

*            <Para> 存储过程参数对象列表

* */

public OraProc(OracleConnection aConn, String aProcName,

ArrayList<Para> aPArray) {

this.conn = aConn;

this.procName = aProcName;

this.pArray = aPArray;

this.paraN = this.pArray.size();

}

public ArrayList<Para> getPArray() {

return pArray;

}

 

public void setPArray(ArrayList<Para> array) {

pArray = array;

}

 

public OracleConnection getConn() {

return conn;

}

 

public void setConn(OracleConnection conn) {

this.conn = conn;

}

 

public String getProcName() {

return procName;

}

 

public void setProcName(String procName) {

this.procName = procName;

}

public void execute() throws OracleProException, SQLException {

this.prepareCall();

this.proc.execute();

}

public ResultSet getRS(int parameterIndex) {

ResultSet rs = null;

try {

rs = (ResultSet) proc.getObject(parameterIndex);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return rs;

}

 

public Object getObject(int index) {

Object rO = null;

try {

rO = this.proc.getObject(index);

} catch (SQLException e) {

e.printStackTrace();

}

return rO;

}

private void prepareCall() {

StringBuffer bufferP = new StringBuffer("{ call ");

bufferP.append(this.procName);

bufferP.append("(");

for (int i = 0; i < paraN; i++) {

if (i==0){

bufferP.append("?");

}else{

bufferP.append(",?");

}

}

bufferP.append(") }");

try {

if (this.conn!=null){

this.proc = (OracleCallableStatement) this.conn.prepareCall(bufferP.toString());

for (int i = 0; i < this.pArray.size(); i++) {

Para pa = (Para) this.pArray.get(i);

if (pa.getDirect() == Para.IN) {

swithSetV(pa.getIndex(), pa.getType(), pa.getValue());

} else if (pa.getDirect() == Para.OUT) {

this.proc.registerOutParameter(pa.getIndex(), pa.getType());

} else {

// 异常

                   throw new OracleProException(OracleProException.UNVALID_PARA+"存取过程数据流向参数不明，请使用 Para.OUT(输出) 或者 Para.IN(输入)");

}

}

}else{

// 异常

                throw new OracleProException("数据库Connection对象为空");

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}catch(OracleProException e){

e.printStackTrace();

}catch(Exception e){

e.printStackTrace();

}

}

 

/**

* 循环列举所有值并进行方法对应

* */

private void swithSetV(int index, int type, Object value) {

try {

switch (type) {

case OracleTypes.VARCHAR:

this.proc.setString(index, (String) value);

break;

case OracleTypes.CHAR:

this.proc.setString(index, (String) value);

break;

case OracleTypes.INTEGER:

this.proc.setInt(index, ((Integer) value).intValue());

break;

case OracleTypes.ARRAY:

this.proc.setInt(index, ((Integer) value).intValue());

break;

case OracleTypes.BIGINT:

this.proc.setLong(index, ((Long) value).intValue());

break;

case OracleTypes.BINARY:

if (binaryStreamSize>0){

this.proc.setBinaryStream(index, (InputStream) value, binaryStreamSize);

}else{

throw new OracleProException("binaryStreamSize参数不能为0,请使用setBinaryStreamSize方法为成员变量赋值");

}

break;

case OracleTypes.BIT:

this.proc.setBoolean(index, (Boolean) value);

break;

case OracleTypes.BLOB:

this.proc.setBlob(index, (BLOB) value);

break;

case OracleTypes.BOOLEAN:

this.proc.setBoolean(index, (Boolean) value);

break;

case OracleTypes.CLOB:

this.proc.setClob(index, (CLOB) value);

break;

case OracleTypes.DATALINK:

this.proc.setURL(index, (URL) value);

break;

case OracleTypes.DATE:

this.proc.setDate(index, (Date) value);

break;

case OracleTypes.DOUBLE:

this.proc.setDouble(index, (Double) value);

break;

case OracleTypes.FLOAT:

this.proc.setFloat(index, (Float) value);

break;

case OracleTypes.JAVA_OBJECT:

this.proc.setObject(index, value);

break;

case OracleTypes.LONGVARBINARY:

if (binaryStreamSize>0){

this.proc.setBinaryStream(index, (InputStream) value, binaryStreamSize);

}else{

throw new OracleProException("binaryStreamSize参数不能为0,请使用setBinaryStreamSize方法为成员变量赋值");

}

break;

case OracleTypes.NULL:

this.proc.setNull(index, OracleTypes.NULL);

break;

case OracleTypes.NUMERIC:

this.proc.setNUMBER(index, new NUMBER(value));

break;

case OracleTypes.OTHER:

this.proc.setObject(index, (Object) value);

break;

case OracleTypes.REAL:

this.proc.setFloat(index, (Float) value);

break;

case OracleTypes.REF:

this.proc.setRef(index, (REF) value);

break;

case OracleTypes.SMALLINT:

this.proc.setInt(index, ((Integer) value).intValue());

break;

case OracleTypes.STRUCT:

this.proc.setSTRUCT(index, (STRUCT) value);

break;

case OracleTypes.TIME:

this.proc.setTime(index, (Time) value);

break;

case OracleTypes.TIMESTAMP:

this.proc.setTIMESTAMP(index, (TIMESTAMP) value);

break;

case OracleTypes.TINYINT:

this.proc.setInt(index, ((Integer) value).intValue());

break;

case OracleTypes.VARBINARY:

if (binaryStreamSize>0){

this.proc.setBinaryStream(index, (InputStream) value, binaryStreamSize);

}else{

throw new OracleProException("binaryStreamSize参数不能为0,请使用setBinaryStreamSize方法为成员变量赋值");

}

break;

default:

throw new OracleProException(OracleProException.UNVALID_PARA+"不支持的参数类型，请使用 Oracle.jdbc.OracleTypes类型");

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 返回字符串信息

* */

    public String getString(int parameterIndex){

     String rv=null;

     try {

     rv=this.proc.getString(parameterIndex);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return rv;

    }

    /**

     * 返回整数信息

     * */

    public int getInt(int parameterIndex){

     Integer rv=null;

     try {

     rv=this.proc.getInt(new Integer(parameterIndex));

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return rv.intValue();

    }

    /**

     * 返回CLOB信息

     * */

    public CLOB getCLOB(int parameterIndex){

     CLOB rv=null;

     try {

     rv=this.proc.getCLOB(parameterIndex);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return rv;

    }

    public void close() throws SQLException{

     if (this.proc!=null){

this.proc.close();

     }

    }

}

异常：

 

package  exception;

public class OracleProException extends RuntimeException{

   private static final long serialVersionUID = 1L;

public static final String NULL_PARA="参数值不能为空";

public static final String UNVALID_PARA="不是有效的参数值";

public static final String PARA_NOT_SET="参数值未设置";

        public OracleProException(String mses){

       super(mses);

        }

}

 

 

参数类：

 

/**

 * 存储过程调用参数类型

 * */

public class Para {

/** 输入参数标示 */

public static final int IN = 0;

/** 输出参数标示 */

public static final int OUT = 1;

/** 参数类型 */

private int type = -1;

/** 参数值 */

private Object value = null;

/** 参数 数据流向 IN或者OUT */

private int direct = -1;

/** 参数索引 */

private int index = -1;

 

/**

* 输入数据库存储过程参数对象构造

* 

* @param int aType Oracle数据类型(oracle.jdbc.OracleTypes)

* @param int aDirect 参数数据流向Para.IN或者Para.OUT

* @param Object

*            aValue 参数对象值

* @param int aIndex 参数在存储过程中的过程参数位置索引

* */

public Para(int aType, Object aValue, int aDirect, int aIndex) {

this.type = aType;

this.value = aValue;

this.direct = aDirect;

this.index = aIndex;

}

/**

* 输出类型数据库存储过程参数对象构造

* 

* @param int aType Oracle数据类型(oracle.jdbc.OracleTypes)

* @param int aDirect 参数数据流向Para.IN或者Para.OUT

* @param Object

*            aValue 参数对象值

* @param int aIndex 参数在存储过程中的过程参数位置索引

* */

public Para(int aType, int aDirect, int aIndex) {

this.type = aType;

this.direct = aDirect;

this.index = aIndex; 

}

 

/** 获得参数类型 */

public int getType() {

return type;

}

 

/** 设置参数类型 */

public void setType(int type) {

this.type = type;

}

 

/** 获得参数值 */

public Object getValue() {

return value;

}

 

/** 设置存储过程调用参数值 */

public void setValue(Object value) {

this.value = value;

}

 

/** 获得存储过程参数数据流向 */

public int getDirect() {

return direct;

}

 

/** 设置存储过程参数数据流向 */

public void setDirect(int direct) {

this.direct = direct;

}

 

/** 获得参数位置索引 */

public int getIndex() {

return index;

}

 

/** 设置参数位置索引 */

public void setIndex(int index) {

this.index = index;

}

}

 

 

调用实例：

 

/**

 * CLOB 和BLOB 字段通过存储过程进行文件上传

 * @version Ver1.0

 * <p>

<b>表生成SQL：</b><br>

-- Create table

create table DEMO_CLOB_TEST

(

  ID     NUMBER(4),

  C_CLOB CLOB,

  B_BLOB BLOB

)

tablespace PROCEDURETEST

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

</p>

<p>

<b>存储过程PL/SQL：</b><br>

CREATE OR REPLACE PROCEDURE DEMO_LOB_SProc(id IN NUMBER, cclob IN CLOB, bblob IN BLOB)  AS

BEGIN

   INSERT INTO SYSTEM.DEMO_CLOB_TEST (ID,C_CLOB,B_BLOB) VALUES (id, EMPTY_CLOB(),EMPTY_BLOB());

   UPDATE SYSTEM.DEMO_CLOB_TEST s SET  s.C_CLOB = cclob ,s.b_blob=bblob WHERE s.id = id ;

commit;

END DEMO_LOB_SProc;

</p>

 */

import java.io.BufferedReader;

import java.io.FileInputStream;

import java.io.FileReader;

import java.io.InputStream;

import java.io.Writer;

import java.util.ArrayList;

import oracle.jdbc.driver.OracleConnection;

import oracle.jdbc.OracleTypes;

import oracle.sql.CLOB;

import oracle.sql.BLOB;

/**执行查询，插入记录*/

import com.meritit.cdtf.db.dbpool.*;

import com.meritit.cdtf.db.proc.OraProc;

import com.meritit.cdtf.db.proc.Para;

import com.meritit.cdtf.db.proc.exception.OracleProException;

 

public class LobUpLoad {

/**

* @param args

*/

public static void main(String[] args) {

// TODO Auto-generated method stub

LobUpLoad bup=new LobUpLoad();

DBPara dbp=new DBPara("dbp.xml","dbp1");

OracleConnectionManager OCM = new OracleConnectionManager(dbp);

OracleConnection oraConn = (OracleConnection) OCM.getConnection();

try {

CLOB clob=bup.getCLOB("D://clobtest.txt", oraConn);

BLOB blob=bup.getBLOB("D://blobtest.exe", oraConn);

ArrayList<Para> ayl=new ArrayList<Para>();

ayl.add(new Para(OracleTypes.NUMBER, 1002, Para.IN, 1));

ayl.add(new Para(OracleTypes.CLOB, clob, Para.IN, 2));

ayl.add(new Para(OracleTypes.BLOB, blob, Para.IN, 3));

OraProc orp=new OraProc(oraConn,"DEMO_LOB_SProc",ayl);

orp.execute();

CLOB.freeTemporary(clob);

} catch (OracleProException ex2) {

ex2.printStackTrace();

} catch (Exception ex2) {

ex2.printStackTrace();

} finally {

try {

if (OCM != null) {

OCM.freeConnection();

}

} catch (Exception ex1) {

ex1.printStackTrace();

}

}

}

 

/**

* 利用temporary clob 生成 CLOB 对象并返回

* 

* @param filename

*            需要上传的文件全路径，例如：D:/clob.txt

* @parm conn 字符串连接对象

*/

private CLOB getCLOB(String filename, OracleConnection conn)

throws Exception {

CLOB tempClob = null;

try {

// create a new temporary CLOB

       String lineSep = System.getProperty("line.separator");

 

       BufferedReader br = new BufferedReader(new FileReader(filename));

       String nextLine = "";

       StringBuffer sb = new StringBuffer();

       while ((nextLine = br.readLine()) != null) {

         sb.append(nextLine);

         sb.append(lineSep);

       }

       // Convert the content to string.

       String clobData = sb.toString();

tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);

 

// Open the temporary CLOB in readwrite mode to enable writing

tempClob.open(CLOB.MODE_READWRITE);

 

// Get the output stream to write

 

Writer tempClobWriter = tempClob.setCharacterStream(0);

 

// Write the data into the temporary CLOB

tempClobWriter.write(clobData);

 

// Flush and close the stream

tempClobWriter.flush();

tempClobWriter.close();

 

// Close the temporary CLOB

tempClob.close();

 

} catch (Exception exp) {

tempClob.freeTemporary();

System.out.println("Exception thrown in getCLOB method "

+ "of given status : " + exp.getMessage());

}

return tempClob;

}

/**

* 利用temporary blob 生成 CLOB 对象并返回

* 

* @param filename

*            需要上传的文件全路径，例如：D:/clob.txt

* @parm conn 字符串连接对象

*/

private BLOB getBLOB(String filename, OracleConnection conn)

throws Exception {

BLOB tempBlob = null;

try {

// create a new temporary CLOB

       InputStream in = new FileInputStream(filename);

       int readlength;

       java.io.OutputStream out;

       tempBlob = oracle.sql.BLOB.createTemporary(conn, false,CLOB.DURATION_SESSION);

       out = tempBlob.setBinaryStream(0);

       byte[] b=new byte[100];

       readlength = in.read(b);

       while (readlength != -1) {

        out.write(b, 0, readlength);

        readlength = in.read(b);

       }

       out.flush();

       out.close();

} catch (Exception exp) {

if (tempBlob!=null){

tempBlob.freeTemporary();

}

System.out.println("Exception thrown in getBLOB method "

+ "of given status : " + exp.getMessage());

exp.printStackTrace();

}

return tempBlob;

}

}

存储过程执行：

package com.meritit.cdtf.db.proc.demo;

/**

 *   存储过程执行,无返回值

 *   参数为字符串

 *   @version Ver1.0

 *   @author 郭联伟 guolw@meritit.com

<p>

<b>表生成SQL：</b><br>

create table P_ID

(

  ID   VARCHAR2(100),

  NAME VARCHAR2(100)

)

tablespace PROCEDURETEST

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

</p>

<p>

<b>存储过程PL/SQL：</b><br>

CREATE OR REPLACE PROCEDURE TEST_EXE(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  AS

BEGIN 

   INSERT INTO SYSTEM.P_ID (ID,NAME) VALUES (PARA1, PARA2);

END TEST_EXE;

</p>

 **/

import java.sql.SQLException;

import java.util.ArrayList;

import oracle.jdbc.driver.OracleConnection;

import oracle.jdbc.OracleTypes;

/**执行查询，插入记录*/

import com.meritit.cdtf.tools.*;

import com.meritit.cdtf.db.dbpool.*;

import com.meritit.cdtf.db.proc.OraProc;

import com.meritit.cdtf.db.proc.Para;

import com.meritit.cdtf.db.proc.exception.OracleProException;

public class ProcExecute {

public static void main(String[] args) {

Tools.Debug("数据库连接池测试", MsgConstant.DEBUG_START);

DBPara dbp=new DBPara("dbp.xml","dbp1");

OracleConnectionManager OCM = new OracleConnectionManager(dbp);

OracleConnection oraConn = (OracleConnection) OCM.getConnection();

OraProc orp=null;

try {

ArrayList<Para> ayl=new ArrayList<Para>(); 

ayl.add(new Para(OracleTypes.VARCHAR, "ID_testvalue1", Para.IN, 1));

ayl.add(new Para(OracleTypes.VARCHAR, null, Para.OUT, 2));

orp=new OraProc(oraConn,"SYSTEM.DEMO_SVR",ayl);

orp.execute();

} catch (OracleProException ex2) {

ex2.printStackTrace();

} catch (Exception ex2) {

ex2.printStackTrace();

} finally {

if (orp!=null){

try {

orp.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

try {

if (OCM != null) {

OCM.freeConnection();

}

} catch (Exception ex1) {

ex1.printStackTrace();

}

 

}

 

}

 

}