MyBatis如何调用存储过程

最新推荐文章于 2023-06-13 01:33:01 发布

BX博昕信息李金景Kim

最新推荐文章于 2023-06-13 01:33:01 发布

阅读量635

点赞数

分类专栏： 4.技术细节文章标签：数据库 mybatis mysql java spring

本文链接：https://blog.csdn.net/weixin_44071138/article/details/122184337

版权

4.技术细节专栏收录该内容

42 篇文章 0 订阅

订阅专栏

MyBatis，对象关系映射(ORM)，是将程序中的对象与关系数据库相互映射。存储过程是为了实现特定功能的sql语句集合。本文主要介绍：“在项目中配置MyBatis的使用”、“存储过程的编写”、“测试存储过程”。

1、在项目中配置MyBatis的使用。

(1)配置扫描Dao接口。

结合Spring框架，在ApplicationContext.xml中配置bean：MapperScannerConfigurer，扫描basePackage包下的所有dao层接口：

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.bx.erp.dao" />
	</bean>

(2)配置访问mybatis配置文件和数据源。

配置bean：SqlSessionFactoryBean，设置数据源和mybatis的配置文件：

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dynamicDataSource" />
		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
	</bean>

数据源：

<!--动态数据源的配置 -->
	<bean id="dynamicDataSource" class="com.bx.erp.action.interceptor.DynamicDataSource" primary="true">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
				<entry value-ref="jdbcDataSource_nbr_bx" key="jdbcDataSource_nbr_bx" />
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="jdbcDataSource_nbr_bx" />
	</bean>

这里配置了动态数据源，默认数据源为：

<!-- 配置数据源3 -->
	<bean id="jdbcDataSource_nbr_bx" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			<value>${driverClassName}</value>
		</property>
		<property name="url">
			<value>jdbc:mysql://localhost:3306/nbr_bx?useUnicode=true&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL</value>
		</property>
		<property name="username">
			<value>${db.nbx.mysql.username}</value>
		</property>
		<property name="password">
			<value>${db.nbx.mysql.password}</value>
		</property>
	</bean>

(3)Mybatis配置文件。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC  
    "-//mybatis.org//DTD Config 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<mappers>
		<mapper resource="com/bx/erp/dao/commodity/CommodityMapper.xml" />
		<mapper resource="com/bx/erp/dao/commodity/CategoryMapper.xml" />
		<mapper resource="com/bx/erp/dao/commodity/BrandMapper.xml" />
		<mapper resource="com/bx/erp/dao/warehousing/InventorySheetMapper.xml" />
		<mapper resource="com/bx/erp/dao/warehousing/InventoryCommodityMapper.xml" />
		……

(4)通过注解将Dao接口注入Spring容器。

Dao接口使用@Component注解，注入Spring：

@Component("staffMapper")
public interface StaffMapper extends BaseMapper {
	public Staff resetPassword(Map<String, Object> params);

	public Staff updateOpenidAndUnionid(Map<String, Object> params);

	public void checkICID(Map<String, Object> params);

	public void checkUnionid(Map<String, Object> params);

	public void checkWeChat(Map<String, Object> params);

	public void checkOpenID(Map<String, Object> params);

	public void checkStatus(Map<String, Object> params);

	public void checkPhone(Map<String, Object> params);

	public void checkIsFirstTimeLogin(Map<String, Object> params);

	public void checkName(Map<String, Object> params);
	
	public Staff updateUnsubscribe(Map<String, Object> params);
}

(5)配置Dao接口xml文件。

在同一目录下，配置dao接口对应的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="com.bx.erp.dao.StaffMapper">
	<resultMap id="staffMap" type="com.bx.erp.model.Staff">
		<id property="ID" column="F_ID" />
		<result property="name" column="F_Name" />
		<result property="phone" column="F_Phone" />
		<result property="weChat" column="F_WeChat" />
		<result property="openid" column="F_OpenID" />
		<result property="unionid" column="F_Unionid" />
		<result property="ICID" column="F_ICID" />
		<result property="pwdEncrypted" column="F_pwdEncrypted" />
		<result property="salt" column="F_Salt" />
		<result property="passwordExpireDate" column="F_PasswordExpireDate" />
		<result property="isFirstTimeLogin" column="F_IsFirstTimeLogin" />
		<result property="shopID" column="F_ShopID" />
		<result property="departmentID" column="F_DepartmentID" />
		<result property="status" column="F_Status" />
		<result column="F_CreateDatetime" property="createDatetime" />
		<result column="F_UpdateDatetime" property="updateDatetime" />
		<result column="F_RoleID" property="roleID" />
		<result column="roleName" property="roleName" />
		<result column="companyID" property="companyID"/>
		<result column="shopName" property="shopName"/>
	</resultMap>

