java DBCP 连接数测试

    本文实例测试数据库连接池和普通连接。

    连接池中通过setMaxTotal()设置最大连接数目。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp2.BasicDataSource;

public class ConnectionJDBC  extends Thread{
	/**
	 * @param args
	 */
	//驱动程序就是之前在classpath中配置的JDBC的驱动程序的JAR 包中
	public static final String DBDRIVER = "com.mysql.jdbc.Driver";
	//连接地址是由各个数据库生产商单独提供的，所以需要单独记住
	public static final String DBURL = "jdbc:mysql://192.168.0.4:3306/myDB";
	//连接数据库的用户名
	public static final String DBUSER = "root";
	//连接数据库的密码
	public static final String DBPASS = "0000";	
	public static BasicDataSource ds = null;
	
	public static void dbpoolInit()
	{
		ds = new BasicDataSource();
		ds.setUrl(DBURL);
		ds.setDriverClassName(DBDRIVER);
		ds.setUsername(DBUSER);
		ds.setPassword(DBPASS);
		ds.setMaxTotal(2);
	}
	//连接池
	public static void dbpoolTest()
	{
		Connection con = null; 
		Statement stmt = null;
		ResultSet rs = null;
		try {			
			//获取数据库连接
			con = ds.getConnection(); 
			//创建Statement 
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from person");
			while(rs.next())
			{
				System.out.println(rs.getString("address"));
			}
							
		}catch(SQLException e) {
			//异常处理
			e.printStackTrace();
		}
		finally {
			//将数据库连接归还给连接池
			 try {
				 if(con !=null)  con.close();
				 if(stmt!=null)  stmt.close();
				if(rs!=null)  rs.close();  
			} catch (SQLException e) {
				e.printStackTrace();
			} 		
		}	
	}
	//普通连接
	public void jdbcTest() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName(DBDRIVER);
            conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from person");
            while (rs.next()) {
                System.out.println(rs.getString("address"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }catch (Exception e1) {
        }
        finally {
            try {
                if (conn != null) conn.close();
                if (stmt != null) stmt.close();
                if (rs != null)   rs.close();

            } catch (SQLException e) {
            	e.printStackTrace();
            }
        }
    }

	 //重写父类的run()方法。
    public void run() {
        long start = System.currentTimeMillis();
        while (System.currentTimeMillis() - start < 10000) {
        	jdbcTest();
        	//dbpoolTest();
        }
    }

	public static void main(String[] args) throws Exception {
		//初始化连接池
		 dbpoolInit();
		 //开启10个线程Thread,调用start方法，线程启动，隐含的调用run()方法。
		 for(int i=0;i<10;i++)
		 {
			 new ConnectionJDBC().start();
		 }
	}
}

    使用普通方式jdbcTest(),有10个线程。

mysql> show processlist;
+-----+----------------------+-------------------+------+---------+------+-------+------------------+
| Id  | User                 | Host              | db   | Command | Time | State | Info             |
+-----+----------------------+-------------------+------+---------+------+-------+------------------+
|   4 | root                 | localhost         | myDB | Query   |    0 | NULL  | show processlist |
| 160 | unauthenticated user | 192.168.0.6:63327 | NULL | Connect | NULL | login | NULL             |
| 161 | unauthenticated user | 192.168.0.6:63328 | NULL | Connect | NULL | login | NULL             |
| 162 | unauthenticated user | 192.168.0.6:63330 | NULL | Connect | NULL | login | NULL             |
| 163 | unauthenticated user | 192.168.0.6:63331 | NULL | Connect | NULL | login | NULL             |
| 164 | unauthenticated user | 192.168.0.6:63332 | NULL | Connect | NULL | login | NULL             |
| 165 | unauthenticated user | 192.168.0.6:63333 | NULL | Connect | NULL | login | NULL             |
| 166 | unauthenticated user | 192.168.0.6:63334 | NULL | Connect | NULL | login | NULL             |
| 167 | unauthenticated user | 192.168.0.6:63335 | NULL | Connect | NULL | login | NULL             |
| 168 | unauthenticated user | 192.168.0.6:63336 | NULL | Connect | NULL | login | NULL             |
| 169 | unauthenticated user | 192.168.0.6:63337 | NULL | Connect | NULL | login | NULL             |
+-----+----------------------+-------------------+------+---------+------+-------+------------------+
11 rows in set (0.00 sec)

    使用连接池dbPoolTest(),有2个线程 

mysql> show processlist;
+----+------+-------------------+------+---------+------+--------------+----------------------+
| Id | User | Host              | db   | Command | Time | State        | Info                 |
+----+------+-------------------+------+---------+------+--------------+----------------------+
|  4 | root | localhost         | myDB | Query   |    0 | NULL         | show processlist     |
| 30 | root | 192.168.0.6:61665 | myDB | Query   |    0 | Sending data | select * from person |
| 31 | root | 192.168.0.6:61666 | myDB | Sleep   |    0 |              | NULL                 |
+----+------+-------------------+------+---------+------+--------------+----------------------+
3 rows in set (0.00 sec)