spring mvc 存储过程调用

最新推荐文章于 2021-02-11 10:20:28 发布

Czijun1990914

最新推荐文章于 2021-02-11 10:20:28 发布

阅读量766

点赞数

分类专栏：存储过程文章标签：存储过程 spring mvc

存储过程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、实体类和数据库映射，就是将数据库中的字段和java实体类中的对象对应起来，这是最普遍的做法。

就是这个样子：

  <resultMap id="BaseResultMap" type="com.kt.model.Funeral" >
    <id column="applyid" property="applyid" jdbcType="BIGINT" />
    <id column="no" property="no" jdbcType="SMALLINT" />
    <result column="employeecd" property="employeecd" jdbcType="BIGINT" />
    <result column="agentcd" property="agentcd" jdbcType="BIGINT" />
    <result column="applydate" property="applydate" jdbcType="DATE" />
    <result column="applytype" property="applytype" jdbcType="SMALLINT" />
    <result column="startday" property="startday" jdbcType="DATE" />
    <result column="endday" property="endday" jdbcType="DATE" />
    <result column="applyreason" property="applyreason" jdbcType="VARCHAR" />
    <result column="detailreason" property="detailreason" jdbcType="VARCHAR" />
    <result column="applytime" property="applytime" jdbcType="TIMESTAMP" />
    <result column="admitflg" property="admitflg" jdbcType="SMALLINT" />
  </resultMap>

二、将数据库的字段和java的Map对应起来，也就是key-value的形式，个人认为这种方法适合存储过程的执行，因为存储过程的参数很多情况下都是通过自定义

来进行参数传递的。这样，就ORM的形式更加灵活。

具体实例：

1、dao层接口定义

public interface IFuneralDao {
    
    void saveFuneral(Map<String, Object> funeralMap);
    
    void getFuneral(Map<String, Object> funeralMap);
    
    @SuppressWarnings("rawtypes")
    List<Map> getFuneralMore(@Param("applyId") Long applyId);
}

2、dao层对应mapper.xml

 <!-- 添加数据 -->
  <parameterMap type="Map" id="InsertFuneralMap">
    <parameter property="v_in_employeecd" jdbcType="BIGINT" javaType="Long" mode="IN" />
    <parameter property="v_in_applytype" jdbcType="SMALLINT" javaType="Short" mode="IN" />
    <parameter property="v_in_applyreason" jdbcType="VARCHAR" javaType="String" mode="IN" />
    <parameter property="v_in_detailreason" jdbcType="VARCHAR" javaType="String" mode="IN" />
    <parameter property="v_in_startend" jdbcType="VARCHAR" javaType="String" mode="IN" />
    <parameter property="v_in_agentcd" jdbcType="BIGINT" javaType="Long" mode="IN" />
    <parameter property="o_result" jdbcType="SMALLINT" javaType="Short" mode="OUT"/>
 </parameterMap>
  
   <select id="saveFuneral" parameterMap="InsertFuneralMap" statementType="CALLABLE">  
      {call workplan.proc_insertfuneral_info(
              ?,?,?,?,?,?,?
      ) }  
  </select>  
  
  <!-- 抽取数据，返回多个结果存到List<Map>中-->
  <resultMap type="Map" id="getFuneralMoreResult">
     <result column="employeecd" property="employeecd" javaType="Long" jdbcType="BIGINT" />
     <result column="employeename" property="employeename" javaType="String" jdbcType="VARCHAR"/>
     <result column="applytype" property="applytype" javaType="Short" jdbcType="SMALLINT"/>
     <result column="reason" property="reason" javaType="String" jdbcType="VARCHAR"/>
     <result column="startend" property="startend" javaType="String" jdbcType="VARCHAR"/>
     <result column="days" property="days" javaType="String" jdbcType="VARCHAR"/>
     <result column="method" property="method" javaType="Integer" jdbcType="SMALLINT"/>
  </resultMap>
  
  <!-- 查询信息 -->
 <sql id="funeral_info_single" >
    "employeecd", "employeename", "applytype", "reason", "startend", "days", "method"
  </sql>
  
  <select id="getFuneralMore"  resultMap="getFuneralMoreResult">
          select 
           <include refid="funeral_info_single" />
           from
           workplan.proc_getfuneral_info(#{applyId,jdbcType=BIGINT})
  </select>
  
  <!-- 查询单条数据 -->
  <parameterMap type="Map" id="getFuneralMap">
    <parameter property="v_in_applyid" jdbcType="BIGINT" javaType="Long" mode="IN" />
    <parameter property="employeecd" jdbcType="BIGINT" javaType="Long" mode="OUT" />
    <parameter property="employeename" jdbcType="VARCHAR" javaType="String" mode="OUT" />
    <parameter property="applytype" jdbcType="SMALLINT" javaType="Short" mode="OUT" />
    <parameter property="reason" jdbcType="VARCHAR" javaType="String" mode="OUT" />
    <parameter property="startend" jdbcType="VARCHAR" javaType="String" mode="OUT" />
    <parameter property="days" jdbcType="SMALLINT" javaType="Short" mode="OUT"/>
    <parameter property="method" jdbcType="SMALLINT" javaType="Short" mode="OUT"/>
 </parameterMap>
  
   <select id="getFuneral" parameterMap="getFuneralMap" statementType="CALLABLE">  
      {call workplan.proc_getfuneral_info(
              ?,?,?,?,?,?,?,?
      ) }  
  </select>

3、service层调用

@Service("funeralService")
public class FuneralServiceImpl implements IFuneralService {
    @Resource
    private IFuneralDao funeralDao;
    
    /**
     * 添加信息
     * 将参数通过Map传入到数据库后,返回值同样会保存到传入值的那个Map中
     * @param funeralProc
     * @return 1、成功 0、失败
     */
    public boolean saveFuneral(Map<String, Object> funeralMap) {
        funeralDao.saveFuneral(funeralMap); 
        return (Short) funeralMap.get("o_result")>0;
    }
    
    /**
     * 获取单条信息
     */
    public Map<String , Object> getFuneral(Map<String, Object> funeralMap){
        funeralDao.getFuneral(funeralMap);
        return funeralMap;
    }
    /**
     * 获取多条数据，用List存取
     */
    @SuppressWarnings("rawtypes")
    public List<Map> getFuneralMore(Long applyId) {
        return funeralDao.getFuneralMore(applyId);
    }

    
}