AI专业者开发社区 1 号成员
  •  0 回帖  •  0 浏览  •  5 个月前
 

使用数据库连接池提高执行效率

利用数据库连接池可有效提高数据库操作效率，避免重复打开和关闭数据库连接。具体方法和测试结果如下：

首先建立一个属性文件，将相应的数据库连接方法加入其中：

属性文件具体设定如下：

driverClassName=oracle.jdbc.driver.OracleDriver
username=ORATEST
password=ORATEST
url=jdbc\:oracle\:thin\:@vOracle.imStudio.com\:1521\:vOracle
poolSize=10

然后建立数据库连接池操作类 ConnectionPoolTool.java

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

public class ConnectionPoolTool {


</span><span style="color: rgba(0, 0, 255, 1)">private</span> Vector&lt;Connection&gt;<span style="color: rgba(0, 0, 0, 1)"> pool;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String url;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String username;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String password;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String driverClassName;

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 连接池的大小，也就是连接池中有多少个数据库连接。
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span> poolSize = 1<span style="color: rgba(0, 0, 0, 1)">;

</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> ConnectionPoolTool instance = <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 私有的构造方法，禁止外部创建本类的对象，要想获得本类的对象，通过&lt;code&gt;getIstance&lt;/code&gt;方法。
 * 使用了设计模式中的单子模式。
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionPoolTool() {
    init();
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 连接池初始化方法，读取属性文件的内容 建立连接池中的初始连接
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> init() {
    pool </span>= <span style="color: rgba(0, 0, 255, 1)">new</span> Vector&lt;Connection&gt;<span style="color: rgba(0, 0, 0, 1)">(poolSize);
    readConfig();
    addConnection();
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 返回连接到连接池中
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> release(Connection conn) {
    pool.add(conn);

}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 关闭连接池中的所有数据库连接
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> closePool() {
    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = 0; i &lt; pool.size(); i++<span style="color: rgba(0, 0, 0, 1)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            ((Connection) pool.get(i)).close();
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) {
            e.printStackTrace();
        }
        pool.remove(i);
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 返回当前连接池的一个对象
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionPoolTool getInstance() {
    </span><span style="color: rgba(0, 0, 255, 1)">if</span> (instance == <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">) {
        instance </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionPoolTool();
    }
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> instance;
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 返回连接池中的一个数据库连接
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span><span style="color: rgba(0, 0, 0, 1)"> Connection getConnection() { 
    </span><span style="color: rgba(0, 0, 255, 1)">if</span> (pool.size() &gt; 0<span style="color: rgba(0, 0, 0, 1)">) {
        Connection conn </span>= pool.get(0<span style="color: rgba(0, 0, 0, 1)">);
        pool.remove(conn);
        </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> conn;
    } </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)"> {
        </span><span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 在连接池中创建初始设置的的数据库连接
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> addConnection() {
    Connection conn </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = 0; i &lt; poolSize; i++<span style="color: rgba(0, 0, 0, 1)">) {

        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            Class.forName(driverClassName);
            conn </span>=<span style="color: rgba(0, 0, 0, 1)"> java.sql.DriverManager.getConnection(url, username, password);
            pool.add(conn);

        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (ClassNotFoundException e) {
            e.printStackTrace();
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) {
            e.printStackTrace();
        }

    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 读取设置连接池的属性文件
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> readConfig() {
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        String path </span>= System.getProperty("user.dir") + "\\dbpool.properties"<span style="color: rgba(0, 0, 0, 1)">;
        FileInputStream is </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> FileInputStream(path);
        Properties props </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> Properties();
        props.load(is);
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.driverClassName = props.getProperty("driverClassName"<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.username = props.getProperty("username"<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.password = props.getProperty("password"<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.url = props.getProperty("url"<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.poolSize = Integer.parseInt(props.getProperty("poolSize"<span style="color: rgba(0, 0, 0, 1)">));
    } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e) {
        e.printStackTrace();
        System.err.println(</span>"读取属性文件出错. "<span style="color: rgba(0, 0, 0, 1)">);        
    }
}



}

再加入数据库连接池测试方法 ConnectionPoolTest.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ConnectionPoolTest {


</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * </span><span style="color: rgba(128, 128, 128, 1)">@param</span><span style="color: rgba(0, 128, 0, 1)"> args
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> main(String[] args)<span style="color: rgba(0, 0, 255, 1)">throws</span><span style="color: rgba(0, 0, 0, 1)"> Exception {
    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> TODO Auto-generated method stub</span>
    String sql = "select userid,username,password from tbluser"<span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">long</span> start =<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
    ConnectionPoolTool pool </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;

    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = 0; i &lt; 100; i++<span style="color: rgba(0, 0, 0, 1)">) {
        pool </span>=<span style="color: rgba(0, 0, 0, 1)"> ConnectionPoolTool.getInstance();
        Connection conn </span>=<span style="color: rgba(0, 0, 0, 1)"> pool.getConnection();
        Statement stmt </span>=<span style="color: rgba(0, 0, 0, 1)"> conn.createStatement();
        ResultSet rs </span>=<span style="color: rgba(0, 0, 0, 1)"> stmt.executeQuery(sql);
        </span><span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)"> (rs.next()) {
        }
        rs.close();
        stmt.close();
        pool.release(conn);
    }
    pool.closePool();
    System.out.println(</span>"经过100次的循环调用，使用连接池花费的时间:" + (System.currentTimeMillis() - start) + "ms\n"<span style="color: rgba(0, 0, 0, 1)">);

    String hostName </span>= "vOracle.imStudio.com"<span style="color: rgba(0, 0, 0, 1)">;
    String driverClass </span>= "oracle.jdbc.driver.OracleDriver"<span style="color: rgba(0, 0, 0, 1)">;
    String url </span>= "jdbc:oracle:thin:@" + hostName + ":1521:vOracle"<span style="color: rgba(0, 0, 0, 1)">;
    String user </span>= "ORATEST"<span style="color: rgba(0, 0, 0, 1)">;
    String password </span>= "ORATEST"<span style="color: rgba(0, 0, 0, 1)">;
    start </span>=<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
    
    </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = 0; i &lt; 100; i++<span style="color: rgba(0, 0, 0, 1)">) {
        Class.forName(driverClass);
        Connection conn </span>=<span style="color: rgba(0, 0, 0, 1)"> DriverManager.getConnection(url, user, password);
        Statement stmt </span>=<span style="color: rgba(0, 0, 0, 1)"> conn.createStatement();
        ResultSet rs </span>=<span style="color: rgba(0, 0, 0, 1)"> stmt.executeQuery(sql);
        </span><span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)"> (rs.next()) {
        }
        rs.close();
        stmt.close();
        conn.close();
    }
    System.out.println(</span>"经过100次的循环调用，不使用连接池花费的时间:" + (System.currentTimeMillis() - start) + "ms"<span style="color: rgba(0, 0, 0, 1)">);
}



}

编写完成后，进行测试运行，如下为我本机测试结果：