package com.dev.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Calendar;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.config.AppConfig;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
/*
|
* 历史上数据检测删除线程
|
*/
|
public class HistoryData_RealDelete_Thread implements Runnable{
|
public MysqlConnPool conn_pool;
|
public AppConfig config;
|
public List<Device_inf> devices;
|
//public static final int STORAGE_COUNT_MAX = 60*60*24*365; //最大存储历史数据的时长365天
|
|
|
public HistoryData_RealDelete_Thread(MysqlConnPool conn_pool,AppConfig config,List<Device_inf> devices){
|
this.conn_pool = conn_pool;
|
this.config = config;
|
this.devices = devices;
|
}
|
|
@Override
|
public void run() {
|
System.out.println("HistoryData_RealDelete_Thread start at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
while(true) {
|
try {
|
String nowdate = Com.getDateTimeFormat(new Date(), Com.DTF_Y_M_D);
|
//判断当天的历史数据表是否存在并创建
|
for(int k = 0 ;k < devices.size();k++) {
|
if(!Dev_UDPServer_Thread_SQL.checkHistoryIfNotExistsTable(conn_pool, devices.get(k).dev_id, new Date())) {
|
Dev_UDPServer_Thread_SQL.createDevData_HistoryTable(conn_pool,devices.get(k),nowdate);
|
}
|
if(!Dev_UDPServer_Thread_SQL.checkHistoryIfNotExistsTable(conn_pool, devices.get(k).dev_id, tomorrow(new Date()))) {
|
Dev_UDPServer_Thread_SQL.createDevData_HistoryTable(conn_pool,devices.get(k),Com.getDateTimeFormat(tomorrow(new Date()), Com.DTF_Y_M_D));
|
}
|
}
|
|
Date deldate = getDateBefore(new Date(),config.getEquie_device_historydata_max_days()); //计算应删除记录时间
|
|
//System.err.println(config.getEquie_device_historydata_max_days()+"==="+deldate);
|
//System.out.println(Com.getDateTimeFormat(deldate, Com.DTF_YMDhms));
|
//删除1年前的历史数据表
|
deleteHistoryData(conn_pool,deldate);
|
Thread.sleep(10000);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 返回明天日期
|
* @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();
|
}
|
|
/**
|
* 删除超时的历史数据表
|
*/
|
public void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_equip_history' " +
|
" AND TABLE_NAME like 'tb_devdata_history%' " +
|
" AND UPDATE_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_equip_history." + 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();
|
}
|
}
|
|
public Date getLastYear() {
|
Calendar c = Calendar.getInstance();
|
//过去一年
|
c.setTime(new Date());
|
c.add(Calendar.YEAR, -1);
|
return c.getTime();
|
}
|
|
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();
|
}
|
|
|
public static void main(String[] args) {
|
Date d1 = new Date();
|
System.out.println(Com.getDateTimeFormat(getDateBefore(d1,365), Com.DTF_YMDhms));
|
//System.out.println(Com.getDateTimeFormat(getLastYear(), Com.DTF_YMDhms));
|
}
|
}
|
