Mysql连接池解决并发问题

/**

  * 连接池类

  */

package com.junones.test;

 

import java.sql.Connection;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import java.util.Map.Entry;

 

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

 

public class MySQLPool {

     private static volatile MySQLPool pool;

     private MysqlDataSource ds;

     private Map<Connection, Boolean> map;

  

     private String url = "jdbc:mysql://localhost:3306/test" ;

     private String username = "root" ;

     private String password = "root1234" ;

     private int initPoolSize = 10 ;

     private int maxPoolSize = 200 ;

     private int waitTime = 100 ;

     

     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 HashMap<Connection, Boolean>();

             for ( int i = 0 ; i < initPoolSize; i++) {

                 map.put(getNewConnection(), true );

             }

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     

     public Connection getNewConnection() {

         try {

             return ds.getConnection();

         } catch (SQLException 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 void releaseConnection(Connection conn) {

         if (conn == null ) {

             return ;

         }

         try {

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

         }

     }

}

 

/**

  * 测试类

  */

package com.junones.test;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

public class TestMySQLPool {

     private static volatile int a;

 

     private synchronized static void incr() {

         a++;

     }

 

     public static void main(String[] args) throws InterruptedException {

         int times = 10000 ;

         long start = System.currentTimeMillis();

         for ( int i = 0 ; i < times; i++) {

             new Thread( new Runnable() {

 

                 @Override

                 public void run() {

 

                     MySQLPool pool = MySQLPool.getInstance();

                     Connection conn = pool.getConnection();

                     Statement stmt = null ;

                     ResultSet rs = null ;

                     try {

                         stmt = conn.createStatement();

                         rs = stmt.executeQuery( "select id, name from t_test" );

                         while (rs.next()) {

                             System.out.println(rs.getInt( 1 ) + ", "

                                     + rs.getString( 2 ));

                         }

                     } catch (SQLException e) {

                         e.printStackTrace();

                     } finally {

                         incr();

                         if (rs != null ) {

                             try {

                                 rs.close();

                             } catch (SQLException e) {

                                 e.printStackTrace();

                             }

                         }

                         if (stmt != null ) {

                             try {

                                 stmt.close();

                             } catch (SQLException e) {

                             }

                         }

                         pool.releaseConnection(conn);

                     }

                 }

             }).start();

         }

         while ( true ) {

             if (a == times) {

                 System.out.println( "finished, time:"

                         + (System.currentTimeMillis() - start));

                 break ;

             }

             Thread.sleep( 100 );

         }

     }

}

 

测试结果： 1 万个并发， 5 秒完成。