mybatis结合mysql stored procedure解决复杂逻辑的data persistence

路线纲要：

一、需求分析

为了解决复杂逻辑的持久化，通过mybatis结合存储过程完成单一入口传参，通过存储过程控制存储逻辑，分解逻辑利用

mybatis完成单表持久化。

二、工具版本说明

  1.mybatis

  2.mysql

 3.springmvc

三、技术点介绍

 1.jdbc invoking data procedure

2.data procedure

3.auto data persistence tools

四、方案

(一)、利用mybatis自动生成工具完成单表的操作逻辑

        (二)、数据库存储过程和存储函数

  1.存储过程

CREATE DEFINER='dBUserName'@‘%’ PROCEDURE 'db_risk'.'sp_orderInsert'(client_id varchar(255),business_enterprise_id varchar(255),order_num varchar(255),
order_money decimal(11,2),order_time datetime,out result in)
BEGIN

DECLARE biz_id INT;
DECLARE order_id INT;
DECLARE loan_state INT;
DECLARE combizid INT;
DECLARE ordermoney DECIMAL(11,2);
DECLARE credit_money DECIMAL(11,2);
DECLARE use_money DECIMAL(11,2);
DECLARE left_money DECIMAL(11,2);
DECLARE fund_org_rate DECIMAL(11,2);

SELECT ID into biz_id from tbbiz where ClientId = client_id;
SELECT ID into combizid from tbcompanybiz where BizId = biz_id and bizCompanyUserId = business_enterprise_id LIMIT 1;
SELECT f.FuncCost into fund_org_rate from tbfundorg f inner join tbcreditcompany a on f.id = a.orgId where a.CompanyBizId = combizid;
SELECT a.id,b.loadState,a.amount into order_id,loan_state,ordermoney from tborder a left join tbloan b on a.id = b.orderId
where a.clentID = client_id and a.oderNo = order_num LIMIT 1;

set result = -3;

IF(combizid < 1) THEN
	set result = -2;
  ELSE 
	IF(loan_state = 3) THEN
	 set result = 0;
		ELSE
			IF(order_id>0) THEN	
				DELETE FROM tborder where id=order_id;
				DELETE FROM tbordertail where oderId=order_id;
				DELETE FROM tbloan where orderId=order_id;
				
				update tbcreditcompany set OrderTatalNum = orderTatalNum -1, UseMoney=userMoney-ordermoney,LeftMoney=LeftMoney+ordermoney
				where companyBizId=combizid;
			END IF;
			SELECT CreditMoney into credit_money from tbcrdditcompany where CompanyBizId = combizid;
			SELECT IFNULL(sum(LoanMoney),0) into use_money from tbloan where LoanState in(0,1,2,3) and bizId = biz_id and BizCompanyUserId =
			business_enterprise_id;
			set left_money =credit_money - use_money;
			IF(left_money >= order_money) THEN
				insert tborder(OrderNo,OrderDate,ClientId,bizId,BizCompanyUserId,Amount)
				value(order_num,order_time,client_id,biz_id,business_enterprise_id,order_money);
				SELECT @@IDENTITY into result;
				update tbcreditcompany set OrderTatalNum = orderTatalNum + 1,useMoney =UseMoney + order_money,Left_Money=LeftMoney - order_money
				where companyBizId = combizid;
				insert into tbloan(LoanNo) SELECT f_get_loanno() from tbcreditcompany a inner join tbbiz b on a.bizid =b.idwhere a.companyBizId =combizid;
			ELSE	
				set result = -1;
			END IF;
	END IF;
END IF;
		
END

  2.存储函数

CREATE DEFINER='dbUserName'@'%' FUNCTION 
 'db_risk'.'f_get_loanno'() RETURN varchar(100) CHARSET utf8
BEGIN 
return CONCAT('DZJ_',date_format(now(),'%Y%m%d%H%i%s'),ROUND(RAND()*8999999999+1000000000));
end

        (三)、dao层利用spring jdbcTemplate调用存储过程完成复杂逻辑的持久化

public Map<String,Integer> OrderInsert(Tborder order,TborderInfo orderInfo){
	Integer rtnObjs = -1;
	Integer rtnObjs2 = -1;
	HashMap<String,Integer> map = new HashMap<>();
	if(order != null){
		rtnObjs = (Integer)jdbcTemplate.execute(
			new CallableStatementCreator(){
				public CallableStatement createCallableStatement(Connection con) throws SQLException{
					String storedProc = "{call sp_orderInsert(?,?,?,?,?,?)}";
					CallableStatement cs = con.prepareCall(storedProc);
					cs.setString(1,order.getClientId());
					cs.setString(2,order.getBizCompanyUserId());
					cs.setString(3,order.getOrderNo());
					cs.setBigDecimal(4,order.getAmount());
					cs.setTimestamp(5,new java.sql.Timestamp(order.getOrderDate().getTime()));
					cs.registerOutParameter(6,Type.INTEGER);
					return cs;
				}
			},
			new CallableStatementCallback(){
				public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException{
					cs.execute();
					return cs.getInt(6);
				}
			}
		
		
		);
	
	}
}

五、总结

  complex logic 可以通过jdbc invoking data procedure 控制整个存储逻辑，对于单表处理交给mybatis处理。