数据库连接池的实现(含等待处理)

这是一个数据库连接池实现的例子，简单实现，含连接等待处理。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import org.lt.cj.config.ServerProperty;

//代理连接类
public class MyDataSource {

    private static MyDataSource instance = new MyDataSource();
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/tmall";
    private static String user = "root";
    private static String password = "123456";
    private static int initCount = 3; // 初始化连接数
    private static int maxCount = 10; // 最大连接数
    private static int timeout = 100; //连接用完后再次获取连接间隔时间(毫秒)
    int currentCount = 0; // 当前连接数
    // LinkedList方便入列出列操作，性能比ArrayList好
    private final LinkedList<Connection> connectionsPool = new LinkedList<Connection>();

    public MyDataSource() {
        try {
            for (int i = 0; i < this.initCount; i++) {
                // 把初始化的连接对象存放到链表里面
                this.connectionsPool.addLast(this.createConnection());
                this.currentCount++;
            }
        } catch (SQLException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static MyDataSource getInstance() {
        return instance;
    }

    //静态内部类在没有新建对象的情况下也可以加载
    static {
        ServerProperty sp = new ServerProperty();
        driver = sp.getValue("driver");
        url = sp.getValue("databaseUrl");
        user = sp.getValue("username");
        password = sp.getValue("password");
        initCount = Integer.parseInt(sp.getValue("initCount"));
        maxCount = Integer.parseInt(sp.getValue("maxCount"));
        timeout = Integer.parseInt(sp.getValue("timeout"));
        sp.closeI();

        try {
            Class.forName(driver);// 加载驱动程序，供调用，只执行一次
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public Connection getConnection() {
        synchronized (connectionsPool) {
            if (this.connectionsPool.size() > 0) { // 从链表里面删除头一个连接对象，并返回该连接对象
                return this.connectionsPool.removeFirst();
            }
            if (this.currentCount < maxCount) {
                try {
                    this.currentCount++;
                    return this.createConnection();
                } catch (SQLException ex) {
                    return null;  //无法创建连接，返回空
                }
            }
            return getConnection(timeout);
        }
    }

    /**
     *   根据指定延时取得一个连接
     */
    public synchronized Connection getConnection(long timeout) {
        Connection con = null;
        while ((con = getConnection()) == null) {
            try {
                Thread.sleep(timeout);
            } catch (InterruptedException e) {
            }
        }
        return con;
    }

    // 释放连接，把当前连接加到链表尾,并没有真正关闭
    public void free(Connection conn) {
        synchronized (connectionsPool) {
            this.connectionsPool.addLast(conn);
        }
    }

    // 父类引用指向子类对象，生成一个实现对象供使用
    private Connection createConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }
}

配置文件读取类：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class ServerProperty {

    private Properties prop = null;
    private InputStream in = null;

    public ServerProperty() {
        initProperty();
    }

    public ServerProperty(String path) {
        try {
            prop = new Properties();
            in = new FileInputStream(path);
        } catch (FileNotFoundException ex) {
        }
    }

    private void initProperty() {
        try {
            prop = new Properties();
            in = new FileInputStream("conf/database.properties");
        } catch (FileNotFoundException ex) {
        }
    }

    private void setIs() {
        try {
            if (in == null) {
                in = new FileInputStream("conf/database.properties");
            }
        } catch (IOException ex) {
        }
    }

    public Properties getProperty() {
        try {
            setIs();
            prop.load(in);
        } catch (IOException ex) {
        }
        return prop;
    }

    public String getValue(String key) {
        String value = "";
        try {
            setIs();
            prop.load(in);
            value = prop.getProperty(key);
        } catch (IOException ex) {
        }
        return value;
    }

    public void closeI() {
        try {
            if (in != null) {
                in.close();
            }
        } catch (IOException ex) {
        }
    }
}

工程项目目录下的文件：conf/database.properties
内容：

#Oracle config#
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@192.168.0.23:1521:ora10g
#user=openlab
#password=open123

#Mysql config#
driver=com.mysql.jdbc.Driver
databaseUrl=jdbc:mysql://127.0.0.1:3306/tmall
username=root
password=123456
#初始连接数
initCount=3
#最大连接数
maxCount=20
#连接用完后再次获取连接间隔时间(毫秒)
timeout=100

调用：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

public class CollectImpl implements ICollectDao {

    private static final MyDataSource ds = new MyDataSource();
    private static Connection conn = null;

    public Connection getConnection() {
        return (Connection) ds.getConnection(); 
    }

    //释放操作
    public void closePsRs(PreparedStatement ps, ResultSet rs) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
        }
    }

    public void free(Connection conn) {
        ds.free(conn);
    }

    //对分类表进行操作
    public void insertCategory(String name, int firstCate, int secondCate, String lanFrom, String toLan, int updated) {
        conn = getConnection();
        System.out.println("---------" + conn);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `" + lanFrom + "_category` (`name`,`first_cate`,`second_cate`,`orig_lan`,`to_lan`,`updated`) VALUES(?,?,?,?,?,?)";
        try {
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setInt(2, firstCate);
            ps.setInt(3, secondCate);
            ps.setString(4, lanFrom);
            ps.setString(5, toLan);
            ps.setInt(6, updated);
            ps.executeUpdate();
        } catch (SQLException ex) {
        } finally {
            closePsRs(ps, rs);
            free(conn);
        }
    }
}