package com.battmonitor.data;
|
|
import java.io.File;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
import com.config.AppConfig;
|
|
public class BattData_RT_RamDB_Thread_SQL {
|
|
/**
|
* ´´½¨tb_batt_rtdata±í²¢ÇÒ¸ù¾Ý¼¯ºÏbd_al£¬Ïò±íÖÐÌí¼Óµç³Ø×éidºÍmon_num Öµ
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void createBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
|
{
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.BattRtData_Table + "; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattRtData_Table + " "
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`rec_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`mon_num` INT NOT NULL DEFAULT 0, "
|
+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_tmp` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_res` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_ser` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_conn_res` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`mon_JH_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "INDEX index_BattGroupId (`BattGroupId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String sql_str2 = "INSERT INTO " + Sql_Mysql.BattRtData_Table
|
+ "(BattGroupId,"
|
+ "mon_num)"
|
+ " VALUES ";
|
int cnt = 0;
|
if(bd_al.size() > 0) {
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
for(int mc=0; mc<brt.MonCount; mc++) {
|
if(cnt > 0) {
|
sql_str2 += ",";
|
}
|
sql_str2 += "(" + brt.BattGroupId + "," + (mc+1) + ")";
|
cnt++;
|
}
|
}
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str0);
|
sql.sqlMysqlExecute(sql_str1);
|
if(bd_al.size() > 0) {
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ¸üÐÂtb_batt_rtdata±íÖеÄÊý¾Ý
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void updateBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
for(int index=0; index<bd_al.size(); index++) {
|
|
BattData_RT brt = bd_al.get(index);
|
String sql_str = "UPDATE " + Sql_Mysql.BattRtData_Table
|
+ " SET "
|
+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "'";
|
|
float tmp_dt = 0;
|
String sql_str_vol = " ";
|
for(int n=0; n<brt.MonCount; n++) {
|
tmp_dt = brt.al_MonVol.get(n).monVol;
|
|
//System.out.println(index+"===="+tmp_dt+"%%%%%%%%%%%%%%%%%%%%%%%%");
|
|
if(brt.al_MonVol_RtOld.get(n).monVol != tmp_dt) {
|
brt.al_MonVol_RtOld.get(n).monVol = tmp_dt;
|
sql_str_vol += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_vol.trim().length() > 1) {
|
sql_str_vol = ", mon_vol = CASE mon_num " + sql_str_vol + " ELSE mon_vol END ";
|
}
|
|
String sql_str_tmp = " ";
|
for(int n=0; n<brt.MonCount; n++) {
|
tmp_dt = brt.al_MonVol.get(n).monTmp;
|
if(brt.al_MonVol_RtOld.get(n).monTmp != tmp_dt) {
|
brt.al_MonVol_RtOld.get(n).monTmp = tmp_dt;
|
sql_str_tmp += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_tmp.trim().length() > 1) {
|
sql_str_tmp = ", mon_tmp = CASE mon_num " + sql_str_tmp + " ELSE mon_tmp END ";
|
}
|
|
String sql_str_res = " ";
|
for(int n=0; n<brt.MonCount; n++) {
|
tmp_dt = brt.al_MonVol.get(n).monRes;
|
if(brt.al_MonVol_RtOld.get(n).monRes != tmp_dt) {
|
brt.al_MonVol_RtOld.get(n).monRes = tmp_dt;
|
sql_str_res += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_res.trim().length() > 1) {
|
sql_str_res = ", mon_res = CASE mon_num " + sql_str_res + " ELSE mon_res END ";
|
}
|
|
String sql_str_ser = " ";
|
for(int n=0; n<brt.MonCount; n++) {
|
tmp_dt = brt.al_MonVol.get(n).monSer;
|
if(brt.al_MonVol_RtOld.get(n).monSer != tmp_dt) {
|
brt.al_MonVol_RtOld.get(n).monSer = tmp_dt;
|
sql_str_ser += " WHEN " + (n+1) + " THEN " + String.format("%1.3f", tmp_dt);
|
}
|
}
|
if(sql_str_ser.trim().length() > 1) {
|
sql_str_ser = ", mon_ser = CASE mon_num " + sql_str_ser + " ELSE mon_ser END ";
|
}
|
|
String sql_str_2 = sql_str_vol + sql_str_tmp + sql_str_res + sql_str_ser;
|
if(sql_str_2.trim().length() > 1) {
|
sql_str_2 += " WHERE BattGroupId=" + brt.BattGroupId;
|
/*
|
+ " AND mon_num IN (";
|
for(int n=0; n<brt.MonCount; n++) {
|
if(n > 0) {
|
sql_str_2 += ",";
|
}
|
sql_str_2 += (n+1);
|
}
|
sql_str_2 += ")";
|
*/
|
} else {
|
sql_str_2 = " WHERE BattGroupId=" + brt.BattGroupId;
|
}
|
|
sql_str += sql_str_2;
|
System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
/************************************************************************************************/
|
/************************************************************************************************/
|
/**
|
* ´´½¨tb_batt_rtstateÊý¾Ý¿â±í£¬²¢ÇÒ½«bd_alÊý×éÖеÄBattGroupIdÊý¾Ý²åÈëµ½±íÖÐ
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void createBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
|
{
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.BattRtState_Table + "; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattRtState_Table + " "
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
|
+ "`batt_count` INT NOT NULL DEFAULT 0, "
|
+ "`rec_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`online_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_tmp` FLOAT NOT NULL DEFAULT 0, "
|
+ "`group_curr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_state` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_type` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`batt_test_recordtime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`batt_test_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`batt_test_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_real_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_cap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_power1_time` INT NOT NULL DEFAULT 0, "
|
+ "`batt_rest_power2_time` INT NOT NULL DEFAULT 0, "
|
+ "INDEX index_BattGroupId (`BattGroupId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String sql_str2 = "INSERT INTO " + Sql_Mysql.BattRtState_Table
|
+ "(BattGroupId)"
|
+ " VALUES ";
|
if(bd_al.size() > 0) {
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
if(n > 0) {
|
sql_str2 += ",";
|
}
|
sql_str2 += "(" + brt.BattGroupId + ")";
|
}
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str0);
|
sql.sqlMysqlExecute(sql_str1);
|
if(bd_al.size() > 0) {
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸ù¾Ýbd_al¸üÐÂtb_batt_rtstate±í
|
* @param conn_pool
|
* @param bd_al
|
*/
|
public static void updateBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al)
|
{
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try
|
{
|
String sql_str = "UPDATE " + Sql_Mysql.BattRtState_Table
|
+ " SET "
|
+ " rec_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',";
|
|
sql_str += " batt_count = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.MonCount;
|
}
|
|
sql_str += " END, " + " online_vol = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getOnlineVol();
|
}
|
|
sql_str += " END, " + " group_vol = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupVol();
|
}
|
|
sql_str += " END, " + " group_tmp = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupTmp();
|
}
|
|
sql_str += " END, " + " group_curr = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCurr();
|
}
|
|
sql_str += " END, " + " batt_state = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattState();
|
}
|
|
sql_str += " END, " + " batt_test_type = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattTestType();
|
}
|
|
sql_str += " END, " + " batt_test_starttime = CASE BattGroupId ";
|
Date dt = new Date();
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
dt.setTime(brt.getTestStartTime());
|
sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
|
}
|
|
sql_str += " END, " + " batt_test_recordtime = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
dt.setTime(brt.getTestRecordTime());
|
sql_str += " WHEN " + brt.BattGroupId + " THEN '" + Com.getDateTimeFormat(dt, Com.DTF_YMDhms) + "'";
|
}
|
|
sql_str += " END, " + " batt_test_tlong = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestTimeLong();
|
}
|
|
sql_str += " END, " + " batt_test_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCap();
|
}
|
|
sql_str += " END, " + " batt_real_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRealCap();
|
}
|
|
sql_str += " END, " + " batt_rest_cap = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestCap();
|
}
|
|
sql_str += " END, " + " batt_rest_power1_time = CASE BattGroupId ";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
sql_str += " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestTime();
|
}
|
|
sql_str += " END WHERE BattGroupId IN (";
|
for(int n=0; n<bd_al.size(); n++) {
|
BattData_RT brt = bd_al.get(n);
|
if(n > 0) {
|
sql_str += ",";
|
}
|
sql_str += brt.BattGroupId;
|
}
|
sql_str += ")";
|
System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
/**
|
* ½¨tb_server_state±í²¢ÉèÖÃÖµ
|
* @param conn_pool
|
*/
|
public static void createServerState_RamDB_Table(MysqlConnPool conn_pool, float ser_ver)
|
{
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.ServerState_Table + "; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.ServerState_Table + " "
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`server_version` FLOAT NOT NULL DEFAULT 0, "
|
+ "`server_datetime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`max_mem` BIGINT NOT NULL DEFAULT 0, "
|
+ "`total_mem` BIGINT NOT NULL DEFAULT 0, "
|
+ "`free_men` BIGINT NOT NULL DEFAULT 0, "
|
|
+ "`total_disc_space` BIGINT NOT NULL DEFAULT 0, "
|
+ "`free_disc_space` BIGINT NOT NULL DEFAULT 0, "
|
|
+ "`db_conn_max` INT NOT NULL DEFAULT 0, "
|
+ "`db_conn_count` INT NOT NULL DEFAULT 0, "
|
|
+ "`app_conn_max` INT NOT NULL DEFAULT 0, " //Ö÷³ÌÐòÊý¾Ý¿âÁ¬½Ó³Ø×î´óÁ¬½ÓÊý
|
+ "`app_busy_conn_cnt` INT NOT NULL DEFAULT 0, " //Ö÷³ÌÐòÊý¾Ý¿âÁ¬½Ó³ØÕýÔÚʹÓõÄÁ¬½ÓÊý
|
+ "`app_idle_conn_cnt` INT NOT NULL DEFAULT 0, " //Ö÷³ÌÐòÊý¾Ý¿âÁ¬½Ó³Ø¿ÕÓàÁ¬½ÓÊý
|
|
+ "`server_cpu_rate` FLOAT NOT NULL DEFAULT 0, " //·þÎñÆ÷cpuʹÓÃÂÊ
|
+ "`server_network_rate` FLOAT NOT NULL DEFAULT 0, "//·þÎñÆ÷ÍøÂçʹÓÃÂÊ
|
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=MEMORY DEFAULT CHARSET=utf8;";
|
String sql_str2 = "INSERT INTO " + Sql_Mysql.ServerState_Table
|
+ "(server_version) VALUES (" + ser_ver + ")";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str0);
|
sql.sqlMysqlExecute(sql_str1);
|
sql.sqlMysqlExecute(sql_str2);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**************************************************************************************
|
**************************************************************************************/
|
/**
|
* ¸üÐÂtb_server_state±íÖеÄÖµ
|
* @param conn_pool
|
*/
|
public static void updateServerState_RamDB_Table(MysqlConnPool conn_pool, AppConfig app_cfg, String app_path)
|
{
|
Runtime rt = Runtime.getRuntime();
|
File diskPartition = new File(app_path);
|
long totalCapacity = diskPartition.getTotalSpace() / (1024*1024);
|
long usablePatitionSpace = diskPartition.getUsableSpace() / (1024*1024);
|
|
int conn_max = 0;
|
int conn_count = 0;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
ResultSet res = sql.sqlMysqlQuery("show variables like 'max_connections'");
|
if(res.next()) {
|
conn_max = res.getInt(2);
|
}
|
res = sql.sqlMysqlQuery("show status like 'Threads_connected'");
|
if(res.next()) {
|
conn_count = res.getInt(2);
|
}
|
|
String sql_str = "UPDATE " + Sql_Mysql.ServerState_Table
|
+ " SET "
|
+ "server_datetime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "',"
|
+ "max_mem=" + rt.maxMemory() + ","
|
+ "total_mem=" + rt.totalMemory() + ","
|
+ "free_men=" + rt.freeMemory() + ","
|
|
+ "total_disc_space=" + totalCapacity + ","
|
+ "free_disc_space=" + usablePatitionSpace + ","
|
+ "db_conn_max=" + conn_max + ","
|
+ "db_conn_count=" + conn_count + ","
|
|
+ "app_conn_max=" + conn_pool.getMaxPoolSize() + ","
|
+ "app_busy_conn_cnt=" + conn_pool.getNumBusyConnections() + ","
|
+ "app_idle_conn_cnt=" + conn_pool.getNumIdleConnections() + ","
|
+ "server_network_rate=" + app_cfg.getFbsDevCommRate();
|
//+ "server_cpu_rate=" + ComputerMonitorUtil.getCpuUsage();
|
|
sql.sqlMysqlExecute(sql_str);
|
|
sql.sqlMysqlUseDB("web_site");
|
if(true == sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
|
String sql_str_1 = "UPDATE " + "tb_process_survey" + " SET "
|
+ " ProcessTime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "' "
|
+ " WHERE ProcessId=11001";
|
sql.sqlMysqlExecute(sql_str_1);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/************************************************************************************************/
|
public static boolean updateServerStartInfTo_RamDB_Table(MysqlConnPool conn_pool, double ver)
|
{
|
boolean res = false;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
if(true == sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
|
String sql_str_1 = "UPDATE " + "tb_process_survey" + " SET "
|
+ " Process_starttime='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
|
+ " ProcessVersion='" + String.format("V%1.3f", ver) + "' "
|
+ " WHERE ProcessId=11001";
|
//System.out.println(sql_str_1);
|
sql.sqlMysqlExecute(sql_str_1);
|
res = true;
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
res = false;
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
return res;
|
}
|
/************************************************************************************************/
|
/************************************************************************************************/
|
}
|
