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

最新推荐文章于 2024-06-10 07:11:38 发布

ysds20211402

最新推荐文章于 2024-06-10 07:11:38 发布

阅读量1.6k

点赞数 1

分类专栏： java mysql 文章标签： java mysql

原文链接：https://www.weidianyuedu.com/content/4317738242235.html

版权

java 同时被 2 个专栏收录

148 篇文章 0 订阅

订阅专栏

mysql

86 篇文章 1 订阅

订阅专栏

转自：微点阅读 https://www.weidianyuedu.com/content/4317738242235.html

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

1. 引言

无论是采用SPRInG MVc框架开发WEB画面,还是开发需按一定时间间隔执行的批处理,都可能要调用数据库的存储过程或函数。其间调用参数设置不正会浪费大量调试时间初学者甚至放弃使用存储过程。本文记录了通过MyBatis调用MySql存储过程和函数的具体参数设置内容，供参考。 2．MySql存储过程例

/*全公司员工下一年度带薪休假一发赋予处理*/

cREATE DEFInER=`DBuser`@`%` PRocEDURE `paid_vacation_compute `(

oUT p_返回值 InT(11) , InoUT p_员工号 cHAR(3) , p_操作者ID VARcHAR(3)) PRoc_START: BEGIn

/* 变量声明 */

DEcLARE done InT; #异常退出控制变量 DEcLARE empno cHAR(3); #员工号 DEcLARE dateHire date; #分公司就职日 DEcLARE workYears InT; #集团内工作年数

DEcLARE lastYearRemainDays FLoAT; #昨年残日数（允许以小时为单位休假） DEcLARE nowYearleaveDays FLoAT; #今年休暇日数（允许以小时为单位休假） DEcLARE elapseYear InT; #入集团经过年度数

/* 游标声明 */ #上年带薪休假数据

DEcLARE staffPaidVacationDayscur cURSoR FoR SELEcT a.EMP_no, #员工号

a.DATE_HIRE, #入职日期

a.WoRK_YEARS, #工作年限

b.REMAIn_DAYS # 上年带薪休假应休但未休残日数 FRoM T_EMPLoYEE AS a,

T_PAID_VAcATIon AS b WHERE a. EMP_no = b. EMP_no

/* 程序退出规定声明 */

DEcLARE conTInUE HAnDLER FoR noT FoUnD SET SET done = 1;

DEcLARE EXIT HAnDLER FoR SQLWARnInG SET done = 2;

DEcLARE conTInUE HAnDLER FoR SQLEXcEPTIon BEGIn

SET done = 3;

RoLLBAcK; #SQL异常回滚

EnD;

/* １．输入参数妥当性检查 */

IF (p_操作者 IS nULL oR p_操作者 = "") THEn

SET p_返回值 = 9;

LEAVE PRoc_START; EnD IF; SET done = 0; START TRAnSAcTIon; loop_label: LooP

# 读入一条记录, 代入到各个变量中 FETcH staffPaidVacationDayscur InTo empno, #员工号

dateHire, #就职日

workYears, #集团内工作年数 lastYearRemainDays #昨年残日数 ;

IF done = 1 THEn

LEAVE loop_label;

EnD IF;

/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/

SET elapseYear = YEAR（current_timestamp）- Year(dateHire)

+ workYears;

IF elapseYear = 0 THEn

SET nowYearLeaveDays = 10; SET nowYearLeaveDays = 11; SET nowYearLeaveDays = 12;

ELSEIF elapseYear = 1 THEn ELSEIF elapseYear = 2 THEn ELSEIF elapseYear = 3 THEn

SET nowYearLeaveDays = 14; SET nowYearLeaveDays = 16; SET nowYearLeaveDays = 18; SET nowYearLeaveDays = 20;

ELSEIF elapseYear = 4 THEn ELSEIF elapseYear = 5 THEn ELSEIF elapseYear >= 6 THEn EnD IF; SET done = 0; SET p_员工号 = empno; UPDATE T_PAID_VAcATIon SET

LAST_YEAR_REMAIn_DAYS = lastYearRemainDays, THIS_YEAR_BASE_DAYS = nowYearLeaveDays, UPDATE_DATETIME = current_timestamp, UPDATE_USER_ID = "SYS",

UPDATE_TERMInAL_ID = "MAnUAL"

WHERE EMP_no = conVERT(empno USInG binary); IF done = 3 THEn SET p_返回值 = 6; LEAVE PRoc_START; EnD IF;

EnD LooP; coMMIT;

EnD

3．MySql函数例

