spring 中StoredProcedure的用法载

浏览数：14 / 时间：2015年06月08日

StoredProcedure是一个抽象类，必须写一个子类来继承它，这个类是用来简化JDBCTemplate执行存储过程操作的。

首先我们写一个实现类：

package com.huaye.framework.dao;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.object.StoredProcedure;

/**
 * Name:StoredProcedureTemplate User: HP Date: 2007-7-21 Time: 7:40:01
 * Description:
 */
public class StoredProcedureTemplate extends StoredProcedure {

    private HashMap<String, Object> map = new HashMap<String, Object>();

    public StoredProcedureTemplate() {
        super();

    }

    
    public HashMap getMap()
    {
        return this.map;
    }
    
    public void setValue(String key, Object obj) {
        map.put(key, obj);
    }

    public Map execute() {
        if (this.getSql() == null || this.getSql().equals(""))
            return null;
        this.compile();
        return execute(map);
    }

    public void setVarcharParam(String param) {
        this.declareParameter(new SqlParameter(param, Types.VARCHAR));
    }

    public void setDoubleParam(String param) {
        this.declareParameter(new SqlParameter(param, Types.DOUBLE));
    }

    public void setIntegerParam(String param) {
        this.declareParameter(new SqlParameter(param, Types.INTEGER));
    }

    public void setVarcharOutParam(String param) {
        this.declareParameter(new SqlOutParameter(param, Types.VARCHAR));
    }

    public void setDoubleOutParam(String param) {
        this.declareParameter(new SqlOutParameter(param, Types.DOUBLE));
    }

    public void setIntegerOutParam(String param) {
        this.declareParameter(new SqlOutParameter(param, Types.INTEGER));
    }


    public void setInParam(String param,int valueType)
    {
        this.declareParameter(new SqlParameter(param, valueType));
        
    }
    
    public void setOutParam(String param,int valueType)
    {
        this.declareParameter(new SqlOutParameter(param, valueType));
        
    }
    
    public void setReturnParam(String param, RowMapper rowMapper) {
        this.declareParameter(new SqlReturnResultSet(param,rowMapper));
    }

}

写一个测试：

public void test2() {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                "classpath:spring/applicationContext-base.xml");
        JdbcTemplate jdbc = (JdbcTemplate) context.getBean("jdbcTemplate");

        StoredProcedureTemplate template = new StoredProcedureTemplate();
        
        template.setJdbcTemplate(jdbc);
        template.setSql("testproc");
        //注意有返回结果集的时候，第一个参数必须设置为返回结果集参数，不然会报错。
        template.setReturnParam("rows", new FirstReportRowMapper());
        
        template.setIntegerParam("@parama");
        
        template.setValue("@parama", 9);
        
        Map map = template.execute();
        Object o = map.get("rows");
        List<FirstReportVO> list = (List<FirstReportVO>)o;
        for (FirstReportVO vo : list) {
            System.out.println(vo.getSortID()+","+vo.getSortName());
        }
    }