java数据库连接池 V1.0

package com.mdj.dmdatabase.test; 

import java.io.IOException; 
import java.sql.Connection; 
import java.sql.SQLException; 
import java.util.List; 
import java.util.Map; 

import com.mdj.dmdatabase.DmDriverManager.DatabaseManager; 
import com.mdj.dmdatabase.javabean.Guest; 

/** 
* @since <a href="http://blog.csdn.net/mak0000">Connectionpool 
*        EasyconnectionPool</a> 
* @author 武汉软件工程职业学院<br> 
*         孟德军<br> 
*         2009-01-01<br> 
*         封装了Dbutils工具<br> 
*         的部分细节 
* @version 2.0 
* 
*/ 
public class Test { 
    /** 
     * @see #manager DatabaseManager类的实例. 
     */ 
    private DatabaseManager manager; 
    /** 
     * @see #con <p bgcolor="#9acd32">java.sql.Connection con Connection实例</p> 
     */ 
    private Connection con = null; 

    public Test(String path) throws IOException, SQLException { 
         
     
        manager = DatabaseManager.getinstance(path); 
        // con = manager.getConnection(); 
        /** 
         * 此处书写代码. 查询数据无需用connection接收 
         */ 
        /*int a=manager.generalUpdate("insert into guestbook(author,title,ip,updatetime,content) values('王红梅','你过得怎么样','192.168.18.1','2009-01-04','你过得怎么样')"); 
        System.out.println(a);*/ 
        Map map = manager.mapexecuteQuery("select * from guestbook"); 
        for (int i = 0; i < map.size(); i++) { 
            System.out.println(map.get("author") + " " + map.get("title") + " " 
                    + map.get("ip") + map.get("updatetime") + " " 
                    + map.get("content")); 
        } 
        List list = manager.listexecuteQuery("select * from guestbook", 
                Guest.class); 
        Guest guestbook = null; 
        for (int i = 0; i < list.size(); i++) { 
            guestbook = (Guest) list.get(i); 
            System.out 
                    .println(guestbook.getAuthor() + "\t" 
                            + guestbook.getTitle() + "\t" + guestbook.getIp() 
                            + "\t" + guestbook.getUpdatetime() + "\t" 
                            + guestbook.getContent()); 
        } 
         
         
         manager.releaseConnection(con); manager.closeConnectionPool(); 
          

    } 
     
    public static void main(String[] args) { 

        String path = System.getProperty("user.dir") + "\\src\\sysconfig.xml"; 
        try { 
            new Test(path); 
        } catch (SQLException e) { 

        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
    } 

} 

配置文件

<Context>
<Resource name="DMdatabase" type="javax.sql.DataSource"
   auth="Container" driverClassName="dm.jdbc.driver.DmDriver"
   url="jdbc:dm://localhost:12345/guest" username="SYSDBA"
   password="SYSDBA" maxActive="10" maxIdle="5" maxWait="6000" delaytime="10" logpath="..//poolserver.log" logsize="1024" />
</Context>
<!--
1.0使用的配置文件，已被以上内容替代.
<?xml version="1.0" encoding="UTF-8"?>
<driver xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<dbmsdriver name="华工达梦数据库系统">
<driverclass>dm.jdbc.driver.DmDriver</driverclass>
<url>jdbc:dm://localhost:12345/guest</url>
<username>SYSDBA</username>
<password>123456</password>
<maxconnection>8</maxconnection>
<minconnection>1</minconnection>
<maxwait>5</maxwait>
<logpath>..//poolserver.log</logpath>
</dbmsdriver>
</driver>
-->
<!--
<Context>
.........................................
</Context>
-->
<!--实例数据库
create database guest datafile 'd:\guest\guest.dbf' size 35;
set current database guest;
create table guestbook(author varchar(16) not null,title varchar(30),ip varchar(16),updatetime date,content text);
insert into guestbook(author,title,ip,updatetime,content)values('孟德军','达梦数据库测试','192.168.18.1','2009-01-05','示例数据库');
select * from guestbook;
-->

name="DMdatabase"
type="javax.sql.DataSource"
auth="Container"
driverClassName="dm.jdbc.driver.DmDriver"
url="jdbc:dm://localhost:12345/guest"
username="SYSDBA"
password="SYSDBA"
maxActive=4
maxIdle=2
maxWait=6000
delaytime=10
logpath="..//poolserver.log"
logsize=1024

