发一个自己写的数据库连接池

现在大家都是用配置来连接数据库了，可能都不用写的了。可是如果是在远程的服务器的话，我还不知道怎么去配置，所以自己写了个连接池，各位看了后给点意见，高手轻点拍砖哈。

DB.java

package com.database.config;

public class DB
{
    public final static String driver="sun.jdbc.odbc.JdbcOdbcDriver";
   
    //public final static String driver="com.mysql.jdbc.Driver";
   
    public final static  String dbName="stu.mdb";
   
    public final static String url="jdbc:odbc:driver= {Microsoft Access Driver (*.mdb)};DBQ=D:/javaWeb/Stu/WEB-INF/classes/stu.mdb";
      
    //public final static String url="jdbc:mysql://localhost/test";
   
    public final static String port="";
   
    public final static String username="";
   
    public final static String pwd="";
   
    public final static int max =50;
   
    public final static int min=5;
   
    /*
     * 经测试以下参数为最佳合理配置，请勿更改!
     * 根据不同的电脑配置,配置不同的参数
     * */
    public final static int inc=5;
   
    public final static int timeout=20; //等待timeout时间后获取连接
   
    public final static int timer=1000;   //多少时间关掉多余的连接
   
}


//数据库连接包装类
ConnState.java

/*
* 创建日期 Jan 2, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;

public class ConnState
{
    private boolean isOpen=false;
   
    private Connection conn;

    public Connection getConn()
   
    {
        return conn;
    }

    public void setConn(Connection conn)
    {
        this.conn = conn;
    }

    public boolean isOpen()
    {
        return isOpen;
    }

    public void setOpen(boolean isOpen)
    {
        this.isOpen = isOpen;
    }
   
   
}

DatabasePool.java
/*
* 创建日期 Jan 2, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;

import com.database.config.DB;



/*数据库连接池
* 程序会将多余的连接关掉和在连接池里删除掉
*
* */

public class DatabasePool
{
    private static List<ConnState> pool;
   
    private static DatabasePool instance;
   
