SpringMVC与Mybatis集合实现调用存储过程、事务控制

最新推荐文章于 2024-02-08 11:47:25 发布

Jay_1989

最新推荐文章于 2024-02-08 11:47:25 发布

阅读量1.1w

点赞数 6

分类专栏： Java 文章标签： ssm 存储过程 PROCEDURE 事务 Transactional

本文链接：https://blog.csdn.net/Jay_1989/article/details/52184288

版权

Java 专栏收录该内容

80 篇文章 2 订阅

订阅专栏

在SSM框架中经常会用到调用数据库中的存储过程、以及事务控制，下面以保存某单据为例，介绍一下：

1、Oracle中存储过程代码如下(主要逻辑将单据编码自动加1，并将该单据编码返回)：

CREATE OR REPLACE PROCEDURE "UPDATE_DJBHZT" (p_GSID in varchar2, p_TBLNAME in varchar2,
                                   NewRecNo out Number) as
begin
    update BHDJ set BHDJ02 = BHDJ02+1 where GSXX01 = p_GSID and BHDJ01 = p_TBLNAME;
    if sql%rowcount = 0 then
       insert into BHDJ (GSXX01, BHDJ01,BHDJ02) values(p_GSID, p_TBLNAME,1);
    end if;
    select BHDJ02 into NewRecNo from BHDJ where GSXX01 = p_GSID and BHDJ01 = p_TBLNAME;
end;

2、Mybatis中代码如下：