 接口

java 数据库连接池(接口)2009-05-08 06:17 P.M.

package com.mdj.dmdatabase.develop; 

import java.io.PrintWriter; 
import java.io.Serializable; 
import java.io.UnsupportedEncodingException; 
import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.util.List; 
import java.util.Map; 
import java.util.Vector; 

import javax.sql.DataSource; 

/** 
* @see Connectionpool EasyconnectionPool 
* @author 武汉软件工程职业学院<br> 
*         孟德军<br> 
*         2009-01-01<br> 
*         封装了Dbutils工具<br> 
*         的部分细节 
* @version 2.0 
*/ 
public interface DmDataSource extends DataSource, Serializable { 

    /** 
     * @see #resultset 存放数据库的表信息. 
     */ 
    ResultSet resultset = null; 
    /** 
     * @see #con 数据库连接 
     */ 
    Connection con = null; 
    /** 
     * @see #pool 连接池容器.可自行选择。 
     */ 
    Vector<Connection> pool = null; 
    /** 
     * @see #driverclass 数据库驱动类 
     */ 
    String driverclass = null; 
    /** 
     * @see #uername 数据库用户名 
     */ 
    String uername = null; 
    /** 
     * @see #password 数据库密码 
     */ 
    String password = null; 
    /** 
     * @see #url 连接数据库url 
     */ 
    String url = null; 
    /** 
     * @see #filepath 配置文件路径. 
     */ 
    String filepath = null; 
    /** 
     * @see #logpath 日志文件路径. 
     */ 
    String logpath = null; 
    /** 
     * @see #maxwaittime 允许等待时间 
     */ 
    int maxwaittime = 0; 
    /** 
     * @see #delaytime 延迟时间 
     */ 
    int delaytime = 0; 
    /** 
     * @see #maxconnection 最大连接 
     */ 
    int maxconnection = 0; 
    /** 
     * @see #minconnection 最小连接 
     */ 
    int minconnection = 0; 
    /** 
     * @see #poolsize 连接池大小. 
     */ 
    int poolsize = 0; 

    public void closeConnectionPool() throws SQLException; 
    /** 
     * @param username 数据库用户名<br>password 数据库密码 
     * @deprecated 建议使用getconnection() 
     * @see #getConnection() 参见 
     */ 
    public Connection getConnection(String username, String password) 
            throws SQLException; 
    /** 
     * @see #getConnection() 无用户名和密码，以在配置文件中配置。 
     */ 
    public Connection getConnection() throws SQLException; 

    public PrintWriter getLogWriter() throws SQLException; 

    public int getLoginTimeout() throws SQLException; 

    public void releaseConnection(Connection con); 

    public void setLogWriter(PrintWriter out) throws SQLException; 

    public void setLoginTimeout(int seconds) throws SQLException; 

    public Map mapexecuteQuery(String sql) throws SQLException; 

    public List listexecuteQuery(String sql, Class beanclass) 
            throws SQLException; 

    public ResultSet getTables(String catalog, String schemapattern, 
            String tablenamepattern, String[] types) throws SQLException; 

    public int getJDBCMajorVersion() throws SQLException; 

    public int generalUpdate(String sql) throws SQLException; 

    public ResultSet generalQuery(String sql) throws SQLException; 

