欢迎使用CSDN-markdown编辑器

存储过程
在java中的调用：
DELIMITER //
create procedure date_pro_troubleanalysisone_count(pTROUBLE_DATE_BEGIN datetime, pTROUBLE_DATE_END datetime,pSYSTEM_ID VARCHAR(255),pSOURCE VARCHAR(255))

BEGIN
declare tmp_num int default -1;
declare total_num int default -1;

select ifnull(count_number,-1) into tmp_num from bussi_analysis_rule_troubleanalysisone_count where SYSTEM_ID = pSYSTEM_ID AND SOURCE =pSOURCE AND TROUBLE_DATE = pTROUBLE_DATE_BEGIN AND count_number is not null;
select tmp_num;

IF(tmp_num = -1 ) THEN
insert into bussi_analysis_rule_troubleanalysisone_count(count_number,SOURCE,SYSTEM_ID,TROUBLE_DATE) select count(*),pSOURCE AS SOURCE,pSYSTEM_ID AS SYSTEM_ID,pTROUBLE_DATE_BEGIN AS TROUBLE_DATE
from bussi_analysis_rule_troubleanalysisone where SYSTEM_ID = pSYSTEM_ID AND SOURCE =pSOURCE
AND TROUBLE_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END;
else
update bussi_analysis_rule_troubleanalysisone_count set count_number = ( select count(*)
from bussi_analysis_rule_troubleanalysisone where SYSTEM_ID = pSYSTEM_ID AND SOURCE =pSOURCE
AND TROUBLE_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END) where SYSTEM_ID = pSYSTEM_ID AND SOURCE =pSOURCE and TROUBLE_DATE=pTROUBLE_DATE_BEGIN;
end IF;
END

drop PROCEDURE date_pro_troubleanalysisone_count_byday
call date_pro_troubleanalysisone_count(‘2017-07-16’,’2017-07-17’,’4’,’数据质量分析模块’);

drop PROCEDURE date_pro_troubleanalysisone_count_byday

DELIMITER //
create procedure date_pro_troubleanalysisone_count_byday(pTROUBLE_DATE_BEGIN datetime, pTROUBLE_DATE_END datetime)

BEGIN
declare tmp_num int default -1;
declare total_num int default -1;

select ifnull(count_number,-1) into tmp_num from bussi_analysis_rule_troubleanalysisone_count_byday where TROUBLE_DATE = pTROUBLE_DATE_BEGIN and count_number is not null;
select tmp_num;
IF(tmp_num = -1 ) THEN

insert into bussi_analysis_rule_troubleanalysisone_count_byday(TROUBLE_DATE,SYSTEM_ID,SOURCE,count_number) select date_format(TROUBLE_DATE,’%Y-%m-%d’) as TROUBLE_DATE, SYSTEM_ID,SOURCE, count(*) as NUMBER from bussi_analysis_rule_troubleanalysisone
where TROUBLE_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END group by date_format(TROUBLE_DATE,’%Y-%m-%d’),SYSTEM_ID;
else
update bussi_analysis_rule_troubleanalysisone_count_byday set count_number = ( select count(*)
from bussi_analysis_rule_troubleanalysisone where TROUBLE_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END) where TROUBLE_DATE=pTROUBLE_DATE_BEGIN;
end IF;
END
call date_pro_troubleanalysisone_count_byday(‘2017-7-19’,’2017-7-20’)
select date_format(TROUBLE_DATE,’%Y-%m-%d’) as TROUBLE_DATE, SYSTEM_ID, count(*) as NUMBER from bussi_analysis_rule_troubleanalysisone_count_byday
where TROUBLE_DATE between ‘2017-7-12’ AND ‘2017-7-13’ group by date_format(TROUBLE_DATE,’%Y-%m-%d’),SYSTEM_ID limit 0,100;

