自己实现的一个简单的数据库连接池

      自己突然就找到了以前学习的感觉,要不是完了这几个月英爱能找一份比现在好点的工作,说实话还是有点后悔,但是没关系,从现在开始加油还来得及。

今天首先学的是数据库连接，就想到了实现一个数据库连接池，虽然很Lower 但是还是记录下来，一步一个脚印.

首先实现方法是创建一个连接池对象,里面放一个List对象用来保存数据库连接,自己写一个类,实现Connection对象，在里面的close方法里面将连接还给连接池。

然后在从数据库连接池中获取数据的时候用removeFirst方法,移除并返回第一个对象，好的废话不多说上代码

package com.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class JDBC_POOL {
private static final String URL = "jdbc:mysql://localhost:3306/JDBC_TEST?useUnicode=true&characterEncoding=UTF-8";
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String USER_NAME = "root";
private static final String PASSWORD = "dbq228367/";
/**
 * 用来装连接池的容器
 */
public static LinkedList<myConnection> list = new LinkedList<myConnection>();

/**
 * 设置容器的大小
 */
public static int size = 100;

static{
 try{
 for(int i=0;i<3;i++){
 Connection conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
 System.out.println("创建了数据库连接 : " + conn + " : " + conn.hashCode());
 myConnection mc = new myConnection(conn);
 list.add(mc);
 } 
 }catch(Exception e){
 System.out.println(e.getMessage());
 e.printStackTrace();
 }
}

public myConnection getConnection()throws Exception{
if(list.size()==0 || list.size()<0){
//连接池已经满了
throw new Exception("连接池已经没有连接对象");
}else{
System.out.println("程序获取连接对象 ： " + list.get(0).getConnection());
return list.removeFirst();
}
}
}

　　然后是实现Connection接口的自定义类

package com.mysql;

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;

public class myConnection implements Connection{

private Connection conn;

public myConnection(Connection conn){
this.conn = conn;
}

@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return conn.unwrap(iface);
}

@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return conn.isWrapperFor(iface);
}

@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement();
}

@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql);
}

@Override
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall(sql);
}

@Override
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return conn.nativeSQL(sql);
}

@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub
conn.setAutoCommit(autoCommit);
}

@Override
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return conn.getAutoCommit();
}

@Override
public void commit() throws SQLException {
// TODO Auto-generated method stub
conn.commit();
}

@Override
public void rollback() throws SQLException {
// TODO Auto-generated method stub
conn.rollback();
}

@Override
public void close() throws SQLException {
// TODO Auto-generated method stub
//在这里将连接对象还给连接池
System.out.println("将连接对象还给连接池  : " + conn);
JDBC_POOL.list.add(new myConnection(conn));
}

@Override
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return conn.isClosed();
}

@Override
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return conn.getMetaData();
}

@Override
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub
conn.setReadOnly(readOnly);
}

@Override
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return conn.isReadOnly();
}

@Override
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub
conn.setCatalog(catalog);
}

@Override
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return conn.getCatalog();
}

@Override
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub
conn.setTransactionIsolation(level);
}

@Override
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return conn.getTransactionIsolation();
}

@Override
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return conn.getWarnings();
}

@Override
public void clearWarnings() throws SQLException {
// TODO Auto-generated method stub
conn.clearWarnings();
}

@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement(resultSetType, resultSetConcurrency);
}

@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, resultSetType,resultSetConcurrency);
}

@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetConcurrency);
}

@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return conn.getTypeMap();
}

@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub
conn.setTypeMap(map);
}

@Override
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub
conn.setHoldability(holdability);
}

@Override
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return conn.getHoldability();
}

@Override
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return conn.setSavepoint();
}

@Override
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return conn.setSavepoint(name);
}

@Override
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
conn.rollback(savepoint);
}

@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
    conn.releaseSavepoint(savepoint);
}

@Override
public Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
}

@Override
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement(sql, resultSetType, resultSetConcurrency,resultSetHoldability);
}

