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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。