MySQL操作工具类（单链接不关闭版）

小咪咪 2022-05-13 23:49 118阅读 0赞

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();
            }
        }
    }