package com.data.comm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.data.Motor_inf;
|
import com.data.Oil_comm;
|
import com.data.Ups_comm;
|
import com.data.Water_comm;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Lubrication_cooling_SQL {
|
//³õʼ»¯Êý¾Ý¿âÐÅÏ¢
|
public static void init(MysqlConnPool pool) {
|
CreateDB_MW_Motor(pool);
|
|
CreateDB_MW_Motor_History(pool);
|
|
CreateTB_oil_comm(pool);
|
|
CreateTB_water_comm(pool);
|
|
CreateTB_ups_comm(pool);
|
}
|
//´´½¨upsͨѶ±í
|
private static void CreateTB_ups_comm(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_ups_comm+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `input_vol` float NOT NULL DEFAULT '0' COMMENT 'ÊäÈëµçѹ'," +
|
" `input_rate` float NOT NULL DEFAULT '0' COMMENT 'ÊäÈëƵÂÊ'," +
|
" `output_vol` float NOT NULL DEFAULT '0' COMMENT 'Êä³öµçѹ'," +
|
" `output_rate` float NOT NULL DEFAULT '0' COMMENT 'Êä³öƵÂÊ'," +
|
" `load_cap` float NOT NULL DEFAULT '0' COMMENT '¸ºÔØÈÝÁ¿'," +
|
" `load_curr` float NOT NULL DEFAULT '0' COMMENT '¸ºÔصçÁ÷'," +
|
" `ups_inTmp` float NOT NULL DEFAULT '0' COMMENT 'UPSÄÚ²¿Î¶È'," +
|
" `batt_vol` float NOT NULL DEFAULT '0' COMMENT 'µç³Øµçѹ'," +
|
" `batt_cap` float NOT NULL DEFAULT '0' COMMENT 'µç³ØÈÝÁ¿'," +
|
" `rest_time` float NOT NULL DEFAULT '0' COMMENT 'Ê£Óàʱ¼ä'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}finally {
|
sql.close_con();
|
}
|
}
|
//´´½¨Ë®Õ¾Í¨Ñ¶±í
|
private static void CreateTB_water_comm(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_water_comm+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `water_state` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվÔËÐÐ'," +
|
" `water_highTmp` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվ¸ßÓÍα¨¾¯'," +
|
" `water_lowLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµÍҺ뱨¾¯'," +
|
" `water_highLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվ¸ßҺ뱨¾¯'," +
|
" `water_eleMachine1` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµç»ú1¹ÊÕÏ'," +
|
" `water_eleMachine2` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµç»ú1¹ÊÕÏ'," +
|
" `water_returnTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվ»ØÓÍζÈ'," +
|
" `water_outFlow` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮ³ö¿ÚÁ÷Á¿'," +
|
" `water_pre1` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮѹÁ¦1'," +
|
" `water_pre2` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮѹÁ¦2'," +
|
" `water_outpre1` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú1ѹÁ¦'," +
|
" `water_outpre2` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú2ѹÁ¦'," +
|
" `water_outpre3` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú3ѹÁ¦'," +
|
" `water_outpre4` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú4ѹÁ¦'," +
|
" `water_returnFlow` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮ»ØÓÍÁ÷Á¿'," +
|
" `water_coolpre` float NOT NULL DEFAULT '0' COMMENT 'ˮվÀäȴˮѹÁ¦'," +
|
" `water_coolTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվÀäÈ´Ë®»ØˮζÈ'," +
|
" `water_outTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³öÓÍζÈ'," +
|
" `compressor_state` float NOT NULL DEFAULT '0' COMMENT 'ѹËõ»ú״̬'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}finally {
|
sql.close_con();
|
}
|
}
|
//´´½¨ÓÍվͨѶ±í
|
private static void CreateTB_oil_comm(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_oil_comm+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `oil_state` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÔËÐÐ'," +
|
" `oil_highTmp` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾¸ßÓÍα¨¾¯'," +
|
" `oil_lowLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µÍҺ뱨¾¯'," +
|
" `oil_highLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾¸ßҺ뱨¾¯'," +
|
" `oil_eleMachine1` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µç»ú1¹ÊÕÏ'," +
|
" `oil_eleMachine2` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µç»ú1¹ÊÕÏ'," +
|
" `oil_returnTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾»ØÓÍζÈ'," +
|
" `oil_outFlow` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬Óͳö¿ÚÁ÷Á¿'," +
|
" `oil_pre1` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍѹÁ¦1'," +
|
" `oil_pre2` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍѹÁ¦2'," +
|
" `oil_outpre1` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú1ѹÁ¦'," +
|
" `oil_outpre2` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú2ѹÁ¦'," +
|
" `oil_outpre3` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú3ѹÁ¦'," +
|
" `oil_outpre4` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú4ѹÁ¦'," +
|
" `oil_returnFlow` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍ»ØÓÍÁ÷Á¿'," +
|
" `oil_coolpre` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÀäȴˮѹÁ¦'," +
|
" `oil_coolTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÀäÈ´Ë®»ØˮζÈ'," +
|
" `oil_outTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³öÓÍζÈ'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
}finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ¹¹Ôì3.5mwÊý¾Ý¿â
|
* @param pool
|
*/
|
public static void CreateDB_MW_Motor(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(" CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_MW_Motor);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ¹¹Ôì3.5mwÀúʷʵʱÊý¾Ý¿â
|
* @param pool
|
*/
|
public static void CreateDB_MW_Motor_History(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(" CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_MW_Motor_History);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}finally {
|
sql.close_con();
|
}
|
}
|
//¼ÓÔØÊÔÑę́Èó»¬Ë®ÀäϵͳÐÅÏ¢
|
public static void loadDeviceData(MysqlConnPool pool, List<Motor_inf> motors) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Tb_MW_Motor_inf + " WHERE sys_id = 100004";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
Motor_inf motor = new Motor_inf(res.getInt("dev_id"));
|
motor.setDev_ip(res.getString("dev_ip"));
|
motor.setDev_name(res.getString("dev_name"));
|
motor.setSys_id(res.getInt("sys_id"));
|
motor.setSys_name(res.getString("sys_name"));
|
motor.setNote(res.getString("note"));
|
motors.add(motor);
|
}
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
//ʵʱ¸üÐÂÓÍÕ¾Êý¾Ý
|
public static void insertOrUpdateTb_oil_comm(MysqlConnPool pool, Oil_comm oil_comm) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.TB_oil_comm + " where dev_id = " + oil_comm.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.TB_oil_comm + "(dev_id) values("+oil_comm.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.TB_oil_comm + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',oil_state = " + oil_comm.getOil_state()
|
+ ",oil_highTmp = " + oil_comm.getOil_highTmp()
|
+ ",oil_lowLevel = " + oil_comm.getOil_lowLevel()
|
+ ",oil_highLevel = " + oil_comm.getOil_highLevel()
|
+ ",oil_eleMachine1 = " + oil_comm.getOil_eleMachine1()
|
+ ",oil_eleMachine2 = " + oil_comm.getOil_eleMachine2()
|
+ ",oil_returnTmp = " + oil_comm.getOil_returnTmp()
|
+ ",oil_outFlow = " + oil_comm.getOil_outFlow()
|
+ ",oil_pre1 = " + oil_comm.getOil_pre1()
|
+ ",oil_pre2 = " + oil_comm.getOil_pre2()
|
+ ",oil_outpre1 = " + oil_comm.getOil_outpre1()
|
+ ",oil_outpre2 = " + oil_comm.getOil_outpre2()
|
+ ",oil_outpre3 = " + oil_comm.getOil_outpre3()
|
+ ",oil_outpre4 = " + oil_comm.getOil_outpre4()
|
+ ",oil_returnFlow = " + oil_comm.getOil_returnFlow()
|
+ ",oil_coolpre = " + oil_comm.getOil_coolpre()
|
+ ",oil_coolTmp = " + oil_comm.getOil_coolTmp()
|
+ ",oil_outTmp = " + oil_comm.getOil_outTmp()
|
+ " Where dev_id = " + oil_comm.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
//ʵʱ¸üÐÂˮվÊý¾Ý
|
public static void insertOrUpdateTb_water_comm(MysqlConnPool pool, Water_comm water_comm) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.TB_water_comm + " where dev_id = " + water_comm.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.TB_water_comm + "(dev_id) values("+water_comm.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.TB_water_comm + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',water_state = " + water_comm.getWater_state()
|
+ ",water_highTmp = " + water_comm.getWater_highTmp()
|
+ ",water_lowLevel = " + water_comm.getWater_lowLevel()
|
+ ",water_highLevel = " + water_comm.getWater_highLevel()
|
+ ",water_eleMachine1 = " + water_comm.getWater_eleMachine1()
|
+ ",water_eleMachine2 = " + water_comm.getWater_eleMachine2()
|
+ ",water_returnTmp = " + water_comm.getWater_returnTmp()
|
+ ",water_outFlow = " + water_comm.getWater_outFlow()
|
+ ",water_pre1 = " + water_comm.getWater_pre1()
|
+ ",water_pre2 = " + water_comm.getWater_pre2()
|
+ ",water_outpre1 = " + water_comm.getWater_outpre1()
|
+ ",water_outpre2 = " + water_comm.getWater_outpre2()
|
+ ",water_outpre3 = " + water_comm.getWater_outpre3()
|
+ ",water_outpre4 = " + water_comm.getWater_outpre4()
|
+ ",water_returnFlow = " + water_comm.getWater_returnFlow()
|
+ ",water_coolpre = " + water_comm.getWater_coolpre()
|
+ ",water_coolTmp = " + water_comm.getWater_coolTmp()
|
+ ",water_outTmp = " + water_comm.getWater_outTmp()
|
+ " Where dev_id = " + water_comm.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
//ʵʱ¸üÐÂupsÊý¾Ý
|
public static void insertOrUpdateTb_ups_comm(MysqlConnPool pool, Ups_comm ups_comm) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.TB_ups_comm+ " where dev_id = " + ups_comm.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.TB_ups_comm + "(dev_id) values("+ups_comm.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.TB_ups_comm + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',input_vol = " + ups_comm.getInput_vol()
|
+ ",input_rate = " + ups_comm.getInput_rate()
|
+ ",output_vol = " + ups_comm.getOutput_vol()
|
+ ",output_rate = " + ups_comm.getOutput_rate()
|
+ ",load_cap = " + ups_comm.getLoad_cap()
|
+ ",load_curr = " + ups_comm.getLoad_curr()
|
+ ",ups_inTmp = " + ups_comm.getUps_inTmp()
|
+ ",batt_vol = " + ups_comm.getBatt_vol()
|
+ ",batt_cap = " + ups_comm.getBatt_cap()
|
+ ",rest_time = " + ups_comm.getRest_time()
|
+ " Where dev_id = " + ups_comm.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
//´´½¨ÓÍÕ¾Àúʷʵʱ±í
|
public static void createTb_oil_comm_RealData(MysqlConnPool pool, int dev_id, Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_oil_comm_RealData+dev_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `oil_state` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÔËÐÐ'," +
|
" `oil_highTmp` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾¸ßÓÍα¨¾¯'," +
|
" `oil_lowLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µÍҺ뱨¾¯'," +
|
" `oil_highLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾¸ßҺ뱨¾¯'," +
|
" `oil_eleMachine1` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µç»ú1¹ÊÕÏ'," +
|
" `oil_eleMachine2` int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾µç»ú1¹ÊÕÏ'," +
|
" `oil_returnTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾»ØÓÍζÈ'," +
|
" `oil_outFlow` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬Óͳö¿ÚÁ÷Á¿'," +
|
" `oil_pre1` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍѹÁ¦1'," +
|
" `oil_pre2` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍѹÁ¦2'," +
|
" `oil_outpre1` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú1ѹÁ¦'," +
|
" `oil_outpre2` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú2ѹÁ¦'," +
|
" `oil_outpre3` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú3ѹÁ¦'," +
|
" `oil_outpre4` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³ö¿Ú4ѹÁ¦'," +
|
" `oil_returnFlow` float NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÓÍ»ØÓÍÁ÷Á¿'," +
|
" `oil_coolpre` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÀäȴˮѹÁ¦'," +
|
" `oil_coolTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾ÀäÈ´Ë®»ØˮζÈ'," +
|
" `oil_outTmp` float NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾³öÓÍζÈ'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
//´´½¨Ë®Õ¾Àúʷʵʱ±í
|
public static void createTb_water_comm_RealData(MysqlConnPool pool, int dev_id, Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_water_comm_RealData+dev_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `water_state` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվÔËÐÐ'," +
|
" `water_highTmp` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվ¸ßÓÍα¨¾¯'," +
|
" `water_lowLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµÍҺ뱨¾¯'," +
|
" `water_highLevel` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվ¸ßҺ뱨¾¯'," +
|
" `water_eleMachine1` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµç»ú1¹ÊÕÏ'," +
|
" `water_eleMachine2` int(11) NOT NULL DEFAULT '0' COMMENT 'ˮվµç»ú1¹ÊÕÏ'," +
|
" `water_returnTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվ»ØÓÍζÈ'," +
|
" `water_outFlow` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮ³ö¿ÚÁ÷Á¿'," +
|
" `water_pre1` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮѹÁ¦1'," +
|
" `water_pre2` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮѹÁ¦2'," +
|
" `water_outpre1` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú1ѹÁ¦'," +
|
" `water_outpre2` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú2ѹÁ¦'," +
|
" `water_outpre3` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú3ѹÁ¦'," +
|
" `water_outpre4` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³ö¿Ú4ѹÁ¦'," +
|
" `water_returnFlow` float NOT NULL DEFAULT '0' COMMENT 'À䶳ˮ»ØÓÍÁ÷Á¿'," +
|
" `water_coolpre` float NOT NULL DEFAULT '0' COMMENT 'ˮվÀäȴˮѹÁ¦'," +
|
" `water_coolTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվÀäÈ´Ë®»ØˮζÈ'," +
|
" `water_outTmp` float NOT NULL DEFAULT '0' COMMENT 'ˮվ³öÓÍζÈ'," +
|
" `compressor_state` float NOT NULL DEFAULT '0' COMMENT 'ѹËõ»ú״̬'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
//´´½¨upsÀúʷʵʱ±í
|
public static void createTb_ups_comm_RealData(MysqlConnPool pool, int dev_id, Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_ups_comm_RealData+dev_id+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" `input_vol` float NOT NULL DEFAULT '0' COMMENT 'ÊäÈëµçѹ'," +
|
" `input_rate` float NOT NULL DEFAULT '0' COMMENT 'ÊäÈëƵÂÊ'," +
|
" `output_vol` float NOT NULL DEFAULT '0' COMMENT 'Êä³öµçѹ'," +
|
" `output_rate` float NOT NULL DEFAULT '0' COMMENT 'Êä³öƵÂÊ'," +
|
" `load_cap` float NOT NULL DEFAULT '0' COMMENT '¸ºÔØÈÝÁ¿'," +
|
" `load_curr` float NOT NULL DEFAULT '0' COMMENT '¸ºÔصçÁ÷'," +
|
" `ups_inTmp` float NOT NULL DEFAULT '0' COMMENT 'UPSÄÚ²¿Î¶È'," +
|
" `batt_vol` float NOT NULL DEFAULT '0' COMMENT 'µç³Øµçѹ'," +
|
" `batt_cap` float NOT NULL DEFAULT '0' COMMENT 'µç³ØÈÝÁ¿'," +
|
" `rest_time` float NOT NULL DEFAULT '0' COMMENT 'Ê£Óàʱ¼ä'," +
|
" `note` varchar(255) 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(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
//ɾ³ý´´½¨Ê±¼äÔÚÖ¸¶¨Ê±¼ä֮ǰ´´½¨µÄÀúʷʵʱÊý¾Ý±í
|
public static void deleteHistoryData(MysqlConnPool pool, Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME,CREATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_3.5mw_motor_history' " +
|
" AND (TABLE_NAME like 'tb_oil_comm_%' or TABLE_NAME like 'tb_water_comm_%' or TABLE_NAME like 'tb_ups_comm_%')" +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
Sql_Mysql sql = new Sql_Mysql(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_3.5mw_motor_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 static void insertTb_oil_comm_RealData(MysqlConnPool pool, Oil_comm oil_comm, Date time) {
|
String sql_str = " insert into " + Sql_Mysql.Tb_oil_comm_RealData+oil_comm.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)
|
+"(dev_id,record_time,oil_state,oil_highTmp,oil_lowLevel,oil_highLevel,oil_eleMachine1,oil_eleMachine2,oil_returnTmp,oil_outFlow,oil_pre1,oil_pre2,oil_outpre1,oil_outpre2,oil_outpre3,oil_outpre4,oil_returnFlow,oil_coolpre,oil_coolTmp,oil_outTmp) "
|
+ " values("
|
+ ""+oil_comm.getDev_id()
|
+ ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)
|
+ "',"+oil_comm.getOil_state()
|
+ ","+oil_comm.getOil_highTmp()
|
+ ","+oil_comm.getOil_lowLevel()
|
+ ","+oil_comm.getOil_highLevel()
|
+ ","+oil_comm.getOil_eleMachine1()
|
+ ","+oil_comm.getOil_eleMachine2()
|
+ ","+oil_comm.getOil_returnTmp()
|
+ ","+oil_comm.getOil_outFlow()
|
+ ","+oil_comm.getOil_pre1()
|
+ ","+oil_comm.getOil_pre2()
|
+ ","+oil_comm.getOil_outpre1()
|
+ ","+oil_comm.getOil_outpre2()
|
+ ","+oil_comm.getOil_outpre3()
|
+ ","+oil_comm.getOil_outpre4()
|
+ ","+oil_comm.getOil_returnFlow()
|
+ ","+oil_comm.getOil_coolpre()
|
+ ","+oil_comm.getOil_coolTmp()
|
+ ","+oil_comm.getOil_outTmp()
|
+ ")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//²åÈëupsÊý¾Ý
|
public static void insertTb_ups_comm_RealData(MysqlConnPool pool,Ups_comm ups_comm, Date time) {
|
String sql_str = " insert into " + Sql_Mysql.Tb_ups_comm_RealData+ups_comm.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)
|
+"(dev_id,record_time,input_vol,input_rate,output_vol,output_rate,load_cap,load_curr,ups_inTmp,batt_vol,batt_cap,rest_time) "
|
+ " values("
|
+ ""+ups_comm.getDev_id()
|
+ ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)
|
+ "',"+ups_comm.getInput_vol()
|
+ ","+ups_comm.getInput_rate()
|
+ ","+ups_comm.getOutput_vol()
|
+ ","+ups_comm.getOutput_rate()
|
+ ","+ups_comm.getLoad_cap()
|
+ ","+ups_comm.getLoad_curr()
|
+ ","+ups_comm.getUps_inTmp()
|
+ ","+ups_comm.getBatt_vol()
|
+ ","+ups_comm.getBatt_cap()
|
+ ","+ups_comm.getRest_time()
|
+ ")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//²åÈëˮվÊý¾Ý
|
public static void insertTb_water_comm_RealData(MysqlConnPool pool, Water_comm water_comm, Date time) {
|
String sql_str = " insert into " + Sql_Mysql.Tb_water_comm_RealData+water_comm.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)
|
+"(dev_id,record_time,water_state,water_highTmp,water_lowLevel,water_highLevel,water_eleMachine1,water_eleMachine2,water_returnTmp,water_outFlow,water_pre1,water_pre2,water_outpre1,water_outpre2,water_outpre3,water_outpre4,water_returnFlow,water_coolpre,water_coolTmp,water_outTmp,compressor_state) "
|
+ " values("
|
+ ""+water_comm.getDev_id()
|
+ ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)
|
+ "',"+water_comm.getWater_state()
|
+ ","+water_comm.getWater_highTmp()
|
+ ","+water_comm.getWater_lowLevel()
|
+ ","+water_comm.getWater_highLevel()
|
+ ","+water_comm.getWater_eleMachine1()
|
+ ","+water_comm.getWater_eleMachine2()
|
+ ","+water_comm.getWater_returnTmp()
|
+ ","+water_comm.getWater_outFlow()
|
+ ","+water_comm.getWater_pre1()
|
+ ","+water_comm.getWater_pre2()
|
+ ","+water_comm.getWater_outpre1()
|
+ ","+water_comm.getWater_outpre2()
|
+ ","+water_comm.getWater_outpre3()
|
+ ","+water_comm.getWater_outpre4()
|
+ ","+water_comm.getWater_returnFlow()
|
+ ","+water_comm.getWater_coolpre()
|
+ ","+water_comm.getWater_coolTmp()
|
+ ","+water_comm.getWater_outTmp()
|
+ ","+water_comm.getCompressor_state()
|
+ ")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
}
|