Mapper namespace声明了要关联的dao接口为StaffMapper。

ResultMap结果映射声明了type为Staff，Staff对象的属性与数据库表列关联起来。

(6)MyBatis调用存储过程。

<select id="create" statementType="CALLABLE" useCache="false" resultMap="staffMap">
		{CALL SP_Staff_Create(
		#{iErrorCode, jdbcType=INTEGER, mode=OUT},
		#{sErrorMsg, jdbcType=VARCHAR, mode=OUT},
		#{phone, mode=IN},
		#{name, mode=IN},
		#{ICID,mode=IN},
		#{weChat, mode=IN},
		#{salt, mode=IN},
		#{passwordExpireDate, mode=IN},
		#{isFirstTimeLogin, mode=IN},
		#{shopID,mode=IN},
		#{departmentID, mode=IN},
		#{roleID, mode=IN},
		#{status, mode=IN},
		#{returnSalt,mode=IN}
		)}
	</select>

Select标签的id属性对应了Dao接口的create方法。

StatementType属性声明了使用存储过程操作SQL语句。

ResultMap属性声明了结果集映射，即定义好的staffMap。

使用call命令调用了存储过程SP_Staff_Create，传递给存储过程的参数要使用#{}括起来。

参数需要设置mode属性，值为IN说明是需要传递给存储过程，值为OUT说明是需要存储过程返回的。参数间使用逗号隔开。

Dao接口使用map传递参数：

public BaseModel create(Map<String, Object> params);

Map存放参数：

params.put(field.getFIELD_NAME_phone(), s.getPhone() == null ? "" : s.getPhone());
			params.put(field.getFIELD_NAME_name(), s.getName() == null ? "" : s.getName());
			params.put(field.getFIELD_NAME_ICID(), s.getICID() == "" ? null : s.getICID());

2、存储过程的编写。

(1)创建存储过程。

