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

您所在的位置：网站首页 › jdbc读取数据多线程处理 › java 多线程执行 sql 语句(用ExecutorService实现)

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

2024-07-12 21:41| 来源: 网络整理| 查看: 265

可应用场景: 数据量较大, 而单次执行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 ; @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 getResult() { return result; } public void setResult(List result) { this.result = result; } public List getListAll() { return listAll; } public void setListAll(List listAll) { this.listAll = listAll; } }

【本文地址】

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

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

今日新闻

推荐新闻