package com.battdata_rt;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
import java.io.File;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
public class BattData_RT_RamDB_Thread_SQL {
|
public static void createBattData_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al) {
|
String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_batt_rtdata`; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_batt_rtdata` ( `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, INDEX index_BattGroupId (`BattGroupId`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String sql_str2 = "INSERT INTO `db_ram_db`.`tb_batt_rtdata`(BattGroupId,mon_num) VALUES ";
|
|
int cnt = 0;
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
for (int mc = 0; mc < brt.MonCount; mc++) {
|
if (cnt > 0) {
|
sql_str2 = sql_str2 + ",";
|
}
|
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);
|
sql.sqlMysqlExecute(sql_str2);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
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 = (BattData_RT) bd_al.get(index);
|
String sql_str = "UPDATE `db_ram_db`.`tb_batt_rtdata` SET rec_datetime='"
|
+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "'";
|
|
float tmp_dt = 0.0F;
|
String sql_str_vol = " ";
|
for (int n = 0; n < brt.MonCount; n++) {
|
tmp_dt = ((MonVolData) brt.al_MonVol.get(n)).monVol;
|
|
if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monVol != tmp_dt) {
|
((MonVolData) brt.al_MonVol_RtOld.get(n)).monVol = tmp_dt;
|
sql_str_vol = sql_str_vol + " WHEN " + (n + 1) + " THEN "
|
+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monTmp;
|
if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monTmp != tmp_dt) {
|
((MonVolData) brt.al_MonVol_RtOld.get(n)).monTmp = tmp_dt;
|
sql_str_tmp = sql_str_tmp + " WHEN " + (n + 1) + " THEN "
|
+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monRes;
|
if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monRes != tmp_dt) {
|
((MonVolData) brt.al_MonVol_RtOld.get(n)).monRes = tmp_dt;
|
sql_str_res = sql_str_res + " WHEN " + (n + 1) + " THEN "
|
+ String.format("%1.3f", new Object[] { Float.valueOf(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 = ((MonVolData) brt.al_MonVol.get(n)).monSer;
|
if (((MonVolData) brt.al_MonVol_RtOld.get(n)).monSer != tmp_dt) {
|
((MonVolData) brt.al_MonVol_RtOld.get(n)).monSer = tmp_dt;
|
sql_str_ser = sql_str_ser + " WHEN " + (n + 1) + " THEN "
|
+ String.format("%1.3f", new Object[] { Float.valueOf(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 = sql_str_2 + " WHERE BattGroupId=" + brt.BattGroupId;
|
} else {
|
sql_str_2 = " WHERE BattGroupId=" + brt.BattGroupId;
|
}
|
|
sql_str = sql_str + sql_str_2;
|
|
sql.sqlMysqlExecute(sql_str);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void createBattState_RT_RamDB_Table(MysqlConnPool conn_pool, ArrayList<BattData_RT> bd_al) {
|
String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_batt_rtstate`; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_batt_rtstate` ( `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 `db_ram_db`.`tb_batt_rtstate`(BattGroupId) VALUES ";
|
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
if (n > 0) {
|
sql_str2 = sql_str2 + ",";
|
}
|
sql_str2 = sql_str2 + "(" + brt.BattGroupId + ")";
|
}
|
|
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) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
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 `db_ram_db`.`tb_batt_rtstate` SET rec_datetime='"
|
+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "',";
|
|
sql_str = sql_str + " batt_count = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.MonCount;
|
}
|
|
sql_str = sql_str + " END, online_vol = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getOnlineVol();
|
}
|
|
sql_str = sql_str + " END, group_vol = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupVol();
|
}
|
|
sql_str = sql_str + " END, group_tmp = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getGroupTmp();
|
}
|
|
sql_str = sql_str + " END, group_curr = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCurr();
|
}
|
|
sql_str = sql_str + " END, batt_state = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattState();
|
}
|
|
sql_str = sql_str + " END, batt_test_type = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattTestType();
|
}
|
|
sql_str = sql_str + " END, batt_test_starttime = CASE BattGroupId ";
|
Date dt = new Date();
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
dt.setTime(brt.getTestStartTime());
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN '"
|
+ Com.getDateTimeFormat(dt, "yyyy-MM-dd HH:mm:ss") + "'";
|
}
|
|
sql_str = sql_str + " END, batt_test_recordtime = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
dt.setTime(brt.getTestRecordTime());
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN '"
|
+ Com.getDateTimeFormat(dt, "yyyy-MM-dd HH:mm:ss") + "'";
|
}
|
|
sql_str = sql_str + " END, batt_test_tlong = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestTimeLong();
|
}
|
|
sql_str = sql_str + " END, batt_test_cap = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getTestCap();
|
}
|
|
sql_str = sql_str + " END, batt_real_cap = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRealCap();
|
}
|
|
sql_str = sql_str + " END, batt_rest_cap = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestCap();
|
}
|
|
sql_str = sql_str + " END, batt_rest_power1_time = CASE BattGroupId ";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
sql_str = sql_str + " WHEN " + brt.BattGroupId + " THEN " + brt.getBattRestTime();
|
}
|
|
sql_str = sql_str + " END WHERE BattGroupId IN (";
|
for (int n = 0; n < bd_al.size(); n++) {
|
BattData_RT brt = (BattData_RT) bd_al.get(n);
|
if (n > 0) {
|
sql_str = sql_str + ",";
|
}
|
sql_str = sql_str + brt.BattGroupId;
|
}
|
sql_str = sql_str + ")";
|
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void createServerState_RamDB_Table(MysqlConnPool conn_pool) {
|
String sql_str0 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_server_state`; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_server_state` ( `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, PRIMARY KEY (`num`)) ENGINE=MEMORY DEFAULT CHARSET=utf8;";
|
|
String sql_str2 = "INSERT INTO `db_ram_db`.`tb_server_state`(server_version) VALUES (1.101)";
|
|
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) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateServerState_RamDB_Table(MysqlConnPool conn_pool) {
|
Runtime rt = Runtime.getRuntime();
|
String path = System.getProperty("user.dir");
|
File diskPartition = new File(path.substring(0, path.indexOf(":") + 1));
|
long totalCapacity = diskPartition.getTotalSpace() / 1048576L;
|
long usablePatitionSpace = diskPartition.getUsableSpace() / 1048576L;
|
|
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 `db_ram_db`.`tb_server_state` SET server_datetime='"
|
+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "'," + "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;
|
|
sql.sqlMysqlExecute(sql_str);
|
|
sql.sqlMysqlUseDB("web_site");
|
if (sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
|
String sql_str_1 = "UPDATE tb_process_survey SET ProcessTime='"
|
+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "' " + " WHERE ProcessId=11001";
|
sql.sqlMysqlExecute(sql_str_1);
|
}
|
} catch (SQLException e) {
|
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("web_site");
|
if (sql.sqlMysqlCheckIfTableExist("tb_process_survey")) {
|
String sql_str_1 = "UPDATE tb_process_survey SET Process_starttime='"
|
+ Com.getDateTimeFormat(new Date(), "yyyy-MM-dd HH:mm:ss") + "', " + " ProcessVersion='"
|
+ String.format("V%1.3f", new Object[] { Double.valueOf(ver) }) + "' "
|
+ " WHERE ProcessId=11001";
|
sql.sqlMysqlExecute(sql_str_1);
|
res = true;
|
}
|
} catch (SQLException e) {
|
res = false;
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
return res;
|
}
|
}
|
|
/*
|
* Location:
|
* C:\Users\LiJun\Desktop\公司各种设备资料\9600显示模块相关文件\后台程序\2018-09-07\BattFBS9600XSP.
|
* jar Qualified Name: com.battdata_rt.BattData_RT_RamDB_Thread_SQL JD-Core
|
* Version: 0.6.2
|
*/
|
