SpringMVC與Mybatis集合實現調用存儲過程、事務控制實例

在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"); } } }); }

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。