环境： 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>
=============================================================
MyBatis Generator 配置
下载链接：
https://github.com/mybatis/generator/releases
运行命令行
java -jar mybatis-generator-core-1.3.2.jar -configfile generatorConfig.xml -overwrite
generatorConfig.xml内容：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 数据库驱动-->
<classPathEntry location="mysql-connector-java-5.1.34.jar"/>
<context id="MYSQLTables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="true"/>
<!-- 是否去除自动生成的注释 true：是 ： false:否 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接URL，用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://123.57.251.185/CancerDB" userId="xuanli" password="xuanli2015">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 生成模型的包名和位置-->
<javaModelGenerator targetPackage="com.ds.tech.entity.forum" targetProject="src">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成映射文件的包名和位置-->
<sqlMapGenerator targetPackage="com.ds.tech.mapper" targetProject="src">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- 生成DAO的包名和位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.ds.tech.dao" targetProject="src">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 要生成哪些表，所有表用：<table tableName="%" />-->
<table tableName="forum_count_stock" domainObjectName="forumCountStock" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"></table>
</context>
</generatorConfiguration>
=========================================================
MyBatis另外一直写法如下，原始Dao是继承org.mybatis.spring.support.SqlSessionDaoSupport的用法。现在版本很少使用了。
spring 配置文件片段：
<!-- 配置数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/javabbsTest?useUnicode=true&characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="Abcd1234" />
<property name="filters" value="stat" />
<property name="maxActive" value="20" />
<property name="initialSize" value="2" />
<property name="maxWait" value="60000" />
<property name="minIdle" value="10" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="SELECT '1'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="maxOpenPreparedStatements" value="100" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="180" />
<property name="logAbandoned" value="true" />
</bean>
<!-- session工厂的配置 -->
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!-- <property name="configLocation" value="classpath:config/mapper/configuration.xml"/> -->
<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
<property name="mapperLocations" value="classpath:mybatis/sqlMap/*.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="cc.javaee.bbs.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory" />
</bean>
红字部分是重点要加的。
---------------------------------------------------------------------------------------------
DAO类写法：
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import cc.javaee.bbs.model.Bankuai;
import cc.javaee.bbs.model.PageBean;
@Repository
public class BankuaiDao {
@Autowired
private SqlSessionTemplate sqlSession;
String ns="jbbs_bankuai.";
public List<Bankuai> findpage(PageBean<Bankuai> page) {
return sqlSession.selectList(ns+"findpage", page);
}
public int findpagecount(PageBean<Bankuai> page) {
return sqlSession.selectOne(ns+"findpagecount", page);
}
public int findmaxorderby(int parentid) {
return sqlSession.selectOne(ns+"findmaxorderby", parentid);
}
public List<Bankuai> find(Bankuai bankuai) {
return sqlSession.selectList(ns+"find", bankuai);
}
public List<Bankuai> select(Bankuai bankuai) {
return sqlSession.selectList(ns+"select", bankuai);
}
public void insert(Bankuai bankuai) {
sqlSession.insert(ns+"insert", bankuai);
}
public void delete(Integer id) {
sqlSession.delete(ns+"delete", id);
}
public void update(Bankuai bankuai) {
sqlSession.update(ns+"update", bankuai);
}
public Bankuai findfbankuaibyid(int parseInt) {
return sqlSession.selectOne(ns+"findfbankuaibyid", parseInt);
}
}
-------------------------------------------------------------------------
BankuaiMapper.xml配置文件片段。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="jbbs_bankuai" >
<!-- 单表查询 -->
<select id="select" resultType="cc.javaee.bbs.model.Bankuai" parameterType="cc.javaee.bbs.model.Bankuai" >
SELECT
t1.id,
t1.name,
t1.img,
t1.type,
t1.descs,
t1.parentid,
t1.orderby,
t1.createtime,
t1.createuserid,
t1.fatiegroup,
t1.huifugroup,
t1.findgroup,
t1.yuming
FROM
jbbs_bankuai t1
where 1=1
<if test="id != null" >
and t1.id = #{id,jdbcType=INTEGER}
</if>
<if test="yuming != null" >
and t1.yuming = #{yuming,jdbcType=VARCHAR}
</if>
<if test="parentId != null" >
and t1.parentId = #{parentId,jdbcType=INTEGER}
</if>
order by t1.parentid
</select>
<select id="find" resultType="cc.javaee.bbs.model.Bankuai" parameterType="cc.javaee.bbs.model.Bankuai" >
SELECT
t3.createtime tiezicreateTime,
t3.id tieziid,
t3.name tieziname,
t3.createuserid userid,
t4.loginname username,
t1.id,
t1.name,
t1.img,
t1.type,
t1.parentid,
t1.orderby,
t1.createtime createTime,
t1.createuserid,
t1.descs,
t1.findgroup,
t1.fatiegroup,
t1.huifugroup,
t1.yuming,
(SELECT
COUNT(*)
FROM
jbbs_tiezi t2
WHERE t1.id = t2.bankuai_id and t2.isuse='1' ) zhuticount,
(SELECT COUNT(*) FROM jbbs_huifu tt1 LEFT JOIN jbbs_tiezi tt2 ON tt1.tieziid=tt2.id WHERE t1.id = tt2.bankuai_id and tt2.isuse='1') huifucount
FROM
jbbs_bankuai t1
LEFT JOIN (SELECT MAX(id) id,bankuai_id FROM jbbs_tiezi where isuse='1' GROUP BY bankuai_id) t2 ON t2.bankuai_id=t1.id
LEFT JOIN jbbs_tiezi t3 ON t3.id =t2.id
LEFT JOIN jbbs_user t4 ON t4.id=t3.createuserid
where 1=1
<if test="id != null" >
and t1.id = #{id,jdbcType=INTEGER}
</if>
<if test="parentId != null" >
and (t1.parentId = #{parentId,jdbcType=INTEGER} or t1.id = #{parentId,jdbcType=INTEGER})
</if>
ORDER BY t1.orderby
</select>
。。。。。。
重点是没有interface，直接使用DAO服务。命名空间写法也很奇怪的。
563
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
