package com.sql;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
|
import org.apache.logging.log4j.LogManager;
|
import org.apache.logging.log4j.Logger;
|
|
import com.base.Com;
|
|
/**
|
* ´´½¨Êý¾Ý¿âÒÔ¼°±í¸ñ
|
* @author lxw
|
*
|
*/
|
public class Sql_Mysql
|
{
|
//--------------------------------------------------------------------------------------------//
|
final public static String DB_MW_Motor = "`db_3.5mw_motor`"; //3.5mwʵʱÊý¾Ý¿â
|
final public static String DB_MW_Motor_History = "`db_3.5mw_motor_history`"; //3.5mwÀúʷʵʱÊý¾Ý¿â
|
final public static String DB_AppSys = "`db_app_sys`";
|
//--------------------------------------------------------------------------------------------//
|
public final static String AppSys_Table = DB_AppSys + ".`tb_app_sys`";
|
public final static String Tb_MW_Motor_inf = DB_MW_Motor + ".`tb_3.5mw_motor_inf`";
|
public final static String TB_oil_comm = DB_MW_Motor + ".`tb_oil_comm`";
|
public final static String TB_water_comm = DB_MW_Motor + ".`tb_water_comm`";
|
public final static String TB_ups_comm = DB_MW_Motor + ".`tb_ups_comm`";
|
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_oil_comm_RealData = DB_MW_Motor_History + ".tb_oil_comm_";
|
public final static String Tb_water_comm_RealData = DB_MW_Motor_History + ".tb_water_comm_";
|
public final static String Tb_ups_comm_RealData = DB_MW_Motor_History + ".tb_ups_comm_";
|
|
//--------------------------------------------------------------------------------------------//
|
|
public Logger logger = null;
|
public Connection mysql_con;
|
|
public Sql_Mysql(Connection conn)
|
{
|
mysql_con = conn;
|
logger = LogManager.getLogger(this.getClass());
|
}
|
|
/*
|
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 {
|
mysql_con.close();
|
} catch (SQLException e) {
|
logger.error(e.toString(),e);
|
}
|
}
|
|
|
//ʹÓÃ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;
|
}
|
|
//Ö´ÐÐsqlÓï¾ä
|
public void sqlMysqlExecute(String sql_str) throws SQLException
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
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) {
|
// TODO Auto-generated catch block
|
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 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());
|
}
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
