自分で書いたJDBC接続プールは、ざらざらしています
37540 ワード
1と2は接続プール、3と4はデータをテストまたは挿入するための駆動である.
プログラム実行後のコマンドの例:
new 100は100個の接続を申請する
drop 100は100を池に戻します
Query 100000SQL文100000回、例えばデータの挿入
quit終了
プログラム実行後のコマンドの例:
new 100は100個の接続を申請する
drop 100は100を池に戻します
Query 100000
quit終了
- //1.connPool.java
-
- package Data;
-
- import java.util.ArrayList;
-
- import java.util.List;
-
- import java.sql.*;
-
-
-
-
- public class connPool {
-
-
-
- private List<Connection> pool; //it contains connections
-
-
-
- public connPool()
-
- {
-
- pool=new ArrayList<Connection>();
-
- }
-
-
-
- synchronized public boolean putConn(Connection conn) throws SQLException //put connection back
-
- {
-
- if(conn==null||conn.isClosed()) //check,throws SQLException
-
- return false;
-
- else
-
- {
-
- return pool.add(conn);
-
- }
-
- }
-
-
-
- synchronized public Connection getConn() //fetch a connection
-
- {
-
- if(pool.size()<=0)
-
- return null;
-
- else
-
- {
-
- return pool.remove(0);
-
- }
-
- }
-
-
-
- synchronized public boolean reduceOne() //reduce the connections storage
-
- {
-
- if(pool.size()<=0)
-
- return false;
-
- else
-
- {
-
- try {
-
- pool.remove(0).close();
-
- } catch (SQLException e) {
-
- e.printStackTrace();
-
- return false;
-
- }
-
- return true;
-
- }
-
- }
-
-
-
- public int getNum() //how many connections is there
-
- {
-
- return pool.size();
-
- }
-
-
-
- }
-
- //2.DBDriver.java
- package Data;
-
-
- import java.sql.*;
-
-
- public class DBDriver {
-
-
- static final int NumWhenStart = 10; // how many conns should be created whenstart
-
-
- static final int NumWhenIncrease = 5;// how many conns should add when thereis few conns
-
-
- static final int NumJudgeOver = 20; // num to judge if the pool has too many conns;
-
-
- static final int NumReduceTo = 10; // num should the pool reduce to
-
- static final int NumLowCase = 2; //num few conns
-
-
- private String host; //host name
- private String user; //database user
- private String pwd; //password
- private String database; //database name
- private String driverName; //JDBC Driver
- private String dbURL; //database connection url
- private connPool pool; //connection pool
-
- private static DBDriver instance = null; //singleton instance
-
-
- public static DBDriver getInstance() { //singleton
- if (instance == null) {
- synchronized (DBDriver.class) {
- if (instance == null) {
- try {
- instance = new DBDriver();
- } catch (Exception e) {
- e.printStackTrace();
- return null;
- }
- }
- }
- }
- return instance;
- }
-
-
- private DBDriver() throws Exception { //constructor
- pool = new connPool();
- host = "localhost";
- user = "sa";
- pwd = "put yours pwd here";
- database = "innov";
- driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
- dbURL = "jdbc:sqlserver://" + host + ":1433; DatabaseName="
- + database;
- Class.forName(driverName);
- for (int i = 0; i < NumWhenStart; i++) {
- Connection conn = DriverManager.getConnection(dbURL, user, pwd);
- pool.putConn(conn);
- }
- }
-
-
- public Connection getConn() { //get a connection
- Connection conn = null;
- if (pool.getNum() <= NumLowCase) {
- try {
- Class.forName(driverName);
- for (int i = 0; i < NumWhenIncrease; i++) {
- conn = DriverManager.getConnection(dbURL, user, pwd);
- pool.putConn(conn);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- return pool.getConn();
- }
-
-
- public boolean close(Connection conn) { //put connections back
- boolean bool = false;
- try {
- bool = pool.putConn(conn);
- } catch (SQLException e) {
- e.printStackTrace();
- }
- if (pool.getNum() > NumJudgeOver) {
- while (pool.getNum() > NumReduceTo)
- pool.reduceOne();
- }
- return bool;
- }
-
-
- public int getNum() { //return pool's connection number
- return pool.getNum();
- }
- }
- /*************************** **********************************/
- /*************************** ***********************/
- //3.SqlThread.java //use multiple thread
- import java.sql.*;
- public class SqlThread extends Thread
- {
- Connection conn;
- String sql;
- int times;
- public SqlThread(String name,Connection conn,String sql,int times)
- {
- super(name);
- this.times=times;
- this.sql=sql;
- this.conn=conn;
- }
-
-
- @Override
- public void run()
- {
- try
- {
- Statement stmt=conn.createStatement();
- for(int i=0;i<times;i++)
- {
- stmt.execute(sql);
- System.out.print("Thread "+this.getName()+" Reporting:"+(i+1)+" querys done.
");
- sleep(3);
- }
- }catch(Exception e)
- {
- e.printStackTrace();
- }
- System.out.print("Thread "+this.getName()+" quit."+times+" querys done.
");
- }
- }
-
-
- //4.test.java
-
-
- import java.sql.*;
- import java.util.*;
- import Data.DBDriver;
-
-
- public class test {
-
-
- /**
- * @param args
- */
- public static void main(String[] args) {
- System.out.print("program start
");
- DBDriver dbd = DBDriver.getInstance();
- List<Connection> connList = new ArrayList<Connection>();
- for(int i=0;i<3;i++)
- {
- connList.add(dbd.getConn());
- }
- System.out.print("using " + connList.size() + " conns,while "
- + dbd.getNum() + " in pool
");
- Scanner jin = new Scanner(System.in);
- String cmd;
- int flag = 0;
- while (jin.hasNext()) {
- cmd = jin.next();
- if (flag == 0) {
- if (cmd.equals("quit")) {
- System.out.print("Bye Bye");
- break;
- } else if (cmd.equals("new")) {
- flag = 1;
- } else if (cmd.equals("drop")) {
- flag = 2;
- } else if (cmd.equals("query")) {
- flag = 3;
- } else {
- System.out.print("Unknown command
");
- }
- } else {
- if (flag == 1) {
- int num = Integer.parseInt(cmd);
- for (int i = 0; i < num; i++) {
- connList.add(dbd.getConn());
- }
- System.out.print("System:operation done!
using "
- + connList.size() + " conns,while " + dbd.getNum()
- + " in pool
");
- flag = 0;
- } else if (flag == 2) {
- int num = Integer.parseInt(cmd);
- int size = connList.size();
- for (int i = 0; i < ((num > size) ? size : num); i++) {
- if (dbd.close(connList.get(0))) {
- connList.remove(0);
- } else {
- System.out.print("fail when drop " + i + "
");
- }
- }
- System.out.print("System:operation done!
using "
- + connList.size() + " conns,while " + dbd.getNum()
- + " in pool
");
- flag = 0;
- } else if (flag == 3) {
- int num = Integer.parseInt(cmd);
- String sql = jin.nextLine();
- for (int i = 0; i < 3; i++) {
- try {
- Thread t=new SqlThread(String.valueOf(i),connList.get(i),sql,num/3+((i<2)?0:(num%3)));
- t.start();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- flag = 0;
- } else if (flag == 4) {
- int num = Integer.parseInt(cmd);
- int size = connList.size();
- String sql = jin.nextLine();
- Statement stmt = null;
- for (int i = 0; i < ((num > size) ? size : num); i++) {
- try {
- Thread t=new SqlThread(String.valueOf(i),connList.get(i),sql,1);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- System.out.print("System:operation done!
using "
- + connList.size() + " conns,while " + dbd.getNum()
- + " in pool
");
- flag = 0;
- }
- }
- }
- }
- }