spring 调用存储过程

由于项目中想调用存储过程，写了一个调用存储过程公共类;

注意一下：过程名要加数据库访问用户(有权限的用户)
格式为：
(1)dbuser + procedure 
(2)dbuser + package + function

基类：
XbaseJdbcDaoImpl.java 的源程序如下：
/**
     * 执行存储过程
     * @param sql
     * @param declaredParameters
     * @param inPara
     * @return map
     */
    public Map executeCall(String sql, List declaredParameters, Map inPara) {
        return this.executeCall(sql,declaredParameters,inPara,false);
    }

    /**
     *
     * @param sql
     * @param declaredParameters
     * @param inPara
     * @param function  true 是调用函数  false 调用过程
     * @return map
     */
    public Map executeCall(String sql, List declaredParameters, Map inPara,boolean function) {
        XbaseStoreProcedure xsp = new XbaseStoreProcedure(this.getJdbcTemplate(),sql,declaredParameters);
        xsp.setFunction(function);
        return xsp.execute(inPara);
    }

    /**
     * 返回结果集
     * @param sql
     * @param declaredParameters
     * @param inPara
     * @return map
     */
    public List<HashMap> executeCallResultList(String sql, List declaredParameters, Map inPara){
        XbaseStoreProcedure xsp = new XbaseStoreProcedure(this.getJdbcTemplate(),sql);
        for(int i=0;i<declaredParameters.size();i++){
            SqlParameter parameter = (SqlParameter)declaredParameters.get(i);
            if(parameter instanceof SqlOutParameter){
                xsp.setOutParameter(parameter.getName(),parameter.getSqlType());
            }else xsp.setParameter(parameter.getName(),parameter.getSqlType());
        }
        xsp.SetInParam(inPara);
        xsp.execute();

        return xsp.set;
    }

XbaseStoreProcedure.java 的源程序
package com.xjj.app.jdbc.xbase;

import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.core.*;

import javax.sql.DataSource;
import java.util.*;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
* Created by IntelliJ IDEA.
* User: Administrator_xusg
* Date: 2008-2-21
* Time: 11:34:01
* DESP:
* To change this template use File | Settings | File Templates.
*/
public class XbaseStoreProcedure extends StoredProcedure {
    private Map initParameterData = new Hashtable();             //数据传入

    private boolean callFunction = false;                       //是过程还是函数

    public ArrayList<HashMap> set = new ArrayList<HashMap>();   //声明一个用于接收结果集的数据结构,其中的元素为row,用map存放

    private Map inParam;                                        //输入参数

    private RowMapper rm = new RowMapper(){
        public Object mapRow(ResultSet rs,int rowNum) throws SQLException {
            return null;//不用从存储过程本身获取结果
        }
    };

    //回调方法
    private  RowMapperResultSetExtractor callback = new RowMapperResultSetExtractor (rm){
        public Object extractData(ResultSet rs) throws SQLException{//回调处理
            int count = rs.getMetaData().getColumnCount();
            String[] header = new String[count];
            for(int i=0;i<count;i++)
            header[i] = rs.getMetaData().getColumnName(i+1);
            while(rs.next()){
                HashMap<String,String> row = new HashMap(count+7);
                for(int i=0;i<count;i++){
                    row.put(header[i],rs.getString(i+1));
                }
                set.add(row);
            }
            return null;
        }
    };

    //设置输出集的分类(游标)
    public void setOutParameter(String column,int type){
        declareParameter(new SqlOutParameter(column, type,callback));//利用回调句柄注册输出参数
    }

    //设置输入值的数据类型
    public void setParameter(String column,int type){
        declareParameter(new SqlParameter(column, type));
    }

    public void SetInParam(Map inParam){
        this.inParam = inParam;
    }

    public Map execute() {
        compile();
        return execute(this.inParam);
    }

    /**
     * 构造存储过程的参数
     * @param dataSource
     * @param sql
     */
    public XbaseStoreProcedure(DataSource dataSource,String sql){
        super(dataSource,sql);
        this.compile();
    }

    protected XbaseStoreProcedure(JdbcTemplate jdbcTemplate, String sql) {
        setJdbcTemplate(jdbcTemplate);
        setSql(sql);
    }