    public TimerTask task=new TimerTask()
    {
        public void run()
        {
            System.out.println("run()");
            System.out.println("DatabasePool 类使用中的连接总数:"+ getConnCount());
          // if(pool.size()>DB.min)
            {
                Iterator<ConnState> it =pool.iterator();
               
                while(it.hasNext())
                {
                    ConnState st=it.next();
                    if(pool.size()<=DB.min)
                    {
                        if(st.isOpen()==true && null!=st.getConn())
                        {
                            try
                            {
                                st.getConn().commit();
                                st.setOpen(false);
                            }catch(SQLException e)
                            {       
                                System.out.println("强行执行sql语句失败");
                            }
                        }
                    }
                    else
                    {
                        if(st.isOpen()==true && null!=st.getConn())
                        {
                            try
                            {
                                st.getConn().commit();
                                st.getConn().close();
                                //it.remove();
                            }catch(SQLException e)
                            {
                               try
                               {
                                   Thread.sleep(200);
                                   st.getConn().commit();
                                   st.getConn().close();
                               }catch(InterruptedException ex)
                               {
                                   ex.printStackTrace();
                               }catch(SQLException se)
                               {
                                  se.printStackTrace();
                               }
                            }finally
                            {
                                try
                                {
                                    st.setConn(null);
                                    st=null;
                                    System.out.println("最后的关闭");
                                    it.remove();
                                    System.gc();
                                }catch(Exception e)
                                {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                }
            }
        }
    };
    public DatabasePool() throws Exception
    {
        /*
         * 连接池使用ArrayList对象进行封装ConnState，这里使用了泛型
         * */
       pool=new ArrayList<ConnState>();
       Init();
       StartTimer();
    }
/*    创建数据库连接
* 返回ConnState对象
* ConnState 对象封装Connection对象
* */
    private static ConnState createConnection() throws ClassNotFoundException,SQLException
    {
         Connection conn;
        
        Class.forName(DB.driver);
       
        conn=DriverManager.getConnection(DB.url);
       
        conn.setAutoCommit(false);
       
        ConnState state =new ConnState();
       
        state.setConn(conn);
       
        return state;
    }
   
    /*数据库连接池的初始化过程*/
    private static  void Init() throws Exception
    {
        if(null==pool)
        {
           getInstance();
        }
        else
        {
           
            /*
             * 创建预定义大小的连接
             * */
            for(int i=0;i<DB.min;i++)
            {
                pool.add(createConnection());
            }
        }
    }
   
    private static void inc() throws Exception
    {
        if(null==pool)
        {
            getInstance();
            Init();
        }
        else if(pool.size()<DB.max)
        {
            int c=(pool.size()+DB.inc)>DB.max?DB.max-pool.size():DB.inc;
           
            for(int i=0;i<c;i++)
            {
                pool.add(createConnection());
            }
        }
    }
    private static   DatabasePool getInstance() throws Exception
    {
        instance =new DatabasePool();
        return instance;
    }
    /*
     * 返回数据库连接
     * */
    public synchronized static Connection getConnection() throws Exception
    {
        if(null ==instance )
        {
            getInstance();
        }
       Connection conn=findConnection();
       int count=1;
       /*
        * 如果没有找到可用的连接或三次
        * 将会抛出异常
        * */
      while(null==conn && count<=3)
      {
         count++;
          Thread.sleep(DB.timeout);
          conn=findConnection();
          if(null!=conn)
          {
              break;
          }
          else
          {
              inc();   // 增加指定个数的连接
              conn=findConnection();
          }
       
      }
     
      if(null==conn) throw new Exception("当前连接己达到最连接池,没有可以使用的数据库连接了");
       return conn;
    }
   
    private static Connection findConnection() throws Exception   
    {
        Iterator<ConnState> it =pool.iterator();
        Connection conn=null;

        while(it.hasNext())
        {
            ConnState cs=it.next();
            if(cs.isOpen()==false)
            {
                conn=cs.getConn();
                cs.setOpen(true);
                break;
            }
        }
        return conn;
    }
   
    public void StartTimer()
    {
        Timer time=new Timer();
        time.schedule(task, DB.timer,2000);
    }
   
    public static int getConnCount()
    {
        return pool.size();
    }
}

 

还没有完，继续。奇怪了，我看了别人的代码都有格式的，我的怎么就没有了呢？

SqlAware.java

/*
* 创建日期 Jan 3, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.util.List;
import java.util.Map;

public interface SqlAware
{
    public List getData(String sql) throws Exception;
   
    public Map getOneData(String sql) throws Exception;
   
    public int execute(String sql) throws Exception;
   
    public int executeBatch(String sql) throws Exception;
}


SqlDao.java
/*
* 创建日期 Jan 3, 2009
*
* TODO 要更改此生成的文件的模板，请转至
* 窗口 － 首选项 － Java － 代码样式 － 代码模板
*/
package com.database.pool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.database.exception.SqlException;

public  class SqlDao implements SqlAware
{
    public void close(ResultSet rs) throws SQLException
    {
        if(null!=rs)
        {
            rs.close();
        }
    }
   
    public void close(ResultSet rs,Statement stmt) throws SQLException
    {
        if(null!=rs) rs.close();
        if(null!=stmt) stmt.close();
    }
   
    public void close(PreparedStatement pstmt) throws SQLException
    {
        if(null!=pstmt) pstmt.close();
    }
   
    public void close(ResultSet rs,PreparedStatement pstmt) throws SQLException
    {
        if(null!=rs) rs.close();
        if(null!=pstmt) pstmt.close();
    }

    public int executeBatch(ArrayList<Map<String,String>> list, String sql) throws Exception
    {
        // TODO 自动生成方法存根
        return 0;
    }

    public int executeBatch(String sql) throws Exception
    {
        // TODO 自动生成方法存根
        return 0;
    }

    public int execute(String sql) throws Exception
    {
        int n=0;
       
        Connection conn=DatabasePool.getConnection();
       
        PreparedStatement pstmt=conn.prepareStatement(sql);
       
        n=pstmt.executeUpdate();
       
        close(pstmt);
       
        return n;
    }

    public List<Map<String,String>> getData(String sql) throws Exception
    {
        ArrayList<Map<String,String>> list=new ArrayList<Map<String,String>>();
       
        Connection conn=DatabasePool.getConnection();
       
        PreparedStatement pstmt=conn.prepareStatement(sql);
       
        ResultSet rs=pstmt.executeQuery();
       
        ResultSetMetaData rsmd=rs.getMetaData();
       
        int c=rsmd.getColumnCount();
       
        while(rs.next())
        {
           
            Map<String,String>  hp=new HashMap<String,String>();
           
            for(int i=1;i<=c;i++)
            {
                String label=rsmd.getColumnName(i);
               
                System.out.println(label);
               
                String value=rs.getString(i);
               
                hp.put(label, value);
               
                if(null==hp) throw new SqlException();
              
            }
           
            list.add(hp);

        }
       
        close(rs,pstmt);
       
        return list;
    }

    public Map<String,String> getOneData(String sql) throws Exception
    {
        HashMap<String,String> hp=null;
       
        Connection conn=DatabasePool.getConnection();
       
        PreparedStatement pstmt=conn.prepareStatement(sql);
       
        ResultSet rs=pstmt.executeQuery();
       
        ResultSetMetaData rsmd=rs.getMetaData();
       
        int c=rsmd.getColumnCount();
       
        if(rs.next())
        {
            hp=new HashMap<String,String>(1);
           
            for(int i=1;i<c;i++)
            {
                String label=rsmd.getColumnName(i);
               
                hp.put(label, rs.getString(i));
            }
        }
      
        close(rs,pstmt);
       
        if(null==hp) throw new SqlException();
        return hp;
       
    }
   
   
}


一般写一个业务操作类继承SqlDao类就可以了。

各位如有好的意见，请留言.

个人认为重复发明轮子了，连接池现有的很多，而且都做的很好。。。

有getConnection方法.我怎么没看见释放的方法呢?
连接池是List<ConnState>. 从list取了资源不归还它.难道不怕精尽人亡吗?

sqlDao感觉不太好.参考下Spring数据库模版.

另外,代码放在[ code ][/ code ]中,就有格式了.

楼上的朋友,谢谢你们的意见.网上的的确很多而且也很不错,可是那是别人写的,我觉得不写就没会有进步.
Rooock
数据库连接有释放的,只是不是手动的,是自动的