存储过程 在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
46
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
