首页 > 代码库 > 自己实现的一个简单的数据库连接池

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

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

今天首先学的是数据库连接,就想到了实现一个数据库连接池,虽然很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中如果释放了数据库连接,则我们也可以看到连接还给连接池的信息,这里就不再赘述。

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