====================================================================================
nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 0 -1 4 数据质量分析模块 > troubleanalysisone_count_4–1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 1 0 4 数据质量分析模块 > troubleanalysisone_count_4-0.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 2 1 4 数据质量分析模块 > troubleanalysisone_count_4-1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 3 2 4 数据质量分析模块 > troubleanalysisone_count_4-2.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 4 3 4 数据质量分析模块 > troubleanalysisone_count_4-3.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 5 4 4 数据质量分析模块 > troubleanalysisone_count_4-4.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 6 5 4 数据质量分析模块 > troubleanalysisone_count_4-5.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 6 4 数据质量分析模块 > troubleanalysisone_count_4-6.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 0 -1 3 故障诊断模块 > troubleanalysisone_count_3–1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 1 0 3 故障诊断模块 > troubleanalysisone_count_3-0.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 2 1 3 故障诊断模块 > troubleanalysisone_count_3-1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 3 2 3 故障诊断模块 > troubleanalysisone_count_3-2.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 4 3 3 故障诊断模块 > troubleanalysisone_count_3-3.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 5 4 3 故障诊断模块 > troubleanalysisone_count_3-4.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 6 5 3 故障诊断模块 > troubleanalysisone_count_3-5.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 6 3 故障诊断模块 > troubleanalysisone_count_3-6.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 0 -1 2 故障诊断模块 > troubleanalysisone_count_2–1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 1 0 2 故障诊断模块 > troubleanalysisone_count_2-0.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 2 1 2 故障诊断模块 > troubleanalysisone_count_2-1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 3 2 2 故障诊断模块 > troubleanalysisone_count_2-2.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 4 3 2 故障诊断模块 > troubleanalysisone_count_2-3.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 5 4 2 故障诊断模块 > troubleanalysisone_count_2-4.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 6 5 2 故障诊断模块 > troubleanalysisone_count_2-5.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 6 2 故障诊断模块 > troubleanalysisone_count_2-6.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 0 -1 1 故障诊断模块 > troubleanalysisone_count_1–1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 1 0 1 故障诊断模块 > troubleanalysisone_count_1-0.log
`package com.xnrh.flowwork.procedure.executor;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.xnrh.flowwork.procedure.jdbc.AlarmShow;
import com.xnrh.flowwork.procedure.jdbc.DateUtil;

public class ProcedureExecutor {
private static String driverName;
private static String url;
private static String user;
private static String password;

    /*      * 静态代码块，类初始化时加载数据库驱动     */    static {        try {            InputStream inCfg = ProcedureExecutor.class.getClassLoader().getResourceAsStream("configure.properties");             Properties propertiesCfg = new Properties();            propertiesCfg.load(inCfg);            String filePath = propertiesCfg.getProperty("jdbc.cfg.path");            // 加载 /home/jdbc.properties配置文件            InputStream in = new FileInputStream(filePath);            Properties properties = new Properties();            properties.load(in);             // 获取驱动名称、url、用户名以及密码            driverName = properties.getProperty("jdbc_driver");             url = properties.getProperty("jdbc_url");             user = properties.getProperty("jdbc_username");             password = properties.getProperty("jdbc_password");             // 加载驱动             Class.forName(driverName);         } catch (IOException e) {             e.printStackTrace();         } catch (ClassNotFoundException e) {             e.printStackTrace();         }     }     /*      * 获取连接      */     public static Connection getConnection() throws SQLException {         return DriverManager.getConnection(url, user, password);     }     /*      * 释放资源      */     public static void releaseResources(ResultSet resultSet,             Statement statement, Connection connection) {         try {             if (resultSet != null)                 resultSet.close();         } catch (SQLException e) {             e.printStackTrace();         } finally {             resultSet = null;             try {                 if (statement != null)                     statement.close();             } catch (SQLException e) {                 e.printStackTrace();             } finally {                 statement = null;                 try {                     if (connection != null)                         connection.close();                 } catch (SQLException e) {                     e.printStackTrace();                 } finally {                     connection = null;                 }             }         }     }//preStartDays = 7 向前7天//preEndDays=-1  向后一天public List<AlarmShow> execute(String procedure,int preStartDays,int preEndDays,List<Object> params) throws SQLException {    String TROUBLE_DATE_BEGIN = null;    String TROUBLE_DATE_END = null;    List <AlarmShow> list = new ArrayList<AlarmShow>(); //实例化List对象    if(procedure==null||procedure.length()==0)        return list;    System.out.println("0|execute|start to work|"+procedure);    Connection conn = getConnection();  //创建数据库连接    try    {        String procedureDesc = procedure;        procedureDesc += "(";        DateUtil du = new DateUtil();        boolean useDate = procedure.startsWith("date");        if(useDate){            TROUBLE_DATE_BEGIN = du.getPastDate(preStartDays);            TROUBLE_DATE_END = du.getPastDate(preEndDays);            procedureDesc += "?,?";        }        if(params!=null && params.size()>0){            for(int i=0;i<params.size();i++){                if(useDate||i!=0)                    procedureDesc += ",";                procedureDesc += "?";            }        }        procedureDesc += ")";        System.out.println("1|execute|start to prepareCall|"+procedureDesc);        //调用存储过程        CallableStatement cs = conn.prepareCall("{call "+procedureDesc+"}");        System.out.println("2|execute|after prepareCall|"+procedureDesc);        int startOffset = 1;        if(useDate){            cs.setString(1, TROUBLE_DATE_BEGIN);            cs.setString(2, TROUBLE_DATE_END);            startOffset=3;        }        for(int ii=0;ii<params.size();ii++){            Object o = params.get(ii);            if(o instanceof String){                cs.setString(ii+startOffset, (String)o);            } else if(o instanceof byte[]){                InputStream ais = new ByteArrayInputStream((byte[])o);                cs.setBinaryStream(ii+startOffset, ais);            } else if(o instanceof Integer){                int id = ((Integer)o).intValue();                cs.setInt(ii+startOffset, id);            } else if(o instanceof Long){                long l = ((Long)o).longValue();                cs.setLong(ii+startOffset,l);            } else if(o instanceof Double){                double d = ((Double)o).doubleValue();                cs.setDouble(ii+startOffset,d);            }        }        System.out.println("3|execute|start to executeQuery|"+procedureDesc);        ResultSet rs = cs.executeQuery(); //执行查询操作，并获取结果集        System.out.println("4|execute|after executeQuery|"+procedureDesc);        if(rs!=null){            /*while(rs.next())            {                AlarmShow ot = new AlarmShow(); //实例化AlarmShow对象                ot.setSYSTEM_ID(rs.getString("SYSTEM_ID"));                 ot.setNUMBER(rs.getInt("NUMBER"));                 ot.setSOURCE(rs.getString("SOURCE"));                ot.setTROUBLE_DATE(rs.getString("TROUBLE_DATE"));                list.add(ot);            }*/        }    }catch(Exception e)    {        e.printStackTrace();    }            return list;     //返回list}//public boolean mainExecute(String[] args) throws SQLException{    if(args.length>1)    {        ProcedureExecutor PM = new ProcedureExecutor();        String procedure = args[0];        int preStartDays = 0;        int preEndDays = 0;        int offset = 1;        if(procedure.startsWith("date") && args.length>2){            preStartDays = Integer.parseInt(args[1]);            preEndDays = Integer.parseInt(args[2]);            offset=3;        }else if(procedure.startsWith("date")){            System.out.println("date procedure , params is less than 3");            return false;        }        List<Object> params = new ArrayList<Object>();        for(int i=offset;i<args.length;i++)        {            params.add(args[i]);        }        //        System.out.println("0|main|start to work|"+procedure);        //        PM.execute(procedure,preStartDays,preEndDays,params);        System.out.println("1|main|after work|"+procedure);        return true;        /*for (AlarmShow ot : PM.findAll(procedure,preStartDays,preEndDays,params))        {            System.out.print(ot.getSYSTEM_ID() + "--" + ot.getNUMBER() + "--");            System.out.print(ot.getSOURCE() + "--" + ot.getTROUBLE_DATE());            System.out.println();        }*/    }else{        System.out.println("params is less than 1");        System.out.println("样例: java -jar proceduremain.jar 1 30 date_pro_troubleanalysisone_count 7 -1 4 数据质量分析模块");        return false;    }}

}
`&

