7
回答
我自己写的jdbc连接mysql,用了好久好久了,昨天重启了mysql服务器后,现在访问mysql好慢,几次都失败
华为云数据库免费试用   
 
我自己写的jdbc连接mysql,用了好久好久了,昨天重启了mysql服务器后,现在访问mysql好慢,几次都失败

之前一直都好好的,而且还是做数据量特别大,sql特别灵活的查询,啥问题都没有,现在一个简单的查询都很慢,连接创建失败,

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
 



import java.util.concurrent.ConcurrentHashMap;

import com.fz.util.Global;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
 
public class MySQLPool {
    private static volatile MySQLPool pool;
    private MysqlDataSource ds;
    private Map<Connection, Boolean> map;
    
    private static   String url = Global.getConfig("jdbc.url");
    private static	 String username = Global.getConfig("jdbc.username");
    private static  String password = Global.getConfig("jdbc.password");
  
    private int initPoolSize = 100;
    private int maxPoolSize = 1000;
    private int waitTime = 10000;
     
    private MySQLPool() {
        init();
    }
     
    public static MySQLPool getInstance() {
        if (pool == null) {
            synchronized (MySQLPool.class) {
                if(pool == null) {
                    pool = new MySQLPool();
                }
            }
        }
        return pool;
    }
     
    private void init() {
        try {
            ds = new MysqlDataSource();
            ds.setUrl(url);
            ds.setUser(username);
            ds.setPassword(password);
            ds.setCacheCallableStmts(true);
            ds.setConnectTimeout(1000);
            ds.setLoginTimeout(2000);
            ds.setUseUnicode(true);
            ds.setEncoding("UTF-8");
            ds.setZeroDateTimeBehavior("convertToNull");
            ds.setMaxReconnects(5);
            ds.setAutoReconnect(true);
            map = new ConcurrentHashMap<Connection, Boolean>(initPoolSize);

            for (int i = 0; i < initPoolSize; i++) {
                map.put(getNewConnection(), true);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     
    public Connection getNewConnection() {
        try {
            return ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
     
    public synchronized Connection getConnection() {
        Connection conn = null;
        try {
            for (Entry<Connection, Boolean> entry : map.entrySet()) {
                if (entry.getValue()) {
                    conn = entry.getKey();
                    map.put(conn, false);
                    break;
                }
            }
            if (conn == null) {
                if (map.size() < maxPoolSize) {
                    conn = getNewConnection();
                    map.put(conn, false);
                } else {
                    wait(waitTime);
                    conn = getConnection();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    
/*    public synchronized  Statement createSta(Statement stmt) throws Exception {
        Connection conn = pool.getConnection();
        try {
            if (stmt == null) {
            	stmt=conn.createStatement();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stmt;
    }*/
     
    public void releaseConnection(Connection conn  ,Statement stmt ,ResultSet rs) {
        if (conn == null) {
            return;
        }
        try {
        	if(rs!=null){
    			try {
    				rs.close();
    			} catch (Exception e2) {
    				// TODO: handle exception
    				e2.printStackTrace();
    			}
    			rs=null;
    		}
    		
    		if(stmt!=null){
    			try {
    				stmt.close();
    			} catch (Exception e2) {
    				// TODO: handle exception
    				e2.printStackTrace();
    			}
    			stmt=null;
    		}
        	
            if(map.containsKey(conn)) {
                if (conn.isClosed()) {
                    map.remove(conn);
                } else {
                    if(!conn.getAutoCommit()) {
                        conn.setAutoCommit(true);
                    }
                    map.put(conn, true);
                }
            } else {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 
/**
 * 测试类
 */



com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 5 times. Giving up.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2385)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor4.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:443)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:141)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:111)
	at cn.qjj.mysql.MySQLPool.getNewConnection(MySQLPool.java:72)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:32)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2324)
	... 17 more
java.lang.NullPointerException
	at java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
	at java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
	at cn.qjj.mysql.MySQLPool.init(MySQLPool.java:63)
	at cn.qjj.mysql.MySQLPool.<init>(MySQLPool.java:32)
	at cn.qjj.mysql.MySQLPool.getInstance(MySQLPool.java:39)
	at cn.qjj.mysql.SqlUtils.<clinit>(SqlUtils.java:32)



举报
陈all
发帖于2年前 7回/1K+阅
顶部