//操作示例--------------------------------------------------------------------------------------------------------
package com.yyy.mysqltest;

import java.util.ArrayList;
import java.util.List;

public class Testqwe {
public static void main(String[] args) {
MysqlMaster mysqlMaster = MysqlMaster.getMysqlMaster();
MysqlWorker mysqlWorker = mysqlMaster.getMysqlWorker();
List sqls = new ArrayList<>();
sqls.add("insert into student values('4','1','1','1')");
mysqlWorker.setSqls(sqls);
mysqlWorker.start();
mysqlWorker.close();
}
}
//-----------------------------------------------------------------------------------
package com.yyy.mysqltest;

import java.util.ArrayList;
import java.util.List;

public class MysqlMaster {

private static MysqlMaster mysqlMaster;
//单例mysqlMaster
public static MysqlMaster getMysqlMaster(){
    if(mysqlMaster == null){
       mysqlMaster = new MysqlMaster();
    }
    return mysqlMaster;
}

private final int minConnectionNum = 10;

private final int maxConnectionNum = 50;

public List<MysqlWorker> workerGroup;

private MysqlMaster() {
    workerGroup = new ArrayList<>();
    for (int i = 0; i < minConnectionNum; i++) {
       workerGroup.add(new MysqlWorker("permanentWorker"));
    }
}

public MysqlWorker getMysqlWorker(){
    if(workerGroup.size() < maxConnectionNum){
       for (MysqlWorker mysqlWorker: workerGroup) {
         synchronized (mysqlWorker) {
          if(mysqlWorker.used == false){
              mysqlWorker.used = true;
              return mysqlWorker;
          }
         }
       }
       MysqlWorker mysqlWorker = new MysqlWorker("zeroHour");
       workerGroup.add(mysqlWorker);
       return mysqlWorker;
    }else{
       System.out.println("服务器忙....");
       return null;
    }
}

}
//---------------------------------------------------------------------------------------------------------------------------------------
package com.yyy.mysqltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;

public class MysqlWorker extends TimerTask{
//驱动程序就是之前在classpath中配置的JDBC的驱动程序的JAR 包中
public final String DBDRIVER = "com.mysql.jdbc.Driver";
//连接地址是由各个数据库生产商单独提供的，所以需要单独记住
public final String DBURL = "jdbc:mysql://localhost:3306/test1_yyy";
//连接数据库的用户名
public final String DBUSER = "root";
//连接数据库的密码
public final String DBPASS = ""; /**/

public boolean used = false;
//使用次数
private int useCount = 0;

private List<String> sqls;

private Connection con;

private Statement statement;

private ResultSet resultSet;

public MysqlWorker(String str){
    try {
        //使用CLASS 类加载驱动程序  
       Class.forName(DBDRIVER);
       //获取连接对象
       con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
    } catch (Exception e) {
       e.printStackTrace();
    }
    //零时工
    if("zeroHour".equals(str)){
       Timer timer = new Timer();
       timer.schedule(this, 0, 10000);
    }else if("permanentWorker".equals(str)){

    }
}

public List<String> getSqls() {
    return sqls;
}

public void setSqls(List<String> sqls) {
    this.sqls = sqls;
}

public ResultSet start(){
    useCount++;
    try {
       String sql = sqls.get(0);
       statement = con.createStatement();
       sql = sql.trim();
       if(sql.startsWith("select")){
         resultSet = statement.executeQuery(sql);
         return resultSet;
       }
       for(String sql1 : sqls){
         statement.addBatch(sql1);
       }
       statement.executeBatch();
    } catch (Exception e) {
       e.printStackTrace();
    }
    return null;
}

public void close(){
    try {
       if(resultSet != null){
         resultSet.close();
         resultSet = null;
       }
       if(statement != null){
         statement.close();
         statement = null;
       }
    } catch (Exception e) {
       e.printStackTrace();
       if(con != null){
         try {
          con.close();
          con = null;
          System.gc();
         } catch (SQLException e1) {
          // TODO Auto-generated catch block
          e1.printStackTrace();
         }
       }
    } finally{
       used = false;
    }
}

@Override
public void run() {
    if(useCount == 0 && used == false){
       try {
         con.close();
         con = null;
         MysqlMaster.getMysqlMaster().workerGroup.remove(this);
       } catch (SQLException e) {
         e.printStackTrace();
       }
    }
    useCount = 0;
}

}