JAVA调用mysqlCPU过载 java调用mysql函数

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 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
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