读书人
栏目▼

写了一个简单的数据库连接池请帮忙看

发布时间： 2012-12-20 09:53:21 作者： rapoo

写了一个简单的数据库连接池，请帮忙看一下
我是一个新手，第一次发帖，以前都是看别人的帖子。
我写了一个简单的数据库连接池实现，感觉有很多欠缺的地方，希望各位指点一下。十分感谢。
有四个类：
(1)连接池配置对象 Config
(2)连接池主类 ConnectionPool
(3)Properties读取类 PropertyReader
(4)异常类 DBPoolException


ConnectionPool.java

  public class ConnectionPool {private static Logger logger = Logger.getLogger(ConnectionPool.class);//当前已用连接数private static volatile int curConnections = 0;private static Config config = null;//初始化成功标志private static boolean initFlag = false;private static volatile Stack<Connection> conns;static {PropertyReader pr = null;try {pr = new PropertyReader("pool-config.properties");config = new Config();//设置数据库驱动config.setDriver(pr.getValue("driver"));//urlconfig.setUrl(pr.getValue("url"));//uernameconfig.setUsername(pr.getValue("username"));//passwordconfig.setPassword(pr.getValue("password"));//最大连接数if(pr.getValue("maxConnections") != null){config.setMaxConnections(Integer.valueOf(pr.getValue("maxConnections")));}//初始化时最小连接数if(pr.getValue("minConnections") != null){config.setMinConnections(Integer.valueOf(pr.getValue("minConnections")));}//返还连接时是否提交if(pr.getValue("autoCommitOnClose") != null){config.setAutoCommitOnClose(Boolean.valueOf(pr.getValue("autoCommitOnClose")));}//当连接池用完时客户端调用getConn()后等待获取新连接的时间 //Default: (100毫秒)if(pr.getValue("checkoutTimeout") != null){config.setCheckoutTimeout(Integer.valueOf(pr.getValue("checkoutTimeout")));}//当没有可用链接时，尝试获取链接的次数if(pr.getValue("checkTimes") != null){config.setCheckTimes(Integer.valueOf(pr.getValue("checkTimes")));}initPool();} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}/** * 隐藏构造函数 */private ConnectionPool(){}/** * 初始化连接池 保存minConnections个链接 * @throws SQLException * @throws ClassNotFoundException  */private static synchronized void initPool() throws SQLException, ClassNotFoundException{conns = new Stack<Connection>();Class.forName(config.getDriver());for(int i = 0 ; i < config.getMinConnections() ; i++){Connection conn = newConnection();conns.push(conn);}initFlag = true;}/** * 获取一个可用链接 * @return * @throws SQLException  * @throws InterruptedException  * @throws DBPoolException  * @throws Exception */public static Connection getConn() throws SQLException, InterruptedException, DBPoolException  {Connection conn = null;if (initFlag) {synchronized (conns) {// 循环次数int times = 0;while (null == conn && times < config.getCheckTimes() + 1) {times++;// 如果未达到最大链接if (curConnections < config.getMaxConnections()) {// 栈中未空if (!conns.isEmpty()) {conn = conns.pop();// 如果返回的链接不可用if (null == conn || conn.isClosed()) {conn = newConnection();}// 栈中空了} else {conn = newConnection();}} else {conns.wait(config.getCheckoutTimeout());}}if(null == conn){logger.warn("获取链接超时!!!!!");throw new DBPoolException("获取链接超时!!!!!");}else{curConnections++;conns.notifyAll();}}} else {logger.error("连接池初始化失败!!!!");throw new DBPoolException("连接池初始化失败!!!!");}return conn;}/** * 归还一个链接 * @param conn * @throws SQLException * @throws InterruptedException */public static void returnConn(Connection conn) throws SQLException, InterruptedException {synchronized (conns) {if (null != conn && !conn.isClosed()) {// 如果设置归还前自动提交为真if (config.isAutoCommitOnClose()) {conn.commit();} else {conn.rollback();}}int times = 0;//尝试归还5次 如果归还失败则关闭连接while (times < 6) {times++;if (curConnections > 0 && curConnections <= config.getMaxConnections()) {conns.push(conn);curConnections--;break;} else if (curConnections == 0) {conns.wait(1000);} else {if(conn!=null && !conn.isClosed())conn.close();curConnections--;break;}}if(times == 5 && conn != null && !conn.isClosed()){conn.close();}conns.notifyAll();}}/** * 简单的创建一个链接 * @return * @throws SQLException */private static Connection newConnection() throws SQLException{return DriverManager.getConnection(config.getUrl(),config.getUsername(),config.getPassword());}/** * 获取已使用的连接数 * @return */public static int getCurConnections() {return curConnections;}}


Config.java
public class Config {private String driver;private String url;private String username;private String password;private int minConnections = 10;private int maxConnections = 20;private boolean autoCommitOnClose = true;private int checkoutTimeout = 100;private int checkTimes = 10;public String getDriver() {return driver;}public void setDriver(String driver) {this.driver = driver;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public int getMinConnections() {return minConnections;}public void setMinConnections(int minConnections) {this.minConnections = minConnections;}public int getMaxConnections() {return maxConnections;}public void setMaxConnections(int maxConnections) {this.maxConnections = maxConnections;}public boolean isAutoCommitOnClose() {return autoCommitOnClose;}public void setAutoCommitOnClose(boolean autoCommitOnClose) {this.autoCommitOnClose = autoCommitOnClose;}public int getCheckoutTimeout() {return checkoutTimeout;}public void setCheckoutTimeout(int checkoutTimeout) {this.checkoutTimeout = checkoutTimeout;}public int getCheckTimes() {return checkTimes;}public void setCheckTimes(int checkTimes) {this.checkTimes = checkTimes;}}



PropertyReader.java
public class PropertyReader {Logger log = Logger.getLogger(PropertyReader.class);Properties props = null;public PropertyReader(String path) throws IOException{props = new Properties();try {props.load(getClass().getClassLoader().getResourceAsStream(path));} catch (IOException e) {log.error("读取properties失败");throw e;}}public String getValue(String key){return props.getProperty(key);}}



DBPoolException .java
public class DBPoolException extends Throwable {public DBPoolException(String str){super(str);}}



properties配置文件
driver=oracle.jdbc.driver.OracleDriverurl=jdbc:oracle:thin:@localhost:1521:orclusername=testpassword=passwordminConnections=10maxConnections=30#当链接关闭的时候自动将未提交的内容提交autoCommitOnClose=false#当连接池用完时客户端调用getConn()后等待获取新连接的时间,Default: (100毫秒)checkoutTimeout=100#当没有可用链接时，尝试获取链接的次数checkTimes=30
// useableLL.size() + ",正在使用的为" + usingLL.size());
}
} catch (SQLException e) {
e.printStackTrace();
usingLL.remove(con);
conLL.remove(con);
}

}

