mybatis调用mysql存储过程_mybatis调用mysql存储过程的例子

环境： JDK1.7，MySQL 5.6，windows7

存储过程SQL如下：

CREATE  PROCEDURE `saveTender`(IN brwId  VARCHAR(26),IN money VARCHAR(26),IN userid  VARCHAR(26),IN ordid  VARCHAR(20),OUT pResult VARCHAR(512))

COMMENT '--  '

BEGIN

#声明变量必须在程序前面，置后会报错误

DECLARE flag INT DEFAULT 0;

DECLARE err INT DEFAULT 0; #声明一个整形变量err，默认值是0

DECLARE vaccount decimal(11,2) DEFAULT 0; #总额度

DECLARE vSumTend decimal(11,2) DEFAULT 0; #已经投标的额度

DECLARE vBrrNid  varchar(50) DEFAULT ''; #借款编号

START TRANSACTION; #声明事务开始

SET pResult := NULL;

SELECT account,borrow_nid INTO vaccount,vBrrNid FROM yyd_borrow WHERE id= brwId  LIMIT 1;

INSERT into yyd_borrow_tender set chinapnr_OrdId=ordid, chinapnr_isSucc='0', addtime = UNIX_TIMESTAMP(),addip = '127.0.0.1',borrow_nid = vBrrNid,user_id = userid,account = money,contents = '',status = '0',nid = CONCAT ( 'tender_',ordid),account_tender = money;

#最近半小时投资未成功和投资已经成功的资金综合

SELECT sum(account) INTO vSumTend FROM yyd_borrow_tender where  borrow_nid =vBrrNid and (chinapnr_isSucc='1' or (chinapnr_isSucc='0' and  UNIX_TIMESTAMP()-addtime<30*60));

IF vSumTend >vaccount THEN

SET pResult := '001'; #已经满标

rollback;

ELSE

SET pResult := '000';

commit;

END IF;

END

===========================================================================

mybatis的定义文件investBackMapper.xml内容片段：

resultType="String" >

