package com.sql;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
|
import com.base.Com;
|
|
/**
|
* ´´½¨Êý¾Ý¿âÒÔ¼°±í¸ñ
|
* @author ¾ü
|
*
|
*/
|
public class Sql_Mysql
|
{
|
final public static String DB_CkpwrDev_Alarm = "`db_ckpwrdev_alarm`";
|
final public static String DB_CkpwrDev_Break_Alarm = "`db_ckpwrdev_break_alarm`";
|
final public static String DB_CkpwrDev_Data_Rt = "`db_ckpwrdev_data_rt`";
|
final public static String DB_CkpwrDev_Inf = "`db_ckpwrdev_inf`";
|
final public static String DB_User = "`db_user`";
|
|
|
/***************************** db_ckpwrdev_alarm Êý¾Ý¿â **********************************/
|
public final static String Ckpowerdev_Alarm_Table = DB_CkpwrDev_Alarm + ".tb_ckpowerdev_alarm"; //²â¿ØµçÔ´½»Ö±Á÷Åäµç¹ñʵʱ¸æ¾¯±í
|
public final static String Ckpowerdev_Alarm_Config_Table = DB_CkpwrDev_Alarm + ".tb_ckpowerdev_alarm_config"; //²â¿ØµçÔ´½»Ö±Á÷Åäµç¹ñ¸æ¾¯ÅäÖñí
|
|
/***************************** db_ckpwrdev_break_alarm Êý¾Ý¿â **********************************/
|
public final static String Ckpowerdev_Break_Alarm_Table = DB_CkpwrDev_Break_Alarm + ".tb_ckpowerdev_break_alarm"; //²â¿ØµçÔ´¶Ï·Æ÷ʵʱ¸æ¾¯±í
|
public final static String Ckpowerdev_Break_Alarm_Config_Table = DB_CkpwrDev_Break_Alarm + ".tb_ckpowerdev_break_alarm_config"; //²â¿ØµçÔ´½»Ö±Á÷Åäµç¹ñʵʱ¸æ¾¯±í
|
|
/***************************** db_ckpwrdev_data_rt Êý¾Ý¿â **********************************/
|
public final static String Ckpowerdev_Almparam_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_almparam"; //²â¿ØµçÔ´¸æ¾¯²ÎÊý±í
|
public final static String Ckpowerdev_Batt_Rsalarm_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rsalarm"; //ºËÈÝÉ豸¸æ¾¯ÊµÊ±×´Ì¬±í
|
public final static String Ckpowerdev_Batt_Rsalarm_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rsalarm_set"; //ºËÈÝÉ豸¸æ¾¯ÊµÊ±×´Ì¬ÉèÖñí
|
public final static String Ckpowerdev_Batt_Rsalarm_Set_log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rsalarm_set_log"; //ºËÈÝ×°Öø澯ÉèÖüǼ±í
|
public final static String Ckpowerdev_Batt_Rtalarm_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtalarm"; //ºËÈÝÉ豸Ðîµç³Øµ¥Ìå¸æ¾¯ÊµÊ±×´Ì¬±í
|
public final static String Ckpowerdev_Batt_Rtalarm_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtalarm_set"; //ºËÈÝÉ豸Ðîµç³Øµ¥Ìå¸æ¾¯ÊµÊ±×´Ì¬ÉèÖñí
|
public final static String Ckpowerdev_Batt_Rtdata_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtdata"; //²â¿ØµçÔ´ºËÈÝ×°Öõç³Øµ¥Ìåʵʱ״̬±í
|
public final static String Ckpowerdev_Batt_Rtdata_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtdata_set"; //²â¿ØµçÔ´ºËÈÝ×°Öõ¥ÌåÐÅÏ¢ÅäÖñí
|
public final static String Ckpowerdev_Batt_Rtstate_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtstate"; //Ðîµç³Ø×é×é¶ËÒÔ¼°Ö÷¿ØÐÅϢʵʱ״̬±í
|
public final static String Ckpowerdev_Batt_Rtstate_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_rtstate_set"; //Ðîµç³Ø×é×é¶ËÒÔ¼°Ö÷¿ØÐÅϢʵʱ״̬ÉèÖñí
|
public final static String Ckpowerdev_Batt_Set_Log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_batt_set_log"; //ºËÈÝ×°ÖÿØÖƼǼ±í
|
public final static String Ckpowerdev_Breaker_Control_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_breaker_control"; //²â¿ØµçÔ´¶Ï·Æ÷¿ØÖƱí
|
public final static String Ckpowerdev_Breaker_Control_Log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_breaker_control_log"; //²â¿ØµçÔ´-¸ßƵ¿ª¹ØµçÔ´¹ñ-¶Ï·Æ÷·ÖºÏÕ¢²Ù×÷¼Ç¼±í
|
public final static String Ckpowerdev_HrTestParam_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_hrtestparam"; //ºËÈÝ×°ÖÃÔ¶³ÌÒ£µ÷Ò£¿Ø±í
|
public final static String Ckpowerdev_ModeControl_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_modecontrol"; //²â¿ØµçÔ´Ä£¿é¿ØÖƱí
|
public final static String Ckpowerdev_ModeParam_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_modeparam"; //²â¿ØµçÔ´Ä£¿é²ÎÊýÉèÖñí
|
public final static String Ckpowerdev_Param_Log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_param_log"; //²ÎÊýãÐÖµÉèÖñí
|
public final static String Ckpowerdev_RT_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_rt"; //²â¿ØµçÔ´Ò£²âʵʱÊý¾Ý±í
|
public final static String Ckpowerdev_RT_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_rt_set"; //²â¿ØµçÔ´Ò£²âÁ¿Ä£ÄâÉèÖñí
|
public final static String Ckpowerdev_RT_Set_Log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_rt_set_log"; //²â¿ØµçÔ´Ò£²âÁ¿Ä£ÄâÉèÖüǼ±í
|
public final static String Ckpowerdev_Signal_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_signal"; //²â¿ØµçÔ´Ò£ÐÅ״̬±í
|
public final static String Ckpowerdev_Signal_Set_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_signal_set"; //²â¿ØµçÔ´Ò£ÐÅ״̬ңµ÷±í
|
public final static String Ckpowerdev_Signal_Set_Log_Table = DB_CkpwrDev_Data_Rt + ".tb_ckpowerdev_signal_set_log"; //²â¿ØµçÔ´Ò£ÐÅÁ¿Ä£ÄâÉèÖüǼ±í
|
|
|
/***************************** db_ckpwrdev_inf Êý¾Ý¿â **********************************/
|
public final static String Breaker_Inf_Table = DB_CkpwrDev_Inf + ".tb_breaker_inf"; //¶Ï·Æ÷¿ª¹ØÅäÖñí
|
public final static String Ckpwrdev_Inf_Table = DB_CkpwrDev_Inf + ".tb_ckpwrdev_inf"; //²â¿ØµçÔ´ÐÅÏ¢±í
|
public final static String Gateway_Inf_Table = DB_CkpwrDev_Inf + ".tb_gateway_inf"; //Íø¹ØÐÅÏ¢ÅäÖñí
|
|
/***************************** db_user Êý¾Ý¿â **********************************/
|
public final static String Operation_Log_Table = DB_User + ".tb_operation_log"; //Óû§²Ù×÷ÈÕÖ¾
|
public final static String User_Inf_Table = DB_User + ".tb_user_inf"; //Óû§ÐÅÏ¢±í
|
public final static String Temp_Numbers_Table = DB_User + ".temp_numbers"; //
|
|
|
//--------------------------------------------------------------------------------------------//
|
//--------------------------------------------------------------------------------------------//
|
public Connection mysql_con;
|
|
public Sql_Mysql(Connection conn)
|
{
|
mysql_con = conn;
|
}
|
|
/*
|
public static Connection getConnection() throws SQLException, java.lang.ClassNotFoundException{
|
String url = "jdbc:mysql://192.168.48.128:3306/studentinfo";
|
Class.forName("com.mysql.jdbc.Driver");
|
String userName = "root";
|
String password = "lmx8688139";
|
Connection con = DriverManager.getConnection(url,userName,password);
|
return con;
|
}
|
*/
|
|
public void close_con()
|
{
|
try {
|
if(null != mysql_con) {
|
mysql_con.close();
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public void checkAndCreateDB() throws SQLException
|
{
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_CkpwrDev_Alarm);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_CkpwrDev_Break_Alarm);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_CkpwrDev_Data_Rt);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_CkpwrDev_Inf);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_User);
|
|
}
|
|
//ʹÓÃdbÊý¾Ý¿â
|
public void sqlMysqlUseDB(String db) throws SQLException
|
{
|
sqlMysqlExecute("use " + db);
|
}
|
|
|
/**
|
* ¼ì²étb±íÊÇ·ñ´æÔÚ
|
* @param tb
|
* @return
|
* @throws SQLException
|
*/
|
public boolean sqlMysqlCheckIfTableExist(String tb) throws SQLException
|
{
|
String sql_str = "SHOW TABLES LIKE '" + tb + "'";
|
ResultSet res = sqlMysqlQuery(sql_str);
|
boolean exist = false;
|
while(res.next())
|
{
|
exist = true;
|
break;
|
}
|
|
return exist;
|
}
|
|
/**
|
* ¼ì²étb±íÊÇ·ñ´æÔÚ
|
* @param tb ±íÃû²»´øÊý¾Ý¿âÃû
|
* @return true:´æÔÚ false:²»´æÔÚ
|
* @throws SQLException
|
*/
|
public boolean sqlCheckIfTableExist(String tb)
|
{
|
String sql_str = " SELECT COUNT(*) as count FROM information_schema.TABLES " +
|
" WHERE table_name ='" + tb + "';";
|
ResultSet res = sqlMysqlQuery(sql_str);
|
boolean exist = false;
|
try {
|
if (null != res && res.next()) {
|
int count = res.getInt("count");
|
if(count > 0) {
|
exist = true;
|
}
|
}
|
} catch (Exception e) {
|
|
} finally{
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
return exist;
|
}
|
|
|
|
//Ö´ÐÐsqlÓï¾ä
|
public void sqlMysqlExecute(String sql_str) throws SQLException
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(5*60);
|
sql.execute(sql_str);
|
}
|
//Ö´ÐÐsqlÓï¾ä
|
public void sqlMysqlTotalExecute(String sql_str) throws SQLException
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(5*60);
|
sql.execute(sql_str);
|
}
|
|
|
//ÔÚÊÂÎïÖÐÖ´ÐжàÌõsqlÓï¾ä
|
public boolean makeManualCommit(ArrayList<String> al_sql_strs)
|
{
|
boolean exe_res = true;
|
try {
|
mysql_con.setAutoCommit(false);
|
|
for(int n=0; n<al_sql_strs.size(); n++) {
|
sqlMysqlExecute(al_sql_strs.get(n));
|
}
|
|
if(true == exe_res) {
|
mysql_con.commit();
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
exe_res = false;
|
} finally {
|
try {
|
if(false == exe_res) {
|
mysql_con.rollback();
|
}
|
mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
}
|
return exe_res;
|
}
|
|
//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql²éѯÓï¾ä
|
public ResultSet sqlMysqlQuery(String sql_str)
|
{
|
ResultSet res = null;
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
}
|
catch(SQLException ex)
|
{
|
System.err.println("SQLException:" + ex.getMessage());
|
}
|
|
return res;
|
}
|
|
//¸ù¾ÝsqlÓï¾äÖ´ÐдóÅúÁ¿sql²éѯÓï¾ä
|
public ResultSet sqlMysqlTotalQuery(String sql_str)
|
{
|
ResultSet res = null;
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(5*60);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
}
|
catch(SQLException ex)
|
{
|
System.err.println("SQLException:" + ex.getMessage());
|
}
|
|
return res;
|
}
|
|
//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql¸üÐÂÓï¾ä
|
public void sqlMysqlUpdate(String sql_str)
|
{
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
sql.execute(query);
|
}
|
catch(SQLException ex)
|
{
|
System.out.println("SQLException:" + ex.getMessage());
|
}
|
}
|
|
|
//»ñȡд´½¨µÄ¼Ç¼µÄtestrecordcountµÄÖµ
|
public int getBattTestRecordCountNew(int bg_id, String table)
|
{
|
int count = 0;
|
boolean res_exe = true;
|
String sql_str0 = "SELECT test_record_count_ex FROM " + table
|
+ " WHERE BattGroupId=" + bg_id + " FOR UPDATE";
|
String sql_str1 = "UPDATE " + table + " SET test_record_count_ex=test_record_count+1"
|
+ " WHERE BattGroupId=" + bg_id;
|
String sql_str2 = "SELECT MAX(test_record_count_ex) FROM " + table
|
+ " WHERE BattGroupId=" + bg_id;
|
String sql_str3 = "INSERT INTO " + table + " (BattGroupId,test_record_count, test_record_count_ex) "
|
+ " VALUES (" + bg_id + "," + 1 + "," + 1 + ")";
|
try {
|
mysql_con.setAutoCommit(false);
|
ResultSet res = sqlMysqlQuery(sql_str0);
|
if(res.next()) {
|
sqlMysqlExecute(sql_str1);
|
res = sqlMysqlQuery(sql_str2);
|
if(res.next()) {
|
count = res.getInt(1);
|
}
|
} else {
|
count = 1;
|
sqlMysqlExecute(sql_str3);
|
}
|
|
mysql_con.commit();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
res_exe = false;
|
} finally {
|
if(false == res_exe) {
|
try {
|
mysql_con.rollback();
|
mysql_con.setAutoCommit(true);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
}
|
return count;
|
}
|
}
|
