package com.iedscout;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import com.dec.fbs9100.MysqlConnPool;
|
import com.dec.fbs9100.Sql_Mysql;
|
import com.iedscout.BTS61850_IEDScout_Task_Thread.IEDScout_Param;
|
|
public class BTS61850_IEDScout_Task_Thread_SQL {
|
|
/**
|
* 插入默认的初始数据[超过1行数据是删除其余行数据]
|
* @param pool
|
*/
|
public static void insertConnect_Inf_Table(MysqlConnPool pool) {
|
String sql_str = "SELECT count(*)as count_num FROM " + Sql_Mysql.Connect_Inf_Table;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
int count_num = res.getInt("count_num");
|
if(count_num > 1) {
|
sql.sqlMysqlExecute("DELETE FROM " + Sql_Mysql.Connect_Inf_Table);
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Connect_Inf_Table + "(num,target_ip,target_port,conn_st) VALUES(1,'127.0.0.1',102,0);");
|
}else if(count_num == 1){
|
sql.sqlMysqlExecute(" UPDATE " + Sql_Mysql.Connect_Inf_Table + " set conn_st = 0;");
|
}else {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Connect_Inf_Table + "(num,target_ip,target_port,conn_st) VALUES(1,'127.0.0.1',102,0);");
|
}
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.insertIntoIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.insertIntoIed_NodeState_Table():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 查询当前连接状态
|
* @param pool
|
* @param param
|
*/
|
public static void queryConnect_Inf_Table(MysqlConnPool pool, IEDScout_Param param) {
|
String sql_str = "SELECT * FROM " + Sql_Mysql.Connect_Inf_Table;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
param.target_ip = res.getString("target_ip"); //'目标IP地址',
|
param.target_port = res.getInt("target_port"); //'目的端口号',
|
param.target_icd = res.getString("target_icd"); //'目的设备icd文件路径',
|
param.conn_st = res.getInt("conn_st"); //'连接状态0-默认 1-开始连接[平台设置] 2-连接中 3-连接成功 4-连接失败',
|
param.fail_reasion = res.getInt("fail_reasion"); //'失败原因：0-无 1-ICD文件不存在 2-ICD文件不匹配',
|
param.comm_num = res.getLong("comm_num"); //通信计数',
|
param.error_num = res.getLong("error_num"); //'通信错误计数',
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.queryIntoIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.queryIntoIed_NodeState_Table():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/*
|
*
|
* 插入默认的数据
|
*/
|
public static void inseartIed_NodeState_Table(MysqlConnPool pool,BTS_ScoutData_Array array) {
|
final StringBuffer sql_str = new StringBuffer();
|
array.getDataMap().forEach((key, value) -> {
|
if(null == value.node_name || value.node_name.trim().length() == 0) {
|
|
}else {
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
ResultSet res = null;
|
String sql_str_sel = "SELECT * FROM " + Sql_Mysql.Ied_NodeState_Table + " WHERE node_path = '" + value.getNode_path() + "';";
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//sql.sqlMysqlExecute("UPDATE " + Sql_Mysql.Ied_NodeState_Table + " SET node_value = '" + value.node_value + "',node_name = '" + value.node_name + "' WHERE node_path = '" + value.node_path + "';");
|
}else {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Ied_NodeState_Table + "(node_path,node_name,node_value) VALUES('" + value.node_path + "','" + value.node_name + "','" + value.node_value + "');");
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateIed_NodeState_Table():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
}
|
});
|
}
|
|
/*
|
*
|
* 更新默认的数据
|
*/
|
public static void updateIed_NodeState_Table(MysqlConnPool pool,BTS_ScoutData_Array array) {
|
StringBuffer sql_str = new StringBuffer(" UPDATE " + Sql_Mysql.Ied_NodeState_Table + " SET ");
|
boolean isEx = false;
|
StringBuffer sql_str_val = new StringBuffer();
|
StringBuffer sql_str_name = new StringBuffer();
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
array.getDataMap().forEach((key, value) -> {
|
if(null != value.node_name && value.node_name.trim().length() > 0) {
|
//System.out.println("value.node_path:"+value.node_path);
|
try {
|
Thread.sleep(10);
|
//sql_str_val.append(" WHEN '" + value.node_path + "' THEN " + value.node_value);
|
sql_str_val.append(" WHEN '");
|
sql_str_val.append(value.node_path);
|
sql_str_val.append("' THEN ");
|
sql_str_val.append(value.node_value);
|
|
// sql_str_name.append(" WHEN '" + value.node_path + "' THEN '" + value.node_name + "'");
|
sql_str_name.append(" WHEN '");
|
sql_str_name.append(value.node_path);
|
sql_str_name.append("' THEN '");
|
sql_str_name.append(value.node_name + "'");
|
} catch (Exception e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateIed_NodeState_Table():" + e.toString(), e);
|
}
|
}
|
});
|
if(sql_str_val.length() > 0) {
|
isEx = true;
|
sql_str.append(" node_value = CASE node_path " + sql_str_val.toString() + " ELSE node_value END ");
|
}
|
if(sql_str_name.length() > 0) {
|
isEx = true;
|
sql_str.append(" ,node_name = CASE node_path " + sql_str_name.toString() + " ELSE node_name END ");
|
}
|
if(isEx) {
|
try {
|
//System.out.println(sql_str.toString());
|
sql.sqlMysqlExecute(sql_str.toString());
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
|
|
public static void updateupdateIed_NodeState_Table(MysqlConnPool pool,ScoutData data) {
|
StringBuffer sql_str = new StringBuffer(" UPDATE " + Sql_Mysql.Ied_NodeState_Table + " SET node_value = " + data.node_value + ",node_name = '"+ data.node_name +"' where node_path = '"+ data.node_path +"';");
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
//System.out.println(sql_str.toString());
|
sql.sqlMysqlExecute(sql_str.toString());
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateupdateIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* 清空'db_ied_scout.tb_ied_nodestate'表中的所有数据
|
* @param pool
|
*/
|
public static void clearIed_NodeState_Table(MysqlConnPool pool) {
|
String sql_str = "TRUNCATE TABLE " + Sql_Mysql.Ied_NodeState_Table;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
/**
|
* 清空'db_ied_scout.tb_ied_nodestate'表中的所有数据
|
* @param pool
|
*/
|
public static void updateConnect_Inf_Table(MysqlConnPool pool,IEDScout_Param param) {
|
String sql_str = " update " + Sql_Mysql.Connect_Inf_Table
|
+ " SET conn_st = " + param.getConn_st()
|
+ " ,fail_reasion = " + param.getFail_reasion()
|
+ " ,comm_num = " + param.getComm_num()
|
+ " ,error_num = " + param.getError_num()
|
+ " where conn_st != " + IEDScout_Param.CONN_STATE_NULL;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.updateConnect_Inf_Table():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static boolean queryIed_NodeState_TableStopConnect(MysqlConnPool pool) {
|
String sql_str = "SELECT * FROM " + Sql_Mysql.Connect_Inf_Table +" where conn_st = 0;";
|
boolean flag = false;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool);
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
flag = true;
|
}
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.insertIntoIed_NodeState_Table():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("BTS61850_IEDScout_Task_Thread_SQL.insertIntoIed_NodeState_Table():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
return flag;
|
}
|
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("192.168.10.82", 3360, 5);
|
System.out.println(queryIed_NodeState_TableStopConnect(pool));;
|
|
}
|
}
|