{call youqian3.saveTender(

#{brwId,jdbcType=VARCHAR,mode=IN},

#{money,jdbcType=VARCHAR,mode=IN},

#{userid,jdbcType=VARCHAR,mode=IN},

#{ordId,jdbcType=VARCHAR,mode=IN},

#{retStr,jdbcType=VARCHAR,mode=OUT})

}

===========================================================================

InvestBackMapper.java程序内容：

import java.util.List;

import java.util.Map;

import com.youqian.vo.YydBorrow;

import com.youqian.vo.YydBorrowTenderVo;

import com.youqian.vo.YydBorrowVo;

import com.youqian.vo.YydCreditRank;

public interface InvestBackMapper extends Base{

/**

* 查询用户下的投标

* @param userid

* @return

*/

public List queryBrrTendByUser(int userid);

/**

* 翻页查询可投资的项目表，Count记录数

* @param map

* @return

*/

public long qeuryBorrAvaCount(Map map);

/**

* 翻页查询可投资的项目表，

* @param map

* @return

*/

public List qeuryBorrAvaPage(Map map);

/**

* 查询积分等级和图片，类型为6

* @return

*/

public List qeuryCrdRank();

/**

* 调用事务过程，防止冒投

* @return

*/

public String saveTender(Map param);

/**

* ，

* @param tendVo

*/

public void updateTender(YydBorrowTenderVo tendVo);

/**

* 更新yyd_borrow的剩余金额，已经投资金额

* @param tendVo

*/

public void updateBorrow(YydBorrowTenderVo tendVo);

}

===========================================================================

SpringMVC的Service层代码片段

/**

* 调用事务过程，

* @return

* @throws ActionException

*/

public String saveTender(String id,String money,String useid,String ordId) throws ActionException {

try {

Map map = new HashMap();

map.put("brwId", id);    //借款项目 id

map.put("money", money); //投标金额

map.put("userid", useid); //

map.put("ordId", ordId);    //订单号

map.put("retStr", "");

String ret2=investBackMapper.saveTender(map);

//System.out.println("saveTender ret2:"+ret2);

String retStr=(String)map.get("retStr");

return retStr;

} catch (Exception e) {

e.printStackTrace();

throw new ActionException(

"InvestBackServiceImpl.saveTender.err5", e);

}

}

=====================================================

注意的地方是，虽然存储过程有返回值(ret2)，但是返回值很诡异，不可以使用。一般都是用  map.put("retStr", "");输出参数来使用自定义的返回值。

===========================================================

存储过程也可以返回结果集合，XML定义片段如下：

resultMap="itemResult">

{call test.test(

#{queryStr,jdbcType=VARCHAR,mode=IN},

#{retStr,jdbcType=VARCHAR,mode=OUT})

}

存储过程定义如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN p1  VARCHAR(26), OUT pResult VARCHAR(512))

BEGIN

#声明变量必须在程序前面，置后会报错误

DECLARE flag INT DEFAULT 0;

DECLARE err INT DEFAULT 0; #声明一个整形变量err，默认值是0

SET pResult := NULL;

SET pResult :=CONCAT ( 'test',p1);

SELECT * FROM tb2 WHERE commet like  Concat('%',p1, '%');-- this 返回一个结果集

START TRANSACTION; #声明事务开始

break_label:WHILE flag>10 DO

insert into  suser(userName,pwd,signUpTime)values(CONCAT('user',flag),'8899',sysdate());

set flag = flag+1;

IF flag=5 THEN

set err=1;

LEAVE  break_label;  #跳出循环

END if;

END WHILE break_label;

IF err=0 THEN

commit;

ELSE

rollback;

END IF;

END

====================================================

Mybatis的Map程序如下：

import java.util.List;

import java.util.Map;

import com.springdemo.usermgr.vo.SUser;

import com.springdemo.usermgr.vo.Stb2;

public interface SUserMapper {

public int  insertSUser(SUser user);

public SUser getSUser(String name);

public List getTestProc(Map param);

public List selectSUser(int id);

}

=====================================================

调用Map的java程序如下：

import java.io.IOException;

import java.io.Reader;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.springdemo.usermgr.vo.SUserMapper;

import com.springdemo.usermgr.vo.Stb2;

public class Test2 {

public static void main(String[] args) throws IOException {

String resource = "config.xml";

Reader reader = Resources.getResourceAsReader(resource);

SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);

SqlSession session = ssf.openSession(true); //true 为自动提交事务

try {

Map parms = new HashMap();

parms.put("queryStr", "的");

SUserMapper spMapper = session.getMapper(SUserMapper.class);

List as=spMapper.getTestProc(parms);

String outPara=(String) parms.get("retStr");

System.out.println(outPara);

System.out.println(as.toString());

//org.apache.ibatis.type.JdbcType.VARCHAR

// org.apache.ibatis.mapping.ParameterMode.OUT

} catch (Exception e) {

e.printStackTrace();

} finally {

session.close();

}

}

}

==============================================================

批量插入记录的例子：

Service类代码片段：

public void insertBatch(List insVo) throws ActionException {

try{

int reti=dtoListVoMapper.insertBatch(insVo);

System.out.println(reti);

} catch (Exception e) {

e.printStackTrace();

throw new ActionException("DtoListVoService.insertBatch.err1", e);

}

}

Mapper类：

public interface DtoListVoMapper extends Base{

public int insertBatch(List insVo);

public long getDtoListCount(Map map);

public List getDtoListPage(Map map);

public int insertTrfBatch(List listMap);

public long getDtoTrfCount(Map map);

public List getDtoTrfPage(Map map);

public int insertCashBatch(List listMap);

public long getDtoCashCount(Map map);

public List getDtoCashPage(Map map);

public int insertSaveBatch(List listMap);

public long getDtoSaveCount(Map map);

public List getDtoSavePage(Map map);

}

===================================================================

DtoListVoMapper.xml定义片段：

insert into candy_dtosave (

OrdId,  UsrCustId,  MerCustId,  OrdDate,  TransAmt ,  TransStat,  GateBusiId ,

OpenBankId ,  OpenAcctId,  FeeCustId,   FeeAcctId,  FeeAmt ,  addtime)

values

(#{item.OrdId},#{item.UsrCustId},#{item.MerCustId},#{item.OrdDate},#{item.TransAmt},#{item.TransStat},

#{item.GateBusiId},#{item.OpenBankId},#{item.OpenAcctId},#{item.FeeCustId},#{item.FeeAcctId},#{item.FeeAmt},UNIX_TIMESTAMP())