添加历史实时数据记录

whyclj

2020-12-08 439c0a9cccf805c4c838cb1adfc2f5877664c71f

添加历史实时数据记录

 Central_Monitor/src/com/data/history/Central_Data_RealRecord_Thread.java

@@ -1,5 +1,6 @@
package com.data.history;



import java.util.Calendar;

import java.util.Date;

import java.util.List;



@@ -11,6 +12,9 @@
import com.sql.MysqlConnPool;



public class Central_Data_RealRecord_Thread extends Thread{

    public static final int TESTDATA_RECORD_INTERVAL = 1;                //记录数据间隔

    public static final int MAX_SAVE_DAY_COUNT = 3*366;

	
    private MysqlConnPool pool;

    private Logger logger = null;

    private List<Motor_inf> motors;

@@ -26,20 +30,99 @@
        logger.info(" Central_Data_RealRecord_Thread Start ...");

        

        Date now = null;        

        Date last = new Date(0);

        System.out.println(Com.getDateTimeFormat(last, Com.DTF_YMDhms));

		
        for(int i=0;i<motors.size();i++) {

            RecordThread thread = new RecordThread(motors.get(i),pool);

            new Thread(thread).start();

        }

        while(true) {

            try {

                now = new Date();

                for(int i=0;i<motors.size();i++) {

										
                for(int i = 0 ; i < motors.size() ; i++) {

                    if(motors.get(i).getDev_id() == 10005) {

                        //创建直流主配板表历史实时数据表

                        Central_Data_RealRecord_Thread_SQL.createTb_Central_Monitor_Sys_St_RealData(pool, motors.get(i).getDev_id(), now);

						
                        Central_Data_RealRecord_Thread_SQL.createTb_Central_Monitor_Sys_St_RealData(pool, motors.get(i).getDev_id(), tomorrow(now));

					
					
                    }else {

                        //创建进出线屏历史实时数据表

                        Central_Data_RealRecord_Thread_SQL.createTb_Central_Monitor_Sys_Rt_RealData(pool, motors.get(i).getDev_id(), now);

						
                        Central_Data_RealRecord_Thread_SQL.createTb_Central_Monitor_Sys_Rt_RealData(pool, motors.get(i).getDev_id(), tomorrow(now));

                    }

                }

                Date deltime = getDateBefore(now,MAX_SAVE_DAY_COUNT);

                //删除创建时间在指定时间之前创建的历史实时数据表

                Central_Data_RealRecord_Thread_SQL.deleteHistoryData(pool, deltime);

                

                

                Thread.sleep(50);

                Thread.sleep(5000);

            } catch (Exception e) {

                logger.error(e.toString(),e);

            }

        }

    }

	
    class RecordThread implements Runnable{

        public Motor_inf motor_inf;

        public MysqlConnPool pool;

        public RecordThread(Motor_inf motor_inf, MysqlConnPool pool) {

            this.motor_inf = motor_inf;

            this.pool = pool;

        }



        @Override

        public void run() {

            Date last = new Date(0);			
            Date now = null;

            while(true) {

                try {

                    now = new Date();

                    Thread.sleep(100);

                    if(motor_inf.conn_state == 0) {

                        //通讯中断

                        continue;

                    }

                    if(now.getTime()-last.getTime() >= TESTDATA_RECORD_INTERVAL*1000) {

                        if(motor_inf.getDev_id() == 10005) {

                            Central_Data_RealRecord_Thread_SQL.insertTb_Central_Monitor_Sys_St_RealData(pool, motor_inf.stdata, now);

                        }else {

                            Central_Data_RealRecord_Thread_SQL.insertTb_Central_Monitor_Sys_Rt_RealData(pool, motor_inf.rtdata, now);

                        }			
                        last = now;

                    }				
                } catch (Exception e) {

                    logger.error(e.toString(),e);

                }

            }

        }		
    }

	
	
    /**

     *     返回明天日期

     * @param today

     * @return

     */

    public Date tomorrow(Date today) {

        Calendar calendar = Calendar.getInstance();

        calendar.setTime(today);

        calendar.set(Calendar.DATE, calendar.get(Calendar.DATE) + 1);

        return calendar.getTime();

    }

    
    /**

     * 获取指定时间之前多少天的时间

     * @param d

     * @param day

     * @return

     */

    public static Date getDateBefore(Date d,int day){

        Calendar now =Calendar.getInstance();

        now.setTime(d);

        now.set(Calendar.DATE,now.get(Calendar.DATE)-day);

        return now.getTime();

    }

}


 Central_Monitor/src/com/data/history/Central_Data_RealRecord_Thread_SQL.java

New file
@@ -0,0 +1,199 @@
package com.data.history;



import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Date;



import com.base.Com;

import com.data.Central_RT_Data;

