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
|
{
|
/**********************************************************************/
|
public static final String DB_ALARM = "db_alarm";
|
public static final String DB_BATT = "db_batt";
|
public static final String DB_DIS_BATT = "db_dis_batt";
|
public static final String DB_POWER_RT = "db_power_rt";
|
public static final String DB_REAL_BATT = "db_real_batt";
|
public static final String DB_PARAM = "db_param";
|
public static final String DB_POWER_ALARM = "db_power_alarm";
|
|
public static final String DB_LITHIUM_RAM_DB = "db_lithium_ram_db";
|
public static final String DB_LITHIUM_TESTDATA = "db_lithium_testdata";
|
public static final String DB_WORK = "db_work";
|
public static final String DB_USER = "db_user";
|
|
|
/**********************************************************************/
|
public static final String Mon_Inf_Table = DB_BATT + ".mon_inf";
|
public static final String Mon_Plan_Table = DB_BATT + ".mon_plan";
|
public static final String Power_Inf_Table = DB_BATT + ".power_inf";
|
public static final String Sinf_Pinf_Table = DB_BATT + ".sinf_pinf";
|
public static final String Station_Inf_Table = DB_BATT + ".station_inf";
|
|
|
/****************************** db_power_rt ****************************************/
|
public static final String Power_Real_Rt1_Table = DB_POWER_RT + ".`tb_power_real_rt1`";
|
public static final String Power_Real_Rt2_Table = DB_POWER_RT + ".`tb_power_real_rt2`";
|
public static final String Power_Real_Rt3_Table = DB_POWER_RT + ".`tb_power_real_rt3`";
|
|
/******************************* db_dis_batt ***************************************/
|
public static final String Batt_Test_Inf_Table = DB_DIS_BATT + ".batt_test_inf"; //Ðîµç³ØÀúÊ·Êý¾Ý
|
|
|
/******************************* db_real_batt ***************************************/
|
public static final String Rt_Data_Table = DB_REAL_BATT + ".rt_data"; //Ðîµç³Øµ¥Ìåʵʱ
|
public static final String Rt_State_Table = DB_REAL_BATT + ".rt_state"; //Ðîµç³Øµ¥Ìåʵʱ
|
|
|
/******************************* db_param ***************************************/
|
public static final String BattAlarm_Param_Table = DB_PARAM + ".battalarm_param"; //µç³Ø¸æ¾¯²ÎÊý
|
public static final String PowerAlarm_param_Table = DB_PARAM + ".poweralarm_param"; //µçÔ´¸æ¾¯²ÎÊý
|
|
/******************************* db_power_alarm ***************************************/
|
public static final String Power_Alarm_Table = DB_POWER_ALARM + ".power_alarm"; //µçԴʵʱ¸æ¾¯
|
public static final String Power_Alarm_Cfg_Table = DB_POWER_ALARM + ".power_alarm_cfg"; //µçÔ´¸æ¾¯ÅäÖñí
|
|
/**********************************************************************/
|
public static final String Batt_Alarm_Table = DB_ALARM + ".batt_alarm"; //µçԴʵʱ¸æ¾¯·Ö±í
|
|
/**********************************************************************/
|
/**********************************************************************/
|
public static final String A200_RealState_Table = DB_LITHIUM_RAM_DB + ".tb_a200_realstate"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Dev_A200_TestParam_Table = DB_LITHIUM_RAM_DB + ".tb_dev_a200_testparam"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Dev_Inf_Table = DB_LITHIUM_RAM_DB + ".tb_dev_inf"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Lithium_Rtdata_Table = DB_LITHIUM_RAM_DB + ".tb_lithium_rtdata"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Workstate_Cfg_Table = DB_LITHIUM_RAM_DB + ".tb_workstate_cfg"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Workstate_ChangeEvent_Table = DB_LITHIUM_RAM_DB + ".tb_workstate_changeevent"; //µçԴʵʱ¸æ¾¯·Ö±í
|
|
|
/**********************************************************************/
|
public static final String BattTestdata_Inf_Table = DB_LITHIUM_TESTDATA + ".tb_batttestdata_inf"; //µçԴʵʱ¸æ¾¯·Ö±í
|
|
|
/**********************************************************************/
|
public static final String Operation_Log_Table = DB_USER + ".operation_log"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Temp_Numbers_Table = DB_USER + ".temp_numbers"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String User_Inf_Table = DB_USER + ".user_inf"; //µçԴʵʱ¸æ¾¯·Ö±í
|
/**********************************************************************/
|
public static final String Worksheet_Link_Table = DB_WORK + ".worksheet_link"; //µçԴʵʱ¸æ¾¯·Ö±í
|
public static final String Worksheet_Main_Table = DB_WORK + ".worksheet_main"; //µçԴʵʱ¸æ¾¯·Ö±í
|
/**********************************************************************/
|
//--------------------------------------------------------------------------------------------//
|
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();
|
}
|
}
|
|
/**
|
* ³õʼ»¯´´½¨Êý¾Ý¿â
|
* @throws SQLException
|
*/
|
public void checkAndCreateDB() throws SQLException
|
{
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_ALARM + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_BATT + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_DIS_BATT + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_POWER_RT + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_REAL_BATT + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_PARAM + " AUTHORIZATION sysdba");
|
sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + DB_POWER_ALARM + " AUTHORIZATION sysdba");
|
}
|
|
|
//ʹÓÃ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(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;
|
}
|
}
|