    public String toChinese(String str, String oldcharsetName, 
            String newcharsetName) throws UnsupportedEncodingException; 
}
 

 

实现

package com.mdj.dmdatabase.DmDriverManager;

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.FileWriter; 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.PrintWriter; 
import java.io.UnsupportedEncodingException; 
import java.sql.Connection; 
import java.sql.DatabaseMetaData; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.Date; 
import java.util.List; 
import java.util.Map; 
import java.util.Properties; 
import java.util.Timer; 
import java.util.TimerTask; 
import java.util.Vector;

import javax.xml.parsers.DocumentBuilder; 
import javax.xml.parsers.DocumentBuilderFactory; 
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.dbutils.DbUtils; 
import org.apache.commons.dbutils.QueryRunner; 
import org.apache.commons.dbutils.handlers.BeanListHandler; 
import org.apache.commons.dbutils.handlers.MapListHandler; 
import org.w3c.dom.Document; 
import org.w3c.dom.Element; 
import org.w3c.dom.NodeList; 
import org.xml.sax.SAXException;

import com.mdj.dmdatabase.pool.Dmconnection;

/** 
* @see Connectionpool EasyconnectionPool 
* @author <hr><p bgcolor="#9acd32">武汉软件工程职业学院</p> 
*         孟德军<br> 
*         2009-01-03<br> 
*         封装了Dbutils<br> 
*         工具的部分细节 
* 
* @version 2.0 
*/ 
public class DatabaseManager extends Dmconnection {

    /** 
     * 
     */ 
    private static final long serialVersionUID = 1L; 
    /** 
     * @see #databasemetaData 数据源. 
     */ 
    private DatabaseMetaData databasemetaData; 
    /** 
     * @see #resultset 存放数据库的表信息. 
     */ 
    private ResultSet resultset = null; 
    /** 
     * @see #map 存放mapexecuteQuery查询结果 
     */ 
    private Map map = null; 
    /** 
     * @see #list 存放listexecuteQuery查询结果 
     */ 
    private List list = null; 
    /** 
     * @see #con 数据库连接 
     */ 
    private Connection con = null; 
    /** 
     * @see #pool 连接池容器.可自行选择。 
     */ 
    private Vector<Connection> pool = null; 
    /** 
     * @see #driverclass 数据库驱动类 
     */ 
    private String driverclass = "dm.jdbc.driver.DmDriver"; 
    /** 
     * @see #uername 数据库用户名 
     */ 
    private String username = "SYSDBA"; 
    /** 
     * @see #password 数据库密码 
     */ 
    private String password = "123456"; 
    /** 
     * @see #url 连接数据库url 
     */ 
    private String url = "jdbc:dm://localhost:12345/guest"; 
    /** 
     * @see #filepath 配置文件路径. 
     */ 
    private static String filepath = null; 
    /** 
     * @see #logpath 日志文件路径. 
     */ 
    private String logpath = "..//server.log"; 
    /** 
     * @see #maxwaittime 允许等待时间 
     */ 
    private int maxwaittime = 10; 
    /** 
     * @see #delaytime 延迟时间 
     */ 
    private int delaytime = 10; 
    /** 
     * @see #maxconnection 最大连接 
     */ 
    private int maxconnection = 10; 
    /** 
     * @see #minconnection 最小连接 
     */ 
    private int minconnection = 5; 
    /** 
     * @see #poolsize 连接池大小. 
     */ 
    private int poolsize = 10; 
    /** 
     * @see #manager 本类实例. 
     */ 
    private static DatabaseManager manager = null; 
    /** 
     * @see #timer 扫描连接池. 
     */ 
    private Timer timer; 
    private PrintWriter writer; 
    private boolean loading = false;

    /** 
     * @see #addConnection() 为连接池添加连接 
     * @throws ClassNotFoundException 
     */ 
    private DatabaseManager() { 
        try { 
            init(); 
            File file = new File(logpath); 
            writer = new PrintWriter(new FileWriter(file.getAbsolutePath(), 
                    true), true); 
            addConnection(); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        }

    }

    private void init() { 
        if (loading == false) { 
            config(filepath); 
        } else {

            readconfig(filepath); 
        } 
        pool = new Vector<Connection>(maxconnection); 
        timer = new Timer(true); 
    }

