数据源和连接池
DataSource用来取代DriverManager来获取Connection;
通过DataSource获得Connection速度很快;
通过DataSource获得的Connection都是已经被包裹过的(不是驱动原来的连接),他的close方法已经被修改。
一般DataSource内部会用一个连接池来缓存Connection,这样可以大幅度提高数据库的访问速度;
连接池可以理解成一个能够存放Connection的Collection;
我们的程序只和DataSource打交道,不会直接访问连接池;
使用装饰模式的Connection(核心代码)
class MyConnection implements Connection{
private Connection realConn;
private LinkedList connPool;
MyConnection(Connection rConn, LinkedList cPool){
this.realConn=rConn;
this.connPool=cPool;
}
public void close(){
this.connPool.addLast(this);
}
//….
}
class MyDataSource implements DataSource{
private LinkedList connPool = new Vector();
public Connection getConneciton (){
if(this.connPool.size()>0)
return this.connPool.removeFirst(0);
return createConnection();
}
private Connection createConnection(){
Connection realConn = DriverManager.getConnection();
Connection myConn =
new MyConnection(realConn,this.connPool);
return myConn;
}
//….
}
--------------------------------------------------------------------------------------------
public class MyDataSource2 {
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "064417";
private static int initCount = 3;
private static int maxCount = 5;
private int currentCount = 0;
LinkedList<Connection> connectionsPool = new LinkedList<Connection>();
public MyDataSource2() {
for (int i = 0; i < initCount; i++) {
try {
this.connectionsPool.addLast(this.createConnection());
} catch (SQLException e) {
throw new ExceptionInInitializerError(e);
}
}
}
public Connection getConnection() throws SQLException {
synchronized (connectionsPool) {
if (this.connectionsPool.size() > 0) {
return this.connectionsPool.removeFirst();
} else if (this.currentCount < maxCount) {
return this.createConnection();
} else {
throw new SQLException("已没链接");
}
}
}
public void free(Connection conn) {
this.connectionsPool.addLast(conn);
}
private Connection createConnection() throws SQLException {
this.currentCount++;
Connection realconn=DriverManager.getConnection(url, user, password);
// MyConnection myConnection=new MyConnection(realconn,this);
// return myConnection;
MyConnectionHandler proxy= new MyConnectionHandler(this);
return proxy.bind(realconn); }
}
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "064417";
private static int initCount = 3;
private static int maxCount = 5;
private int currentCount = 0;
LinkedList<Connection> connectionsPool = new LinkedList<Connection>();
public MyDataSource2() {
for (int i = 0; i < initCount; i++) {
try {
this.connectionsPool.addLast(this.createConnection());
} catch (SQLException e) {
throw new ExceptionInInitializerError(e);
}
}
}
public Connection getConnection() throws SQLException {
synchronized (connectionsPool) {
if (this.connectionsPool.size() > 0) {
return this.connectionsPool.removeFirst();
} else if (this.currentCount < maxCount) {
return this.createConnection();
} else {
throw new SQLException("已没链接");
}
}
}
public void free(Connection conn) {
this.connectionsPool.addLast(conn);
}
private Connection createConnection() throws SQLException {
this.currentCount++;
Connection realconn=DriverManager.getConnection(url, user, password);
// MyConnection myConnection=new MyConnection(realconn,this);
// return myConnection;
MyConnectionHandler proxy= new MyConnectionHandler(this);
return proxy.bind(realconn); }
}
---------------------------------------------------------------------------------------------
public class MyConnection implements Connection{
public Connection realConnection;
private MyDataSource2 dataSource;
MyConnection(Connection connection,MyDataSource2 dataSource){
this.realConnection=connection;
this.dataSource=dataSource;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return this.realConnection.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return this.realConnection.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
return this.realConnection.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return this.realConnection.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return this.realConnection.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return this.realConnection.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
this.realConnection.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return this.realConnection.getAutoCommit();
}
@Override
public void commit() throws SQLException {
this.realConnection.commit();
}
@Override
public void rollback() throws SQLException {
this.realConnection.rollback();
}
@Override
public void close() throws SQLException {
this.dataSource.connectionsPool.addLast(this);
}
@Override
public boolean isClosed() throws SQLException {
return this.realConnection.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return this.realConnection.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
this.realConnection.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return this.realConnection.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
this.realConnection.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return this.realConnection.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
this.realConnection.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return this.realConnection.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return this.realConnection.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
this.realConnection.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException {
return this.realConnection.createStatement(resultSetType, resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return this.realConnection.prepareStatement(sql, resultSetType, resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return this.realConnection.prepareCall(sql, resultSetType, resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return this.realConnection.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
this.realConnection.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
this.realConnection.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return this.realConnection.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return this.realConnection.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return this.realConnection.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
this.realConnection.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
this.realConnection.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
return this.realConnection.prepareStatement(sql, autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
throws SQLException {
return this.realConnection.prepareStatement(sql, columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames)
throws SQLException {
return this.realConnection.prepareStatement(sql, columnNames);
}
@Override
public Clob createClob() throws SQLException {
return this.realConnection.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return this.realConnection.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return this.realConnection.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return this.realConnection.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return this.realConnection.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value)
throws SQLClientInfoException {
this.realConnection.setClientInfo(name, value);
}
@Override
public void setClientInfo(Properties properties)
throws SQLClientInfoException {
this.realConnection.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return this.realConnection.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return this.realConnection.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements)
throws SQLException {
return this.realConnection.createArrayOf(typeName, elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes)
throws SQLException {
return this.realConnection.createStruct(typeName, attributes) ;
}
@Override
public void setSchema(String schema) throws SQLException {
this.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return this.realConnection.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
this.realConnection.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds)
throws SQLException {
this.realConnection.setNetworkTimeout(executor, milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return this.realConnection.getNetworkTimeout();
}
}
public Connection realConnection;
private MyDataSource2 dataSource;
MyConnection(Connection connection,MyDataSource2 dataSource){
this.realConnection=connection;
this.dataSource=dataSource;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return this.realConnection.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return this.realConnection.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
return this.realConnection.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return this.realConnection.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return this.realConnection.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return this.realConnection.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
this.realConnection.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return this.realConnection.getAutoCommit();
}
@Override
public void commit() throws SQLException {
this.realConnection.commit();
}
@Override
public void rollback() throws SQLException {
this.realConnection.rollback();
}
@Override
public void close() throws SQLException {
this.dataSource.connectionsPool.addLast(this);
}
@Override
public boolean isClosed() throws SQLException {
return this.realConnection.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return this.realConnection.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
this.realConnection.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return this.realConnection.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
this.realConnection.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return this.realConnection.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
this.realConnection.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return this.realConnection.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return this.realConnection.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
this.realConnection.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException {
return this.realConnection.createStatement(resultSetType, resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return this.realConnection.prepareStatement(sql, resultSetType, resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return this.realConnection.prepareCall(sql, resultSetType, resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return this.realConnection.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
this.realConnection.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
this.realConnection.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return this.realConnection.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return this.realConnection.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return this.realConnection.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
this.realConnection.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
this.realConnection.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
return this.realConnection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
return this.realConnection.prepareStatement(sql, autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
throws SQLException {
return this.realConnection.prepareStatement(sql, columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames)
throws SQLException {
return this.realConnection.prepareStatement(sql, columnNames);
}
@Override
public Clob createClob() throws SQLException {
return this.realConnection.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return this.realConnection.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return this.realConnection.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return this.realConnection.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return this.realConnection.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value)
throws SQLClientInfoException {
this.realConnection.setClientInfo(name, value);
}
@Override
public void setClientInfo(Properties properties)
throws SQLClientInfoException {
this.realConnection.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return this.realConnection.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return this.realConnection.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements)
throws SQLException {
return this.realConnection.createArrayOf(typeName, elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes)
throws SQLException {
return this.realConnection.createStruct(typeName, attributes) ;
}
@Override
public void setSchema(String schema) throws SQLException {
this.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return this.realConnection.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
this.realConnection.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds)
throws SQLException {
this.realConnection.setNetworkTimeout(executor, milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return this.realConnection.getNetworkTimeout();
}
}
---------------------------------------------------------------------------------------------
public final class JdbcUtils {
// private static String url = "jdbc:mysql://localhost:3306/jdbc";
// private static String user = "root";
// private static String password = "064417";
private static MyDataSource2 myDataSource = null;
private JdbcUtils() {}
static {
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");// 推荐
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());//2
// System.setProperty("jdbc.drivers",
// "com.mysql.jdbc.Driver[:...:...]");//注册方式3可以注册多个驱动用“:”分隔。
myDataSource = new MyDataSource2();
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
// return DriverManager.getConnection(url, user, password);
return myDataSource.getConnection();
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
// myDataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
// private static String url = "jdbc:mysql://localhost:3306/jdbc";
// private static String user = "root";
// private static String password = "064417";
private static MyDataSource2 myDataSource = null;
private JdbcUtils() {}
static {
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");// 推荐
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());//2
// System.setProperty("jdbc.drivers",
// "com.mysql.jdbc.Driver[:...:...]");//注册方式3可以注册多个驱动用“:”分隔。
myDataSource = new MyDataSource2();
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
// return DriverManager.getConnection(url, user, password);
return myDataSource.getConnection();
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
// myDataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
--------------------------------------------------------------------------------------------
public class Base {
public static void main(String[] args) throws SQLException {
// template();
for(int i=0;i<10;i++){
Connection conn=JdbcUtils.getConnection();
System.out.println(conn);
JdbcUtils.free(null, null, conn);
}
}
public static void main(String[] args) throws SQLException {
// template();
for(int i=0;i<10;i++){
Connection conn=JdbcUtils.getConnection();
System.out.println(conn);
JdbcUtils.free(null, null, conn);
}
}
}
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
public class MyConnectionHandler implements InvocationHandler {
private Connection realConnection;
private Connection warpedConnection;
private MyDataSource2 dataSource;
private int maxUseCount=5;
private int currentUserCount=0;
MyConnectionHandler(MyDataSource2 dataSource) {
this.dataSource=dataSource;
}
Connection bind(Connection realconn) {
this.realConnection=realconn;
// newProxyInstance()在内存中动态产生一个新的类。第二个参数为所要实现的接口,
warpedConnection = (Connection) Proxy.newProxyInstance(this
.getClass().getClassLoader(), new Class[] { Connection.class },
this);
return warpedConnection;
}
@Override//调用处理器
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
if("close".equals(method.getName())){
this.currentUserCount++;
if(this.currentUserCount<this.maxUseCount)
this.dataSource.connectionsPool.addLast(this.warpedConnection);
else{
this.realConnection.close();
this.currentUserCount--;
}
}
return method.invoke(this.realConnection, args);
}
}
private Connection realConnection;
private Connection warpedConnection;
private MyDataSource2 dataSource;
private int maxUseCount=5;
private int currentUserCount=0;
MyConnectionHandler(MyDataSource2 dataSource) {
this.dataSource=dataSource;
}
Connection bind(Connection realconn) {
this.realConnection=realconn;
// newProxyInstance()在内存中动态产生一个新的类。第二个参数为所要实现的接口,
warpedConnection = (Connection) Proxy.newProxyInstance(this
.getClass().getClassLoader(), new Class[] { Connection.class },
this);
return warpedConnection;
}
@Override//调用处理器
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
if("close".equals(method.getName())){
this.currentUserCount++;
if(this.currentUserCount<this.maxUseCount)
this.dataSource.connectionsPool.addLast(this.warpedConnection);
else{
this.realConnection.close();
this.currentUserCount--;
}
}
return method.invoke(this.realConnection, args);
}
}