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

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

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

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

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

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

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

public void test() throws Exception{List> 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 result = new ArrayList();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 result ;List> listAll ;@Overridepublic void run() {ResultSet rse;try {// 声明Connection对象Connection con = null;// 驱动程序名String driver = "com.mysql.jdbc.Driver";// URL指向要访问的数据库名mydataString 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 getResult() {return result;}public void setResult(List result) {this.result = result;}public List> getListAll() {return listAll;}public void setListAll(List> listAll) {this.listAll = listAll;}}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！