JDBC连接池

自定义JDBC连接池

可扩展增强某个类方法的功能的三种方式：

（一）创建该类的子类，并覆盖相应的方法；（较少使用）

需要将被增强父类的其他所需信息也要传递到子类中，而在开发中， 经常无法知晓这些所需信息， 所以使用子类覆盖被增强类方法的方式只是用于被增强类的内容较为简单的情景。

（二）使用装饰(包装)设计模式；（可以使用，但有时书写的方法太多）

1. 定义一个类，实现与被增强类相同的接口；
2. 在类中定义一个变量，记住被增强对象；
3. 定义一个构造函数，接收被增强对象；
4. 覆盖想增强的方法；
5. 对于不想增强的方法，直接调用目标对象（被增强对象）的方法。

（三）使用动态代理技术。（最优的方式。）

基于装饰模式自定义数据库连接池

编写连接池需实现java.sql.DataSource接口。 DataSource接口中定义了两个重载的getConnection方法：

• Connection getConnection()
• Connection getConnection(String username, String password)

实现DataSource接口，并实现连接池功能的步骤：

1. 在DataSource构造函数中批量创建与数据库的连接，并把创建的连接加入LinkedList对象中。
2. 实现getConnection方法，让getConnection方法每次调用时，从LinkedList中取一个Connection返回给用户。
3. 当用户使用完Connection，调用Connection.close()方法时，Collection对象应保证将自己返回到LinkedList中,而不要把conn还给数据库。

Collection保证将自己返回到LinkedList中是此处编程的难点。

public class JdbcPool implements DataSource {

	private static LinkedList<Connection> list = 
		new LinkedList<Connection>();
	private static Properties config = new Properties();

	static{
		try {
			config.load(
					JdbcUtils_DBCP.class.getClassLoader()
					.getResourceAsStream("db.properties"));

			// 以配置文件方式 读取数据库配置信息。 
			Class.forName(config.getProperty("driver"));
			for (int i=0;i<10;i++) {
				Connection conn = DriverManager.getConnection(
						config.getProperty("url"), 
						config.getProperty("username"), 
						config.getProperty("password"));
				list.add(conn);
			}
		} catch (Exception e) { throw new ExceptionInInitializerError(e); }
	}

	// 此方法会将连接返回给数据库，所以不可用，
	// 需要自定义增强其功能，将连接返回到List集合中。
	// conn.close()

	/* 在实际开发，发现对象的方法满足不了开发需求时，有三种方式对其进行增强
	 * 1.写一个connecton子类，覆盖close方法，增强close方法
	 * 2.用包装设计模式
	 * 3.用动态代理    aop 面向切面编程
	 */
	public Connection getConnection() throws SQLException { 
		if(list.size()<=0){
			throw new RuntimeException("数据库忙，请稍会再来！！");
		}

		Connection conn = list.removeFirst();
		MyConnection my = new MyConnection(conn);    // 调用自定义链接。
		return my;
	} 

	// 内部类，当然也可以使用外部类
	//1.定义一个类，实现与被增强相同的接口
	class MyConnection implements Connection {

		//2.在类中定义一个变量，记住被增强对象
		private Connection conn;      

		//3.定义一个构造函数，接收被增强对象
		public MyConnection(Connection conn) { this.conn = conn; }

		//4.覆盖想增强的方法
		public void close() { list.add(this.conn); } 

		//5.对于不想增强的方法，直接调用目标对象（被增强对象）的方法    
		public void clearWarnings() throws SQLException {
			this.conn.clearWarnings(); 
		}

		public void commit() throws SQLException { this.conn.commit(); }

		public Statement createStatement() throws SQLException {
			return this.conn.createStatement();
		} 

		// 以下省略其他32个 不想增强的方法。
		// （装饰模式的缺点，会实现许多不需要增强的方法）
		// ………………
	}

	public Connection getConnection(String username, String password) 
		throws SQLException 
	{ 
		return null; 
	} 

	public PrintWriter getLogWriter() throws SQLException { return null; } 

	public int getLoginTimeout() throws SQLException { return 0; } 

	public void setLogWriter(PrintWriter arg0) throws SQLException { } 

	public void setLoginTimeout(int arg0) throws SQLException { } 
}

基于动态代理技术实现数据库连接池核心代码

使用动态代理技术构建连接池中的connection

proxyConn = (Connection) Proxy.newProxyInstance(
		this.getClass().getClassLoader(),
		conn.getClass().getInterfaces(),
		new InvocationHandler() {
			// 此处为内部类，当close方法被调用时将conn还回池中,其它方法直接执行
			public Object invoke(Object proxy, Method method,Object[] args) 
				throws Throwable
			{
				if (method.getName().equals("close")) {
					pool.addLast(conn);
					return null;
				}
				return method.invoke(conn, args);
			}
		}    
);

DBCP数据源

DBCP 是 Apache 软件基金组织下的开源连接池实现，使用DBCP数据源，应用程序应在系统中增加如下两个 jar 文件：

