最近在捣鼓SSM使用注解方式调用有输入输出参数的存储过程,趟了不少坑,网上一堆没用抄袭的,废话不多说,直接上代码。
Controller层:
/*** 注册用户** @param userName* @param userPwd* @param userSex* @param userPhone* @return* @throws Exception*/@RequestMapping("/regist")@ResponseBodypublic ExecuteResult regist(String userName, String userPwd, String userSex, String userPhone) throws Exception {ExecuteResult result = new ExecuteResult();ProcRegistParam para = new ProcRegistParam(false, null, userName, userPwd, userSex, userPhone);iUserService.regist(para);result.setResult(para.getResult());result.setMsg(para.getMsg());return result;}
DAO层:
Repository("iUser")public interface IUser {/*** 注册用户** @return* @throws Exception*/@Select({"call Proc_Regist(#{para.userName,mode=IN,jdbcType=VARCHAR},","#{para.userPwd,mode=IN,jdbcType=VARCHAR},","#{para.userSex,mode=IN,jdbcType=VARCHAR},","#{para.userPhone,mode=IN,jdbcType=VARCHAR},","#{para.result,mode=OUT,jdbcType=BOOLEAN},","#{para.msg,mode=OUT,jdbcType=VARCHAR});"})@Options(statementType = StatementType.CALLABLE)void regist(@Param("para") ProcRegistParam procRegistParam) throws Exception;}
参数实体类:
package com.study.daomain;import com.study.util.ExecuteResult;import java.io.Serializable;public class ProcRegistParam implements Serializable {public ProcRegistParam(){}public ProcRegistParam(Boolean result, String msg, String userName, String userPwd, String userSex, String userPhone) {this.result = result;this.msg = msg;this.userName = userName;this.userPwd = userPwd;this.userSex = userSex;this.userPhone = userPhone;}private Boolean result;private String msg;private String userName; //用户名private String userPwd;//用户密码private String userSex;//用户性别private String userPhone;//用户手机号public Boolean getResult() {return result;}public void setResult(Boolean result) {this.result = result;}public String getMsg() {return msg;}public void setMsg(String msg) {this.msg = msg;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getUserPwd() {return userPwd;}public void setUserPwd(String userPwd) {this.userPwd = userPwd;}public String getUserSex() {return userSex;}public void setUserSex(String userSex) {this.userSex = userSex;}public String getUserPhone() {return userPhone;}public void setUserPhone(String userPhone) {this.userPhone = userPhone;}}
存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_Regist`(IN userName VARCHAR ( 50 ),-- 用户名IN userPwd VARCHAR ( 50 ),-- 用户密码IN userSex VARCHAR ( 50 ),-- 用户性别IN userPhone VARCHAR ( 20 ),-- 用户手机号OUT result TINYINT(2),OUT msg VARCHAR ( 4000 ) )begin_label : BEGIN-- return标签-- 定义参数DECLARE newUserId int DEFAULT 0; -- 新添加的用户id-- 定义异常DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN-- 异常信息get diagnostics CONDITION 1 msg=message_text;-- 异常标识SET result=0;END;-- 开始事务START TRANSACTION;-- 初始化状态SET result=1;SET msg='操作成功!';-- 添加用户INSERT INTO s_user(su_name,su_pwd,su_sex,su_phone,create_user,create_time)VALUES(userName,userPwd,userSex,userPhone,userName,NOW());SET newUserId=(SELECT LAST_INSERT_ID());-- 添加用户权限INSERT INTO s_user_power(su_id,sp_id,create_user,create_time) VALUES(newUserId,1,userName,NOW());INSERT INTO s_user_power(su_id,sp_id,create_user,create_time) VALUES(newUserId,2,userName,NOW());INSERT INTO s_user_power(su_id,sp_id,create_user,create_time) VALUES(newUserId,3,userName,NOW());if(result=0)THENROLLBACK;ELSECOMMIT;END IF;END
db.properties文件:
jdbc.driver=com.mysql.cj.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&tinyInt1isBit=falsejdbc.username=rootjdbc.password=admin123
注意:
1.数据库中存储过程返回的TINYINT(2)时,要对应上JavaBean的Boolean类型的result属性,
(1)db.properties文件的url后面加上tinyInt1isBit=false
(2)DAO层中的jdbcType为Boolean类型
(3)参数实体类-ProcRegistParam中必须要存在一个无参数的构造函数(默认隐藏存在),如果有参数的构造函数,会报错,这个其实在整个SSM项目都要注意!
(4)mode=IN 中的IN或者OUT必需都为大写,小写会报错
SSM中使用注解方式调用输入输出参数存储过程 输入参数为javabean方式 Boolean类型对应TINYINT类型
如果觉得《SSM中使用注解方式调用输入输出参数存储过程 输入参数为javabean方式 Boolean类型对》对你有帮助,请点赞、收藏,并留下你的观点哦!