    /**
     *
     * @param jdbcTemplate
     * @param sql
     * @param declareParameterList 输入值及输出值
     */
    protected XbaseStoreProcedure(JdbcTemplate jdbcTemplate, String sql,List declareParameterList) {
        setJdbcTemplate(jdbcTemplate);
        setSql(sql);
        for(int i=0;i<declareParameterList.size();i++){
            SqlParameter parameter = (SqlParameter)declareParameterList.get(i);
            this.declareParameter(parameter);
        }
        this.compile();
    }

    public Map getInitParameterData() {
        return initParameterData;
    }

    public void setInitParameterData(Map initParameterData) {
        this.initParameterData = initParameterData;
    }

    public void setCallFunction(boolean callFunction) {
        this.callFunction = callFunction;
        this.setFunction(callFunction);
    }
}



使用实例如下：
package com.xjj.app.jdbc.test;

import com.xjj.app.jdbc.xbase.XbaseJdbcDaoImpl;
import com.xjj.app.jdbc.xbase.DbConfig;

import java.util.*;
import java.sql.Types;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import oracle.jdbc.OracleTypes;

/**
* Created by IntelliJ IDEA.
* User: Administrator_xusg
* Date: 2008-2-21
* Time: 14:16:38
* DESP:
* To change this template use File | Settings | File Templates.
*/
public class TestClassDao extends XbaseJdbcDaoImpl {

    /**
     * create or replace procedure insertValue(vid in varchar,vname in varchar,vemail in varchar) as
        begin
             insert into callableTest(id,name,email) values(vid,vname,vemail);
        end;

     */
    public void insert(){
        String sql = DbConfig.DBUSRE+".insertValue";

        List list = new ArrayList();
        list.add(new SqlParameter("id",Types.VARCHAR));
        list.add(new SqlParameter("name",Types.VARCHAR));
        list.add(new SqlParameter("email", Types.VARCHAR));

        Map map = new HashMap();
        map.put("id","12121210121212100000");
        map.put("name","nameSpring");
        map.put("email","emailSpring");
        this.executeCall(sql,list,map);
    }

    /**
     * create or replace procedure findvalueById(vid in varchar2,vname out varchar2,vemail out varchar2) as
        begin
             select t.name,t.email into vname,vemail from callableTest t where t.id= vid;
        end;
     */
    public void findValueById(){
        String sql = DbConfig.DBUSRE+".findvalueById";
        List list = new ArrayList();
        list.add(new SqlParameter("id",Types.VARCHAR));
        list.add(new SqlOutParameter("name",Types.VARCHAR));
        list.add(new SqlOutParameter("email",Types.VARCHAR));

        Map map = new HashMap();
        map.put("id","12121210121212100000");
        Map a = this.executeCall(sql,list,map);
        System.out.println(""+a.get("name"));
        System.out.println(""+a.get("email"));
    }

    //返回游标
    public void findlist(){
        String sql = DbConfig.DBUSRE+".text_a2";
        List list = new ArrayList();
        list.add(new SqlOutParameter("cur_result_out", OracleTypes.CURSOR));
        List listresult = this.executeCallResultList(sql,list,new HashMap());
        for(int i=0;i<listresult.size();i++){
            Map m = (HashMap)listresult.get(i);
            Iterator iterator = m.keySet().iterator();
            while(iterator.hasNext()){
                System.out.println(""+m.get(iterator.next()));
            }
        }
    }


}

评论

3 楼 100Air 2009-08-20  

DbConfig.DBUSRE.findvalueById
其中：
DbConfig.DBUSRE 是数据库用户名(为了方便修改，才这样写),而findvalueById 是存储过程名称

2 楼 100Air 2009-08-20  

DbConfig.DBUSRE.findvalueById
其中：
DbConfig.DBUSRE 是数据用户名(为了方便修改，才这样写),而findvalueById 是存储过程名称

1 楼 itlieying 2009-08-17  

Air,你好，能提供下com.xjj.app.jdbc.xbase.DbConfig吗？
最近我也在研究java调用oracle存储过程，能再提供一些实例吗？