• Commons-dbcp.jar：连接池的实现
• Commons-pool.jar：连接池实现的依赖库

Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

使用DBCP示例代码

public class JdbcUtils_DBCP { 

	private static DataSource ds = null;

	static{
		try{
			InputStream in = JdbcUtils_DBCP.class.getClassLoader()
				.getResourceAsStream("conf.prop");
			Properties prop = new Properties();
			prop.load(in);             
			BasicDataSourceFactory factory = new BasicDataSourceFactory();
			ds = factory.createDataSource(prop);
		} catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	} 

	public static void release(Connection conn,Statement st,ResultSet rs)
	{ 
		if(rs!=null){
			try{ rs.close(); }
			catch (Exception e) { e.printStackTrace(); }
			rs = null;
		}
		if(st!=null){
			try{ st.close(); }
			catch (Exception e) { e.printStackTrace(); }
			st = null;
		}
		if(conn!=null){
			try{ conn.close(); }
			catch (Exception e) { e.printStackTrace(); }
		} 
	}
}

配置文件例子：

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day16
username=root
password=root 

initialSize=10      # 初始化连接
maxActive=50		    # 最大连接数量
maxIdle=20    	    # 最大空闲连接
minIdle=5     	    # 最小空闲连接
maxWait=60000       # 超时等待时间以毫秒为单位

# 指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
# 注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8

# driver default 指定由连接池所创建的连接的只读（read-only）状态。
# 如果没有设置该值，则「setReadOnly」方法将不被调用。
# （某些驱动并不支持只读模式，如：Informix）
# defaultReadOnly=

# driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
# 可用值为下列之一：（详情可见javadoc。）
# NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

在Tomcat中使用的例子

<Context>

	<Resource name="jdbc/datasource" auth="Container"  
		type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
		username="root" password="root" 
		url="jdbc:mysql://localhost:3306/jdbc"  maxActive="8" maxIdle="4"  />

	<Resource name="jdbc/EmployeeDB " auth="Container"  
		type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
		username="root" password="root"  
		url="jdbc:mysql://localhost:3306/jdbc"  maxActive="8" maxIdle="4"  />

</Context>

封装JNDI调用DataSource 获取连接的代码。

public class JdbcUtils_Tomcat {

	private static DataSource ds;

	static {
		try {
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}
}

C3P0 数据源

所需jar包：

• c3p0-0.9.2-pre1.jar
• mchange-commons-0.2.jar
• c3p0-oracle-thin-extras-0.9.2-pre1.jar（注：连接oracle数据库时才导入，否则不用导入。）

配置文件名称：c3p0-config.xml 的内容：

<?xml version="1.0" encoding="UTF-8"?>
	<c3p0-config>

	<!-- 默认数据库连接池配置信息-->
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
		<property name="user">root</property>
		<property name="password">root</property>                 
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>    
	</default-config>

	<!--自定义数据源连接信息 -->
	<named-config name="flx">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</named-config>

</c3p0-config>

public class JdbcUtils_C3P0 { 
	private static ComboPooledDataSource ds = null;

	static{
		try{
			ds = new ComboPooledDataSource();  //没指定数据源名称，则使用默认数据源
		}catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}

	public static void release(Connection conn,Statement st,ResultSet rs){ 
		if(rs!=null){
			try{ rs.close(); }
			catch (Exception e) { e.printStackTrace(); }
			rs = null;
		}
		if(st!=null) {
			try{ st.close(); }
			catch (Exception e) { e.printStackTrace(); }
			st = null;
		}
		if(conn!=null){
			try{ conn.close(); }
			catch (Exception e) { e.printStackTrace(); }
		} 
	}
}  

hikari 连接池

配置

方法一，直接通过HikariConfig配置：

val cfg = new HikariConfig();  
cfg.setPoolName(getClass().getName());  
cfg.setDriverClassName(driverClassName);  
cfg.setJdbcUrl(url);  
cfg.setUsername(username);  
cfg.setPassword(password);  
cfg.setMaximumPoolSize(maximumPoolSize);  
cfg.setMaxLifetime(maxLifetime);  
cfg.setConnectionTimeout(connectionTimeout);  
cfg.setIdleTimeout(idleTimeout);  

val ds = new HikariDataSource(jdbcConfig)

方法二，通过properties文件生成HikariConfig：

	val dbProps = new Properties();
	dbProps.setProperty("driverClassName", "com.mysql.jdbc.Driver");
	dbProps.setProperty("jdbcUrl", "jdbc:mysql://localhost:3306/testdb" + //
			"?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8");
	dbProps.setProperty("username", "devuser");
	dbProps.setProperty("password", "devuser");
	dbProps.setProperty("autoCommit", "true");
	dbProps.setProperty("maximumPoolSize", "10");

