package MyUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;
public class DbUtils {
private static Logger logger=Logger.getLogger(DbUtils.class);
private static java.sql.Connection conn= null;
private static java.sql.PreparedStatement ps;
private static ResultSet rs;
static int cnncount=0;
static String addr = ReadConf.mysql_addr;
static String port = ReadConf.mysql_port;
static String user = ReadConf.mysql_user;
static String pass = ReadConf.mysql_pass;
static String data = ReadConf.mysql_data;
static String Url = "jdbc:mysql://"+addr+":"+port+"/"+data+"?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=true";
static String User = user;
static String Passwd = pass;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(Url, User, Passwd);
} catch (Exception e) {
e.printStackTrace();
}
}
public static boolean isconnect(){
try {
return conn.isClosed();
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
private static Connection getConnection() {
try {
DBclose();
logger.warn("获取新的连接:"+cnncount++);
conn=DriverManager.getConnection(Url, User, Passwd);
return conn;
} catch (SQLException e) {
e.printStackTrace();
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static ResultSet excuteQuery(String sql){
if (conn==null) {
logger.warn("conn==null");
getConnection();
}
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
}catch (CommunicationsException e) {
logger.warn("数据库连接错误:"+e.getMessage());
logger.warn("sql:"+sql);
if (getConnection()!=null) {
excuteQuery(sql);
}
}catch (Exception e) {
logger.warn("错误:"+sql);
logger.warn("错误:"+e);
}
return rs;
}
public static int executeUpdate(String sql) {
int result = -1;
try {
ps = conn.prepareStatement(sql);
result = ps.executeUpdate();
} catch (CommunicationsException e) {
logger.warn("数据库连接错误:"+e.getMessage());
logger.warn("sql:"+sql);
if (getConnection()!=null) {
executeUpdate(sql);}
} catch (Exception e) {
logger.warn("sql错误:"+sql);
logger.warn("sql错误:"+e);
}finally {
String bf_mysql = ReadConf.bf_mysql;
if (bf_mysql.equals("1")) {
DbUtils_beifen.executeUpdate(sql);
}
}
return result;
}
public static ResultSet excuteQuery(String sql,Object[] obj){
if (conn == null) {
getConnection();
}
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i <obj.length ; i++) {
ps.setObject(i+1,obj[i]);
}
rs = ps.executeQuery();
}catch (CommunicationsException e) {
e.printStackTrace();
getConnection();
excuteQuery( sql, obj);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static int excuteUpdate(String sql,Object[] obj){
int result = -1;
if (conn == null) {
getConnection();
}
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i <obj.length ; i++) {
ps.setObject(i+1,obj[i]);
}
result = ps.executeUpdate();
} catch (CommunicationsException e) {
e.printStackTrace();
excuteUpdate(sql,obj);
getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 取出ResultSet的数据
*
* */
public static List<Map<String, String>> convertList(ResultSet rs) throws SQLException{
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取行的数量
while (rs.next()) {
Map<String, String> rowData = new HashMap<String, String>();//声明Map
for (int i = 1; i <= columnCount; i++) {
try {
rowData.put(md.getColumnName(i), rs.getObject(i).toString());//获取键名及值
} catch (Exception e) {
continue;
}
}
list.add(rowData);
}
return list;
}
private static void DBclose(){
logger.warn("关闭连接");
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}