package com.xnrh.flowwork.procedure.main;import java.sql.SQLException;import java.util.concurrent.Executors;import java.util.concurrent.ScheduledExecutorService;import java.util.concurrent.TimeUnit;import com.xnrh.flowwork.procedure.executor.ProcedureExecutor;import com.xnrh.flowwork.procedure.jdbc.Object_testjdbczzr;public class RunTaskMain implements Runnable{    private String[] argv;    private int deleys=1;    private int sleeps=2;    public RunTaskMain(String[] args){        if(args!=null && args.length>1)        {            argv = new String[args.length-2];            for(int i=2;i<args.length;i++)            {                argv[i-2] = args[i];            }            deleys = Integer.parseInt(args[0]);            sleeps = Integer.parseInt(args[1]);        } else {            System.out.println("样例: java -jar proceduremain.jar 1 30 date_pro_troubleanalysisone_count 2 -1 4 数据质量分析模块");        }    }    public int getDeleys() {        return deleys;    }    public void setDeleys(int deleys) {        this.deleys = deleys;    }    public int getSleeps() {        return sleeps;    }    public void setSleeps(int sleeps) {        this.sleeps = sleeps;    }    public static void main(String[] args) {        RunTaskMain runnable = new RunTaskMain(args);        ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();        //第二个参数为首次执行的延迟时间        //第三个参数为定时执行的时间间隔        service.scheduleAtFixedRate(runnable, runnable.getDeleys(), runnable.getSleeps() , TimeUnit.MINUTES);    }    @Override    public void run() {        // TODO Auto-generated method stub        ProcedureExecutor PM = new ProcedureExecutor();        try {            PM.mainExecute(argv);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 2 1 1 故障诊断模块 > troubleanalysisone_count_1-1.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 3 2 1 故障诊断模块 > troubleanalysisone_count_1-2.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 4 3 1 故障诊断模块 > troubleanalysisone_count_1-3.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 5 4 1 故障诊断模块 > troubleanalysisone_count_1-4.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 6 5 1 故障诊断模块 > troubleanalysisone_count_1-5.log &

nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 6 1 故障诊断模块 > troubleanalysisone_count_1-6.log &

导出页面存储过程：

DELIMITER //
create procedure date_pro_quality_load_desc_qk(pTROUBLE_DATE_BEGIN datetime, pTROUBLE_DATE_END datetime,pPROVINCE_NAME varchar(255),pSYSTEM_ID VARCHAR(255),pCARRIER_OPERATOR_NAME VARCHAR(255),pCONNECT_TYPE VARCHAR(255))

BEGIN
declare tmp_num int default -1;
declare total_num int default -1;

select ifnull(SOURCE_FILE_TOTAL_NUMBER,-1) into tmp_num from bussi_analysis_data_quality_load_desc_qk where SYSTEM_ID = pSYSTEM_ID AND PROVINCE_NAME=pPROVINCE_NAME AND CARRIER_OPERATOR_NAME = pCARRIER_OPERATOR_NAME AND
CONNECT_TYPE =pCONNECT_TYPE AND SOURCE_FILE_ACCEPT_DATE = pTROUBLE_DATE_BEGIN AND SOURCE_FILE_TOTAL_NUMBER is not null;
select tmp_num;

IF(tmp_num = -1 ) THEN
insert into bussi_analysis_data_quality_load_desc_qk(SYSTEM_ID,PROVINCE_NAME,CARRIER_OPERATOR_NAME,CONNECT_TYPE,SOURCE_FILE_ACCEPT_DATE,SOURCE_FILE_TOTAL_NUMBER) select pSYSTEM_ID AS SYSTEM_ID,pPROVINCE_NAME AS PROVINCE_NAME,pCARRIER_OPERATOR_NAME AS CARRIER_OPERATOR_NAME,
pCONNECT_TYPE AS CONNECT_TYPE , pTROUBLE_DATE_BEGIN AS SOURCE_FILE_ACCEPT_DATE ,sum(SOURCE_FILE_TOTAL_NUMBER)
from bussi_analysis_data_quality_load_desc where SYSTEM_ID = pSYSTEM_ID AND PROVINCE_NAME=pPROVINCE_NAME AND CARRIER_OPERATOR_NAME =pCARRIER_OPERATOR_NAME
AND SOURCE_FILE_ACCEPT_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END;
else
update bussi_analysis_data_quality_load_desc_qk set SOURCE_FILE_TOTAL_NUMBER = ( select sum(SOURCE_FILE_TOTAL_NUMBER) AS SOURCE_FILE_TOTAL_NUMBER
from bussi_analysis_data_quality_load_desc where SYSTEM_ID = pSYSTEM_ID AND PROVINCE_NAME=pPROVINCE_NAME AND CARRIER_OPERATOR_NAME = pCARRIER_OPERATOR_NAME AND
CONNECT_TYPE =pCONNECT_TYPE AND SOURCE_FILE_ACCEPT_DATE between pTROUBLE_DATE_BEGIN AND pTROUBLE_DATE_END) where SYSTEM_ID = pSYSTEM_ID AND PROVINCE_NAME=pPROVINCE_NAME AND CARRIER_OPERATOR_NAME = pCARRIER_OPERATOR_NAME AND
CONNECT_TYPE =pCONNECT_TYPE AND SOURCE_FILE_ACCEPT_DATE = pTROUBLE_DATE_BEGIN;
end IF;
END

drop PROCEDURE date_pro_quality_load_desc_qk
call date_pro_quality_load_desc_qk(‘2017-07-01’,’2017-07-02’,’beijing’,’3’,’YiDong’,’wlan’);

================================================================
eclipse的调用测试过程与linux如何执行的过程：
eclipse->run as ->configuration参数填写 如：（0 6 date_pro_troubleanalysisone_count_byday 1 -1）
->run

eclipse打成jar包：export->

[root@master01 procedureMain]# ll
total 7684
-rwxrwxrwx 1 root root 2952 Jul 20 19:01 jdbc.properties
-rwxrwxrwx 1 root root 3924124 Jul 20 19:36 proceduremainByday.jar
-rwxrwxrwx 1 root root 3926099 Jul 21 11:26 proceduremain.jar
[root@master01 procedureMain]# nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count_byday 2 -1 &
[1] 9423
[root@master01 procedureMain]# nohup: appending output to `nohup.out’

[root@master01 procedureMain]# jps
9423 jar
9495 Jps
8849 Bootstrap

[root@master01 procedureMain]# ps -ef|grep proceduremain.jar
root 9423 8590 1 11:30 pts/3 00:00:00 java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count_byday 2 -1
root 9525 8590 0 11:31 pts/3 00:00:00 grep proceduremain.jar

[root@master01 procedureMain]# nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 -1 4 数据质量分析模块 > /home/count_100_7_-1.log &
[2] 9927
[root@master01 procedureMain]# jps
10211 Jps
9927 jar
9766 jar
8849 Bootstrap
[root@master01 procedureMain]# nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 -1 1 故障诊断模块 > /home/count_systemid_1.log &
[3] 10283
[root@master01 procedureMain]# jps
9927 jar
9766 jar
10283 jar
10313 Jps
8849 Bootstrap
[root@master01 procedureMain]# nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 -1 2 故障诊断模块 > /home/count_systemid_2.log &
[4] 10381
[root@master01 procedureMain]# nohup java -jar proceduremain.jar 0 100 date_pro_troubleanalysisone_count 7 -1 3 故障诊断模块 > /home/count_systemid_3.log &
[5] 10411
[root@master01 procedureMain]# jps