package com.data.comm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
|
import org.apache.logging.log4j.Logger;
|
|
import com.base.Com;
|
import com.data.Motor_inf;
|
import com.data.Rectifier_control;
|
import com.data.Rectifier_power;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Rectifier_power_SQL {
|
//³õʼ»¯Êý¾Ý¿â
|
public static void init(MysqlConnPool pool) {
|
CreateDB_MW_Motor(pool);
|
|
CreateDB_MW_Motor_History(pool);
|
|
CreateTB_rectifier_power_rt(pool);
|
|
CreateTB_rectifier_power_control(pool);
|
|
}
|
//´´½¨4MW´ó¹¦ÂÊÕûÁ÷µçÔ´¿ØÖƱí
|
private static void CreateTB_rectifier_power_control(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_RECTIFIER_POWER_CONTROL+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `remote_stop` int(11) NOT NULL DEFAULT '0' COMMENT '¹¤ÒÕ¡¢×Û¿ØÔ¶·½Í£Ö¹´ó¹¦ÂÊÕûÁ÷Æ÷'," +
|
" `emergency_stop` int(11) 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();
|
}
|
|
}
|
//´´½¨4MW´ó¹¦ÂÊÕûÁ÷µçԴʵʱÐÅÏ¢±í
|
private static void CreateTB_rectifier_power_rt(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_RECTIFIER_POWER_RT+" (" +
|
" `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 '¼Ç¼ʱ¼ä'," +
|
" `switch_on10kv` int(11) NOT NULL DEFAULT '0' COMMENT '10kv¿ª¹ØºÏÕ¢ÐźÅ'," +
|
" `switch_off10kv` int(11) NOT NULL DEFAULT '0' COMMENT '10kv¿ª¹Ø·ÖÕ¢ÐźÅ'," +
|
" `switch_allow` int(11) NOT NULL DEFAULT '0' COMMENT '10KV¸ßѹ¿ª¹ØÔÊÐíºÏÕ¢ÐźÅ'," +
|
" `rectifier_falut` int(11) NOT NULL DEFAULT '0' COMMENT 'ÕûÁ÷Æ÷¹ÊÕÏÌøÕ¢ÐźÅ'," +
|
" `rectifier_power_state` int(11) NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´ÔËÐÐ/ֹͣ״̬ÐźÅ'," +
|
" `rectifier_alarm` int(11) NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´¹ÊÕÏ״̬ÐźÅ'," +
|
" `rectifier_vol` float NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´µçѹÐźÅ'," +
|
" `rectifier_curr` float NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´µçÁ÷ÐźÅ'," +
|
" `remote_stop` int(11) NOT NULL DEFAULT '0' COMMENT '¹¤ÒÕ¡¢×Û¿ØÔ¶·½Í£Ö¹´ó¹¦ÂÊÕûÁ÷Æ÷'," +
|
" `emergency_stop` int(11) NOT NULL DEFAULT '0' COMMENT '¹¤ÒÕ¡¢×Û¿ØÔ¶·½½ô¼±Í£Ö¹´ó¹¦ÂÊÕûÁ÷Æ÷'," +
|
" `com_signal` int(11) 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();
|
}
|
}
|
//¼ÓÔØËùÓеÄ4MW´ó¹¦ÂÊÕûÁ÷µçÔ´
|
public static void loadDeviceData(MysqlConnPool pool, List motors) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Tb_MW_Motor_inf + " WHERE sys_id = 100003";
|
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();
|
}
|
}
|
/**
|
* ¹¹Ôì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();
|
}
|
}
|
//²åÈë»ò¸üÐÂ4MW´ó¹¦ÂÊÕûÁ÷µçԴʵʱÐÅÏ¢
|
public static void insertOrUpdateTb_Rectifier_power(MysqlConnPool pool, Rectifier_power rPower) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.TB_RECTIFIER_POWER_RT + " where dev_id = " + rPower.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.TB_RECTIFIER_POWER_RT + "(dev_id) values("+rPower.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.TB_RECTIFIER_POWER_RT + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',switch_on10kv = " + rPower.getSwitch_on10kv()
|
+ ",switch_off10kv = " + rPower.getSwitch_off10kv()
|
+ ",switch_allow = " + rPower.getSwitch_allow()
|
+ ",rectifier_falut = " + rPower.getRectifier_falut()
|
+ ",rectifier_power_state = " + rPower.getRectifier_power_state()
|
+ ",rectifier_alarm = " + rPower.getRectifier_alarm()
|
+ ",rectifier_vol = " + rPower.getRectifier_vol()
|
+ ",rectifier_curr = " + rPower.getRectifier_curr()
|
+ ",remote_stop = " + rPower.getRemote_stop()
|
+ ",emergency_stop = " + rPower.getEmergency_stop()
|
+ ",com_signal = " + rPower.getCom_signal()
|
+ " Where dev_id = " + rPower.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();
|
}
|
|
}
|
//²éѯ4MW´ó¹¦ÂÊÕûÁ÷µçÔ´¿ØÖƱí
|
public static void queryTb_rectifier_power_control(MysqlConnPool pool, Rectifier_control rControl) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.TB_RECTIFIER_POWER_CONTROL + " where dev_id = " + rControl.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
while (res.next()) {
|
rControl.setEmergency_stop(res.getInt("emergency_stop"));
|
rControl.setRemote_stop(res.getInt("remote_stop"));
|
}
|
} 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();
|
}
|
|
}
|
//¸üÐÂ4MWµçÔ´¿ØÖƱí
|
public static void updateTb_rectifier_power_control(MysqlConnPool pool, Rectifier_control rControl) {
|
String sql_str = " update " + Sql_Mysql.TB_RECTIFIER_POWER_CONTROL + ""
|
+ " SET emergency_stop = " + rControl.getEmergency_stop()
|
+ ",remote_stop = " + rControl.getRemote_stop()
|
+ " Where dev_id = " + rControl.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} 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();
|
}
|
}
|
//´´½¨4MW´ó¹¦ÂʵçÔ´ÀúʷʵʱÊý¾Ý±í
|
public static void createTb_rectifier_power_RealData(MysqlConnPool pool, int dev_id, Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_RECTIFIER_POWER_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 '¼Ç¼ʱ¼ä'," +
|
" `switch_on10kv` int(11) NOT NULL DEFAULT '0' COMMENT '10kv¿ª¹ØºÏÕ¢ÐźÅ'," +
|
" `switch_off10kv` int(11) NOT NULL DEFAULT '0' COMMENT '10kv¿ª¹Ø·ÖÕ¢ÐźÅ'," +
|
" `switch_allow` int(11) NOT NULL DEFAULT '0' COMMENT '10KV¸ßѹ¿ª¹ØÔÊÐíºÏÕ¢ÐźÅ'," +
|
" `rectifier_falut` int(11) NOT NULL DEFAULT '0' COMMENT 'ÕûÁ÷Æ÷¹ÊÕÏÌøÕ¢ÐźÅ'," +
|
" `rectifier_power_state` int(11) NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´ÔËÐÐ/ֹͣ״̬ÐźÅ'," +
|
" `rectifier_alarm` int(11) NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´¹ÊÕÏ״̬ÐźÅ'," +
|
" `rectifier_vol` float NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´µçѹÐźÅ'," +
|
" `rectifier_curr` float NOT NULL DEFAULT '0' COMMENT '´ó¹¦ÂÊÕûÁ÷µçÔ´µçÁ÷ÐźÅ'," +
|
" `remote_stop` int(11) NOT NULL DEFAULT '0' COMMENT '¹¤ÒÕ¡¢×Û¿ØÔ¶·½Í£Ö¹´ó¹¦ÂÊÕûÁ÷Æ÷'," +
|
" `emergency_stop` int(11) NOT NULL DEFAULT '0' COMMENT '¹¤ÒÕ¡¢×Û¿ØÔ¶·½½ô¼±Í£Ö¹´ó¹¦ÂÊÕûÁ÷Æ÷'," +
|
" `com_signal` int(11) 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 conn_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_rectifier_power_%' " +
|
" AND CREATE_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_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();
|
}
|
}
|
//ʵʱ²åÈë4mw´ó¹¦ÂʵçÔ´µÄ¼Ç¼
|
public static void insertTb_rectifier_power_RealData(MysqlConnPool pool, Rectifier_power rPower, Date time) {
|
String sql_str = " insert into " + Sql_Mysql.Tb_RECTIFIER_POWER_RealData+rPower.getDev_id()+"_"+Com.getDateTimeFormat(time, Com.DTF_Y_M_D)
|
+"(dev_id,record_time,switch_on10kv,switch_off10kv,switch_allow,rectifier_falut,rectifier_power_state,rectifier_alarm,rectifier_vol,rectifier_curr,remote_stop,emergency_stop,com_signal) "
|
+ " values("
|
+ ""+rPower.getDev_id()
|
+ ",'"+Com.getDateTimeFormat(time, Com.DTF_YMDhms)
|
+ "',"+rPower.getSwitch_on10kv()
|
+ ","+rPower.getSwitch_off10kv()
|
+ ","+rPower.getSwitch_allow()
|
+ ","+rPower.getRectifier_falut()
|
+ ","+rPower.getRectifier_power_state()
|
+ ","+rPower.getRectifier_alarm()
|
+ ","+rPower.getRectifier_vol()
|
+ ","+rPower.getRectifier_curr()
|
+ ","+rPower.getRemote_stop()
|
+ ","+rPower.getEmergency_stop()
|
+ ","+rPower.getCom_signal()
|
+ ")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