    static public synchronized DatabaseManager getinstance(String path) { 
        // 必须先初始化变量filepath,然后在创建变量. 
        DatabaseManager.filepath = path; 
        if (manager == null) { 
            manager = new DatabaseManager(); 
        } 
        return manager; 
    }

    private void addConnection() { 
        serverlog("server start"); 
        for (int i = 0; i < maxconnection; i++) { 
            try { 
                Class.forName(driverclass); 
                DriverManager.setLogWriter(writer); 
                DriverManager.setLoginTimeout(maxwaittime); 
                con = DriverManager.getConnection(url, username, password); 
                pool.add(con);

            } catch (ClassNotFoundException e) { 
                // TODO Auto-generated catch block 
                serverlog(e.getMessage()); 
            } catch (SQLException e) { 
                // TODO Auto-generated catch block 
                serverlog(e.getMessage()); 
            } 
        } 
    }

    /** 
     * @see #releaseConnection(Connection) 释放连接，返还连接池. 
     * @return con 当前连接. 
     * 
     */ 
    public synchronized Connection getConnection() throws SQLException { 
        if (pool.size() > 0) { 
            con = pool.get(0); 
            pool.remove(0); 
            scaner(); 
            return con; 
        } else { 
            return con; 
        }

    }

    /** 
     * @see #releaseConnection(Connection) 释放连接，返还连接池. 
     * @return con 当前连接. 
     * 
     */ 
    public synchronized Connection getConnection(String username, 
            String password) { 
        if (pool.size() > 0) { 
            con = pool.get(0); 
            pool.remove(0); 
            return con; 
        } else { 
            return con; 
        }

    }

    public synchronized void releaseConnection(Connection con) { 
        pool.add(con);

    }

    /** 
     * @return 无返回值 
     * @see #closeConnectionPool() 关闭连接，清空连接池. 
     * @throws SQLException 
     */ 
    public void closeConnectionPool() throws SQLException { 
        for (int i = 0; i < pool.size(); i++) { 
            pool.get(i).close(); 
            pool.clear(); 
            timer.cancel(); 
        } 
    }

    /** 
     * @see #scaner()扫描连接池. 
     */ 
    private void scaner() { 
        TimerTask task = new TimerTask() { 
            @Override 
            public void run() { 
                // TODO Auto-generated method stub 
                if (manager.pool.size() < maxconnection) { 
                    while (manager.pool.size() < maxconnection) { 
                        addConnection(); 
                    } 
                } 
            }

        }; 
        timer.schedule(task, 10000, 10000); 
    }

    /** 
     * @see #config(String) 读取配置文件.<br> 
     *      初始化数据库连接数据. 
     * @since <hr>http://blog.csdn.net/mak0000 <a 
     *        href="http://blog.csdn.net/mak0000">更多信息</a> 
     * @throws path 
     *             为配置文件路径，文件路径错误会抛出FileNotFoundException异常 
     */ 
    /** 
     * @see #config(String) 读取配置文件. 
     */ 
    private void config(String path) { 
        loading = true; 
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); 
        try { 
            DocumentBuilder builder = factory.newDocumentBuilder(); 
            Document document = builder.parse(DatabaseManager.filepath); 
            NodeList nodelist = document.getElementsByTagName("dbmsdriver"); 
            for (int i = 0; i < nodelist.getLength(); i++) { 
                Element element = (Element) nodelist.item(i); 
                driverclass = element.getElementsByTagName("driverclass").item( 
                        0).getFirstChild().getNodeValue(); 
                url = element.getElementsByTagName("url").item(0) 
                        .getFirstChild().getNodeValue(); 
                username = element.getElementsByTagName("username").item(0) 
                        .getFirstChild().getNodeValue(); 
                password = element.getElementsByTagName("password").item(0) 
                        .getFirstChild().getNodeValue(); 
                maxconnection = Integer 
                        .parseInt(element.getElementsByTagName("maxconnection") 
                                .item(0).getFirstChild().getNodeValue()); 
                minconnection = Integer 
                        .parseInt(element.getElementsByTagName("minconnection") 
                                .item(0).getFirstChild().getNodeValue()); 
                logpath = element.getElementsByTagName("logpath").item(0) 
                        .getFirstChild().getNodeValue(); 
                maxwaittime = Integer.parseInt(element.getElementsByTagName( 
                        "maxwaittime").item(0).getFirstChild().getNodeValue()); 
                delaytime = Integer.parseInt(element.getElementsByTagName( 
                        "delaytime").item(0).getFirstChild().getNodeValue()); 
            } 
        } catch (ParserConfigurationException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
            serverlog(new Date() + ":\t" + e.getMessage() + "\n"); 
        } catch (SAXException e) { 
            e.printStackTrace(); 
            serverlog(new Date() + ":\t" + e.getMessage() + "\n"); 
        } catch (IOException e) { 
            e.printStackTrace(); 
            serverlog(new Date() + ":\t" + e.getMessage() + "\n"); 
        } 
    }

