数据库连接池详细说明
原创
©著作权归作者所有:来自51CTO博客作者JavaAlpha的原创作品,请联系作者获取转载授权,否则将追究法律责任
数据库连接池详细说明 首先建立个池子,里面放这我们需要的链接,党我们需要链接的时候从池子里面取,取的时候先判断是否有空闲的,有就拿来用,否则检查是否全用了,如果没有全用,则新建,否则等待或者异常抛出。 假设我们要链接不同的数据库,把相关的配置写在一个xml文件,格式如下:
ds.config.xml <ds-config> <pool> <type>mysql</type> <name>test</name> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/test</url> <username>root</username> <password>123456</password> <maxconn>100</maxconn> </pool> <pool> <type>mysql</type> <name>user2</name> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/test</url> <username>root</username> <password>123456</password> <maxconn>10</maxconn> </pool> </ds-config> 然后我们建立个javabean来对应这个xml, DSConfigBean.java package com.cgogo.dbPool;public class DSConfigBean { private String type = ""; // 数据库类型 private String name = ""; // 连接池名字 private String driver = ""; // 数据库驱动 private String url = ""; // 数据库url private String username = ""; // 用户名 private String password = ""; // 密码 private int maxconn = 0; // 最大连接数 public DSConfigBean() { // TODO Auto-generated constructor stub } 相关的set和get省略 }接下来需要建立个池的类,来建立和释放链接 DBConnectionPool.java package com.cgogo.dbPool;import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.Timer;public class DBConnectionPool { private Connection con = null; private int inUsed = 0; // 使用的连接数 private ArrayList freeConnections = new ArrayList();// 容器,空闲连接 private int minConn; // 最小连接数 private int maxConn; // 最大连接 private String name; // 连接池名字 private String password; // 密码 private String url; // 数据库连接地址 private String driver; // 驱动 private String user; // 用户名 public Timer timer; // 定时省略set和get public DBConnectionPool() { }public DBConnectionPool(String name, String driver, String URL, String user, String password, int maxConn) { this.name = name; this.driver = driver; this.url = URL; this.user = user; this.password = password; this.maxConn = maxConn; }//用完,释放连接 public synchronized void freeConnection(Connection con) { this.freeConnections.add(con);// 添加到空闲连接的末尾 this.inUsed--; }// timeout 根据timeout得到连接 public synchronized Connection getConnection(long timeout) { Connection con = null; if (this.freeConnections.size() > 0) { con = (Connection) this.freeConnections.get(0); if (con == null) con = getConnection(timeout); // 继续获得连接 } else { con = newConnection(); // 新建连接 } if (this.maxConn == 0 || this.maxConn < this.inUsed) { con = null;// 达到最大连接数,暂时不能获得连接了。 } if (con != null) { this.inUsed++; } return con; }// 从连接池里得到连接 public synchronized Connection getConnection() { Connection con = null; if (this.freeConnections.size() > 0) { con = (Connection) this.freeConnections.get(0); this.freeConnections.remove(0);// 如果连接分配出去了,就从空闲连接里删除 if (con == null) con = getConnection(); // 继续获得连接 } else { con = newConnection(); // 新建连接 } if (this.maxConn == 0 || this.maxConn < this.inUsed) { con = null;// 等待 超过最大连接时 } if (con != null) { this.inUsed++; System.out.println("得到 " + this.name + " 的连接,现有" + inUsed + "个连接在使用!"); } return con; }// 释放全部连接 public synchronized void release() { Iterator allConns = this.freeConnections.iterator(); while (allConns.hasNext()) { Connection con = (Connection) allConns.next(); try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } this.freeConnections.clear(); }//创建新连接 private Connection newConnection() { try { Class.forName(driver); con = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException e) { e.printStackTrace(); System.out.println("sorry can't find db driver!"); } catch (SQLException e1) { e1.printStackTrace(); System.out.println("sorry can't create Connection!"); } return con; } }接下来有个管理连接池的类 DBConnectionManager.java package com.cgogo.dbPool;import java.sql.Connection; import java.util.Enumeration; import java.util.Hashtable; import java.util.Iterator; import java.util.Vector;public class DBConnectionManager { static private DBConnectionManager instance;// 唯一数据库连接池管理实例类 static private int clients; // 客户连接数 private Vector drivers = new Vector();// 驱动信息 private Hashtable pools = new Hashtable();// 连接池// 实例化管理类 public DBConnectionManager() { this.init(); }// 得到唯一实例管理类 static synchronized public DBConnectionManager getInstance() { if (instance == null) { instance = new DBConnectionManager(); } return instance; }// 释放连接 public void freeConnection(String name, Connection con) { DBConnectionPool pool = (DBConnectionPool) pools.get(name);// 根据关键名字得到连接池 if (pool != null) pool.freeConnection(con);// 释放连接 }//得到一个连接根据连接池的名字name public Connection getConnection(String name) { DBConnectionPool pool = null; Connection con = null; pool = (DBConnectionPool) pools.get(name);// 从名字中获取连接池 con = pool.getConnection();// 从选定的连接池中获得连接 if (con != null) System.out.println("得到连接。。。"); return con; }// 得到一个连接,根据连接池的名字和等待时间 public Connection getConnection(String name, long timeout) { DBConnectionPool pool = null; Connection con = null; pool = (DBConnectionPool) pools.get(name);// 从名字中获取连接池 con = pool.getConnection(timeout);// 从选定的连接池中获得连接 System.out.println("得到连接。。。"); return con; }// 释放所有连接 public synchronized void release() { Enumeration allpools = pools.elements(); while (allpools.hasMoreElements()) { DBConnectionPool pool = (DBConnectionPool) allpools.nextElement(); if (pool != null) pool.release(); } pools.clear(); }// 创建连接池 private void createPools(DSConfigBean dsb) { DBConnectionPool dbpool = new DBConnectionPool(); dbpool.setName(dsb.getName()); dbpool.setDriver(dsb.getDriver()); dbpool.setUrl(dsb.getUrl()); dbpool.setUser(dsb.getUsername()); dbpool.setPassword(dsb.getPassword()); dbpool.setMaxConn(dsb.getMaxconn()); System.out.println("ioio:" + dsb.getMaxconn()); pools.put(dsb.getName(), dbpool); }//初始化连接池的参数 private void init() { this.loadDrivers();// 加载驱动程序 Iterator alldriver = drivers.iterator();// 创建连接池 while (alldriver.hasNext()) { this.createPools((DSConfigBean) alldriver.next()); System.out.println("创建连接池。。。"); } System.out.println("创建连接池完毕。。。"); }//加载驱动程序 private void loadDrivers() { ParseDSConfig pd = new ParseDSConfig(); drivers = pd.readConfigInfo("ds.config.xml");// 读取数据库配置文件 System.out.println("加载驱动程序。。。"); } } 对xml文件解析类ParseDSConfig.java package com.cgogo.dbPool;import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import java.util.Vector; import java.util.Iterator; import org.jdom.Document; import org.jdom.Element; import org.jdom.JDOMException; import org.jdom.input.SAXBuilder; import org.jdom.output.Format; import org.jdom.output.XMLOutputter;public class ParseDSConfig { public ParseDSConfig() { }//读取xml配置文件 public Vector readConfigInfo(String path) { String rpath = this.getClass().getResource("").getPath().substring(1) + path; Vector dsConfig = null; FileInputStream fi = null; try { fi = new FileInputStream(rpath);// 读取路径文件 dsConfig = new Vector(); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(fi); Element root = doc.getRootElement(); List pools = root.getChildren(); Element pool = null; Iterator allPool = pools.iterator(); while (allPool.hasNext()) { pool = (Element) allPool.next(); DSConfigBean dscBean = new DSConfigBean(); dscBean.setType(pool.getChild("type").getText()); dscBean.setName(pool.getChild("name").getText()); System.out.println(dscBean.getName()); dscBean.setDriver(pool.getChild("driver").getText()); dscBean.setUrl(pool.getChild("url").getText()); dscBean.setUsername(pool.getChild("username").getText()); dscBean.setPassword(pool.getChild("password").getText()); dscBean.setMaxconn(Integer.parseInt(pool.getChild("maxconn") .getText())); dsConfig.add(dscBean); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (JDOMException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { fi.close(); } catch (IOException e) { e.printStackTrace(); } } return dsConfig; } }把ds.config.xml放置包名下,可以写个测试类 TestPool.java package com.cgogo.dbPool;import java.sql.*; public class TestPool { public static void main(String[] args) { DBConnectionManager connectionMan = DBConnectionManager.getInstance(); String name = "test";// 从上下文得到你要访问的数据库的名字 Connection con = null; // 操作 try { con = connectionMan.getConnection(name); Statement stmt = con.createStatement(); String sql = "select * from test"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (Exception e) { e.printStackTrace(); } finally { connectionMan.freeConnection(name, con);// 释放,但并未断开连接 } try { con = connectionMan.getConnection(name); Statement stmt = con.createStatement(); String sql = "select * from test"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (Exception e) { e.printStackTrace(); } finally { connectionMan.freeConnection(name, con); } } } 控制台输出结果 Test User2 加载驱动程序。。。 ioio:100 创建连接池。。。 ioio:1000 创建连接池。。。 创建连接池完毕。。。 得到 test 的连接,现有1个连接在使用! 得到连接。。。 tangshun tangshun 得到 test 的连接,现有1个连接在使用! 得到连接。。。 tangshun tangshun
|