cREATE DEFInER=`DBuser`@`%` FUncTIon ` paid_vacation_compute `(

p_员工号 cHAR(3) , p_操作者ID VARcHAR(3)) ) RETURnS int(11) BEGIn

/* 变量声明 */

DEcLARE done InT; #异常退出控制变量 DEcLARE empno cHAR(3); #员工号 DEcLARE dateHire date; #分公司就职日 DEcLARE workYears InT; #集团内工作年数

/* 游标声明 */ #上年带薪休假数据

DEcLARE staffPaidVacationDayscur cURSoR FoR SELEcT a.EMP_no, #员工号

a.DATE_HIRE, #入职日期

a.WoRK_YEARS, #工作年限

b.REMAIn_DAYS # 上年带薪休假应休但未休残日数 FRoM T_EMPLoYEE AS a,

T_PAID_VAcATIon AS b WHERE a. EMP_no = b. EMP_no

/* 程序退出规定声明 */

DEcLARE conTInUE HAnDLER FoR noT FoUnD SET SET done = 1; DEcLARE EXIT HAnDLER FoR SQLWARnInG SET done = 2;

DEcLARE conTInUE HAnDLER FoR SQLEXcEPTIon BEGIn

SET done = 3;

RoLLBAcK; #SQL异常回滚

EnD;

/* １．输入参数妥当性检查 */

IF (p_操作者 IS nULL oR p_操作者 = "") THEn

RETURn 9;

EnD IF; SET done = 0; START TRAnSAcTIon; loop_label: LooP

# 读入一条记录, 代入到各个变量中 FETcH staffPaidVacationDayscur InTo empno, #员工号

dateHire, #就职日

workYears, #集团内工作年数 lastYearRemainDays #昨年残日数 ;

IF done = 1 THEn

LEAVE loop_label;

EnD IF;

/*根据集团内工作年限+分公司就职年限计算下一年度带薪天数*/

SET elapseYear = YEAR（current_timestamp）- Year(dateHire)

+ workYears;

IF elapseYear = 0 THEn

SET nowYearLeaveDays = 10; SET nowYearLeaveDays = 11; SET nowYearLeaveDays = 12; SET nowYearLeaveDays = 14; SET nowYearLeaveDays = 16; SET nowYearLeaveDays = 18; SET nowYearLeaveDays = 20;

ELSEIF elapseYear = 1 THEn ELSEIF elapseYear = 2 THEn ELSEIF elapseYear = 3 THEn ELSEIF elapseYear = 4 THEn ELSEIF elapseYear = 5 THEn ELSEIF elapseYear >= 6 THEn EnD IF;

9. DAo与MAP间的交互BEAn

package com.XXX.web.bean;

import java.io.Serializable;

public class VacationcreateBean implements Serializable{

public void setHireDate(String hireDate) { }

HireDate = hireDate;

public String getHireDate() { }

return HireDate;

public void setStaffnumber(String staffnumber) { }

Staffnumber = staffnumber;

public String getStaffnumber() { }

return Staffnumber;

private int ReturnValue; private String Staffnumber; private String HireDate; private String operateID; private int WorkYearsShinKi; public int getReturnValue() { }

public void setReturnValue(int returnValue) { }

ReturnValue = returnValue; return ReturnValue;

}

public String getoperateID() { }

return operateID;

public void setoperateID(String operateID) { }

operateID = operateID;

public int getDhcWorkYearsShinKi() { }

return dhcWorkYearsShinKi;

public void setDhcWorkYearsShinKi(int dhcWorkYearsShinKi) { }

this.dhcWorkYearsShinKi = dhcWorkYearsShinKi;

10. 通过MAIn函数进行调用

package com.ohc.pms.batch;

import java.io.FileInputStream; import java.io.IoException; import java.sql.callableStatement; import java.sql.connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties;

import org.apache.log4j.Logger;

import org.apache.log4j.Propertyconfigurator;

