java数据库连接池技术简单使用

JDBCDemo.java：
package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.itheima.pool.MyPool;

public class JDBCDemo {

	public static void main(String[] args) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MyPool pool = new MyPool();
		try {
			conn = pool.getConnection();
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			//关闭数据库连接
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			/*
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			*/
			//这里不能关闭数据库连接对象connection，应该将其返还给数据库连接池
			pool.returnConn(conn);
		}
	}
}

MyPool.java：

package com.itheima.pool;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class MyPool implements DataSource{

	//list集合保存数据库连接池中的connection对象
	private static List<Connection> pool = new LinkedList<Connection>();
	//静态代码块。用于初始化list集合。即初始化数据库连接池，创建5个connection对象保存当中以备使用
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			for(int i = 0; i < 5; i++) {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");
				pool.add(conn);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	@Override
	public PrintWriter getLogWriter() throws SQLException {
		return null;
	}
	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
	}
	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
	}
	@Override
	public int getLoginTimeout() throws SQLException {
		return 0;
	}
	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		return null;
	}
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

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

	//重写父类的getConnection()方法，返回数据库连接池中的一个connection对象。
	//假设数据库连接池中connection对象都已被使用。即都被取走未返还,则创建3个connection对象保存当中供以后使用
	@Override
	public Connection getConnection() throws SQLException {
		if(pool == null) {
			for(int i = 0; i < 3; i++) {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");
				pool.add(conn);
			}
		}
		return pool.remove(0);
	}
	//创建新方法。用于返回数据库连接对象connection。由于dao层用完数据库的连接后，不应该将其销毁，而是应该将其返还给数据库连接池
	public void returnConn(Connection conn) {
		pool.add(conn);
	}
	@Override
	public Connection getConnection(String username, String password)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	
}

以上代码是数据库连接池的简单使用，可是有一个问题就是当你使用数据库连接池技术时，你须要改动JDBCDemo.java文件里finally代码块中的代码，即conn对象在finally代码块里不应该通过conn.close();方法关闭，而是应该返还给数据库连接池。

这里我们採用“动态代理”的方式，解决该问题，即finally代码块里依旧调用conn.close();方法。

此时以上两个java文件的代码更改例如以下：

package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.itheima.pool.MyPool;

public class JDBCDemo {

	public static void main(String[] args) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MyPool pool = new MyPool();
		try {
			conn = pool.getConnection();
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			//关闭数据库连接
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					conn = null;
				}
			}
		}
	}
}

package com.itheima.pool;

import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class MyPool implements DataSource{

	//list集合保存数据库连接池中的connection对象
	private static List<Connection> pool = new LinkedList<Connection>();
	//静态代码块，用于初始化list集合，即初始化数据库连接池，创建5个connection对象保存当中以备使用
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			for(int i = 0; i < 5; i++) {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");
				pool.add(conn);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	@Override
	public PrintWriter getLogWriter() throws SQLException {
		return null;
	}
	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
	}
	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
	}
	@Override
	public int getLoginTimeout() throws SQLException {
		return 0;
	}
	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		return null;
	}
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

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

	//重写父类的getConnection()方法。返回数据库连接池中的一个connection对象，
	//假设数据库连接池中connection对象都已被使用。即都被取走未返还,则创建3个connection对象保存当中供以后使用
	@Override
	public Connection getConnection() throws SQLException {
		if(pool == null) {
			for(int i = 0; i < 3; i++) {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");
				pool.add(conn);
			}
		}
		final Connection conn = pool.remove(0);
		
		//使用动态代理改造close方法
		//newProxyInstance(类载入器, 要改造的conn对象所实现的全部接口,  匿名内部类)
		Connection proxy = (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
			
			@Override
			public Object invoke(Object proxy, Method method, Object[] args)
					throws Throwable {
				if("close".equals(method.getName())) {
					//假设是close方法。我们进行重写
					returnConn(conn);
					return null;
				} else {
					//假设是其它方法，直接调用
					return method.invoke(conn, args);
				}
			}
		});
		System.out.println("获取一个连接对象,剩余连接对象：" + pool.size());
		return proxy;
	}
	//创建新方法，用于返回数据库连接对象connection，由于dao层用完数据库的连接后，不应该将其销毁，而是应该将其返还给数据库连接池
	public void returnConn(Connection conn) {
		pool.add(conn);
		System.out.println("返还一个连接对象,剩余连接对象：" + pool.size());
	}
	@Override
	public Connection getConnection(String username, String password)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	
}

执行结果：

获取一个连接对象,剩余连接对象：4
a : 1000.0
b : 1000.0
c : 1000.0
返还一个连接对象,剩余连接对象：5