import com.data.Central_ST_Data;

import com.sql.MysqlConnPool;

import com.sql.Sql_Mysql;



public class Central_Data_RealRecord_Thread_SQL {



    /**

     *     创建历史实时表     db_3.5mw_motor.tb_central_monitor_sys_rt_

     * @param pool

     * @param dev_id

     * @param time

     */

    public static void createTb_Central_Monitor_Sys_Rt_RealData(MysqlConnPool pool,int dev_id,Date time) {

        String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Central_Monitor_Sys_Rt_RealData+dev_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+" (" + 
                "  num bigint(20) NOT NULL AUTO_INCREMENT," + 
                "  dev_id int(11) NOT NULL DEFAULT '0' COMMENT '设备id'," + 
                "  record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '更新时间'," + 
                "  switch_close int(11) NOT NULL DEFAULT '0' COMMENT '开关合闸状态'," + 
                "  switch_open int(11) NOT NULL DEFAULT '0' COMMENT '开关分闸状态'," + 
                "  switch_fault int(11) NOT NULL DEFAULT '0' COMMENT '开关故障状态'," + 
                "  panel_vol float(11,0) NOT NULL DEFAULT '0' COMMENT '进线屏电压'," + 
                "  panel_curr float(11,0) NOT NULL DEFAULT '0' COMMENT '进线屏电流'," + 
                "  note varchar(255) NOT NULL DEFAULT '0' COMMENT '备用'," + 
                "  PRIMARY KEY (num)," + 
                "  KEY index_dev_id (dev_id) USING BTREE," + 
                "  KEY index_record_time (record_time) USING BTREE" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            sql.logger.error(e.toString(),e);

        } finally {

            sql.close_con();

        }

    }

	
    /**

     *     创建历史实时表     db_3.5mw_motor.tb_central_monitor_sys_rt_

     * @param pool

     * @param dev_id

     * @param time

     */

    public static void createTb_Central_Monitor_Sys_St_RealData(MysqlConnPool pool,int dev_id,Date time) {

        String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Central_Monitor_Sys_St_RealData+dev_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+" (" + 
                "  num bigint(20) NOT NULL AUTO_INCREMENT," + 
                "  dev_id int(11) NOT NULL DEFAULT '0' COMMENT '设备id'," + 
                "  record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '更新时间'," + 
                "  switch_close_1st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A进线屏开关合闸状态'," + 
                "  switch_open_1st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A进线屏开关分闸状态'," + 
                "  switch_fault_1st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A进线屏开关故障状态'," + 
                "  switch_close_2st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A进线屏开关合闸状态'," + 
                "  switch_open_2st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A进线屏开关分闸状态'," + 
                "  switch_fault_2st_2500A int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A进线屏开关故障状态'," + 
                "  switch_close_bus_screen int(11) NOT NULL DEFAULT '0' COMMENT '母联屏合闸状态'," + 
                "  switch_open_bus_screen int(11) NOT NULL DEFAULT '0' COMMENT '母联屏分闸状态'," + 
                "  switch_fault_bus_screen int(11) NOT NULL DEFAULT '0' COMMENT '母联屏开关故障'," + 
                "  switch_close_1st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A负载屏开关合闸状态'," + 
                "  switch_open_1st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A负载屏开关分闸状态'," + 
                "  switch_fault_1st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '1号2500A负载屏开关故障状态'," + 
                "  switch_close_2st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A负载屏开关合闸状态'," + 
                "  switch_open_2st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A负载屏开关分闸状态'," + 
                "  switch_fault_2st_2500A_load int(11) NOT NULL DEFAULT '0' COMMENT '2号2500A负载屏开关故障状态'," + 
                "  curr_a float NOT NULL DEFAULT '0' COMMENT 'A排电流'," + 
                "  vol_a float NOT NULL DEFAULT '0' COMMENT 'A排电压'," + 
                "  curr_b float NOT NULL DEFAULT '0' COMMENT 'B排电流'," + 
                "  vol_b float NOT NULL DEFAULT '0' COMMENT 'B排电压'," + 
                "  note varchar(255) NOT NULL DEFAULT '' COMMENT '备用'," + 
                "  PRIMARY KEY (num)," + 
                "  KEY index_dev_id (dev_id) USING BTREE," + 
                "  KEY index_record_time (record_time) USING BTREE" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {                                                                                                                                                                                                                  
            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            sql.logger.error(e.toString(),e);

        } finally {

            sql.close_con();

        }

    }

	
    /**

     *     插入直流主配电板历史实时信息

     * @param pool

     * @param rtdata

     * @param time

     */

    public static void insertTb_Central_Monitor_Sys_St_RealData(MysqlConnPool pool,Central_ST_Data stdata,Date time) {

        String sql_str = " insert into " + Sql_Mysql.Tb_Central_Monitor_Sys_St_RealData+stdata.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+"(dev_id,record_time,switch_close_1st_2500A,switch_open_1st_2500A,switch_fault_1st_2500A,switch_close_2st_2500A,switch_open_2st_2500A,switch_fault_2st_2500A,switch_close_bus_screen,switch_open_bus_screen,switch_fault_bus_screen,switch_close_1st_2500A_load,switch_open_1st_2500A_load,switch_fault_1st_2500A_load,switch_close_2st_2500A_load,switch_open_2st_2500A_load,switch_fault_2st_2500A_load,curr_a,vol_a,curr_b,vol_b) "

                + " values("

                + ""+stdata.getDev_id()

                + ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)

                + "',"+stdata.getSwitch_close_1st_2500A()

                + ","+stdata.getSwitch_open_1st_2500A()

                + ","+stdata.getSwitch_fault_1st_2500A()

                + ","+stdata.getSwitch_close_2st_2500A()

                + ","+stdata.getSwitch_open_2st_2500A()

                + ","+stdata.getSwitch_fault_2st_2500A()

                + ","+stdata.getSwitch_close_bus_screen()

                + ","+stdata.getSwitch_open_bus_screen()

                + ","+stdata.getSwitch_fault_bus_screen()

                + ","+stdata.getSwitch_close_1st_2500A_load()

                + ","+stdata.getSwitch_open_1st_2500A_load()

                + ","+stdata.getSwitch_fault_1st_2500A_load()

                + ","+stdata.getSwitch_close_2st_2500A_load()

                + ","+stdata.getSwitch_open_2st_2500A_load()

                + ","+stdata.getSwitch_fault_2st_2500A_load()

                + ","+stdata.getCurr_a()

                + ","+stdata.getVol_a()

                + ","+stdata.getCurr_b()

                + ","+stdata.getVol_b()

                + ")";

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            sql.close_con();

        }

    }

	
    /**

     *     插入进出线屏历史实时信息

     * @param pool

     * @param rtdata

     * @param time

     */

    public static void insertTb_Central_Monitor_Sys_Rt_RealData(MysqlConnPool pool,Central_RT_Data rtdata,Date time) {

        String sql_str = " insert into " + Sql_Mysql.Tb_Central_Monitor_Sys_Rt_RealData+rtdata.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+"(dev_id,record_time,switch_close,switch_open,switch_fault,panel_vol,panel_curr) "

                + " values("+rtdata.getDev_id()+""

                + ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)+"'"

                + ","+rtdata.getSwitch_close()+""

                + ","+rtdata.getSwitch_open()+""

                + ","+rtdata.getSwitch_fault()+""

                + ","+rtdata.getPanel_vol()+""

                + ","+rtdata.getPanel_curr()+""

                + ")";

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            sql.close_con();

        }

    }

	
    /**

     *     删除创建时间超时的历史实时记录表

     * @param conn_pool

     * @param deldate		
     */

    public static void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {

        String sql_select_strs = " select TABLE_NAME,UPDATE_TIME,CREATE_TIME " + 
                                 " from information_schema.tables " + 
                                 " where table_schema='db_watersystem' " + 
                                 " AND TABLE_NAME like 'tb_central_monitor_sys_%' " + 
                                 " AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;

        String sql_delete_strs = " DROP TABLE IF EXISTS ";

        Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());

        ResultSet res = null;

        int count = 0;

        res = sql.sqlMysqlQuery(sql_select_strs);

        try {

            while(res.next()) {

                if(count > 0) {

                    sql_delete_strs += ",";

                }

                sql_delete_strs += "db_watersystem." + res.getString("TABLE_NAME");

                System.out.println("删除: "+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));

                count++;

            }

            if(count >0) {

                sql.sqlMysqlExecute(sql_delete_strs);

            }

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            if(res != null) {

                try {

                    res.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

            sql.close_con();

        }

    }

}


 Central_Monitor/src/com/sql/Sql_Mysql.java

@@ -206,6 +206,10 @@
    public final static String Tb_Central_Monitor_Sys_Rt = DB_MW_Motor + ".tb_central_monitor_sys_rt";
    public final static String Tb_Central_Monitor_Sys_Control = DB_MW_Motor + ".tb_central_monitor_sys_control";
    //--------------------------------------------------------------------------------------------//
    public final static String Tb_Central_Monitor_Sys_St_RealData = DB_MW_Motor + ".tb_central_monitor_sys_st_";				
    public final static String Tb_Central_Monitor_Sys_Rt_RealData = DB_MW_Motor + ".tb_central_monitor_sys_rt_";
	
    //--------------------------------------------------------------------------------------------//
    
    public Logger logger = null;
    public Connection mysql_con;