public class Vacationcreate {

/**JDBc驱动名 */

static String jdbcDriver = \; /**DB URL */

static String dbURL = \;

/** DB用户名 */

static String user = \; /**DB密码 */

static String pass = \;

/**日志输出 */

static protected Logger log = Logger.getLogger(Vacationcreate.class );

public static void main(String[] args) {

connection conn = null; callableStatement stmt = null; try{

Propertyconfigurator.configure(System.getProperty(\) + \ +

\);

log.info(\実行路径：\ + System.getProperty(\));

String filename = \; // 属性文件名 Properties conf = new Properties(); try {

conf.load(new FileInputStream(System.getProperty(\) + \ +

\));

log.info(\调用開始。\);

String sql = \;

log.info(\连接。\);

conn = DriverManager.getconnection(dbURL,user,pass);

} catch (IoException e) {

System.err.println(\ + filename + \); e.printStackTrace();

System.exit(-1); // 程序終了 } // 读入

jdbcDriver = conf.getProperty(\); dbURL = conf.getProperty(\); user = conf.getProperty(\); pass = conf.getProperty(\);

//JDBc driver登録

class.forname(\);

stmt = conn.preparecall(sql);

stmt.registeroutParameter(1,java.sql.Types.InTEGER); stmt.registeroutParameter(2,java.sql.Types.cHAR); stmt.registeroutParameter(3,java.sql.Types.cHAR); stmt.registeroutParameter(4,java.sql.Types.cHAR);

for (int i = 0; i < args.length; i++) {log.info(\执行参数：\ + args[i]);}

if (args.length == 1){

stmt.setString(4, \); stmt.setString(5, \); stmt.setString(6, \); stmt.setInt(7, 0);

}else if(args.length == 5){

stmt.setString(4, args[0]); stmt.setString(5, args[1]); stmt.setString(6, args[2]);

stmt.setInt(7, Integer.parseInt(args[3]));

}else{ }

if (args.length == 1 || args.length == 5){

log.info(\执行中。\); stmt.execute();

log.error(\执行参数个数不正\);

int outputValue = stmt.getInt(1); String staffnumber = stmt.getString(4);

if (outputValue == 0){

log.info(\执行結果:0（正常終了）\);

}else if(outputValue == 6){

log.info(\执行結果:6（休暇日数更新失敗、失敗记录の员工

号：\ + staffnumber + \）\);

}

}else if(outputValue == 9){

log.info(\执行結果:9（参数不正）\);

}else{ }

log.info(\执行結果:\ + outputValue + \想定外\);

} }

stmt.close(); conn.close();

}catch(SQLException se){

//Handle errors for JDBc se.printStackTrace();

}catch(Exception e){

//Handle errors for class.forname e.printStackTrace();

}finally{ }

log.info(\終了!\);

try{

if(stmt!=null)

stmt.close();

}catch(SQLException se2){ } try{

if(conn!=null)

conn.close();

}catch(SQLException se){ }

se.printStackTrace();

11. jdbc.properties

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://172.999.999.35:3306/empdb user=empuser password=empuser123 initialSize=5 maxActive=50 maxIdle=30 minIdle=15 maxWait=60000

12. log4j.properties

#conSoLE,fileoutの双方式

log4j.rootLogger=DEBUG,conSoLE,FILEoUT log4j.addivity.org.apache=true

# conSoLE

log4j.appender.conSoLE=org.apache.log4j.consoleAppender log4j.appender.Threshold=DEBUG

log4j.appender.conSoLE.Target=System.out

log4j.appender.conSoLE.layout=org.apache.log4j.PatternLayout

#log4j.appender.conSoLE.layout.conversionPattern=[framework] %d ? %c -%-4r [%t] %-5p %c %x ? %m%n log4j.appender.conSoLE.layout.conversionPattern=[%-5p] %d{yyyy-MM-dd HH\\:mm\\:ss} \\:%m%n # # FILEoUT

log4j.appender.FILEoUT=org.apache.log4j.RollingFileAppender log4j.appender.FILEoUT.File=${user.dir}\\\\filelog.log log4j.appender.fileout.MaxFileSize=100000KB

# default is true，append to the file; if false, the replace the log file whenever restart system log4j.appender.FILEoUT.Append=true log4j.appender.FILEoUT.MaxBackupIndex=1000 #RollingFileAppender没有DatePattern?个属性

log4j.appender.FILEoUT.layout=org.apache.log4j.PatternLayout

#log4j.appender.conSoLE.layout.conversionPattern=[framework] %d ? %c -%-4r [%t] %-5p %c %x ? %m%n log4j.appender.FILEoUT.layout.conversionPattern=[%-5p]_%d{yyyy-MM-dd HH:mm:ss} :%m%n

ysds20211402

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JAVA通过MyBatis调用MySql存储过程和函数

转自：微点阅读https://www.weidianyuedu.com/content/4317738242235.htmlJAVA通过MyBatis调用MySql存储过程和函数1. 引言无论是采用SPRInG MVc框架开发WEB画面,还是开发需按一定时间间隔执行的批处理,都可能要调用数据库的存储过程或函数。其间调用参数设置不正会浪费大量调试时间初学者甚至放弃使用存储过程。本文记录了通过MyBatis调用MySql存储过程和函数的具体参数设置内容，供参考。 2．MySql存储过程例..
复制链接

扫一扫

专栏目录