package com.dev.data;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Dev_UDPServer_Thread_SQL {
|
|
public void initDB(MysqlConnPool conn_pool) {
|
|
CreateDb_Equipstand(conn_pool); //´´½¨db_equipstandÊý¾Ý¿â
|
|
CreateDb_Equip_History(conn_pool); //´´½¨ÀúʷʵʱÊý¾Ý¼Ç¼Êý¾Ý¿â
|
|
CreateDb_Equip_Alarm(conn_pool); //´´½¨¸æ¾¯Êý¾Ý¿â
|
|
CreateTb_device_inf(conn_pool); //´´½¨É豸ÐÅÏ¢±í
|
|
createTb_DevData_RtTable(conn_pool);
|
|
|
}
|
|
/**
|
* ´´½¨Á¬½Ó״̬±í
|
* @param conn_pool
|
*/
|
public void createTb_DevData_RtTable(MysqlConnPool conn_pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_RtTable+" ( " +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int(11) NOT NULL DEFAULT '0'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `index_dev_id` (`dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈëÉ豸״̬¼Ç¼±í
|
*/
|
public static void insertOrUpdateTb_DevData_RtTable(MysqlConnPool conn_pool,List<Device_inf> devices) {
|
String sql_str = " REPLACE INTO " + Sql_Mysql.Tb_DevData_RtTable + "(dev_id,record_time) values ";
|
for(int i = 0;i<devices.size();i++) {
|
if(i>0) {
|
sql_str += ",";
|
}
|
sql_str += "("+devices.get(i).dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"')";
|
}
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üÐÂÉ豸µÄ¼Ç¼ʱ¼ä
|
* @param conn_pool
|
* @param dev
|
*/
|
public static void updateDeviceDate(MysqlConnPool conn_pool,Device_inf dev) {
|
String sql_str = " UPDATE "+Sql_Mysql.Tb_DevData_RtTable+" SET record_time = '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = " + dev.dev_id;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
|
|
/**
|
* ²åÈëÉ豸ÐÅÏ¢
|
* @param conn_pool
|
*/
|
public void insertTb_device_inf(MysqlConnPool conn_pool,List<Device_inf> devices) {
|
String sql_str = " INSERT INTO " + Sql_Mysql.Tb_Device_InfTable + "(dev_name,dev_id,sys_type,sys_name,analog_count,switch_count,state_count,param_count,note) VALUES";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
Device_inf device = null;
|
//System.out.println("===="+max_dev_id+"====");
|
int count = 0;
|
for(int i=0;i<devices.size();i++) {
|
device = devices.get(i);
|
Device_inf temp = isExistsDevice(conn_pool, device.getDev_id());
|
if(null != temp) {
|
device.setDev_id(temp.getDev_id());
|
device.setConfigsDevId(temp.getDev_id());
|
try {
|
//µ±Ç°É豸ÒÑ´æÔÚ£»¸üе±Ç°É豸Öи÷¸ö±äÁ¿ÔÚÊý¾Ý¿âÖеÄÊýÁ¿
|
String sql_str_update = " update " +Sql_Mysql.Tb_Device_InfTable
|
+ " SET analog_count="+device.getAnalog_count()+","
|
+ " switch_count="+device.getSwitch_count()+","
|
+ " state_count="+device.getState_count()+","
|
+ " param_count="+device.data_count+","
|
+ " sys_type = '"+device.sys_type+"',"
|
+ " sys_name = '"+device.sys_name+"',"
|
+ " note='' "
|
+ " WHERE dev_id = '"+device.getDev_id()+"';";
|
if(device.dev_id < 2000) {
|
//×Óվϵͳ
|
for(int k = 0;k<device.datamean.size();k++) {
|
sql_str_update = " update " +Sql_Mysql.Tb_Device_InfTable
|
+ " SET analog_count="+device.getAnalog_count()+","
|
+ " switch_count="+device.getSwitch_count()+","
|
+ " state_count="+device.getState_count()+","
|
+ " param_count="+device.data_count+","
|
+ " sys_type = '"+device.sys_type+"',"
|
+ " sys_name = '"+device.sys_name+"',"
|
+ " match_type = "+device.datamean.get(k).match_type+","
|
+ " match_str = '"+device.datamean.get(k).match_str+"',"
|
+ " note='' "
|
+ " WHERE dev_name = '"+device.datamean.get(k).dev_name+"' AND dev_id = "+device.dev_id;
|
sql.sqlMysqlExecute(sql_str_update);
|
}
|
}else {
|
//System.out.println(sql_str_update);
|
sql.sqlMysqlExecute(sql_str_update);
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
|
}
|
continue;
|
}
|
|
if(count > 0) {
|
sql_str += ",";
|
}
|
if(device.dev_id < 2000) {
|
//×Óվϵͳ
|
for(int k = 0;k<device.datamean.size();k++) {
|
if(count > 0 && k >0) {
|
sql_str += ",";
|
}
|
sql_str += "("
|
+ "'" + device.datamean.get(k).dev_name + "',"
|
+ "" + device.datamean.get(k).dev_id + ","
|
+ "" + device.getSys_type() + ","
|
+ "'" + device.getSys_name() + "',"
|
+ "" + device.getAnalog_count() + ","
|
+ "" + + device.getSwitch_count() + ","
|
+ "" + + device.getState_count() + ","
|
+ "" + + device.data_count + ","
|
+ "''"
|
+ ")";
|
count++;
|
}
|
}else {
|
sql_str += "("
|
+ "'" + device.getDev_name() + "',"
|
+ "" + device.getDev_id() + ","
|
+ "" + device.getSys_type() + ","
|
+ "'" + device.getSys_name() + "',"
|
+ "" + device.getAnalog_count() + ","
|
+ "" + + device.getSwitch_count() + ","
|
+ "" + + device.getState_count() + ","
|
+ "" + + device.data_count + ","
|
+ "''"
|
+ ")";
|
}
|
count ++;
|
}
|
try {
|
if(count > 0) {
|
System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨É豸ÐÅÏ¢±í
|
* @param conn_pool
|
*/
|
public void CreateTb_device_inf(MysqlConnPool conn_pool) {
|
String sql_str1 = " CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_Device_InfTable+ " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" dev_name varchar(126) NOT NULL DEFAULT '' COMMENT 'É豸ÀàÐÍ'," +
|
" dev_id int(11) NOT NULL DEFAULT '1' COMMENT 'É豸id'," +
|
" sys_type int(11) NOT NULL DEFAULT '0' COMMENT 'ϵͳÀàÐÍ'," +
|
" sys_name varchar(64) NOT NULL DEFAULT '' COMMENT 'ϵͳÃû³Æ'," +
|
" match_type int(11) NOT NULL DEFAULT '0' COMMENT 'Æ¥ÅäÀàÐÍ0:ÎÞ¹æÔò;1:µÈÓÚÆ¥Å䣻2²»µÈÓÚÆ¥Åä'," +
|
" match_str varchar(64) NOT NULL DEFAULT '' COMMENT 'Æ¥Åä×Ö·û´®'," +
|
" analog_count int(11) NOT NULL DEFAULT '0' COMMENT 'Ä£ÄâÁ¿ÊýÁ¿'," +
|
" switch_count int(11) NOT NULL DEFAULT '0' COMMENT '¿ª¹ØÁ¿ÊýÁ¿'," +
|
" state_count int(11) NOT NULL DEFAULT '0' COMMENT '״̬Á¿ÊýÁ¿'," +
|
" param_count int(11) NOT NULL DEFAULT '0' COMMENT '²ÎÊý×ÜÊýÁ¿'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" dev_str varchar(32) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
try {
|
sql.sqlMysqlExecute(sql_str1);
|
//Ìí¼ÓΨһË÷Òý
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_device_inf'"
|
+ " AND column_name='param_count'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
|
+ " ADD COLUMN param_count int(11) NOT NULL DEFAULT '0' COMMENT '²ÎÊý×ÜÊýÁ¿';");
|
}
|
//Ìí¼ÓÆ¥Åä¹æÔò
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_device_inf'"
|
+ " AND column_name='match_type'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
|
+ " ADD COLUMN match_type int(11) NOT NULL DEFAULT '0' COMMENT 'Æ¥ÅäÀàÐÍ0:ÎÞ¹æÔò;1:µÈÓÚÆ¥Å䣻2²»µÈÓÚÆ¥Åä';");
|
}
|
|
//Ìí¼ÓÆ¥Åä×Ö·û´®
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_device_inf'"
|
+ " AND column_name='match_str'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
|
+ " ADD COLUMN match_str varchar(64) NOT NULL DEFAULT '' COMMENT 'Æ¥Åä×Ö·û´®';");
|
}
|
|
//Ìí¼Ó±êʶ×Ö·û´®
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_device_inf'"
|
+ " AND column_name='dev_str'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Device_InfTable
|
+ " ADD COLUMN dev_str varchar(32) NOT NULL DEFAULT '';");
|
}
|
} catch (SQLException e) {
|
//e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨db_equipstandÊý¾Ý¿â
|
* @param conn_pool
|
*/
|
public void CreateDb_Equipstand(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equipstand);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ´´½¨db_equip_history ÀúʷʵʱÊý¾Ý¿â
|
* @param conn_pool
|
*/
|
public void CreateDb_Equip_History(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equip_History);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨db_equip_history É豸¸æ¾¯Êý¾Ý¿â
|
* @param conn_pool
|
*/
|
public void CreateDb_Equip_Alarm(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Equip_Alarm);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* Åжϵ±Ç°É豸ÊÇ·ñÔÚÊý¾Ý¿âÖдæÔÚ
|
* @param conn_pool
|
* @param dev_name
|
* @return
|
*/
|
public Device_inf isExistsDevice(MysqlConnPool conn_pool,int dev_id) {
|
String sql_str = " SELECT * FROM db_equipstand.tb_device_inf WHERE dev_id = '" + dev_id + "';";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
Device_inf dev = null;
|
try {
|
if(res.next()) {
|
dev = new Device_inf();
|
dev.dev_id = res.getInt("dev_id"); //É豸ID
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return dev;
|
}
|
|
|
/**
|
* »ñÈ¡Êý¾Ý¿âÖÐ×î´óµÄÉ豸ID+1
|
* @param conn_pool
|
* @return
|
*/
|
public int getMaxDev_Id(MysqlConnPool conn_pool) {
|
int max_dev_id = 1001;
|
String sql_str = " SELECT MAX(dev_id) as dev_id FROM " + Sql_Mysql.Tb_Device_InfTable;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(res.next()) {
|
int dev_id = res.getInt("dev_id");
|
if(dev_id > 0) {
|
max_dev_id = dev_id+1;
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
return max_dev_id;
|
}
|
|
/**
|
* »ñÈ¡Êý¾Ý¿âÖÐ×î´óµÄÉ豸ID+1
|
* @param conn_pool
|
* @return
|
*/
|
public int getMaxSys_type(MysqlConnPool conn_pool) {
|
int max_sys_type = 1;
|
String sql_str = " SELECT MAX(sys_type) as sys_type FROM " + Sql_Mysql.Tb_Device_InfTable;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(res.next()) {
|
max_sys_type = res.getInt("sys_type")+1;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
return max_sys_type;
|
}
|
|
/**
|
* ´´½¨É豸ʵʱ״̬±í,ÒÔ¼°´´½¨×´Ì¬¶ÔÓ¦±í
|
* @param gB_MysqlConnPool
|
* @param devices
|
*/
|
public void initRtDataCfgTable(MysqlConnPool conn_pool, List<Device_inf> devices) {
|
for(int i=0;i<devices.size();i++) {
|
Device_inf dev = devices.get(i);
|
//´´½¨É豸״̬±í
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int NOT NULL DEFAULT '"+dev.dev_id+"'," +
|
" data_type int NOT NULL DEFAULT '0'," +
|
" param_count int NOT NULL DEFAULT '0'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00',";
|
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
sql_str += " state"+(j+1)+" float NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"',";
|
}
|
sql_str += " PRIMARY KEY (`num`)" +
|
") ENGINE=myisam ROW_FORMAT=DYNAMIC CHARSET=utf8;";
|
//´´½¨ÅäÖÃÐÅÏ¢±í
|
String sql_str_cfg = " CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevData_Rt_Cfg+ "(" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int NOT NULL DEFAULT '1001'," +
|
" data_type int NOT NULL DEFAULT '1'," +
|
" param_count int NOT NULL DEFAULT '0'," +
|
" record_time varchar(20) NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
try {
|
//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
|
sql.sqlMysqlExecute(sql_str);
|
//´´½¨É豸ÅäÖÃÐÅÏ¢±í
|
sql.sqlMysqlExecute(sql_str_cfg);
|
//Ìí¼Ódata_type×Ö¶Î
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
|
+ " AND column_name='data_type'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
|
+ " ADD COLUMN data_type int NOT NULL DEFAULT '0' after dev_id");
|
}
|
res = null;
|
//Ìí¼Ódata_type×Ö¶Î
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
|
+ " AND column_name='param_count'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
|
+ " ADD COLUMN param_count int NOT NULL DEFAULT '0' after data_type");
|
}
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
|
+ " AND column_name='state"+(j+1)+"'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
|
+ " ADD COLUMN `state"+(j+1)+"` float DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'");
|
}
|
}
|
res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "' AND data_type = 2");
|
if(!res.next()) {
|
//²»´æÔÚ״̬ÐÅϢʱ²åÈëĬÈÏÊý¾Ý
|
sql.sqlMysqlExecute("INSERT INTO "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+"(data_type,param_count,dev_id,record_time) VALUES('2','"+dev.data_count+"',"+dev.dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');");;
|
}
|
|
|
/******¿ªÊ¼´´½¨ÅäÖñí******************************************************************************************************************************/
|
//Ìí¼Ódata_type×Ö¶Î
|
res = sql.sqlMysqlQuery(" SELECT * FROM information_schema.columns "
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_cfg'"
|
+ " AND column_name='data_type'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
|
+ " ADD COLUMN data_type int NOT NULL DEFAULT '0' after dev_id");
|
}
|
res = null;
|
//Ìí¼Ódata_type×Ö¶Î
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_cfg'"
|
+ " AND column_name='param_count'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
|
+ " ADD COLUMN param_count int NOT NULL DEFAULT '0' after data_type");
|
}
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_cfg'"
|
+ " AND column_name='state"+(j+1)+"'");
|
if(false == res.next()) {
|
String data_type = "varchar(30)";
|
if(j>400) {
|
data_type = "varchar(15)";
|
}
|
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg
|
+ " ADD COLUMN `state"+(j+1)+"` "+data_type+" NOT NULL DEFAULT ''");
|
}
|
}
|
|
|
|
//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
|
res = sql.sqlMysqlQuery(" SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt_Cfg+" WHERE dev_id = '" + dev.dev_id + "' AND data_type = " + ChildStoodParse_Thread.Config_DataType);
|
if(res.next()) {
|
//´æÔÚÅäÖÃ(¸üÐÂÅäÖÃÐÅÏ¢)
|
sql_str = " UPDATE " + Sql_Mysql.Tb_DevData_Rt_Cfg +
|
" SET record_time = '¼Ç¼ʱ¼ä' "+",param_count='"+dev.data_count+"'";
|
for(int k = 0;k<dev.cfgdatas.size();k++) {
|
ConfigData cfg = dev.cfgdatas.get(k);
|
sql_str += ",state"+(k+1)+"='"+dev.cfgdatas.get(k).data_value+"&&"+cfg.vartype+(cfg.vartype == 1?"&&"+cfg.unit:"")+"'";
|
}
|
sql.sqlMysqlExecute(sql_str+" WHERE dev_id = " + dev.dev_id + " AND data_type = "+ChildStoodParse_Thread.Config_DataType);
|
}else {
|
//²»´æÔÚÅäÖÃÐÅÏ¢
|
sql_str = "INSERT INTO "+Sql_Mysql.Tb_DevData_Rt_Cfg+"(data_type,param_count,dev_id,record_time";
|
String sql_str_end = " VALUES('1','"+dev.data_count+"','"+dev.dev_id+"','¼Ç¼ʱ¼ä'";
|
for(int k = 0;k<dev.cfgdatas.size();k++) {
|
ConfigData cfg = dev.cfgdatas.get(k);
|
sql_str += ",state"+(k+1);
|
sql_str_end +=",'"+dev.cfgdatas.get(k).data_value+"&&"+cfg.vartype+(cfg.vartype == 1?"&&"+cfg.unit:"")+"'";
|
}
|
sql.sqlMysqlExecute(sql_str+") "+sql_str_end+");");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
|
|
/**
|
* ´´½¨É豸ʵʱ״̬±í,ÒÔ¼°´´½¨×´Ì¬¶ÔÓ¦±í
|
* @param gB_MysqlConnPool
|
* @param devices
|
*/
|
public void initRtDataTable(MysqlConnPool conn_pool, List<Device_inf> devices) {
|
for(int i=0;i<devices.size();i++) {
|
Device_inf dev = devices.get(i);
|
//System.err.println(dev.dev_id+":"+dev.cfgdatas.size());
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int NOT NULL DEFAULT '"+dev.dev_id+"'," +
|
" record_time varchar(20) NOT NULL DEFAULT '2000-01-01 00:00:00',";
|
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
sql_str += " state"+(j+1)+" varchar(8) NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"',";
|
}
|
sql_str += " PRIMARY KEY (`num`)" +
|
") ENGINE=myisam ROW_FORMAT=DYNAMIC CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
try {
|
//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
|
sql.sqlMysqlExecute(sql_str);
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_"+(dev.dev_id)+"'"
|
+ " AND column_name='state"+(j+1)+"'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt+dev.dev_id
|
+ " ADD COLUMN `state"+(j+1)+"` varchar(8) DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'");
|
}
|
|
}
|
res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "'");
|
if(!res.next()) {
|
//²»´æÔÚʱ²åÈëĬÈÏÊý¾Ý
|
sql.sqlMysqlExecute("INSERT INTO "+Sql_Mysql.Tb_DevData_Rt+dev.dev_id+"(dev_id,record_time) VALUES("+dev.dev_id+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');");;
|
}
|
|
/******¿ªÊ¼´´½¨ÅäÖñí******************************************************************************************************************************/
|
sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id varchar(12) NOT NULL DEFAULT '"+dev.dev_id+"'," +
|
" record_time varchar(12) NOT NULL DEFAULT '¼Ç¼ʱ¼ä',";
|
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
sql_str += " state"+(j+1)+" varchar(20) NOT NULL DEFAULT '"+dev.cfgdatas.get(j).data_value+"',";
|
}
|
sql_str += " PRIMARY KEY (`num`)" +
|
") ENGINE=myisam DEFAULT CHARSET=utf8;";
|
//´´½¨É豸״̬±í(ÒÔ¼°²¹È«×Ö¶Î)
|
sql.sqlMysqlExecute(sql_str);
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equipstand'"
|
+ " AND table_name='tb_devdata_rt_cfg_"+(dev.dev_id)+"'"
|
+ " AND column_name='state"+(j+1)+"'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id
|
+ " ADD COLUMN `state"+(j+1)+"` varchar(32) DEFAULT '"+dev.cfgdatas.get(j).data_value+"'");
|
}
|
|
}
|
res = sql.sqlMysqlQuery("SELECT dev_id FROM "+Sql_Mysql.Tb_DevData_Rt_Cfg+dev.dev_id+" WHERE dev_id = '" + dev.dev_id + "'");
|
if(res.next()) {
|
//´æÔÚÅäÖÃ(¸üÐÂÅäÖÃÐÅÏ¢)
|
sql_str = " UPDATE " + Sql_Mysql.Tb_DevData_Rt_Cfg + dev.dev_id +
|
" SET record_time = '¼Ç¼ʱ¼ä' ";
|
for(int k = 0;k<dev.cfgdatas.size();k++) {
|
sql_str += ",state"+(k+1)+"='"+dev.cfgdatas.get(k).data_value+"'";
|
}
|
sql.sqlMysqlExecute(sql_str+" WHERE dev_id = " + dev.dev_id);
|
}else {
|
//²»´æÔÚÅäÖÃÐÅÏ¢
|
sql_str = " INSERT INTO " + Sql_Mysql.Tb_DevData_Rt_Cfg + dev.dev_id + "(dev_id,record_time";
|
String sql_str_end = " VALUES('"+dev.dev_id+"','¼Ç¼ʱ¼ä'";
|
for(int k = 0;k<dev.cfgdatas.size();k++) {
|
sql_str += ",state"+(k+1);
|
sql_str_end +=",'"+dev.cfgdatas.get(k).data_value+"'";
|
}
|
sql.sqlMysqlExecute(sql_str+") "+sql_str_end+");");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
|
/**
|
* ´´½¨ÀúʷʵʱÊý¾Ý±í
|
* @param conn_pool
|
* @param devices
|
*/
|
public static void createDevData_HistoryTable(MysqlConnPool conn_pool,Device_inf dev,String date_str) {
|
String tname = "tb_devdata_history_" + date_str + "_" + dev.dev_id;
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id + "(" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int NOT NULL DEFAULT '1004'," +
|
" `data_type` int NOT NULL DEFAULT '0'," +
|
" `param_count` int NOT NULL DEFAULT '0'," +
|
" `record_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
//²¹È«ËùÓÐ×Ö¶Î
|
sql_str = "";
|
int colcount = 0;
|
sql_str += "ALTER TABLE " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id;
|
for(int j=0;j<dev.cfgdatas.size();j++) {
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_equip_history'"
|
+ " AND table_name='"+tname+"'"
|
+ " AND column_name='state"+(j+1)+"'");
|
if(false == res.next()) {
|
if(colcount > 0) {
|
sql_str += ",";
|
}
|
sql_str += " ADD COLUMN `state"+(j+1)+"` float NOT NULL DEFAULT '"+dev.cfgdatas.get(j).defalutval+"'";
|
colcount ++;
|
//sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_DevData_historyTable+date_str + "_" + dev.dev_id
|
// + " ADD COLUMN `state"+(j+1)+"` float DEFAULT '"+dev.cfgdatas.get(j).defalutval+"';");
|
}
|
}
|
if(colcount > 0) {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* Åжϵ±Ç°ÀúʷʵʱÊý¾Ý±íÊÇ·ñ´æÔÚ
|
* @param conn_pool
|
* @param dev_id
|
* @param date
|
* @return true:ÒÑ´æÔÚ false:²»´æÔÚ
|
*/
|
public static boolean checkHistoryIfNotExistsTable(MysqlConnPool conn_pool,int dev_id,Date date) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
String tname;
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_Equip_History);
|
tname = Sql_Mysql.Tb_DevData_historyTable + Com.getDateTimeFormat(date, Com.DTF_Y_M_D) + "_" + dev_id;
|
return sql.sqlMysqlCheckIfTableExist(tname);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return false;
|
}
|
|
/**
|
* ´´½¨É豸ʵʱºÍÀúÊ·¸æ¾¯¼Ç¼±í
|
* @param conn_pool
|
* @param device
|
*/
|
public static void createDevAlarmDataTable(MysqlConnPool conn_pool,Device_inf device) {
|
String sql_str_alarm = " CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevAlarm_Data+device.dev_id+ " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" alarm_name varchar(24) NOT NULL DEFAULT '' COMMENT '×Ö¶ÎÃû³Æ'," +
|
" alarm_value float NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Öµ'," +
|
" alarm_type int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÀàÐÍ'," +
|
" alarm_starttime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯¿ªÊ¼Ê±¼ä'," +
|
" alarm_endtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯½áÊøʱ¼ä'," +
|
" alarm_index int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Ï±ê,¶ÔÓ¦stateX'," +
|
" note varchar(24) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_dev_id` (`dev_id`) USING BTREE " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"; //¸æ¾¯ÊµÊ±¼Ç¼±í
|
String str_sql_alm_his = "CREATE TABLE IF NOT EXISTS " +Sql_Mysql.Tb_DevAlarm_Data_History+device.dev_id+ " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" alarm_name varchar(24) NOT NULL DEFAULT '' COMMENT '×Ö¶ÎÃû³Æ'," +
|
" alarm_value float NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Öµ'," +
|
" alarm_type int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÀàÐÍ'," +
|
" alarm_starttime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯¿ªÊ¼Ê±¼ä'," +
|
" alarm_endtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯½áÊøʱ¼ä'," +
|
" alarm_index int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯Ï±ê,¶ÔÓ¦stateX'," +
|
" note varchar(24) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_dev_id` (`dev_id`) USING BTREE " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"; //¸æ¾¯ÀúÊ·¼Ç¼±í
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
//System.out.println(sql_str_alarm);
|
sql.sqlMysqlExecute(sql_str_alarm);
|
sql.sqlMysqlExecute(str_sql_alm_his);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
}
|