public void run() {
if ((float) usingLL.size() / (float) conLL.size() < 0.4) {
if (((new java.util.Date()).getTime() - lastDate.getTime()) > 5 * 60 * 1000) {
batchClose((int) ((max - min) * (1 - (float) usingLL.size()
/ (float) conLL.size())));
}
} else {
lastDate = new java.util.Date();
}

if (conLL.size() > 0) {
while (useableLL.size() < 2 && conLL.size() < max) {
batchCreate(5);
}
} else {
batchCreate(min);
}

}

private void batchClose(int count) {
// System.out.println("自动关闭连接前共有"+ conLL.size() + "个连接，可用的为" +
// useableLL.size() + ",正在使用的为" + usingLL.size());
Connection con = null;
for (int i = 0; i < count; i++) {
if (useableLL.size() > min) {
con = (Connection) useableLL.getLast();
useableLL.remove(con);
conLL.remove(con);
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// System.out.println("自动关闭后连接前共有"+ conLL.size() + "个连接，可用的为" +
// useableLL.size() + ",正在使用的为" + usingLL.size());
}

private void batchCreate(int count) {
for (int i = 0; i < count; i++) {
Connection con = createConnection(db);
try {
if (con != null && !con.isClosed()) {
conLL.add(con);
useableLL.add(con);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

private static Connection createConnection(DataBase db) {
try {
Class.forName(db.getDriver());
Connection con = java.sql.DriverManager.getConnection(db.getDburl(), db.getUser(), db.getPwd());
return con;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}

private String[] dbdrivers = {
"oracle.jdbc.driver.OracleDriver",
"com.microsoft.jdbc.sqlserver.SQLServerDriver",
"org.gjt.mm.mysql.Driver",
"com.ibm.db2.jdbc.app.DB2Driver",
"com.sybase.jdbc.SybDriver",
"net.sourceforge.jtds.jdbc.Driver"
};

private String[] dburls = {
"jdbc:oracle:thin:@",
"jdbc:microsoft:sqlserver://",
"jdbc:mysql://",
"jdbc:db2://",
"jdbc:sybase:Tds:",
"jdbc:jtds:sqlserver://"
};

private String[] dbports = { "1521", "1433", "3306", "5000", "5007","1433" };

private int checktype(String adbtype) {
int check = -1;
for(int i=0;i<dbtypes.length;i++)
{if(adbtype.equals(dbtypes[i]))
{
check=i;
break;
}
}
return check;
}
public String getDriver() {
int index=checktype(this.typeName);
return dbdrivers[index];
}

public String getDburl() {
int index=checktype(this.typeName);
String dburl="";
switch(index){
case 0:{
dburl=dburls[index]+ip+":"+port+":"+name;
break;
}
case 1:{
dburl=dburls[index]+ip+":"+port+";DatabaseName="+name;
break;
}
case 2:{
dburl=dburls[index]+ip+"/"+name;
break;
}
case 3:{
dburl=dburls[index]+ip+":"+port+"/"+name;
break;
}
case 4:{
dburl=dburls[index]+ip+":"+port+"/"+name;
break;
}
case 5:{
dburl = dburls[index] + ip + ":" + port + "/" + name;
break;
}
}
return dburl;
}
public String getId() {
return id;
}

public void setId(String id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getTypeName() {
return typeName;
}

public void setTypeName(String typeName) {
this.typeName = typeName;
}

public String getPort() {
return port;
}

public void setPort(String port) {
this.port = port;
}

public String getUser() {
return user;
}

public void setUser(String user) {
this.user = user;
}

public String getIp() {
return ip;
}

public void setIp(String ip) {
this.ip = ip;
}

public String getPwd() {
return pwd;
}

public void setPwd(String pwd) {
this.pwd = pwd;
}


}
//for (int i = 0, n = r.getAttributeCount(); i < n; ++i)
//System.out.println("Attribute: "+ r.getAttributeName(i) + "="+ r.getAttributeValue(i));
break;
}
case XMLStreamConstants.CHARACTERS: {
if (r.isWhiteSpace())
break;
if(pos==2){
VALUE=VALUE+r.getText();
}
//System.out.println("Text: " + r.getText());
break;
}
case XMLStreamConstants.END_ELEMENT: {
NAME=r.getName().toString();
if("DB".equalsIgnoreCase(NAME)){
al.add(db);
pos--;
}else if(pos==2){
if("IP".equalsIgnoreCase(NAME)){
db.setIp(VALUE);
}else if("id".equalsIgnoreCase(NAME)){
db.setId(VALUE);
}else if("PORT".equalsIgnoreCase(NAME)){
db.setPort(VALUE);
}else if("PWD".equalsIgnoreCase(NAME)){
db.setPwd(VALUE);
}else if("typeName".equalsIgnoreCase(NAME)){
db.setTypeName(VALUE);
}else if("user".equalsIgnoreCase(NAME)){
db.setUser(VALUE);
}else if("name".equalsIgnoreCase(NAME)){
db.setName(VALUE);
}

}
VALUE=null;
//System.out.println("End Element:" + r.getName());
break;
}
case XMLStreamConstants.END_DOCUMENT: {
//System.out.println("End Document.");
pos--;
break;
}
}
try {
if (!r.hasNext())
break;
event = r.next();
} catch (XMLStreamException e) {
e.printStackTrace();
}
}
} finally {
try {
r.close();
} catch (XMLStreamException e) {
e.printStackTrace();
}
}

}
public void save(){

}
public static void main(String[] arg){
DBConfigReader r=new DBConfigReader();
List l=r.getList();
int t=0;
t++;
}

}
31 楼 qqggcc 2011-03-14 <DBConfig>
<DB>
<id>HBSK</id>
<name>dbname</name>
<typeName>jtds</typeName>
<ip>192.168.0.16</ip>
<port>1433</port>
<user>sa</user>
<pwd>sa123</pwd>
</DB>
<DBConfig> 32 楼 wendal 2011-03-14 没有复杂功能的连接池实现,都很快的.

以前一个简单的连接池实现,
http://myblog.sunfarms.net/243.html

真正的连接池,都是需要使用Connection/Statement/ResultSet接口等一系列接口的. 33 楼 yy59750901 2011-03-14 qqggcc 写道<DBConfig>
<DB>
<id>HBSK</id>
<name>dbname</name>
<typeName>jtds</typeName>
<ip>192.168.0.16</ip>
<port>1433</port>
<user>sa</user>
<pwd>sa123</pwd>
</DB>
<DBConfig>
辛苦你了 我去研究一下 34 楼 ymkyve 2011-03-14 javaso 写道实现DataSource

头像把偶误导了,不过一语道破 35 楼 nianien 2011-03-15 yy59750901 写道/**
* 获取一个可用链接
* @return
* @throws SQLException
* @throws InterruptedException
* @throws DBPoolException
* @throws Exception
*/
public static Connection getConn() throws SQLException, InterruptedException, DBPoolException {
Connection conn = null;
if (initFlag) {
synchronized (conns) {
// 循环次数
int times = 0;
while (null == conn && times < config.getCheckTimes() + 1) {
times++;
// 如果未达到最大链接
if (curConnections < config.getMaxConnections()) {
// 栈中未空
if (!conns.isEmpty()) {
conn = conns.pop();
// 如果返回的链接不可用
if (null == conn || conn.isClosed()) {
conn = newConnection();
}
// 栈中空了
} else {
conn = newConnection();
}
} else {
conns.wait(config.getCheckoutTimeout());
}

}
if(null == conn){
logger.warn("获取链接超时!!!!!");
throw new DBPoolException("获取链接超时!!!!!");
}else{
curConnections++;
conns.notifyAll();
}

}
} else {
logger.error("连接池初始化失败!!!!");
throw new DBPoolException("连接池初始化失败!!!!");
}

return conn;
}




你获取连接的逻辑不对吧?我之前也写过连接池,但是感觉很不靠谱,有些地方的实现需要挺复杂的技术来优化,说说我的几点看法吧:
第一,你的等待超时根本不是用时间来衡量的,你的getCheckTimes只不过一个计数器而已
第二,因为用的synchronized的关键字进行同步,那么等待超时就应该启动一个线程来监听,
否则的话,当多个请求同时到达的话,只能对一个请求计时,造成超长等待的问题
第三,对于从线程获取的数据库连接,你需要重写其close方法和isClosed()方法,以及其他依赖于close方法的方法
这里可以我觉得可以使用代理模式来实现
第四,释放数据库连接需要同步么?我觉得同步会造成不必要的阻塞,一般来说获取资源同步,释放资源我觉得不必要,因为我们每次取资源的时候会进行校验,当然也可能有其他的问题,只是我没想到
第五,是否需要一个监听进程来动态维护线程池的数量?总是当线程太满或者太空都是浪费资源 36 楼 xika.xiang 2011-03-15 学习了原理，对连接池还还没深入过 37 楼 rhythm_01 2011-03-21 kimmking 写道个人觉得，一个数据库连接池，必须是非侵入性的，和jdbc本身兼容。

即我只需要把jdbc/datasource配置改成连接池的设置，
原来用jdbc写的代码什么都不用改。

比如只换掉一个jdbc driver类。

2楼的意思应该就是实现DataSource，这样程序里使用时，或者说使用Hibernate、Spring来配置连接池时可以不用修改。

不过我觉得这个也是没必要的，真要是想用连接池可以使用第三方的，现在有很多成熟的，但是这种连接池的设计思路可以学习借签。

读书人网 >其他数据库

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net