java存储过程实际使用-MYSQL-LINUX

存储过程在java中的调用：DELIMITER //create procedure date_pro_troubleanalysisone_count(pTROUBLE_DATE_BEGIN datetime, pTROUBLE_DATE_END datetime,pSYSTEM_ID VARCHAR(255),pSOURCE VARCHAR(255))BEGINdeclare 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 ) THENinsert 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;elseupdate 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;ENDdrop PROCEDURE date_pro_troubleanalysisone_count_bydaycall date_pro_troubleanalysisone_count('2017-07-16','2017-07-17','4','数据质量分析模块');drop PROCEDURE date_pro_troubleanalysisone_count_bydayDELIMITER //create procedure date_pro_troubleanalysisone_count_byday(pTROUBLE_DATE_BEGIN datetime, pTROUBLE_DATE_END datetime)BEGINdeclare 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 ) THENinsert 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;elseupdate 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;ENDcall 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);}@Overridepublic void run() {// TODO Auto-generated method stubProcedureExecutor PM = new ProcedureExecutor();try {PM.mainExecute(argv);} catch (SQLException e) {// TODO Auto-generated catch blocke.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))BEGINdeclare 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 ) THENinsert 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;elseupdate 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;ENDdrop PROCEDURE date_pro_quality_load_desc_qkcall 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）->runeclipse打成jar包：export->[root@master01 procedureMain]# lltotal 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]# jps9423 jar9495 Jps8849 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 -1root      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]# jps10211 Jps9927 jar9766 jar8849 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]# jps9927 jar9766 jar10283 jar10313 Jps8849 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