同时连接多个数据库 得到连接慢的问题 项目采用的struts,用dbcp配了主数据库连接池,然后当主数据库处于连接状态时用jdbc连接第二数据库时候,得到连接的速度超级慢,从打印测试得到,第二连接得到前与得到后大概要用5秒,直接导致前端页面卡那里卡5-8秒。请教一下大家这个怎么解决?目前正采用数据库连接池配2个数据库,不知道能不能解决。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 检查下jdbc连接的代码连接池配2个数据库应该可行 可能是你连接池配置的问题!在多个数据库数据之间来回倒换,需要为每个数据库建立一个池,建立一个管理这些池的管理类,达到的目的是:在我们需要一个链接的时候,只要提供需要那个连接池(每个连接池对于一个名字,所有的连接池都装载入一个Map中)的名字,就可以得到链接进行操作,完毕需要关闭,关闭这个链接并不会真正的关闭,在池中他还是存在的。首先定义个xml文件作为我们需要的连接池的配置文件:如下:config.xmlXml代码<?xml version="1.0" encoding="UTF-8"?> <pools> <pool> <!-- 连接池的名字 --> <name>one</name> <!-- 连接数据库的名字 --> <username>name1</username> <!-- 连接数据库的密码 --> <password>pswd1</password> <!-- 连接数据库的url --> <jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl> <!-- 连接池的最大容纳链接数目 --> <max>10</max> <!-- 连接池取不到链接等待时间 --> <wait></wait> <!-- 连接数据库驱动 --> <driver>com.mysql.jdbc.Driver</driver> </pool> <pool> <name>two</name> <username>name2</username> <password>paswd2</password> <jdbcurl>jdbc:mysql://IP/UIC</jdbcurl> <max>10</max> <wait></wait> <driver>com.mysql.jdbc.Driver</driver> </pool> </pools> <?xml version="1.0" encoding="UTF-8"?><pools> <pool> <!-- 连接池的名字 --> <name>one</name> <!-- 连接数据库的名字 --> <username>name1</username> <!-- 连接数据库的密码 --> <password>pswd1</password> <!-- 连接数据库的url --> <jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl> <!-- 连接池的最大容纳链接数目 --> <max>10</max> <!-- 连接池取不到链接等待时间 --> <wait></wait> <!-- 连接数据库驱动 --> <driver>com.mysql.jdbc.Driver</driver> </pool> <pool> <name>two</name> <username>name2</username> <password>paswd2</password> <jdbcurl>jdbc:mysql://IP/UIC</jdbcurl> <max>10</max> <wait></wait> <driver>com.mysql.jdbc.Driver</driver> </pool></pools>作为对象操作,这个xml有相对应的Bean:如下:BaseConnBean.javaJava代码public class BaseConnBean { private String name; private String username; private String password; private String jdbcurl; private int max; private long wait; private String driver; public String getDriver() { return driver; } public void setDriver(String driver) { this.driver = driver; } ……//其他set get方法 } public class BaseConnBean { private String name; private String username; private String password; private String jdbcurl; private int max; private long wait; private String driver; public String getDriver() { return driver; } public void setDriver(String driver) { this.driver = driver; }……//其他set get方法}还需要一个操作就是吧xml文件组装成Bean,并把这些bean加到list里面,代码如下ConfigXml.javaJava代码import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.jdom.Document; import org.jdom.Element; import org.jdom.JDOMException; import org.jdom.input.SAXBuilder; import com.cgogo.mymodel.po.BaseConnBean; public class ConfigXml { public static void main(String[] args) { read("config.xml"); } public static List<BaseConnBean> read(){ return read("config.xml"); } public static List<BaseConnBean> read(String path) { String rpath = ConfigXml.class.getResource("").getPath().substring(1) + path; FileInputStream fi = null; List<BaseConnBean> pools=new ArrayList<BaseConnBean>(); try { fi = new FileInputStream(rpath); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(fi); Element root = doc.getRootElement(); List list=root.getChildren(); Element pool = null; Iterator allPool = list.iterator(); while(allPool.hasNext()){ pool=(Element) allPool.next(); BaseConnBean bcBean=new BaseConnBean(); bcBean.setName(pool.getChildText("name")); bcBean.setUsername(pool.getChildText("username")); bcBean.setPassword(pool.getChildText("password")); bcBean.setJdbcurl(pool.getChildText("jdbcurl")); try{ bcBean.setMax(Integer.parseInt(pool.getChildText("max"))); }catch(NumberFormatException e){ bcBean.setMax(0); } try{ bcBean.setWait(Long.parseLong(pool.getChildText("wait"))); }catch(NumberFormatException e){ bcBean.setWait(-1L); } bcBean.setDriver(pool.getChildText("driver")); pools.add(bcBean); } } catch (FileNotFoundException e) { System.out.println("file does not find"); e.printStackTrace(); } catch (JDOMException e) { System.out.println("jdom exception"); e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return pools; } } import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import org.jdom.Document;import org.jdom.Element;import org.jdom.JDOMException;import org.jdom.input.SAXBuilder;import com.cgogo.mymodel.po.BaseConnBean;public class ConfigXml { public static void main(String[] args) { read("config.xml"); } public static List<BaseConnBean> read(){ return read("config.xml"); } public static List<BaseConnBean> read(String path) { String rpath = ConfigXml.class.getResource("").getPath().substring(1) + path; FileInputStream fi = null; List<BaseConnBean> pools=new ArrayList<BaseConnBean>(); try { fi = new FileInputStream(rpath); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(fi); Element root = doc.getRootElement(); List list=root.getChildren(); Element pool = null; Iterator allPool = list.iterator(); while(allPool.hasNext()){ pool=(Element) allPool.next(); BaseConnBean bcBean=new BaseConnBean(); bcBean.setName(pool.getChildText("name")); bcBean.setUsername(pool.getChildText("username")); bcBean.setPassword(pool.getChildText("password")); bcBean.setJdbcurl(pool.getChildText("jdbcurl")); try{ bcBean.setMax(Integer.parseInt(pool.getChildText("max"))); }catch(NumberFormatException e){ bcBean.setMax(0); } try{ bcBean.setWait(Long.parseLong(pool.getChildText("wait"))); }catch(NumberFormatException e){ bcBean.setWait(-1L); } bcBean.setDriver(pool.getChildText("driver")); pools.add(bcBean); } } catch (FileNotFoundException e) { System.out.println("file does not find"); e.printStackTrace(); } catch (JDOMException e) { System.out.println("jdom exception"); e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return pools; }} 最后,最关键的类就是管理类了,(需要增加dbcp的jar包,同时还需要commons-pools)代码如下Java代码 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.dbcp.ConnectionFactory; import org.apache.commons.dbcp.DriverManagerConnectionFactory; import org.apache.commons.dbcp.PoolableConnectionFactory; import org.apache.commons.dbcp.PoolingDriver; import org.apache.commons.pool.ObjectPool; import org.apache.commons.pool.impl.GenericObjectPool; import com.cgogo.mymodel.po.BaseConnBean; import com.cgogo.mymodel.util.ConfigXml; public class MyDbPool { public static void main(String[] args) { } private static String dbJdbc = null; private static String dbUser = null; private static String dbPwd = null; private static int max; private static long wait; private static String driver = null; private static Class driverClass = null; private static ObjectPool connectionPool = null; public static Map<String, ObjectPool> map = null; public MyDbPool() { } /** *//** * 初始化数据源 */ private static synchronized void initDataSource() { // 驱动数据源 if (driverClass == null) { try { driverClass = Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } } /** *//** * 连接池启动 * * @throws Exception */ public static void StartPool(String poolname, String dbJdbc, String dbUser, String dbPwd, int max, long wait) { // 初始化数据源 initDataSource(); // 如果连接池为空 if (connectionPool != null) { ShutdownPool(); } try { connectionPool = new GenericObjectPool(null, max, (byte) 1, wait); ConnectionFactory connectionFactory = new DriverManagerConnectionFactory( dbJdbc, dbUser, dbPwd); PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory( connectionFactory, connectionPool, null, null, false, true); Class.forName("org.apache.commons.dbcp.PoolingDriver"); PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); driver.registerPool(poolname, connectionPool); map.put(poolname, connectionPool); System.out.println("Create " + poolname + " for Database Connection Succees."); } catch (Exception e) { e.printStackTrace(); } } /** *//** * 释放连接池 */ public static void ShutdownPool() { try { PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); driver.closePool("dbpool"); } catch (SQLException e) { e.printStackTrace(); } } /** *//** * 打印连接池状态 */ public static String GetPoolStats(String poolname) { StringBuffer stat = new StringBuffer(); try { PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); ObjectPool connectionPool = driver.getConnectionPool(poolname); stat.append("-- Active Connection: "); stat.append(connectionPool.getNumActive()); stat.append(" ,"); stat.append("Free Connection: "); stat.append(connectionPool.getNumIdle()); stat.append(" . --"); } catch (Exception e) { e.printStackTrace(); } return stat.toString(); } /** *//** * 取得连接池中的连接 * * @return */ public synchronized static Connection getDbConnection(String poolname) { Connection conn = null; if (map == null) { System.out.println("map null"); map = new HashMap<String, ObjectPool>(); } if (map.get(poolname) == null) { init(poolname);// 初始化基本数据 StartPool(poolname, dbJdbc, dbUser, dbPwd, max, wait); } try { conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + poolname); } catch (SQLException e) { e.printStackTrace(); } return conn; } private static void init(String poolname) { List<BaseConnBean> pools = ConfigXml.read(); for (BaseConnBean baseConnBean : pools) { if (baseConnBean.getName().equals(poolname)) { dbJdbc = baseConnBean.getJdbcurl(); dbUser = baseConnBean.getUsername(); dbPwd = baseConnBean.getPassword(); max = baseConnBean.getMax(); wait = baseConnBean.getWait(); driver = baseConnBean.getDriver(); } } } public static void close(Connection c) { try { if (c != null) c.close(); } catch (SQLException e) { e.printStackTrace(); } } } import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.dbcp.ConnectionFactory;import org.apache.commons.dbcp.DriverManagerConnectionFactory;import org.apache.commons.dbcp.PoolableConnectionFactory;import org.apache.commons.dbcp.PoolingDriver;import org.apache.commons.pool.ObjectPool;import org.apache.commons.pool.impl.GenericObjectPool;import com.cgogo.mymodel.po.BaseConnBean;import com.cgogo.mymodel.util.ConfigXml;public class MyDbPool { public static void main(String[] args) { } private static String dbJdbc = null; private static String dbUser = null; private static String dbPwd = null; private static int max; private static long wait; private static String driver = null; private static Class driverClass = null; private static ObjectPool connectionPool = null; public static Map<String, ObjectPool> map = null; public MyDbPool() { } /** *//** * 初始化数据源 */ private static synchronized void initDataSource() { // 驱动数据源 if (driverClass == null) { try { driverClass = Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } } /** *//** * 连接池启动 * * @throws Exception */ public static void StartPool(String poolname, String dbJdbc, String dbUser, String dbPwd, int max, long wait) { // 初始化数据源 initDataSource(); // 如果连接池为空 if (connectionPool != null) { ShutdownPool(); } try { connectionPool = new GenericObjectPool(null, max, (byte) 1, wait); ConnectionFactory connectionFactory = new DriverManagerConnectionFactory( dbJdbc, dbUser, dbPwd); PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory( connectionFactory, connectionPool, null, null, false, true); Class.forName("org.apache.commons.dbcp.PoolingDriver"); PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); driver.registerPool(poolname, connectionPool); map.put(poolname, connectionPool); System.out.println("Create " + poolname + " for Database Connection Succees."); } catch (Exception e) { e.printStackTrace(); } } /** *//** * 释放连接池 */ public static void ShutdownPool() { try { PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); driver.closePool("dbpool"); } catch (SQLException e) { e.printStackTrace(); } } /** *//** * 打印连接池状态 */ public static String GetPoolStats(String poolname) { StringBuffer stat = new StringBuffer(); try { PoolingDriver driver = (PoolingDriver) DriverManager .getDriver("jdbc:apache:commons:dbcp:"); ObjectPool connectionPool = driver.getConnectionPool(poolname); stat.append("-- Active Connection: "); stat.append(connectionPool.getNumActive()); stat.append(" ,"); stat.append("Free Connection: "); stat.append(connectionPool.getNumIdle()); stat.append(" . --"); } catch (Exception e) { e.printStackTrace(); } return stat.toString(); } /** *//** * 取得连接池中的连接 * * @return */ public synchronized static Connection getDbConnection(String poolname) { Connection conn = null; if (map == null) { System.out.println("map null"); map = new HashMap<String, ObjectPool>(); } if (map.get(poolname) == null) { init(poolname);// 初始化基本数据 StartPool(poolname, dbJdbc, dbUser, dbPwd, max, wait); } try { conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + poolname); } catch (SQLException e) { e.printStackTrace(); } return conn; } private static void init(String poolname) { List<BaseConnBean> pools = ConfigXml.read(); for (BaseConnBean baseConnBean : pools) { if (baseConnBean.getName().equals(poolname)) { dbJdbc = baseConnBean.getJdbcurl(); dbUser = baseConnBean.getUsername(); dbPwd = baseConnBean.getPassword(); max = baseConnBean.getMax(); wait = baseConnBean.getWait(); driver = baseConnBean.getDriver(); } } } public static void close(Connection c) { try { if (c != null) c.close(); } catch (SQLException e) { e.printStackTrace(); } }}至此,基本工作已经完成,需要链接,只要MyDbPool.getDbConnection("name1"),就可以得到一个链接,注意,用完需要归还下多线程测试例子Java代码import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; public class Test1 extends Thread{ private String sql; private String poolname; private Connection c; public Test1(String poolname,Connection c,String sql){ this.poolname=poolname; this.c=c; this.sql=sql; } public static void main(String[] args) { for(int i=0;i<100;i++){ String sql="select * from table1 limit "+i+",1"; new Test1("datamininfo",MyDbPool.getDbConnection("datamininfo"),sql).start(); sql="select * from table2 limit "+i+",1"; new Test1("uic",MyDbPool.getDbConnection("uic"),sql).start(); } } public void run(){ try { ResultSet rs=c.createStatement().executeQuery(sql); while(rs.next()){ System.out.println(rs.getString(1)); } if(poolname.equals("datamininfo")) System.out.println("DataMinInfo:\n"+MyDbPool.GetPoolStats("name1")); else System.out.println("UIC:\n"+MyDbPool.GetPoolStats("name2")); } catch (SQLException e) { e.printStackTrace(); }finally{ MyDbPool.close(c); } } } 不连第一个数据库的时候,直接用jdbc连第二个怎么样。是同一机器上的数据库吗?如果你jdbc连接数据库的地方写得没有问题,那么看一下是不是第一个数据库连接池对后面的jdbc获取连接造成了影响。你的第一个连接池可能保持着许多物理连接,但是jdbc获取的时候无法利用这些连接。观察一下数据库的连接使用情况吧 2个数据库不再同一机器上,第2个数据库是用jdbc直接连过去的,这样的话,后面的查询应该是不受前面的连接池的影响的。你把用jdbc连接远程数据库的那个代码单独执行一下,应该也比较慢吧 把常常用的那个数据库用连接池,另一个用jdbc,我也是这样做的速度还可以,两个都用的话就要看机器有没有那么多内存来用了, jdbc获取连接本来就慢的,建议楼主还是把后者也弄到连接池里面吧,比如配两个连接池。看你什么数据库,oracle里面可以用toad看session。 WEB-INFO下的JSP文件 小弟想问一个JAVA连AS400的问题,求高手 快疯了我,是不是配置问题??初学 Tomcat5.5启动暴慢,求解决办法! 同一秒钟有有5000个数据库插入请求 路过的诸位看看有没遇到类似的问题 Tomcat有没有最大并发数限制 关于Java Web Start,请大家指教 weblogic6.1+MS sql7.0 建立连接池我为啥配不通??? 请问大侠,这段struts代码如何使用<html:iterate>, javascript存储cookie在servlet读取 SSH1项目中Hibernate的load和Get方法都不能取出实体了,崩溃啦!!!
在多个数据库数据之间来回倒换,需要为每个数据库建立一个池,建立一个管理这些池的管理类,达到的目的是:在我们需要一个链接的时候,只要提供需要那个连接池(每个连接池对于一个名字,所有的连接池都装载入一个Map中)的名字,就可以得到链接进行操作,完毕需要关闭,关闭这个链接并不会真正的关闭,在池中他还是存在的。
首先定义个xml文件作为我们需要的连接池的配置文件:如下:config.xmlXml代码
<?xml version="1.0" encoding="UTF-8"?>
<pools>
<pool>
<!-- 连接池的名字 -->
<name>one</name>
<!-- 连接数据库的名字 -->
<username>name1</username>
<!-- 连接数据库的密码 -->
<password>pswd1</password>
<!-- 连接数据库的url -->
<jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl>
<!-- 连接池的最大容纳链接数目 -->
<max>10</max>
<!-- 连接池取不到链接等待时间 -->
<wait></wait>
<!-- 连接数据库驱动 -->
<driver>com.mysql.jdbc.Driver</driver>
</pool>
<pool>
<name>two</name>
<username>name2</username>
<password>paswd2</password>
<jdbcurl>jdbc:mysql://IP/UIC</jdbcurl>
<max>10</max>
<wait></wait>
<driver>com.mysql.jdbc.Driver</driver>
</pool>
</pools> <?xml version="1.0" encoding="UTF-8"?>
<pools>
<pool>
<!-- 连接池的名字 -->
<name>one</name>
<!-- 连接数据库的名字 -->
<username>name1</username>
<!-- 连接数据库的密码 -->
<password>pswd1</password>
<!-- 连接数据库的url -->
<jdbcurl>jdbc:mysql://IP/DataminInfo</jdbcurl>
<!-- 连接池的最大容纳链接数目 -->
<max>10</max>
<!-- 连接池取不到链接等待时间 -->
<wait></wait>
<!-- 连接数据库驱动 -->
<driver>com.mysql.jdbc.Driver</driver>
</pool>
<pool>
<name>two</name>
<username>name2</username>
<password>paswd2</password>
<jdbcurl>jdbc:mysql://IP/UIC</jdbcurl>
<max>10</max>
<wait></wait>
<driver>com.mysql.jdbc.Driver</driver>
</pool>
</pools>
作为对象操作,这个xml有相对应的Bean:如下:BaseConnBean.java
Java代码
public class BaseConnBean {
private String name;
private String username;
private String password;
private String jdbcurl;
private int max;
private long wait;
private String driver;
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
……//其他set get方法
} public class BaseConnBean { private String name;
private String username;
private String password;
private String jdbcurl;
private int max;
private long wait;
private String driver;
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
……//其他set get方法
}
还需要一个操作就是吧xml文件组装成Bean,并把这些bean加到list里面,代码如下ConfigXml.javaJava代码
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import com.cgogo.mymodel.po.BaseConnBean;
public class ConfigXml {
public static void main(String[] args) {
read("config.xml");
}
public static List<BaseConnBean> read(){
return read("config.xml");
}
public static List<BaseConnBean> read(String path) {
String rpath = ConfigXml.class.getResource("").getPath().substring(1)
+ path;
FileInputStream fi = null;
List<BaseConnBean> pools=new ArrayList<BaseConnBean>();
try {
fi = new FileInputStream(rpath);
SAXBuilder sb = new SAXBuilder();
Document doc = sb.build(fi);
Element root = doc.getRootElement();
List list=root.getChildren();
Element pool = null;
Iterator allPool = list.iterator();
while(allPool.hasNext()){
pool=(Element) allPool.next();
BaseConnBean bcBean=new BaseConnBean();
bcBean.setName(pool.getChildText("name"));
bcBean.setUsername(pool.getChildText("username"));
bcBean.setPassword(pool.getChildText("password"));
bcBean.setJdbcurl(pool.getChildText("jdbcurl"));
try{
bcBean.setMax(Integer.parseInt(pool.getChildText("max")));
}catch(NumberFormatException e){
bcBean.setMax(0);
}
try{
bcBean.setWait(Long.parseLong(pool.getChildText("wait")));
}catch(NumberFormatException e){
bcBean.setWait(-1L);
}
bcBean.setDriver(pool.getChildText("driver"));
pools.add(bcBean);
}
} catch (FileNotFoundException e) {
System.out.println("file does not find");
e.printStackTrace();
} catch (JDOMException e) {
System.out.println("jdom exception");
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return pools;
}
} import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;import com.cgogo.mymodel.po.BaseConnBean;public class ConfigXml {
public static void main(String[] args) {
read("config.xml");
}
public static List<BaseConnBean> read(){
return read("config.xml");
} public static List<BaseConnBean> read(String path) {
String rpath = ConfigXml.class.getResource("").getPath().substring(1)
+ path;
FileInputStream fi = null;
List<BaseConnBean> pools=new ArrayList<BaseConnBean>();
try {
fi = new FileInputStream(rpath);
SAXBuilder sb = new SAXBuilder();
Document doc = sb.build(fi);
Element root = doc.getRootElement();
List list=root.getChildren();
Element pool = null;
Iterator allPool = list.iterator();
while(allPool.hasNext()){
pool=(Element) allPool.next();
BaseConnBean bcBean=new BaseConnBean();
bcBean.setName(pool.getChildText("name"));
bcBean.setUsername(pool.getChildText("username"));
bcBean.setPassword(pool.getChildText("password"));
bcBean.setJdbcurl(pool.getChildText("jdbcurl"));
try{
bcBean.setMax(Integer.parseInt(pool.getChildText("max")));
}catch(NumberFormatException e){
bcBean.setMax(0);
}
try{
bcBean.setWait(Long.parseLong(pool.getChildText("wait")));
}catch(NumberFormatException e){
bcBean.setWait(-1L);
}
bcBean.setDriver(pool.getChildText("driver"));
pools.add(bcBean);
}
} catch (FileNotFoundException e) {
System.out.println("file does not find");
e.printStackTrace();
} catch (JDOMException e) {
System.out.println("jdom exception");
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return pools;
}
}
Java代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import com.cgogo.mymodel.po.BaseConnBean;
import com.cgogo.mymodel.util.ConfigXml;
public class MyDbPool {
public static void main(String[] args) {
}
private static String dbJdbc = null;
private static String dbUser = null;
private static String dbPwd = null;
private static int max;
private static long wait;
private static String driver = null;
private static Class driverClass = null;
private static ObjectPool connectionPool = null;
public static Map<String, ObjectPool> map = null;
public MyDbPool() {
}
/** *//**
* 初始化数据源
*/
private static synchronized void initDataSource() {
// 驱动数据源
if (driverClass == null) {
try {
driverClass = Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
/** *//**
* 连接池启动
*
* @throws Exception
*/
public static void StartPool(String poolname, String dbJdbc, String dbUser,
String dbPwd, int max, long wait) {
// 初始化数据源
initDataSource();
// 如果连接池为空
if (connectionPool != null) {
ShutdownPool();
}
try {
connectionPool = new GenericObjectPool(null, max, (byte) 1, wait);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
dbJdbc, dbUser, dbPwd);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
connectionFactory, connectionPool, null, null, false, true);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool(poolname, connectionPool);
map.put(poolname, connectionPool);
System.out.println("Create " + poolname
+ " for Database Connection Succees.");
} catch (Exception e) {
e.printStackTrace();
}
}
/** *//**
* 释放连接池
*/
public static void ShutdownPool() {
try {
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
driver.closePool("dbpool");
} catch (SQLException e) {
e.printStackTrace();
}
}
/** *//**
* 打印连接池状态
*/
public static String GetPoolStats(String poolname) {
StringBuffer stat = new StringBuffer();
try {
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
ObjectPool connectionPool = driver.getConnectionPool(poolname);
stat.append("-- Active Connection: ");
stat.append(connectionPool.getNumActive());
stat.append(" ,");
stat.append("Free Connection: ");
stat.append(connectionPool.getNumIdle());
stat.append(" . --");
} catch (Exception e) {
e.printStackTrace();
}
return stat.toString();
}
/** *//**
* 取得连接池中的连接
*
* @return
*/
public synchronized static Connection getDbConnection(String poolname) {
Connection conn = null;
if (map == null) {
System.out.println("map null");
map = new HashMap<String, ObjectPool>();
}
if (map.get(poolname) == null) {
init(poolname);// 初始化基本数据
StartPool(poolname, dbJdbc, dbUser, dbPwd, max, wait);
}
try {
conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:"
+ poolname);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private static void init(String poolname) {
List<BaseConnBean> pools = ConfigXml.read();
for (BaseConnBean baseConnBean : pools) {
if (baseConnBean.getName().equals(poolname)) {
dbJdbc = baseConnBean.getJdbcurl();
dbUser = baseConnBean.getUsername();
dbPwd = baseConnBean.getPassword();
max = baseConnBean.getMax();
wait = baseConnBean.getWait();
driver = baseConnBean.getDriver();
}
}
}
public static void close(Connection c) {
try {
if (c != null)
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;import com.cgogo.mymodel.po.BaseConnBean;
import com.cgogo.mymodel.util.ConfigXml;public class MyDbPool {
public static void main(String[] args) { } private static String dbJdbc = null; private static String dbUser = null; private static String dbPwd = null; private static int max; private static long wait; private static String driver = null; private static Class driverClass = null; private static ObjectPool connectionPool = null; public static Map<String, ObjectPool> map = null; public MyDbPool() {
} /** *//**
* 初始化数据源
*/
private static synchronized void initDataSource() {
// 驱动数据源
if (driverClass == null) {
try {
driverClass = Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
} /** *//**
* 连接池启动
*
* @throws Exception
*/
public static void StartPool(String poolname, String dbJdbc, String dbUser,
String dbPwd, int max, long wait) {
// 初始化数据源
initDataSource();
// 如果连接池为空
if (connectionPool != null) {
ShutdownPool();
}
try {
connectionPool = new GenericObjectPool(null, max, (byte) 1, wait);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
dbJdbc, dbUser, dbPwd);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
connectionFactory, connectionPool, null, null, false, true);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool(poolname, connectionPool);
map.put(poolname, connectionPool);
System.out.println("Create " + poolname
+ " for Database Connection Succees.");
} catch (Exception e) {
e.printStackTrace();
}
}
/** *//**
* 释放连接池
*/
public static void ShutdownPool() {
try {
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
driver.closePool("dbpool");
} catch (SQLException e) {
e.printStackTrace();
}
} /** *//**
* 打印连接池状态
*/
public static String GetPoolStats(String poolname) { StringBuffer stat = new StringBuffer();
try {
PoolingDriver driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
ObjectPool connectionPool = driver.getConnectionPool(poolname); stat.append("-- Active Connection: ");
stat.append(connectionPool.getNumActive());
stat.append(" ,");
stat.append("Free Connection: ");
stat.append(connectionPool.getNumIdle());
stat.append(" . --");
} catch (Exception e) {
e.printStackTrace();
}
return stat.toString();
} /** *//**
* 取得连接池中的连接
*
* @return
*/
public synchronized static Connection getDbConnection(String poolname) {
Connection conn = null;
if (map == null) {
System.out.println("map null");
map = new HashMap<String, ObjectPool>();
}
if (map.get(poolname) == null) {
init(poolname);// 初始化基本数据
StartPool(poolname, dbJdbc, dbUser, dbPwd, max, wait);
}
try {
conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:"
+ poolname);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
} private static void init(String poolname) {
List<BaseConnBean> pools = ConfigXml.read();
for (BaseConnBean baseConnBean : pools) {
if (baseConnBean.getName().equals(poolname)) {
dbJdbc = baseConnBean.getJdbcurl();
dbUser = baseConnBean.getUsername();
dbPwd = baseConnBean.getPassword();
max = baseConnBean.getMax();
wait = baseConnBean.getWait();
driver = baseConnBean.getDriver();
} }
} public static void close(Connection c) {
try {
if (c != null)
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}}
至此,基本工作已经完成,需要链接,只要MyDbPool.getDbConnection("name1"),就可以得到一个链接,注意,用完需要归还下多线程测试例子
Java代码
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test1 extends Thread{
private String sql;
private String poolname;
private Connection c;
public Test1(String poolname,Connection c,String sql){
this.poolname=poolname;
this.c=c;
this.sql=sql;
}
public static void main(String[] args) {
for(int i=0;i<100;i++){
String sql="select * from table1 limit "+i+",1";
new Test1("datamininfo",MyDbPool.getDbConnection("datamininfo"),sql).start();
sql="select * from table2 limit "+i+",1";
new Test1("uic",MyDbPool.getDbConnection("uic"),sql).start();
}
}
public void run(){
try {
ResultSet rs=c.createStatement().executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString(1));
}
if(poolname.equals("datamininfo"))
System.out.println("DataMinInfo:\n"+MyDbPool.GetPoolStats("name1"));
else
System.out.println("UIC:\n"+MyDbPool.GetPoolStats("name2"));
} catch (SQLException e) {
e.printStackTrace();
}finally{
MyDbPool.close(c);
}
}
}
如果你jdbc连接数据库的地方写得没有问题,那么看一下是不是第一个数据库连接池对后面的jdbc获取连接造成了影响。你的第一个连接池可能保持着许多物理连接,但是jdbc获取的时候无法利用这些连接。观察一下数据库的连接使用情况吧
jdbc获取连接本来就慢的,建议楼主还是把后者也弄到连接池里面吧,比如配两个连接池。看你什么数据库,oracle里面可以用toad看session。