使用java调用存储过程的通用封装

本文章所提及的全部源码和测试用例已经上传到http://download.csdn.net/source/459115欢迎朋友们下载、测试、评判、指正,给出宝贵意见.谢谢!

对于在java中调用存储过程,我一直因为是一件比较头疼的事情,因为各个数据库的实现往往不相同,这样就很难保证同一套程序在sqlserver和oracle上都能执行.尽管两个数据库中的存储过程名字和参数已经基本相同,以前用spring封装过一个,用了几个抽象类做了简单的设计,但是总感觉不是很好,而且最新的spring貌似已经废弃那几个类了,看来是有问题,周末花了些时间重新写了一个比较简单赤裸的封装.代码虽然不多但是就不一一讲解了.这里先把存储过程发出来.

sqlsever版

/*返回2个结果集,外加输出out参数*/
create procedure p1
@outputParam int output,
@id          int
as
select @outputParam=@id
select  @outputParam+@outputParam
select  @outputParam
/*直接返回值*/
create procedure p2
@outputParam int output,
@id          int
as
select @outputParam=@id
return @outputParam 
/*返回一个结果集*/
create procedure p3
@outputParam int output,
@id          int
as
select @outputParam=@id
select @outputParam
/*最简单的形式*/
create procedure p4
as
print 'helloword'

 

对应的oracle版,oracle中存储过程好像不能直接返回值.至少我还不知道怎么返回.目前依靠out参数返回游标作为结果集。

CREATE OR REPLACE PROCEDURE p1(
       outputParam OUT int,
       id IN int,
       CURSOR1 out sys_refcursor,
       CURSOR2 out sys_refcursor
) AS
BEGIN
    select id into p1.outputParam from dual;
    OPEN CURSOR1 FOR select 'syj1' as id from dual union all select 'syj2' as id from dual;
    OPEN CURSOR2 FOR select 'abc1' as id from dual union all select 'abc2' as id from dual;
END p1;

CREATE OR REPLACE PROCEDURE p3(
       outputParam IN OUT int,
       id IN int,
       CURSOR1 out sys_refcursor
) AS
BEGIN
    select id into P3.outputParam from dual;
   OPEN CURSOR1 FOR select 'syj1' as id from dual union all select 'syj2' as id from dual;
END P3;

CREATE OR REPLACE PROCEDURE p4
 AS
BEGIN
  dbms_output.put_line('helloword');
END P4;

 因为mssql和oracle对结果集的返回形式不相同所以就很难在java代码中保证一致的调用方式。解决这个方法的唯一途径就是分析他们的相同和不同之处，想办法在设计上解决。经典的jdbc调用,在这里就不说了,百度一下有很多资料,下面说下经过我这次封装后的调用方式,无论是oracle还是mssql都可以使用下面代码执行。只要把ORACLE过程中的游标参数放置到最后声明就可以了。

public void test1() {
  ProcedureResult result = getSimpleJdbcTemplate().execProcedure("p1",
    new ProcedureCallBack() {//执行返回结果集的过程
     public Object mapRow(ResultSet rs, int rowNum)
       throws SQLException {
      return rs.getObject(1);
     }

     public void registerParameter() throws SQLException {
      registerOutParameter(1, Types.INTEGER);
      setInt(2, 9);
      addOracleCursor(3);
      addOracleCursor(4);
     }
    });
  TestUtil.println(result);
 }
   
 public void test2() {//执行直接返回值的过程 
  ProcedureResult result = getSimpleJdbcTemplate().execProcedure("p2",
    Types.INTEGER, new ProcedureCallBack() {

     public void registerParameter() throws SQLException {
      registerOutParameter(1, Types.INTEGER);
      setInt(2, 9);
      addOracleCursor(3);
     }

    });
  TestUtil.println(result);
 }
  
 public void test4() {//执行普通的简单过程
  ProcedureResult result = getSimpleJdbcTemplate().execProcedure("p4",
    new ProcedureCallBack() {
    });
  TestUtil.println(result);
 }

 

更多使用方法可以参考junit测试代码包