DROP PROCEDURE IF EXISTS `SP_Staff_Create`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_Staff_Create` (
	OUT iErrorCode INT,
	OUT sErrorMsg VARCHAR(64),
	IN sPhone VARCHAR(32),
   	IN sName VARCHAR(12),
	IN sICID VARCHAR(20),
	IN sWeChat VARCHAR(20),
	IN sSalt VARCHAR(32),
	IN dPasswordExpireDate DATETIME,
	IN iIsFirstTimeLogin INT,
	IN iShopID INT,
	IN iDepartmentID INT,
	IN iRoleID INT,
	IN iStatus INT,
	IN iReturnSalt INT
)
BEGIN
……

DEFINER声明了存储过程访问数据库的权限，root角色为最高权限，可以对数据库做任何操作。

参数为OUT类型说明是需要存储过程返回给调用者的，参数为IN类型说明是需要调用者传递给存储过程的。

存储过程以BEGIN关键字开头，END关键字结尾

BEGIN

END;

执行SQL语句出现异常时，可以自定义错误码和错误信息，回滚数据：

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
	BEGIN
		SET iErrorCode := 3;
		SET sErrorMsg := '数据库错误';
		ROLLBACK;
	END;

SQL语句集合：

IF EXISTS (SELECT 1 FROM t_staff WHERE F_ICID = sICID AND F_status = 0) THEN
			SET iErrorCode := 7;
			SET sErrorMsg := '在职员工中已经存在相同身份证，不能再创建这样的员工';
		ELSEIF EXISTS (SELECT 1 FROM t_staff WHERE F_WeChat = sWeChat AND F_status = 0) THEN
			SET iErrorCode := 7;
			SET sErrorMsg := '在职员工中已经存在相同微信员工，不能再创建这样的员工';
		ELSEIF EXISTS (SELECT 1 FROM t_staff WHERE F_Phone = sPhone AND F_status = 0) THEN
			SET iErrorCode := 7;
			SET sErrorMsg := '在职员工中已经存在相同电话的员工，不能再创建这样的员工';
		ELSEIF NOT EXISTS(SELECT 1 FROM t_shop WHERE F_ID = iShopID) THEN
			SET iErrorCode := 7;
			SET sErrorMsg := '不能使用不存在的门店进行创建';
		ELSEIF NOT EXISTS (SELECT 1 FROM t_department WHERE F_ID = iDepartmentID) THEN
			SET iErrorCode := 7;
	   		SET sErrorMsg := '不能使用不存在的部门进行创建';
		ELSEIF NOT EXISTS (SELECT 1 FROM t_role WHERE F_ID = iRoleID) THEN
			SET iErrorCode := 7;
			SET sErrorMsg := '不能使用不存在的角色进行创建';
		ELSE
			INSERT INTO t_staff (
				F_Phone, 
				F_Name,
				F_ICID,
		……

(2)存储过程开启事务。

Mysql开启事务，以START TRANSACTION开头，COMMIT结尾，中间包裹SQL语句：

START TRANSACTION;
	SQL语句;
COMMIT;

(3)存储过程数据的返回和Dao接口接收数据。

数据库返回结果集，DAO接口接收返回数据，一个select对应一个java对象或一个list集合。返回一行数据对应一个java对象，返回多行数据对应一个list集合：

SELECT 
				F_ID, 
				F_Name,
				F_Phone, 
				F_ICID, 
				F_WeChat, 
				F_OpenID, 
				F_Unionid,
				F_pwdEncrypted,
	   			IF(iReturnSalt = 0, NULL, F_Salt) AS F_Salt,
				F_PasswordExpireDate, 
				F_IsFirstTimeLogin, 
				F_ShopID, 
				F_DepartmentID, 
				F_Status,
				F_CreateDatetime,
				F_UpdateDatetime,
				iRoleID AS F_RoleID
	   		FROM t_staff WHERE F_ID = staffID;

public BaseModel create(Map<String, Object> params);

	public BaseModel retrieve1(Map<String, Object> params);

	public List<BaseModel> retrieveN(Map<String, Object> params);

如果有多个结果接即多个select语句，那么DAO接口需要一个多个list集合接收返回的数据：

SELECT
				F_ID,
				F_ShopID,
				F_SN,
				F_WarehouseID,
				F_Scope, 
				F_Status, 
				F_StaffID, 
				F_ApproverID,
				F_CreateDatetime, 
				F_Remark
			FROM t_inventorysheet WHERE F_ID = iID;
			
			SELECT 
				F_ID, 
				F_InventorySheetID, 
				F_CommodityID, 
				F_CommodityName, 
				F_Specification, 
				F_BarcodeID, 
				F_PackageUnitID, 
				F_NOReal, 
				F_NOSystem, 
				F_CreateDatetime, 
				F_UpdateDatetime
			FROM t_inventorycommodity
			WHERE F_InventorySheetID = iID ;

public List<List<BaseModel>> retrieve1Ex(Map<String, Object> params);

(4)存储过程调用函数

对于一些比较复杂的检查，会使用函数来减轻存储过程的工作，也便于维护。通过返回错误信息来判断格式是否正常：

-- 检查该商品是否有销存(有依赖)等记录。
-- 返回值：
-- 1，有依赖，不能删除该商品。
-- 0，无依赖，可以删除该商品。
drop function IF EXISTS Func_CheckCommodityDependency;
CREATE DEFINER=`root`@`localhost` FUNCTION `Func_CheckCommodityDependency`(
	iID INT,
	sErrorMsg VARCHAR(32)
) RETURNS VARCHAR(32)
BEGIN
	DECLARE NO INT;
	DECLARE iType INT;
	
	SELECT F_Type INTO iType FROM t_commodity WHERE F_ID = iID; 
 --	SELECT F_Type INTO iType FROM t_commodity WHERE F_ID = iID;
	
--	IF NO <> 0 
	IF EXISTS(SELECT 1 FROM t_commodityshopinfo WHERE F_CommodityID = iID AND F_NO > 0) THEN
	    SET sErrorMsg := '该商品还有库存，不能删除';
    ELSEIF EXISTS(SELECT 1 FROM t_retailtradecommoditysource WHERE F_ReducingCommodityID = iID) THEN
		SET sErrorMsg := '该商品有商品来源表依赖，不能删除';
	……

(5)存储过程使用游标

使用游标可以很方便的编译一个数据库表或多行表数据，类似于Java的for循环。

定义游标结束标记：

DECLARE done INT DEFAULT  false;

定义一个游标，包含多条表数据：

DECLARE list CURSOR FOR(
			SELECT 
			   	F_ID,  
				F_DBName
			FROM nbr_bx.t_company
			WHERE  F_Status = 0
   	);

打开游标，循环遍历游标：

OPEN list;-- 
				read_loop1: LOOP

获取遍历的值赋值给变量：

FETCH list INTO ID, dbName;

结束循环和关闭游标：

END LOOP read_loop1;
			CLOSE list;

完整SQL语句：

OPEN list;-- 
				read_loop1: LOOP
				FETCH list INTO ID, dbName;
				IF done THEN
					LEAVE read_loop1;
				END IF;
				-- 
				IF dbName <> 'nbr_bx' THEN
					-- 
					SET createTmpTable_sql = concat('create temporary table tmp_table(select F_Mobile from ',dbName,'.t_vip', ' where F_Mobile = ''', sMobile, ''')');  
				    SET @createTmpTable_sql = createTmpTable_sql;   -- 将连成成的字符串赋值给一个变量
					prepare statement from @createTmpTable_sql;  -- 预处理需要执行的动态SQL，其中statement是一个变量   
					EXECUTE statement;      -- 执行SQL语句    		
		     		deallocate prepare statement;     -- 释放掉预处理段  
					-- 
					IF EXISTS(SELECT 1 FROM tmp_table) THEN -- 如果该公司有这个VIP, 插入临时公司表
	 						INSERT INTO tmp_t_company (F_ID) VALUES (ID);
		     		END IF;
		     		DROP TEMPORARY TABLE tmp_table;       -- 删除临时表 
		     		-- 
				END IF;
				-- 
				END LOOP read_loop1;
			CLOSE list;

3、测试存储过程。

和Java功能代码一样，存储过程应该需要单元测试来测试功能的稳定性。

测试和验证测试结果如下：

SELECT '-----------------CASE1: 正常添加------------------' AS 'CASE1';

SET @iErrorCode = 0;
SET @sErrorMsg = '';
SET @sName = '123132113';
SET @sPhone = '12341567891';
SET @sName = '123131213';
SET @sName = '店长1123';
SET @sICID = '4310251971841233';
SET @sWeChat = '';
SET @sPassword = '12341167';
SET @sSalt = 'E10ADC3949BA59ABBE56E057F20F883E';
SET @dPasswordExpireDate = '2017-05-03';
SET @iIsFirstTimeLogin = 1;
SET @iShopID = 1;


SET @iDepartmentID = 1;
SET @iRoleID = 1;
SET @iStatus = 0;
SET @iReturnSalt = 1;

CALL SP_Staff_Create(@iErrorCode, @sErrorMsg, @sPhone, @sName, @sICID, @sWeChat, @sSalt, @dPasswordExpireDate, @iIsFirstTimeLogin, 
	@iShopID, @iDepartmentID,@iRoleID, @iStatus, @iReturnSalt);

SELECT @sErrorMsg;	
SELECT IF(found_rows() = 1 AND @iErrorCode = 0, '测试成功', '测试失败') AS 'Case1 Testing Result';
DELETE FROM t_staffrole WHERE F_ID = last_insert_id();
DELETE FROM t_staff WHERE F_Phone = '12341567891';

BX博昕信息李金景Kim

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MyBatis如何调用存储过程

MyBatis，对象关系映射(ORM)，是将程序中的对象与关系数据库相互映射。存储过程是为了实现特定功能的sql语句集合。本文主要介绍：“在项目中配置MyBatis的使用”、“存储过程的编写”、“测试存储过程”。1、在项目中配置MyBatis的使用。(1)配置扫描Dao接口。结合Spring框架，在ApplicationContext.xml中配置bean：MapperScannerCon...
复制链接

扫一扫