@Override
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareCall( sql,  resultSetType,
 resultSetConcurrency,  resultSetHoldability);
}

@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement( sql,  autoGeneratedKeys);
}

@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement( sql, columnIndexes);
}

@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames)
throws SQLException {
// TODO Auto-generated method stub
return conn.prepareStatement( sql,  columnNames);
}

@Override
public Clob createClob() throws SQLException {
// TODO Auto-generated method stub
return conn.createClob();
}

@Override
public Blob createBlob() throws SQLException {
// TODO Auto-generated method stub
return conn.createBlob();
}

@Override
public NClob createNClob() throws SQLException {
// TODO Auto-generated method stub
return conn.createNClob();
}

@Override
public SQLXML createSQLXML() throws SQLException {
// TODO Auto-generated method stub
return conn.createSQLXML();
}

@Override
public boolean isValid(int timeout) throws SQLException {
// TODO Auto-generated method stub
return conn.isValid(timeout);
}

@Override
public void setClientInfo(String name, String value)
throws SQLClientInfoException {
// TODO Auto-generated method stub
conn.setClientInfo(name, value);
}

@Override
public void setClientInfo(Properties properties)
throws SQLClientInfoException {
// TODO Auto-generated method stub
conn.setClientInfo(properties);
}

@Override
public String getClientInfo(String name) throws SQLException {
// TODO Auto-generated method stub
return conn.getClientInfo(name);
}

@Override
public Properties getClientInfo() throws SQLException {
// TODO Auto-generated method stub
return conn.getClientInfo();
}

@Override
public Array createArrayOf(String typeName, Object[] elements)
throws SQLException {
// TODO Auto-generated method stub
return conn.createArrayOf(typeName, elements);
}

@Override
public Struct createStruct(String typeName, Object[] attributes)
throws SQLException {
// TODO Auto-generated method stub
return conn.createStruct(typeName, attributes);
}

public Connection getConnection(){
return this.conn;
}
}

　　注意看里面的close方法,和最后一个getConnection方法,getConnection方法是为了直观的观察数据库连接池里面对象的流转情况

然后在servlet里面调用并打印数据库连接池信息

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.JDBC_POOL;
import com.mysql.myConnection;

public class getConnection extends HttpServlet {

private static final long serialVersionUID = 1806541121588301641L;

public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
         doPost(request,response);
}

public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
          PrintWriter out = response.getWriter();
          try{
          Connection conn = new JDBC_POOL().getConnection();
          out.print(conn+" : " + conn.hashCode());
          }catch(Exception e){
          System.out.println(e.getMessage());
          e.printStackTrace();
          }finally{

          }
}
}

　　最后我们来看看效果

还有控制台打印的信息

创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@66922804 : 1720854532
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@47124746 : 1192380230
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@26aae86e : 648734830
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@66922804

　　再访问一次

 

创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@66922804 : 1720854532
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@47124746 : 1192380230
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@26aae86e : 648734830
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@66922804
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@47124746

　　第三次

 

创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@66922804 : 1720854532
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@47124746 : 1192380230
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@26aae86e : 648734830
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@66922804
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@47124746
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@26aae86e

　　好的这时候三个连接对象都取完了，我们来第四次访问

 

创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@66922804 : 1720854532
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@47124746 : 1192380230
创建了数据库连接 : com.mysql.jdbc.JDBC4Connection@26aae86e : 648734830
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@66922804
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@47124746
程序获取连接对象 ： com.mysql.jdbc.JDBC4Connection@26aae86e
连接池已经没有连接对象
java.lang.Exception: 连接池已经没有连接对象
at com.mysql.JDBC_POOL.getConnection(JDBC_POOL.java:41)
at com.servlet.getConnection.doPost(getConnection.java:26)
at com.servlet.getConnection.doGet(getConnection.java:20)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)

　　到这里和预想的一样,在servlet中如果释放了数据库连接,则我们也可以看到连接还给连接池的信息，这里就不再赘述。