环境： 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内容片段：
<!--事务过程保存投标，防止冒投-->
<select id="saveTender" parameterType="java.util.Map" statementType="CALLABLE"
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})
}
</select>
===========================================================================
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<YydBorrow>{
/**
* 查询用户下的投标
* @param userid
* @return
*/
public List<YydBorrowTenderVo> queryBrrTendByUser(int userid);
/**
* 翻页查询可投资的项目表，Count记录数
* @param map
* @return
*/
public long qeuryBorrAvaCount(Map<String, Object> map);
/**
* 翻页查询可投资的项目表，
* @param map
* @return
*/
public List<YydBorrowVo> qeuryBorrAvaPage(Map<String, Object> map);
/**
* 查询积分等级和图片，类型为6
* @return
*/
public List<YydCreditRank> qeuryCrdRank();
/**
* 调用事务过程，防止冒投
* @return
*/
public String saveTender(Map<String, Object> 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<String, Object> map = new HashMap<String, Object>();
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 type="com.springdemo.usermgr.vo.Stb2" id="itemResult">
</resultMap>
<select id="getTestProc" parameterType="java.util.Map" statementType="CALLABLE"
resultMap="itemResult">
{call test.test(
#{queryStr,jdbcType=VARCHAR,mode=IN},
#{retStr,jdbcType=VARCHAR,mode=OUT})
}
</select>
存储过程定义如下：
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<Stb2> getTestProc(Map<String, Object> param);
public List<Stb2> 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<String, Object> parms = new HashMap<String, Object>();
parms.put("queryStr", "的");
SUserMapper spMapper = session.getMapper(SUserMapper.class);
List<Stb2> 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<DtoListVo> 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<DtoListVo>{
public int insertBatch(List<DtoListVo> insVo);
public long getDtoListCount(Map<String, Object> map);
public List<DtoListVo> getDtoListPage(Map<String, Object> map);
public int insertTrfBatch(List<DtoListVo> listMap);
public long getDtoTrfCount(Map<String, Object> map);
public List<DtoListVo> getDtoTrfPage(Map<String, Object> map);
public int insertCashBatch(List<DtoListVo> listMap);
public long getDtoCashCount(Map<String, Object> map);
public List<DtoListVo> getDtoCashPage(Map<String, Object> map);
public int insertSaveBatch(List<DtoListVo> listMap);
public long getDtoSaveCount(Map<String, Object> map);
public List<DtoListVo> getDtoSavePage(Map<String, Object> map);
}
===================================================================
DtoListVoMapper.xml定义片段：
<!-- 批量插入语句， -->
<insert id="insertSaveBatch" >
insert into candy_dtosave (
OrdId, UsrCustId, MerCustId, OrdDate, TransAmt , TransStat, GateBusiId ,
OpenBankId , OpenAcctId, FeeCustId, FeeAcctId, FeeAmt , addtime)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{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())
</foreach>
</insert>