    /** 
     * @deprecated 该方法为候选方法. 
     * @param path 
     *            配置文件路径. 
     */ 
    public void readconfig(String path) { 
        loading = false; 
        Properties properties = new Properties(); 
        try { 
            InputStream in = new FileInputStream(DatabaseManager.filepath); 
            properties.load(in); 
            driverclass = properties.getProperty("driverclass"); 
            url = properties.getProperty("url"); 
            username = properties.getProperty("username"); 
            password = properties.getProperty("password"); 
            maxconnection = Integer.parseInt(properties 
                    .getProperty("maxconnection")); 
            minconnection = Integer.parseInt(properties 
                    .getProperty("minconnection")); 
            delaytime = Integer.parseInt(properties.getProperty("delaytime")); 
            logpath = properties.getProperty("logpath"); 
            in.close(); 
        } catch (FileNotFoundException e) {

            e.printStackTrace(); 
        } catch (IOException e) {

            e.printStackTrace(); 
        } 
    }

    /** 
     * @see #serverlog(String) 日志. 
     * @param msg 
     *            异常及日常信息. 
     * @since <a href="http://www.w3.org/xmlschema">建议将变量以配置文件形式存放，<br> 
     *        以方便使用,例如xml,properties<br> 
     *        文件.</a> 
     */

    private void serverlog(String msg) { 
        writer.println(new Date() + ":" + msg); 
        writer.close(); 
    }

    /** 
     * @see #setLogWriter(PrintWriter) set delay time 
     * @throws SQLException 
     */ 
    public void setLoginTimeout(int seconds) throws SQLException { 
        this.maxwaittime = seconds; 
    }

    /** 
     * @see #getLoginTimeout() get delay time. 
     * @throws SQLException 
     */ 
    public int getLoginTimeout() throws SQLException { 
        return maxwaittime; 
    }

    /** 
     * @see #setLogWriter(PrintWriter) set logwriter. 
     * @throws SQLException 
     */

    public void setLogWriter(PrintWriter out) throws SQLException { 
        this.writer = out; 
    }

    /** 
     * @see #getLogWriter() get logwriter 
     * @throws SQLException 
     */ 
    public PrintWriter getLogWriter() throws SQLException { 
        return writer; 
    }

    /** 
     * @see #listexecuteQuery(String, Class) put the var in list,list includes objects bean 
     * @since Book.class<br> 
     *        Book book=(Book)result.get(i);<br> 
     *        book.getid(); 
     */ 
    @Override 
    public List listexecuteQuery(String sql, Class beanclass) 
            throws SQLException { 
        con = getConnection(); 
        DbUtils.loadDriver(driverclass); 
        //build a queryrunner instance. 
        QueryRunner queryrunner = new QueryRunner(); 
        list = (List) queryrunner.query(con, sql, 
                new BeanListHandler(beanclass)); 
        releaseConnection(con); 
        return list;

    }

