需求:jdbc注册驱动创建驱动需要消耗大量资源,创建一个连接池,里面的连接循环调用
DataSource用来取代DriverManager来获取Connection;
通过DataSource获得Connection速度很快;
通过DataSource获得的Connection都是已经被包裹过的(不是驱动原来的连接),他的close方法已经被修改。
一般DataSource内部会用一个连接池来缓存Connection,这样可以大幅度提高数据库的访问速度;
连接池可以理解成一个能够存放Connection的Collection;
我们的程序只和DataSource打交道,不会直接访问连接池;
以前版本:
package com.us.daoimpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class jdbcUtils {
private static String url = "jdbc:mysql://192.168.11.138:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
private static String user = "root";
private static String password = "root";
private jdbcUtils() {
}
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs,Statement st,Connection conn) {
//6.释放资源
try {
if (rs !=null)
st.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (st!=null)
st.close();
}catch (Exception e) {
e.printStackTrace();
}finally {
if (conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
连接池:
package datasource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
/*
* 在集合中添加数据库链接,需要的时候从集合中取出来。
* arreylist 和linkedlist 的区别
* linkedlist 增加和删除的性能比较好
* */
public class MydataSource {
private static String url = "jdbc:mysql://192.168.11.138:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
private static String user = "root";
private static String password = "root";
private LinkedList<Connection> connectionsPool = new LinkedList<Connection>();
public MydataSource(){
try {
for (int i=0;i<10;i++) {
this.connectionsPool.addLast(this.CreateConnection());
}
} catch (SQLException e) {
// TODO: handle exception
throw new ExceptionInInitializerError(e);
}
}
private Connection CreateConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public Connection GetConnection() {
return this.connectionsPool.removeFirst();
}
public void free(Connection conn) {
this.connectionsPool.addLast(conn);
}
}
package datasource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class jdbcUtils {
/*private static String url = "jdbc:mysql://192.168.11.138:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
private static String user = "root";
private static String password = "root";*/
private static MydataSource mydataSource=null;
private jdbcUtils() {
}
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
mydataSource = new MydataSource();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return mydataSource.GetConnection();
}
public static void free(ResultSet rs,Statement st,Connection conn) {
//6.释放资源
try {
if (rs !=null)
st.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (st!=null)
st.close();
}catch (Exception e) {
e.printStackTrace();
}finally {
if (conn!=null)
try {
//conn.close();
mydataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
测试:
package com.us.test;
import java.sql.Connection;
import java.sql.SQLException;
import datasource.jdbcUtils;
public class pooltest {
public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
for (int i=0;i<15;i++) {
Connection conn = jdbcUtils.getConnection();
System.out.println(conn);
jdbcUtils.free(null, null, conn);
}
}
}
输出:
com.mysql.cj.jdbc.ConnectionImpl@5d3411d
com.mysql.cj.jdbc.ConnectionImpl@2471cca7
com.mysql.cj.jdbc.ConnectionImpl@5fe5c6f
com.mysql.cj.jdbc.ConnectionImpl@6979e8cb
com.mysql.cj.jdbc.ConnectionImpl@763d9750
com.mysql.cj.jdbc.ConnectionImpl@5c0369c4
com.mysql.cj.jdbc.ConnectionImpl@2be94b0f
com.mysql.cj.jdbc.ConnectionImpl@d70c109
com.mysql.cj.jdbc.ConnectionImpl@17ed40e0
com.mysql.cj.jdbc.ConnectionImpl@50675690
com.mysql.cj.jdbc.ConnectionImpl@5d3411d
com.mysql.cj.jdbc.ConnectionImpl@2471cca7
com.mysql.cj.jdbc.ConnectionImpl@5fe5c6f
com.mysql.cj.jdbc.ConnectionImpl@6979e8cb
com.mysql.cj.jdbc.ConnectionImpl@763d9750
有重复实现了循环使用。
升级版:通过代理模式实现用户通过常规模式关闭连接。
package datasource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
/*
* 在集合中添加数据库链接,需要的时候从集合中取出来。
* arreylist 和linkedlist 的区别
* linkedlist 增加和删除的性能比较好
* */
public class MydataSource2 {
private static String url = "jdbc:mysql://192.168.11.138:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
private static String user = "root";
private static String password = "root";
private static int initCount = 3;
private static int maxCount =5;
int currentCount =0;
LinkedList<Connection> connectionsPool = new LinkedList<Connection>();
public MydataSource2(){
try {
for (int i=0;i<initCount;i++) {
this.connectionsPool.addLast(this.CreateConnection());
this.currentCount++;
}
} catch (SQLException e) {
// TODO: handle exception
throw new ExceptionInInitializerError(e);
}
}
private MyConnection CreateConnection() throws SQLException {
Connection realConn = DriverManager.getConnection(url, user, password);
MyConnection myConnection = new MyConnection(realConn, this);
return myConnection;
}
public Connection GetConnection() throws SQLException {
//多线程来取连接时保证都能取到链接,并且不是同一个,加上线程锁
synchronized (connectionsPool) {
if (this.connectionsPool.size()>0) {
return this.connectionsPool.removeFirst();
}
if (this.currentCount<maxCount) {
this.currentCount++;
return this.CreateConnection();
}else {
throw new SQLException("已经没有连接");
}
}
}
public void free(Connection conn) {
if (conn instanceof MyConnection) {
this.connectionsPool.addLast(conn);
}
}
}
package datasource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class jdbcUtils {
/*private static String url = "jdbc:mysql://192.168.11.138:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
private static String user = "root";
private static String password = "root";*/
private static MydataSource2 mydataSource=null;
private jdbcUtils() {
}
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
mydataSource = new MydataSource2();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return mydataSource.GetConnection();
}
public static void free(ResultSet rs,Statement st,Connection conn) {
//6.释放资源
try {
if (rs !=null)
st.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (st!=null)
st.close();
}catch (Exception e) {
e.printStackTrace();
}finally {
if (conn!=null)
try {
conn.close();
//mydataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
package datasource;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
public class MyConnection implements Connection {
private Connection realConnetion;
private MydataSource2 dataSource;
//限制使用此时
private int maxUseCount = 10;
private int currentUseCount =0;
MyConnection(Connection connetion,MydataSource2 dataSource) {
// TODO Auto-generated constructor stub
this.realConnetion = connetion;
this.dataSource = dataSource;
}
//--------------------------重写-------------------------
@Override
public void close() throws SQLException {
currentUseCount++;
if(this.currentUseCount<this.maxUseCount) {
this.dataSource.connectionsPool.addLast(this);
}else {
this.realConnetion.close();
this.dataSource.currentCount--;
}
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return this.realConnetion.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return this.realConnetion.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return this.realConnetion.createStatement();
}
}
除了重写close方法也可以使用动态代理
package datasource;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
class MyconnectionHandler implements InvocationHandler{
private Connection realConn;
private MydataSource2 dataSource;
private Connection wrapdConnection;
private static int maxCount =5;
int currentCount =0;
public MyconnectionHandler(MydataSource2 dataSource) {
this.dataSource = dataSource;
}
Connection bind(Connection conn) {
this.realConn = conn;
wrapdConnection = (Connection)Proxy.newProxyInstance(this.getClass().getClassLoader(),new Class[] { Connection.class },this);
return wrapdConnection;
}
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
// TODO Auto-generated method stub
if("close".equals(method.getName())) {
this.currentCount ++;
if(this.currentCount<maxCount) {
this.dataSource.connectionsPool.addLast(this.wrapdConnection);
}else {
this.realConn.close();
this.dataSource.currentCount--;
}
}
return method.invoke(this.realConn, args);
}
}