	val cfg = new HikariConfig(dbProps);
	val ds = new HikariDataSource(cfg);

MySQL与Hikari连接池的优化

hikariCP

hikariCP参数配置

• maxLifetime 连接生命时长, 比mysql的wait_timeout短1分钟左右
• maximumPoolSize 连接池最大连接数: cpu核心数*2 + 硬盘数, 默认10 一个连接池一般来讲连接数在10-20个, 根据部署的应用程序数量可以推测出mysql的max_connections值
• readonly 是否是只读数据源, 做读写分离时有用, 保证slave数据源的安全
• connectionTimeout 等待连接池分配连接的最大时长 30s, 如果超过这个时间还没有分配到连接, commit会抛异常
• 其他参数一般用默认即可

MySQL

根据硬件配置Mysql系统参数

• wait_timeout 　　非交互连接的最大存活时间, 10-30min
• max_connections 全局最大连接数 默认100 根据情况调整
• back_log 达到max_connections后, 还能继续接受的连接请求数, linux不超过1000, 根据情况调整
• thread_concurrency MySQL处理线程数 cpu核心数*2
• innodb_buffer_pool_size innodb引擎的数据页大小, 一般是总内存的50%
• innodb_flush_log_at_try_commit 将logbuffer写入日志并flush进硬盘的时间点, 一般0(每秒)或者2(提交事务时)
• innodb_log_buffer_size log缓存大小 1~8m
• innodb_addtional_mem_pool_size 数据字典等数据结构的内存池大小 24~48m
• innodb_thread_concurrency cpu核心数*2

max_connections的值

• show status like 'max%connections'
• max_used_connections / max_connections　　过去的最大连接数 / 设置的最大连接数, 应该在10%~80%, 可据此调整max_connections

wait_timeout

• show processlist;
• sleep 查看当前连接状态, 如果sleep太多, 可以调小wait_timeout值

key_buffer_size优化

• 索引页大小, 基础设置256~512m

> show global status like 'key_read%'
key_reads / key_read_request < 1 / 100 

-- ==========================================================================
-- 使用硬盘里的索引 / 所有索引请求  如果比例过大, 说明索引缓存不够, 
-- 大量索引无法加入缓存, 只能从硬盘直接读, 这是要适当调大key_buffer_size值
-- ==========================================================================

> show global status like 'key_blocks_u%' 

key_blocks_unused  -- 未使用过的索引页内存簇
key_blocks_used　　-- 使用过的索引页内存簇　　

-- ==========================================================================
-- 如果所有索引页内存簇都被使用过, 说明索引页内存使用较多, 
-- 可以适当调大key_buffer_size
-- ==========================================================================

query_cache查询缓存

• query_cache_size 查询缓存大小 48~256m
• query_cache_type 是否用查询缓存 1
• query_cache_limit 单次查询缓存最大值 1~4m
• query_cache_min_res_unit　缓存空间最小单位 4k
• show global status like 'qcache%'
• qcache_free_blocks / qcache_total_blocks < 20% 过大说明查询的一般都是小数据量查询, 可以适当减少query_cache_min_res_unit
• qcache_free_memory / query_cache_size < 20% 过大说明大量查询缓存未用, 可以适当减少query_cache_size
• qcache_lowmem_prunes > 50说明内存碎片太多或者query_cache_size不够
• qcache_hits / qcache_hits_qcache_inserts 命中率低说明读少写多

table_cache 表缓存 256m

• show global status like 'Open%tables'
• open_table / table_cache　　　正在缓存中的表　　过大说明表缓存被利用的很充分, 可以适当增加
• opened_table / table_cache　　缓存过的表

max_heap_table_size , tmp_table_size 内存临时表大小限制, 由最小值决定

show global status like 'create_tmp%'

-- created_tmp_disk_tables / created_tmp_tables < 25%  
-- 过大说明由大量临时表被创建在硬盘中, 
-- 这时要适当增加max_heap_table_size和tmp_table_size

thread_cache_size 缓存的线程数

• show global status like 'Thread%'
• Thread_created 过大表示创建线程数太频繁, 可以增大thread_cache_size已缓存更多的线程

10.sort_buffer_size , record_rnd_buffer_size 排序缓存 , 随机读缓存

• show global status like 'sort%'
• sort_merge_passes 太大说明在硬盘中创建了很多临时文件, 可以增大sort_buffer_size 和 record_rnd_buffer_size

慢查询

> show variables like '%slow%'  
-- slow_launch_time   超过多长时间的查询视为慢查询 一般 2~5s

> show global status like '%slow%'
-- slow_queries　　慢查询次数

> show variables like '%log_query_file%'
-- slow_query_log　　是否开启了慢查询日志
-- slow_query_log_file 　慢查询日志文件

表扫描

• show global status like 'handler_read%'
• show global status like 'com_select'
• Handler_read_rnd_next / com_select > 4000 太大说明表扫描太多, 索引没建好

表锁使用

• show global status like 'table_locks%'
• Table_locks_immediate / Table_locks_waited > 5000 立即释放的锁 / 需要等待的锁, 太大的话最好用Innodb, 用行级锁替代立即释放的表锁