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

java 数据库连接池和普通连接 对比

自由自在，闲话省略

包：c3p0-***.jar

mchange-commons-java-**.jar

ojdba.jar

mysql-connector.jaava-**bin.jar

 

ConnectionManager

package com.adao.c3p0;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;


public final class ConnectionManager {
//使用单利模式创建数据库连接池
private static ConnectionManager instance;
private static ComboPooledDataSource dataSource;


</span><span style="color: rgba(0, 0, 255, 1)">private</span> ConnectionManager() <span style="color: rgba(0, 0, 255, 1)">throws</span><span style="color: rgba(0, 0, 0, 1)"> SQLException, PropertyVetoException {
    dataSource </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ComboPooledDataSource();

    dataSource.setUser(</span>"coll_platform");     <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">用户名</span>
    dataSource.setPassword("coll_platform"); <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">密码</span>
    dataSource.setJdbcUrl("jdbc:oracle:thin:@192.168.127.156:1521:ORCL");<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">数据库地址</span>
    dataSource.setDriverClass("oracle.jdbc.driver.OracleDriver"<span style="color: rgba(0, 0, 0, 1)">);
    dataSource.setInitialPoolSize(</span>5); <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">初始化连接数</span>
    dataSource.setMinPoolSize(1);<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">最小连接数</span>
    dataSource.setMaxPoolSize(10);<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">最大连接数</span>
    dataSource.setMaxStatements(50);<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">最长等待时间</span>
    dataSource.setMaxIdleTime(60);<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">最大空闲时间，单位毫秒</span>



    }


</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)">final</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionManager 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)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">try</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)"> ConnectionManager();
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e) {
            e.printStackTrace();
        }
    }
    </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, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">final</span><span style="color: rgba(0, 0, 0, 1)"> Connection getConnection() {
    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)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        conn </span>=<span style="color: rgba(0, 0, 0, 1)"> dataSource.getConnection();
    } </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, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> conn;
}



}

ConnectionDemo

package com.adao.c3p0;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;


public class ConnectionDemo {


</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)"> SQLException, ClassNotFoundException {
    System.out.println(</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; 20; i++<span style="color: rgba(0, 0, 0, 1)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">long</span> beginTime =<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
        Connection conn </span>=<span style="color: rgba(0, 0, 0, 1)"> ConnectionManager.getInstance().getConnection();
        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            PreparedStatement pstmt </span>= conn.prepareStatement("select * from T_TERMINAL"<span style="color: rgba(0, 0, 0, 1)">);
            ResultSet rs </span>=<span style="color: rgba(0, 0, 0, 1)"> pstmt.executeQuery();
            </span><span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)"> (rs.next()) {
                 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> do nothing...</span>



                }
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


        </span><span style="color: rgba(0, 0, 255, 1)">long</span> endTime =<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
        System.out.println(</span>"第" + (i + 1) + "次执行花费时间为:" + (endTime -<span style="color: rgba(0, 0, 0, 1)"> beginTime));
    }
    Connection con </span>= <span style="color: rgba(0, 0, 255, 1)">null</span>;<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 创建一个数据库连接</span>
    System.out.println("不使用连接池................................"<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; 20; i++<span style="color: rgba(0, 0, 0, 1)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">long</span> beginTime =<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">oracle</span>
        Class.forName("oracle.jdbc.driver.OracleDriver");<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 加载Oracle驱动程序</span>
        String url = "jdbc:oracle:thin:@192.168.127.156:1521:ORCL"<span style="color: rgba(0, 0, 0, 1)">;
        String user </span>= "coll_platform"<span style="color: rgba(0, 0, 0, 1)">;
        String password </span>= "coll_platform"<span style="color: rgba(0, 0, 0, 1)">;
        con </span>= DriverManager.getConnection(url, user, password);<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, 128, 0, 1)"> mysql</span>
        // MysqlDataSource mds = <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> MysqlDataSource();
        // mds.setURL(</span>"jdbc:mysql://localhost:3306/zww"<span style="color: rgba(0, 0, 0, 1)">);
        // mds.setUser(</span>"root"<span style="color: rgba(0, 0, 0, 1)">);
        // mds.setPassword(</span>"123456"<span style="color: rgba(0, 0, 0, 1)">);
        // Connection conn </span>=<span style="color: rgba(0, 0, 0, 1)"> mds.getConnection();
        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            PreparedStatement pstmt </span>= con.prepareStatement("select * from T_TERMINAL"<span style="color: rgba(0, 0, 0, 1)">);
            ResultSet rs </span>=<span style="color: rgba(0, 0, 0, 1)"> pstmt.executeQuery();
            </span><span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)"> (rs.next()) {
                                </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> do nothing...</span>



                }
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
long endTime = System.currentTimeMillis();
System.out.println("第" + (i + 1) + "次执行花费时间为:"
+ (endTime - beginTime));
}


}



}

执行结果

 

 

flawless