    /** 
     * @see #mapexecuteQuery(String) map形式存放数据.list includes map 
     */ 
    @Override 
    public Map mapexecuteQuery(String sql) throws SQLException { 
        // TODO Auto-generated method stub 
        con = getConnection(); 
        DbUtils.loadDriver(driverclass); 
        QueryRunner queryrunner = new QueryRunner(); 
        list = (List) queryrunner.query(con, sql, new MapListHandler()); 
        releaseConnection(con); 
        for (int i = 0; i < list.size(); i++) { 
            map = (Map) list.get(i); 
        } 
        return map; 
    }

    /** 
     * @see #getTables(String, String, String, String[])获取数据库表结构. 
     * @param catalog 
     *            ,schemaPattern,tableNamePattern,types 
     * 
     */ 
    public ResultSet getTables(String catalog, String schemaPattern, 
            String tableNamePattern, String[] types) throws SQLException { 
        con = getConnection(); 
        databasemetaData = con.getMetaData(); 
        resultset = databasemetaData.getTables(catalog, schemaPattern, 
                tableNamePattern, types); 
        releaseConnection(con); 
        return resultset; 
    }

    /** 
     * @see #getJDBCMajorVersion() jdbc主版本号. 
     */ 
    @Override 
    public int getJDBCMajorVersion() throws SQLException { 
        // TODO Auto-generated method stub 
        con = getConnection(); 
        databasemetaData = con.getMetaData(); 
        int version = databasemetaData.getJDBCMajorVersion(); 
        releaseConnection(con); 
        return version; 
    }

    /** 
     * @see #getJDBCMinorVersion() jdbc次版本号. 
     */ 
    @Override 
    public int getJDBCMinorVersion() throws SQLException { 
        // TODO Auto-generated method stub 
        con = getConnection(); 
        databasemetaData = con.getMetaData(); 
        int version = databasemetaData.getJDBCMinorVersion(); 
        releaseConnection(con); 
        return version; 
    }

    @Override 
    /* 
     * @see #getMaxConnections() 数据库支持的同时建立连接的对象数目. 
     */ 
    public int getMaxConnections() throws SQLException {

        con = getConnection(); 
        databasemetaData = con.getMetaData(); 
        int maxconnection = databasemetaData.getMaxConnections(); 
        releaseConnection(con); 
        return maxconnection; 
    }

    /** 
     * @see #getMaxStatements() 数据库支持同时打开statement的数目. 
     */ 
    @Override 
    public int getMaxStatements() throws SQLException { 
        // TODO Auto-generated method stub 
        con = getConnection(); 
        databasemetaData = con.getMetaData(); 
        int minconnection = databasemetaData.getMaxStatements(); 
        releaseConnection(con); 
        return minconnection; 
    }

    /** 
     * @see #generalUpdate(String) 更新. 
     */ 
    public int generalUpdate(String sql) { 
        int result = 0; 
        Statement statement; 
        try { 
            con = getConnection(); 
            statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_UPDATABLE); 
            result = statement.executeUpdate(sql); 
            statement.close(); 
            releaseConnection(con); 
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            serverlog(e.getSQLState() + "\t" + e.getMessage()); 
        }

        return result;

    }

    public ResultSet generalQuery(String sql) { 
        ResultSet result = null; 
         
        Statement statement; 
        try { 
            con = getConnection(); 
            statement = con.createStatement(); 
            result = statement.executeQuery(sql); 
            result.close(); 
            statement.close(); 
            releaseConnection(con); 
        } catch (SQLException e) { 
            serverlog(e.getSQLState() + "\t" + e.getMessage()); 
        } 
        return result; 
    }

    public String toChinese(String str, String oldcharsetName, 
            String newcharsetName) { 
        String chinesestr = null; 
        if (str.equals(null) || str == "") { 
            chinesestr = ""; 
        } else { 
            try { 
                chinesestr = new String(str.getBytes(oldcharsetName), 
                        newcharsetName); 
            } catch (UnsupportedEncodingException e) { 
                serverlog("\t" + e.getMessage()); 
            } 
        } 
        return chinesestr; 
    }

}

 测试