核心代码部分摘要如下关键地方我都加了注释

    /**
     * 执行存储过程
     * 
     * @param procedure过程名
     * @param sqlType
     *            返回值类型,无返回值传null
     * @param procedureCallBack
     *            回调对象
     * @return
     */
    private ProcedureResult execProc(String procedure, Integer sqlType,
            ProcedureCallBack procedureCallBack) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ProcedureResult procResult = new ProcedureResult();
        try {
            procedureCallBack.jt = this;
            conn = getConnection();// 取连接
            procedureCallBack.registerParameter();// 计算参数的个数
            int parameterSize = procedureCallBack.parametersCount;// 取参数的个数
            procedureCallBack.parametersCount = 0;
            if (parameterSize > 0)
                procedure = procedure + getProcParameters(parameterSize, "?");// 根据参数的个数拼装sql
            String sql = "{" + (sqlType != null ? "?=" : "") + "call "
                    + procedure + "}";
            logger.debug("exec " + sql);
            CallableStatement cstm = conn.prepareCall(sql);// 预编译过程
            procedureCallBack.cstm = cstm;
            procedureCallBack.startParametersIdx = sqlType != null ? 1 : 0;// 对外界忽略带返回值过程的参数下标
            if (sqlType != null)
                procedureCallBack.registerOutParameter(0, sqlType.intValue());// 注册带返回值过程的返回类型oracle不支持mssql支持
            procedureCallBack.registerParameter();// 注册参数
            boolean results = cstm.execute();// 执行过程
            int rsIndex = 1;// 结果集个数计数器
            for (; results; rsIndex++) {
                rs = cstm.getResultSet();// 取当前结果集
                List list = new ArrayList();// 为当前结果集创建一个容器
                while (rs.next())
                    list.add(procedureCallBack.mapRow(rs, rsIndex));
                procResult.addRs(list);
                results = cstm.getMoreResults();// 取下一个结果集
            }
            Map map = procedureCallBack.getOutParameters();// 取声明的out参数,
            Iterator iterator = map.keySet().iterator();// 遍历声明的out参数
            while (iterator.hasNext()) {
                String key = iterator.next().toString();// out参数类型
                int idx = Integer.valueOf(key).intValue();
                if (new Integer(-10).equals(map.get(key))) {// oracle结果集列表是通过游标的形式利用out参数输出的oracle.jdbc.OracleTypes.CURSOR的值是-10
                    closeResultSet(rs);// 关闭上次使用的结果集
                    rs = (ResultSet) cstm.getObject(Integer.valueOf(key)
                            .intValue());// 从out参数中取结果集
                    List list = new ArrayList();// 为当前结果集创建一个容器
                    while (rs.next())
                        list.add(procedureCallBack.mapRow(rs, rsIndex));
                    rsIndex++;// 结果集计数器累加
                    procResult.addRs(list);
                } else if (idx == 1 && sqlType != null)// 如果过程有返回值,从第一个out参数中取得这个返回值
                    procResult.setValue(JdbcUtils.getCallableStatementValue(
                            cstm, idx));
                else
                    procResult.getOutput().add(
                            JdbcUtils.getCallableStatementValue(cstm, idx));// 取的普通的out参数输出到out输出容器
            }
            if (sqlType == null && procResult.getOutput().size() > 0)
                // 如果过程没用返回值,例如oracle过程根本不支持返回值,将第一个非游标的out参数返回值当作过程返回值
                procResult.setValue(procResult.getOutput().get(0));
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return procResult;
    }
    public class ProcedureResult {
    private List rs = new ArrayList();//过程返回的n个结果集列表
    private List output = new ArrayList();//过程的out参数输出
    private Object value;//过程本身的返回值
    略
    }

上面代码基本上保证了在oracle和sqlserver两个数据库中存储过程名称相同参数顺序相同（oracle中的游标置于最后）的的情况下，使用同样的java代码进行调用。不知道是否有遗漏之处，如果有那个地方还没有覆盖到或者有bug请来访朋友指正，共同学习，分析快乐。