java 多线程执行 sql 语句(用ExecutorService实现)

可应用场景: 数据量较大, 而单次执行sql时间长

数据量过大的情况, 单线程执行需要很长一段时间,我们需要开启多线程执行,

注意mysql支持的连接数, 不要超过这个连接数

 创建 服务, Executors.newFixedThreadPool(70);

里面的数字根据自己的需求和mysql的能力来决定 比如我这边开70个

这里我用的读取文件流之后开启线程, 具体场景根据需求自己定

public void test() throws Exception{
		List<List<String>> listAll = new  ArrayList<>();
		//要写入的文件
		File filere = new File("ssd.xlsx");
		//要读取的文件
		File file = new File("dddd.csv");
		InputStreamReader inputStream = new InputStreamReader(new FileInputStream(file),"UTF-8");
		BufferedReader bufferedReader = new BufferedReader(inputStream);
		String  rudata = "";
		ExecutorService loop = Executors.newFixedThreadPool(70);
		int i = 0;
		while ((rudata = bufferedReader.readLine()) != null) {
			List<String> result = new ArrayList<String>();
			rudata = rudata.replaceAll("", "");
			String[] strings = rudata.split(",");
			String daima = "";
			String nianfen = "";
			String kaishi = "";
			String jieshu = "";
			try {
				daima = strings[0];
			} catch (Exception e) {
			}
			try {
				nianfen = strings[1];
			} catch (Exception e) {
			}
			try {
				kaishi = strings[2];
			} catch (Exception e) {
			}
			try {
				jieshu = strings[3];
			} catch (Exception e) {
			}

			result.clear();
			result.add(daima);
			result.add(nianfen);
			result.add(kaishi);
			result.add(jieshu);
			String sql = "SELECT newsnum_title FROM shares WHERE scode = '"+daima+"' ";
				i++;
				Thread2CollectionRate tempTread = new Thread2CollectionRate();
				//把需要装数据的Map放到线程中去
				tempTread.setResult(result);
				tempTread.setListAll(listAll);
				//把需要执行的SQL放到线程中去
				tempTread.setSql(sql);
				//执行线程
				loop.execute(tempTread);
		}
		loop.shutdown();
		try {
			while(true) {
				if(loop.isTerminated()) {
					//listMap中就是你所需要的全部数据
					System.out.println("listAll.size() :" + listAll.size());
					break;
				}
				//每次循环等待250毫秒
				Thread.sleep(250);
			}
		} catch (InterruptedException e1) {
			e1.printStackTrace();
		}
		bufferedReader.close();
		ExcelUtil.write(filere, listAll);
	}

创建一个继承Runnable的类用来执行sql语句并且返回(要记得释放连接))

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.mysql.jdbc.Connection;

public class Thread2CollectionRate  implements Runnable{



	private String sql;
	
	private Statement statement;

	
	private List<String> result ;
	List<List<String>> listAll ;

	@Override
	public void run() {
		ResultSet rse;
		try {
			// 声明Connection对象
			Connection con = null;
			// 驱动程序名
			String driver = "com.mysql.jdbc.Driver";
			// URL指向要访问的数据库名mydata
			String url = "jdbc:mysql://********ToNull";
			// MySQL配置时的用户名
			String user = "root";
			// MySQL配置时的密码
			String password = "****";
			// 遍历查询结果集
			System.out.println("开始连接数据库");
			// 加载驱动程序
			Class.forName(driver);
			con = (Connection) DriverManager.getConnection(url, user, password);
			if (!con.isClosed())
				System.out.println("连接数据库成功");
			// 2.创建statement类对象，用来执行SQL语句！！
			Statement statement = con.createStatement();
			rse = statement.executeQuery(sql);
			while (rse.next()) {
				result.add(rse.getString("newsnum_title"));
			}
			statement.close();
			con.close();
			listAll.add(result);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}


	public Statement getStatement() {
		return statement;
	}

	public void setStatement(Statement statement) {
		this.statement = statement;
	}

	public List<String> getResult() {
		return result;
	}

	public void setResult(List<String> result) {
		this.result = result;
	}

	public List<List<String>> getListAll() {
		return listAll;
	}

	public void setListAll(List<List<String>> listAll) {
		this.listAll = listAll;
	}

}