package com.motor.conn;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.modbus.data.MyModbusMaster;
|
import com.modbus.historydata.Motor_HistoryData_Record_Thread_SQL;
|
import com.motor.alarm.Motor_Alarm;
|
import com.motor.data.Load_motor_state;
|
import com.motor.data.Motor_control;
|
import com.motor.data.Motor_inf;
|
import com.motor.data.Subject_motor_state;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Motor_Task_SQL {
|
|
|
public static void init(MysqlConnPool conn) {
|
|
createDB_Elemachinery(conn);
|
|
//´´½¨µç»úÀúʷʵʱÊý¾Ý¿â
|
Motor_HistoryData_Record_Thread_SQL.createDB_Motor_Realhistory(conn);
|
|
//´´½¨µç»úÐÅÏ¢±í
|
createTb_Motor_InfTable(conn);
|
|
//´´½¨¼ÓÔصç»ú״̬±í
|
createTb_Load_Motor_StateTable(conn);
|
|
//´´½¨ÊÜÊÔµç»ú״̬±í
|
createTb_Subject_Motor_StateTable(conn);
|
|
//´´½¨µç»ú¿ØÖƱí
|
createTb__Motor_ControlTable(conn);
|
|
|
}
|
|
private static void createDB_Elemachinery(MysqlConnPool conn) {
|
Sql_Mysql sql = new Sql_Mysql(conn.getConn());
|
try {
|
sql.sqlMysqlExecute(" CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Elemachinery);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨µç»úÐÅÏ¢±í
|
* @param conn_pool
|
*/
|
public static void createTb_Motor_InfTable(MysqlConnPool conn_pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Motor_inf+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" motor_id int(11) NOT NULL DEFAULT '2001' COMMENT 'µç»úid'," +
|
" motor_name varchar(255) NOT NULL DEFAULT '' COMMENT 'µç»úÃû³Æ'," +
|
" motor_type int(11) NOT NULL DEFAULT '0' COMMENT 'µç»úÀàÐÍ(±¸ÓÃ)'," +
|
" motor_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'µç»úip(ÖØÒª)'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ×Ö¶Î'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY motor_id_index (motor_id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 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();
|
}
|
}
|
|
/**
|
* ´´½¨¼ÓÔصç»ú״̬±í
|
* @param conn_pool
|
*/
|
public static void createTb_Load_Motor_StateTable(MysqlConnPool conn_pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Load_Motor_state+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ĬÈÏ'," +
|
" motor_id int(11) NOT NULL DEFAULT '1001' COMMENT 'µç»úid'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸üÐÂʱ¼ä'," +
|
" load_power float NOT NULL DEFAULT '0' COMMENT '¼ÓÔع¦Âʸø¶¨'," +
|
" load_torque float NOT NULL DEFAULT '0' COMMENT '¼ÓÔØת¾Ø¸ø¶¨'," +
|
" load_motor_speed_limit float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úתËÙÏÞÖÆ'," +
|
" load_motor_torque_limit float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úת¾ØÏÞÖÆ'," +
|
" load_step_length float NOT NULL DEFAULT '0' COMMENT '¼ÓÔز½³¤'," +
|
" load_motor_inwater_tmp float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úË®Àä½øË®·çÎÂ'," +
|
" load_motor_outwater_tmp float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úË®Àä³öË®·çÎÂ'," +
|
" load_motor_curr float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úµçÁ÷'," +
|
" load_motor_power float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»ú¹¦ÂÊ'," +
|
" load_motor_speed float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úתËÙ'," +
|
" load_motor_vol float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úµçѹ'," +
|
" load_motor_wind_tmp1 float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÈÆ×éζÈ1'," +
|
" load_motor_wind_tmp2 float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÈÆ×éζÈ2'," +
|
" load_motor_wind_tmp3 float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÈÆ×éζÈ3'," +
|
" load_motor_wind_tmp1_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ1'," +
|
" load_motor_wind_tmp2_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ2'," +
|
" load_motor_wind_tmp3_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ3'," +
|
" load_motor_bear_tmp1 float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÖá³ÐζÈ1'," +
|
" load_motor_bear_tmp2 float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÖá³ÐζÈ2'," +
|
" load_motor_out_tmp float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»ú³ö¿ÚζÈ'," +
|
" load_motor_into_tmp float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»ú½ø¿ÚζÈ'," +
|
" load_motor_torque float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úת¾Ø'," +
|
" load_motor_start float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÆô¶¯'," +
|
" load_motor_downtime float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úÍ£»ú'," +
|
" load_add float NOT NULL DEFAULT '0' COMMENT '¼ÓÔØ'," +
|
" load_reduct float NOT NULL DEFAULT '0' COMMENT '¼õÔØ'," +
|
" load_manual float NOT NULL DEFAULT '0' COMMENT 'ÊÖ¶¯¼ÓÔØ'," +
|
" load_motor_stop int(11) NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»ú½ôÍ£'," +
|
" load_motor_fan_state int(11) NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»ú·ç»ú״̬'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY motor_id_index (motor_id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
//Ìí¼Ó±¸ÓüÓÔصç»úÈÆ×éζÈ1
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_elemachinery'"
|
+ " AND table_name='tb_load_motor_state'"
|
+ " AND column_name='load_motor_wind_tmp1_bp'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Load_Motor_state
|
+ " ADD COLUMN load_motor_wind_tmp1_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ1';");
|
}
|
//Ìí¼Ó±¸ÓüÓÔصç»úÈÆ×éζÈ2
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_elemachinery'"
|
+ " AND table_name='tb_load_motor_state'"
|
+ " AND column_name='load_motor_wind_tmp2_bp'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Load_Motor_state
|
+ " ADD COLUMN load_motor_wind_tmp2_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ2';");
|
}
|
//Ìí¼Ó±¸ÓüÓÔصç»úÈÆ×éζÈ3
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_elemachinery'"
|
+ " AND table_name='tb_load_motor_state'"
|
+ " AND column_name='load_motor_wind_tmp3_bp'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Load_Motor_state
|
+ " ADD COLUMN load_motor_wind_tmp3_bp float NOT NULL DEFAULT '0' COMMENT '±¸ÓüÓÔصç»úÈÆ×éζÈ3';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ÊÜÊÔµç»ú״̬±í
|
* @param conn_pool
|
*/
|
public static void createTb_Subject_Motor_StateTable(MysqlConnPool conn_pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Subject_Motor_state+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" motor_id int(11) NOT NULL DEFAULT '2001' COMMENT 'µç»úid'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" sensor_torque float NOT NULL DEFAULT '0' COMMENT '´«¸ÐÆ÷ת¾Ø'," +
|
" sensor_power float NOT NULL DEFAULT '0' COMMENT '´«¸ÐÆ÷¹¦ÂÊ'," +
|
" fault_confirm int(11) NOT NULL DEFAULT '0' COMMENT '¹ÊÕÏÈ·ÈÏ'," +
|
" gear_box_power float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖṦÂÊ'," +
|
" advance_power float NOT NULL DEFAULT '0' COMMENT 'ÍƽøÖṦÂÊ'," +
|
" gear_box_tmp1 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ1'," +
|
" gear_box_tmp2 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ2'," +
|
" gear_box_tmp3 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ3'," +
|
" gear_box_tmp4 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ4'," +
|
" gear_box_tmp5 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ5'," +
|
" gear_box_tmp6 float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖá³ÐζÈ6'," +
|
" gas_station_start int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍÕ¾Æô¶¯'," +
|
" gas_station_stop int(11) NOT NULL DEFAULT '0' COMMENT 'ÓÍվͣ»ú'," +
|
" pow_control int(11) NOT NULL DEFAULT '0' COMMENT '¹¦ÂÊ¿ØÖÆ'," +
|
" torque_control int(11) NOT NULL DEFAULT '0' COMMENT 'ת¾Ø¿ØÖÆ'," +
|
" transformer_hightmp_alarm int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷¸ßα¨¾¯'," +
|
" transformer_hightmp_fault int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷¸ßιÊÕÏ'," +
|
" transformer_lackphase_alarm int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷ȱÏ౨¾¯'," +
|
" lubrication_pump1_start int(11) NOT NULL DEFAULT '0' COMMENT 'È󻬱Ã1ÔËÐÐ'," +
|
" lubrication_pump2_start int(11) NOT NULL DEFAULT '0' COMMENT 'È󻬱Ã2ÔËÐÐ'," +
|
" lubrication_remote_start_allow int(11) NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÔÊÐíÔ¶³Ì·ÃÎÊ'," +
|
" remote_control int(11) NOT NULL DEFAULT '0' COMMENT 'Ò£¿Ø/¾ÍµØ'," +
|
" inverter_ready int(11) NOT NULL DEFAULT '0' COMMENT '±äƵÆ÷¾ÍÐ÷'," +
|
" inverter_running int(11) NOT NULL DEFAULT '0' COMMENT '±äƵÆ÷ÔËÐÐ'," +
|
" inverter_alarm int(11) NOT NULL DEFAULT '0' COMMENT '±äƵÆ÷±¨¾¯'," +
|
" inverter_fault int(11) NOT NULL DEFAULT '0' COMMENT '±äƵÆ÷¹ÊÕÏ'," +
|
" lubrication_host_running_allow int(11) NOT NULL DEFAULT '0' COMMENT 'Èó»¬ÔÊÐíÖ÷»úÔËÐÐ'," +
|
" main_switch_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ö÷¿ª¹Ø״̬'," +
|
" subject_motor_running int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÜÊÔµç»úÔËÐÐ'," +
|
" subject_motor_alarm int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÜÊÔµç»ú±¨¾¯'," +
|
" subject_motor_fault int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÜÊÔµç»ú¹ÊÕÏ'," +
|
" subject_motor_stop int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÜÊÔµç»ú½ôÍ£'," +
|
" lubrication_falut int(11) NOT NULL DEFAULT '0' COMMENT 'È󻬹ÊÕÏ'," +
|
" lubrication_alarm int(11) NOT NULL DEFAULT '0' COMMENT 'È󻬹ÊÕÏ'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY motor_id_index (motor_id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 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();
|
}
|
}
|
|
/**
|
* ´´½¨µç»ú¿ØÖƱí
|
* @param conn_pool
|
*/
|
public static void createTb__Motor_ControlTable(MysqlConnPool conn_pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Motor_control + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" motor_id int(11) NOT NULL DEFAULT '2001' COMMENT 'µç»úid'," +
|
" load_power float NOT NULL DEFAULT '0' COMMENT '¼ÓÔع¦Âʸø¶¨'," +
|
" sensor_torque float NOT NULL DEFAULT '0' COMMENT '´«¸ÐÆ÷ת¾Ø'," +
|
" load_torque float NOT NULL DEFAULT '0' COMMENT '¼ÓÔØת¾Ø'," +
|
" load_motor_speed_limit float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úתËÙÏÞÖÆ'," +
|
" load_motor_torque_limit float NOT NULL DEFAULT '0' COMMENT '¼ÓÔصç»úת¾ØÏÞÖÆ'," +
|
" load_step_length float NOT NULL DEFAULT '0' COMMENT '¼ÓÔز½³¤'," +
|
" sensor_power float NOT NULL DEFAULT '0' COMMENT '´«¸ÐÆ÷¹¦ÂÊ'," +
|
" gear_box_power float NOT NULL DEFAULT '0' COMMENT '³ÝÂÖÏäÖṦÂÊ'," +
|
" advance_power float NOT NULL DEFAULT '0' COMMENT 'ÍƽøÖṦÂÊ'," +
|
" note varchar(255) NOT NULL DEFAULT '' COMMENT '±¸ÓÃ'," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY motor_id_index (motor_id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 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();
|
}
|
}
|
|
/**
|
* ²éѯËùÓеĵç»úÐÅÏ¢
|
* @param conn_pool
|
* @return
|
*/
|
public static List<Motor_inf> queryAllMotor_inf(MysqlConnPool conn_pool) {
|
List<Motor_inf> motors = new ArrayList<Motor_inf>();
|
String sql_str = " SELECT * from " + Sql_Mysql.Tb_Motor_inf;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
Motor_inf motor = new Motor_inf(res.getInt("motor_id"));
|
motor.setMotor_ip(res.getString("motor_ip"));
|
motor.setMotor_name(res.getString("motor_name"));
|
motor.setMotor_type(res.getInt("motor_type"));
|
|
for(int i = 0;i<234;i++) {
|
Motor_Alarm alarm = new Motor_Alarm(10001, motor.motor_id, i+1);
|
motor.alarms.add(alarm);
|
}
|
//System.out.println(motor.toString());
|
motors.add(motor);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return motors;
|
}
|
|
/**
|
* ¸üеç»ú¿ØÖƱí
|
*/
|
public static void updateMotor_Control(MysqlConnPool conn_pool,Motor_control control) {
|
String sql_str = "UPDATE " + Sql_Mysql.Tb_Motor_control +
|
" SET load_power = " + control.load_power + "," +
|
" sensor_torque = " + control.sensor_torque + "," +
|
" load_torque = " + control.load_torque + "," +
|
" load_motor_speed_limit=" + control.load_motor_speed_limit + "," +
|
" load_motor_torque_limit = " + control.load_motor_torque_limit + "," +
|
" load_step_length = " + control.load_step_length + "," +
|
" sensor_power = " + control.sensor_power + "," +
|
" gear_box_power = " + control.gear_box_power + ","+
|
" advance_power = " + control.advance_power +
|
" WHERE motor_id = "+control.motor_id;
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üмÓÔصç»ú״̬
|
*/
|
public static void updateLoadMotor_State(MysqlConnPool conn_pool,Load_motor_state state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Tb_Load_Motor_state +
|
" SET record_time = '" + Com.getDateTimeFormat(state.record_time, Com.DTF_YMDhms) + "'," +
|
" load_power = "+state.load_power+"," +
|
" load_torque = "+state.load_torque+"," +
|
" load_motor_speed_limit = "+state.load_motor_speed_limit+"," +
|
" load_motor_torque_limit = "+state.load_motor_torque_limit+" ," +
|
" load_step_length = "+state.load_step_length+"," +
|
" load_motor_inwater_tmp = "+state.load_motor_inwater_tmp+"," +
|
" load_motor_outwater_tmp = " +state.load_motor_outwater_tmp+"," +
|
" load_motor_curr = " + state.load_motor_curr +"," +
|
" load_motor_power = "+state.load_motor_power+"," +
|
" load_motor_speed = "+state.load_motor_speed+"," +
|
" load_motor_vol = "+state.load_motor_vol+", " +
|
" load_motor_wind_tmp1 = "+state.load_motor_wind_tmp1+"," +
|
" load_motor_wind_tmp2 = "+state.load_motor_wind_tmp2+"," +
|
" load_motor_wind_tmp3 = "+state.load_motor_wind_tmp3+"," +
|
" load_motor_wind_tmp1_bp = "+state.load_motor_wind_tmp1_bp+"," +
|
" load_motor_wind_tmp2_bp = "+state.load_motor_wind_tmp2_bp+"," +
|
" load_motor_wind_tmp3_bp = "+state.load_motor_wind_tmp3_bp+"," +
|
" load_motor_bear_tmp1 = "+state.load_motor_bear_tmp1+"," +
|
" load_motor_bear_tmp2 = "+state.load_motor_bear_tmp2+"," +
|
" load_motor_out_tmp = "+state.load_motor_out_tmp+"," +
|
" load_motor_into_tmp = "+state.load_motor_into_tmp+"," +
|
" load_motor_torque = "+state.load_motor_torque+"," +
|
" load_motor_start = "+state.load_motor_start+"," +
|
" load_motor_downtime = "+state.load_motor_downtime+"," +
|
" load_add = "+state.load_add+"," +
|
" load_reduct = "+state.load_reduct+"," +
|
" load_manual = "+state.load_manual+"," +
|
" load_motor_stop = "+state.load_motor_stop+"," +
|
" load_motor_fan_state = "+state.load_motor_fan_state +
|
" WHERE motor_id = "+state.motor_id;
|
|
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 updateSubjectMotor_State(MysqlConnPool conn_pool,Subject_motor_state state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Tb_Subject_Motor_state +
|
" SET record_time = '" + Com.getDateTimeFormat(state.record_time, Com.DTF_YMDhms) + "'," +
|
"sensor_torque = " + state.sensor_torque + "," +
|
"sensor_power = " + state.sensor_power + "," +
|
"fault_confirm = " + state.fault_confirm + "," +
|
"gear_box_power = " + state.gear_box_power + " ," +
|
"advance_power = " + state.advance_power + "," +
|
"gear_box_tmp1 = " + state.gear_box_tmp1 + "," +
|
"gear_box_tmp2 = " + state.gear_box_tmp2 + "," +
|
"gear_box_tmp3 = " + state.gear_box_tmp3 + "," +
|
"gear_box_tmp4 = " + state.gear_box_tmp4 + "," +
|
"gear_box_tmp5 = " + state.gear_box_tmp5 + "," +
|
"gear_box_tmp6 = " + state.gear_box_tmp6 + "," +
|
"gas_station_start = " + state.gas_station_start + "," +
|
"gas_station_stop = " + state.gas_station_stop + "," +
|
"pow_control = " + state.pow_control + "," +
|
"torque_control = " + state.torque_control + "," +
|
"transformer_hightmp_alarm = " + state.transformer_hightmp_alarm + "," +
|
"transformer_hightmp_fault = " + state.transformer_hightmp_fault +"," +
|
"transformer_lackphase_alarm = " + state.transformer_lackphase_alarm + "," +
|
"lubrication_pump1_start = " + state.lubrication_pump1_start + "," +
|
"lubrication_pump2_start = " + state.lubrication_pump2_start + "," +
|
"lubrication_remote_start_allow = " + state.lubrication_remote_start_allow + "," +
|
"remote_control = " + state.remote_control + "," +
|
"inverter_ready = " + state.inverter_ready + "," +
|
"inverter_running = " + state.inverter_running +"," +
|
"inverter_alarm = " + state.inverter_alarm + "," +
|
"inverter_fault = " + state.inverter_fault + "," +
|
"lubrication_host_running_allow = " + state.lubrication_host_running_allow + "," +
|
"main_switch_state = " + state.main_switch_state + "," +
|
"subject_motor_running = " + state.subject_motor_running + "," +
|
"subject_motor_alarm = " + state.subject_motor_alarm + "," +
|
"subject_motor_fault = " + state.subject_motor_fault + "," +
|
"subject_motor_stop = " + state.subject_motor_stop +"," +
|
"lubrication_falut = " + state.lubrication_falut +"," +
|
"lubrication_alarm = " + state.lubrication_alarm +
|
" WHERE motor_id = "+state.motor_id;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
//Date t1 = new Date();
|
sql.sqlMysqlExecute(sql_str);
|
//System.err.println((new Date().getTime()-t1.getTime()));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üеç»ú¿ØÖƱí
|
* @param conn_pool
|
* @param control
|
*/
|
public static void insertOrUpdateMotor_ControlTable(MysqlConnPool conn_pool,Motor_control control) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_Motor_control + " where motor_id = " + control.motor_id;
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_Motor_control + "(motor_id,load_power,sensor_torque,load_torque,load_motor_speed_limit,load_motor_torque_limit,load_step_length,sensor_power,gear_box_power,advance_power) "
|
+" VALUES("+control.motor_id+","+control.load_power+","+control.sensor_torque+","+control.load_torque+","+control.load_motor_speed_limit+","+control.load_motor_torque_limit+","+control.load_step_length+","+control.sensor_power+","+control.gear_box_power+","+control.advance_power+");";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//¸üе±Ç°¿ØÖÆ״̬
|
updateMotor_Control(conn_pool, control);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üмÓÔصç»ú״̬ÐÅÏ¢
|
* @param conn_pool
|
* @param state
|
*/
|
public static void insertOrUpdateLoadMotor_StateTable(MysqlConnPool conn_pool,Load_motor_state state) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_Load_Motor_state + " where motor_id = " + state.motor_id;
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Tb_Load_Motor_state + "(motor_id) values("+state.motor_id+")";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//¸üеç»úÐÅÏ¢
|
updateLoadMotor_State(conn_pool, state);
|
}else {
|
//²åÈëµç»úÐÅÏ¢
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üмÓÔصç»ú״̬ÐÅÏ¢
|
* @param conn_pool
|
* @param state
|
*/
|
public static void insertOrUpdateSubjectMotor_StateTable(MysqlConnPool conn_pool,Subject_motor_state state) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_Subject_Motor_state + " where motor_id = " + state.motor_id;
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Tb_Subject_Motor_state + "(motor_id) values("+state.motor_id+")";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//¸üеç»úÐÅÏ¢
|
updateSubjectMotor_State(conn_pool, state);
|
}else {
|
//²åÈëµç»úÐÅÏ¢
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯµ±Ç°É豸µÄ¿ØÖÆÇé¿ö
|
* @param conn_pool
|
* @param control
|
*/
|
public static void queryMotor_ControlTable(MysqlConnPool conn_pool,Motor_control control) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_Motor_control +" Where motor_id = " + control.motor_id;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
control.setLoad_power(res.getFloat("load_power"));
|
control.setSensor_torque(res.getFloat("sensor_torque"));
|
control.setLoad_torque(res.getFloat("load_torque"));
|
control.setLoad_motor_speed_limit(res.getFloat("load_motor_speed_limit"));
|
control.setLoad_motor_torque_limit(res.getFloat("load_motor_torque_limit"));
|
control.setLoad_step_length(res.getFloat("load_step_length"));
|
control.setSensor_power(res.getFloat("sensor_power"));
|
control.setGear_box_power(res.getFloat("gear_box_power"));
|
control.setAdvance_power(res.getFloat("advance_power"));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üе±Ç°É豸µÄÁ¬½Ó״̬
|
* @param pool
|
* @param dev_id
|
* @param master
|
*/
|
public static void insertOrUpdateDeviceConnectState(MysqlConnPool pool,int dev_id,MyModbusMaster master) {
|
String sql_str_sel = " SELECT * FROM "+Sql_Mysql.Tb_Device_ConnectState+" WHERE dev_id = " + dev_id;
|
String sql_str_ins = " INSERT INTO "+Sql_Mysql.Tb_Device_ConnectState+"(dev_id,connect_en,record_time) values("+dev_id+","+master.getConnectState()+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');";
|
String sql_str_upd = " UPDATE "+Sql_Mysql.Tb_Device_ConnectState+" SET connect_en = "+master.getConnectState()+",record_time='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = " + dev_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 (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
}
|
}
|