<select id="update_djbhzt" parameterType="java.util.Map" statementType="CALLABLE">
<![CDATA[  
    {call UPDATE_DJBHZT(#{p_GSID,mode=IN,jdbcType=VARCHAR},#{p_TBLNAME,mode=IN,jdbcType=VARCHAR},#{NewRecNo,mode=OUT,jdbcType=BIGINT})}  
]]>
</select>

3、Dao层代码如下：

package com.pcmall.dao.sale.stock;

import java.util.List;
import java.util.Map;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.dao.common.BaseMapper;
import com.pcmall.domain.sale.stock.Zcd;

public interface ZcdMapper extends BaseMapper<Zcd> {
    
    void update_djbhzt(Map<String,Object> map); 

}

4、Service层代码如下：

接口：

package com.pcmall.service.sale.stock;

import java.util.List;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.domain.sale.order.HssnCmmx;
import com.pcmall.domain.sale.stock.Zcd;
import com.pcmall.domain.sale.stock.Zcditem;
import com.pcmall.domain.sale.user.User;
import com.pcmall.domain.vo.ResponseVO;
import com.pcmall.service.common.IBaseService;

public interface IZcdService extends IBaseService<Zcd> {

	Long getZcdNo(String gsxx01, String tablename);

	ResponseVO saveZcd(Zcd zcd, User user) throws Exception;
	
}

实现类：

package com.pcmall.service.sale.stock.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.axis.holders.SchemaHolder;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.google.common.collect.Collections2;
import com.pcmall.common.utils.DateUtils;
import com.pcmall.dao.sale.stock.ZcdMapper;
import com.pcmall.dao.sale.stock.ZcditemMapper;
import com.pcmall.domain.sale.order.HssnCmmx;
import com.pcmall.domain.sale.promotion.HsCxlx;
import com.pcmall.domain.sale.stock.Zcd;
import com.pcmall.domain.sale.stock.Zcditem;
import com.pcmall.domain.sale.stock.bo.CkspBO;
import com.pcmall.domain.sale.user.User;
import com.pcmall.domain.vo.ResponseVO;
import com.pcmall.service.common.AbstractServiceImpl;
import com.pcmall.service.sale.order.IOrderService;
import com.pcmall.service.sale.stock.IStockService;
import com.pcmall.service.sale.stock.IZcdService;

@Service
public class ZcdServiceImpl extends AbstractServiceImpl<Zcd> implements IZcdService {
	
	@Resource
	private ZcdMapper zcdMapper;
	
	@Resource
	private ZcditemMapper zcditemMapper;
	
	@Resource
	private IStockService stockServiceImpl;
	
	
	@Transactional(rollbackFor = Exception.class)
	@Override
	public ResponseVO saveZcd(Zcd zcd, User user) throws Exception {
		ResponseVO responseVO = new ResponseVO();
		Long zcd01 = getZcdNo(zcd.getGsxx01(), "ZCD");
		zcd.setZcd01(zcd01);
		zcd.setZcd05(user.getRyxx().getRyxx02());
		zcd.setZcd06(new Date());
		
		Date nowTime = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("hhmmssms");
		zcd.setTime01(sdf.format(nowTime));
		
		
		for(Zcditem zcditem : zcd.getZcditem()){
			zcditem.setZcd01(zcd01);
			zcditemMapper.insertSelective(zcditem);
		}
		
		zcdMapper.insertSelective(zcd);
		
		responseVO.setData(zcd);
		return responseVO;
	}


	@Override
	public Long getZcdNo(String gsxx01, String tablename) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("p_GSID", gsxx01);
		map.put("p_TBLNAME", tablename);

		zcdMapper.update_djbhzt(map);
		Long NewRecNo = (Long) map.get("NewRecNo");
		return NewRecNo;
	}
}

5、Control层代码如下：

package com.pcmall.controller.stock.zcd;

import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.github.miemiedev.mybatis.paginator.domain.PageBounds;
import com.pcmall.common.base.BaseController;
import com.pcmall.domain.sale.stock.Zcd;
import com.pcmall.domain.sale.stock.Zcditem;
import com.pcmall.domain.sale.stock.bo.CkspDetailBO;
import com.pcmall.domain.sale.stock.bo.ZcdBO;
import com.pcmall.domain.sale.system.Gzzqx;
import com.pcmall.domain.sale.system.GzzqxKey;
import com.pcmall.domain.sale.system.Ryxx;
import com.pcmall.domain.sale.user.Czy;
import com.pcmall.domain.sale.user.User;
import com.pcmall.domain.vo.ResponseVO;
import com.pcmall.service.sale.stock.IStockService;
import com.pcmall.service.sale.stock.IZcdService;
import com.pcmall.service.sale.system.IGzzqxService;

@Controller
@RequestMapping("/stock/zcd")
public class ZCDController extends BaseController {
	
	private static Logger logger=LoggerFactory.getLogger(ZCDController.class);
	
	@Resource
	private IZcdService zcdServiceImpl;
	
	
	@Resource 
	private IStockService stockServiceImpl;
	
	@Resource
	private IGzzqxService gzzqxServiceImpl;
	
	
	
	@RequestMapping("/saveZcd")
	@ResponseBody
	public ResponseVO saveZcd(HttpServletRequest request, @RequestBody Zcd zcd){
		ResponseVO responseVO = new ResponseVO();
		try{
			responseVO = zcdServiceImpl.saveZcd(zcd, getLoginUser());
			responseVO.setSuccess(true);
		}
		catch (Exception e) {
			logger.error("",e);
			responseVO.setSuccess(false);
			responseVO.setErrorMsg(!"".equals(e.getMessage()) ? e
					.getMessage() : "后台异常");
		}
		return responseVO;
	}
	
}

6、前端js层代码如下：

function save() {
		$("#save").addClass("disabled");
		
		if ($("#selSHCK").val() == "") {
			layer.msg('请填写收货仓库', {
				icon : 5
			});
			$("#save").removeClass("disabled");
			return;
		}
		
		if($("#selSHCK").val() == $("#selFHCK").val()){
			layer.msg('发货仓库与收货仓库不能一样', {
				icon : 5
			});
			$("#save").removeClass("disabled");
			return;
		}
		
		

		var param = {};
		param.bm01 = $("#selBm").attr("valuea");
		param.zcd02 = $("#selFHCK").attr("valuea");
		param.zcd03 = $("#selSHCK").attr("valuea");
		param.zcd04 = $("#zcd04").val();
		param.gsxx01 = $("#gsxx01").val();

		var zcditemAry = [];
		var flag = 0;
		$("#tbody1").find("tr").each(function() {
			var zcditem = {};
			var arrtd = $(this).children();

			zcditem.spxx01 = $.trim(arrtd.eq(0).text());
			zcditem.wldw01 = $.trim(arrtd.eq(6).text());
			zcditem.zcdi01 = $.trim(arrtd.eq(7).text());
			
			if($.trim(arrtd.eq(2).children(".zcdi03").val()) == ""){
				/* layer.msg('请输入转仓数量', {
					icon : 5
				});
				$("#save").removeClass("disabled"); */
				
				flag = 1;
				return;
			}
			
			zcditem.zcdi02 = $.trim(arrtd.eq(2).children(".zcdi03").val());
			zcditem.zcdi03 = $.trim(arrtd.eq(2).children(".zcdi03").val());
			zcditem.zcdi05 = $.trim(arrtd.eq(8).text());
			zcditem.zcdi06 = $.trim(arrtd.eq(4).children(".zcdi06").val());
			zcditem.gsxx01 = $("#gsxx01").val();
			zcditem.zcdi07 = $.trim(arrtd.eq(9).text());
			zcditemAry.push(zcditem);
		})
		param.zcditem = zcditemAry;

		if(flag == 1){
			layer.msg('请输入转仓数量', {
				icon : 5
			});
			$("#save").removeClass("disabled");
			return;
		}
		
		if (zcditemAry.length == 0) {
			layer.msg('请输入转仓商品信息', {
				icon : 5
			});
			$("#save").removeClass("disabled");
			return;
		}
	/* 	else{
			for(var i=0;i<zcditemAry;i++){
				if(zcditemAry[i].zcdi03 == ""){
					layer.msg('请输入转仓数量', {
						icon : 5
					});
					$("#save").removeClass("disabled");
					return;
				}
			}
		} */

		$.ajax({
			url : "${ctx }/stock/zcd/saveZcd",
			data : $.json.decode(param),
			contentType : "application/json",
			type : "POST",
			dataType : "json",
			success : function(data) {
				if (data.success) {
					$("#zcd01").val(data.data.zcd01);
					$("#zcd05").val(data.data.zcd05);
					$("#zcd06").val(data.data.zcd06);
					layer.msg('制定转仓单成功', {
						icon : 6
					});
				} else {
					layer.msg('制定转仓单失败' + data.errorMsg, {
						icon : 5
					});
					$("#save").removeClass("disabled");
				}
			}
		});
	}