添加历史数据循环删除功能，目前历史实时数据最多保留30天

whyclj

2020-09-22 407abe3852ece21928e52f83bd8ca4d49a76ad24

添加历史数据循环删除功能，目前历史实时数据最多保留30天

 ElectricalSystem_MonitorServer_2KW/src/com/electrical/TwoKW/Electric_2Kw_TestData_Thread.java

@@ -10,6 +10,8 @@


public class Electric_2Kw_TestData_Thread implements Runnable{

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

    public static final int MAX_SAVE_DAY_COUNT = 30;

	
    

    public MysqlConnPool pool;

    public List<TestData_Record_Thread> threads;

@@ -48,8 +50,11 @@
                    //构造今天和明天的历史实时数据表

                    Electrical_2KWTask_SQL.createTb_electric2mw_realdata(pool, einf.electric2KW_id, now);

                    Electrical_2KWTask_SQL.createTb_electric2mw_realdata(pool, einf.electric2KW_id, tomorrow(now));

					
				
                }

                Date del_time = getDateBefore(now,MAX_SAVE_DAY_COUNT);

                Electrical_2KWTask_SQL.deleteHistoryData(pool, del_time);

                //System.out.println(Com.getDateTimeFormat(del_time, Com.DTF_YMDhms));

                Thread.sleep(3000);

            } catch (Exception e) {

                try {

@@ -107,4 +112,17 @@
        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();

    }

}


 ElectricalSystem_MonitorServer_2KW/src/com/electrical/TwoKW/Electrical_2KWTask_SQL.java

@@ -749,6 +749,50 @@
            
        }
    }
	
	
    /**
     * 删除创建时间超时的历史实时记录表
     * @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_electric2mwsystem' " + 
                                 " AND TABLE_NAME like 'tb_electric2mw_realdata_%' " + 
                                 " 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_electric2mwsystem." + 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();
        }
    }
}