JAVA通过MyBatis调用MySql存储过程和函数

JAVA通过MyBatis调用MySql存储过程和函数1.引言无论是采用SPRINGMVC框架开发WEB画面,还是开发需按一定时间间隔执行的批处理,都可能要调用数据库的存储过程或函数。其间调用参数设置不正会浪费大量调试时间初学者甚至放弃使用存储过程。本文记录了通过MyBatis调用MySql存储过程和函数的具体参数设置内容，供参考。2．MySql存储过程例/*全公司员工下一年度带薪休假一发赋予处理*/CREATEDEFINER=`DBuser`@`%`PROCEDURE`paid_vacation_compute`(OUTp_返回值INT(11),INOUTp_员工号CHAR(3),p_操作者IDVARCHAR(3))PROC_START:BEGIN/*变量声明*/DECLAREdoneINT;#异常退出控制变量DECLAREempNoCHAR(3);#员工号DECLAREdateHiredate;#分公司就职日DECLAREworkYearsINT;#集团内工作年数DECLARElastYearRemainDaysFLOAT;#昨年残日数（允许以小时为单位休假）DECLAREnowYearleaveDaysFLOAT;#今年休暇日数（允许以小时为单位休假）DECLAREelapseYearINT;#入集团经过年度数/*游标声明*/#上年带薪休假数据DECLAREstaffPaidVacationDaysCurCURSORFORSELECTa.EMP_NO,#员工号a.DATE_HIRE,#入职日期a.WORK_YEARS,#工作年限b.REMAIN_DAYS#上年带薪休假应休但未休残日数FROMT_EMPLOYEEASa,T_PAID_VACATIONASbWHEREa.EMP_NO=b.EMP_NO/*程序退出规定声明*/DECLARECONTINUEHANDLERFORNOTFOUNDSETSETdone=1;DECLAREEXITHANDLERFORSQLWARNINGSETdone=2;DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGINSETdone=3;ROLLBACK;#SQL异常回滚END;/*１．输入参数妥当性检查*/IF(p_操作者ISNULLORp_操作者='')THENSETp_返回值=9;LEAVEPROC_START;ENDIF;SETdone=0;STARTTRANSACTION;loop_label:LOOP#读入一条记录,代入到各个变量中FETCHstaffPaidVacationDaysCurINTOempNo,#员工号dateHire,#就职日workYears,#集团内工作年数lastYearRemainDays#昨年残日数;IFdone=1THENLEAVEloop_label;ENDIF;/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/SETelapseYear=YEAR（current_timestamp）-Year(dateHire)+workYears;IFelapseYear=0THENSETnowYearLeaveDays=10;ELSEIFelapseYear=1THENSETnowYearLeaveDays=11;ELSEIFelapseYear=2THENSETnowYearLeaveDays=12;ELSEIFelapseYear=3THENSETnowYearLeaveDays=14;ELSEIFelapseYear=4THENSETnowYearLeaveDays=16;ELSEIFelapseYear=5THENSETnowYearLeaveDays=18;ELSEIFelapseYear=6THENSETnowYearLeaveDays=20;ENDIF;SETdone=0;SETp_员工号=empNo;UPDATET_PAID_VACATIONSETLAST_YEAR_REMAIN_DAYS=lastYearRemainDays,THIS_YEAR_BASE_DAYS=nowYearLeaveDays,UPDATE_DATETIME=current_timestamp,UPDATE_USER_ID='SYS',UPDATE_TERMINAL_ID='MANUAL'WHEREEMP_NO=CONVERT(empNoUSINGbinary);IFdone=3THENSETp_返回值=6;LEAVEPROC_START;ENDIF;ENDLOOP;COMMIT;END3．MySql函数例CREATEDEFINER=`DBuser`@`%`FUNCTION`paid_vacation_compute`(p_员工号CHAR(3),p_操作者IDVARCHAR(3)))RETURNSint(11)BEGIN/*变量声明*/DECLAREdoneINT;#异常退出控制变量DECLAREempNoCHAR(3);#员工号DECLAREdateHiredate;#分公司就职日DECLAREworkYearsINT;#集团内工作年数DECLARElastYearRemainDaysFLOAT;#昨年残日数（允许以小时为单位休假）DECLAREnowYearleaveDaysFLOAT;#今年休暇日数（允许以小时为单位休假）DECLAREelapseYearINT;#入集团经过年度数/*游标声明*/#上年带薪休假数据DECLAREstaffPaidVacationDaysCurCURSORFORSELECTa.EMP_NO,#员工号a.DATE_HIRE,#入职日期a.WORK_YEARS,#工作年限b.REMAIN_DAYS#上年带薪休假应休但未休残日数FROMT_EMPLOYEEASa,T_PAID_VACATIONASbWHEREa.EMP_NO=b.EMP_NO/*程序退出规定声明*/DECLARECONTINUEHANDLERFORNOTFOUNDSETSETdone=1;DECLAREEXITHANDLERFORSQLWARNINGSETdone=2;DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGINSETdone=3;ROLLBACK;#SQL异常回滚END;/*１．输入参数妥当性检查*/IF(p_操作者ISNULLORp_操作者='')THENRETURN9;ENDIF;SETdone=0;STARTTRANSACTION;loop_label:LOOP#读入一条记录,代入到各个变量中FETCHstaffPaidVacationDaysCurINTOempNo,#员工号dateHire,#就职日workYears,#集团内工作年数lastYearRemainDays#昨年残日数;IFdone=1THENLEAVEloop_label;ENDIF;/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/SETelapseYear=YEAR（current_timestamp）-Year(dateHire)+workYears;IFelapseYear=0THENSETnowYearLeaveDays=10;ELSEIFelapseYear=1THENSETnowYearLeaveDays=11;ELSEIFelapseYear=2THENSETnowYearLeaveDays=12;ELSEIFelapseYear=3THENSETnowYearLeaveDays=14;ELSEIFelapseYear=4THENSETnowYearLeaveDays=16;ELSEIFelapseYear=5THENSETnowYearLeaveDays=18;ELSEIFelapseYear=6THENSETnowYearLeaveDays=20;ENDIF;SETdone=0;SETp_员工号=empNo;UPDATET_PAID_VACATIONSETLAST_YEAR_REMAIN_DAYS=lastYearRemainDays,THIS_YEAR_BASE_DAYS=nowYearLeaveDays,UPDATE_DATETIME=current_timestamp,UPDATE_USER_ID='SYS',UPDATE_TERMINAL_ID='MANUAL'WHEREEMP_NO=CONVERT(empNoUSINGbinary);IFdone=3THENRETURN6;ENDIF;ENDLOOP;COMMIT;END4．MySql存储过程调用时的iBatis用Mapper例BaseInfoEditMapper.xml?xmlversion=1.0encoding=UTF-8?!DOCTYPEmapperPUBLIC-//ibatis.apache.org//DTDMapper3.0//EN=com.xxx.web.mapper.base_info_editselectid=VacationProcedureparameterType=VacationBeanstatementType=CALLABLE{callpaid_vacation_compute(#{ReturnValue,javaType=INTEGER,jdbcType=INTEGER,mode=OUT},#{StaffNumber,javaType=String,jdbcType=CHAR,mode=INOUT},#{HireDate,javaType=String,jdbcType=VARCHAR,mode=IN},#{OperateID,javaType=String,jdbcType=VARCHAR,mode=IN})}/select/mapper5．MySql函数调用时的iBatis用Mapper例BaseInfoEditMapper.xml?xmlversion=1.0encoding=UTF-8?!DOCTYPEmapperPUBLIC-//ibatis.apache.org//DTDMapper3.0//EN=com.xxx.web.mapper.base_info_editselectid=VacationProcedureparameterType=VacationBeanstatementType=CALLABLE{#{ReturnValue,javaType=INTEGER,jdbcType=INTEGER,mode=OUT}=callpaid_vacation_compute(#{StaffNumber,javaType=String,jdbcType=CHAR,mode=IN},#{HireDate,javaType=String,jdbcType=VARCHAR,mode=IN},#{OperateID,javaType=String,jdbcType=VARCHAR,mode=IN})}/select/mapper6．JAVA调用例1（MySql存储过程和函数相同）packagecom.XXX.impl;importjava.util.ArrayList;importjava.util.List;importcom.XXX.web.bean.VacationCreateBean;importcom.XXX.web.dao.BaseInfoEditDAO;importcom.XXX.web.util.BasicSqlSupport;publicclassBaseInfo