多线程查询大数据（测试）

多线程查询大数据（测试）

线程的学习参考：https://www.cnblogs.com/ggq-insist-qiang/p/10619346.html

导入jar包：mysql-connector-java-8.0.15.jar

代码演示：

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;

public class Multithreading implements Callable<List<Map<String, Object>>> {
    
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    
    static final String DB_URL ="jdbc:mysql://127.0.0.1:3306/aaa?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8";
    
    static final String USER = "root";
    
    static final String PASS = "root";
    
    private List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();

    private int bindex;
    
    private int num;
    
    public Multithreading(List<Map<String, Object>> multithreadingService,int bindex,int num){
        this.list=multithreadingService;
        this.bindex=bindex;
        this.num=num;
    }
 
    @Override
    public List<Map<String, Object>> call() throws Exception {
        System.out.println(bindex+"~"+(bindex+num+"开始查询"));
        //通过service查询得到对应结果
        List<Map<String, Object>>  list  =new ArrayList<>();
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(DB_URL,USER,PASS);
                ps = conn.prepareStatement("select * from aaa limit ?,?");
                ps.setInt(1, bindex);
                ps.setInt(2, num);
                rs = ps.executeQuery();
                while(rs.next()){
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("id", rs.getInt("id"));
                    map.put("name", rs.getString("name"));
                    list.add(map);
                }
        }catch (Exception e) {
               e.printStackTrace();
        }finally{
               if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
               if(ps!=null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
               if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
           }
        
            Thread.sleep(1000);
            System.out.println(bindex+"~"+(bindex+num+"查询结束"));
            return list;
    }
}

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

public class Demo {
    
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    
    static final String DB_URL ="jdbc:mysql://127.0.0.1:3306/aaa?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8";
    
    static final String USER = "root";
    
    static final String PASS = "root";

    public static void main(String[] args) {
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        long start = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(DB_URL,USER,PASS);
                ps = conn.prepareStatement("select count(1) from aaa");
                rs = ps.executeQuery();
                int count = 0;
                while(rs.next()){
                      count = rs.getInt(1);
                }
               int num = 50;
               int times=count / num;
               if(count%num !=0) {
                   times=times+1;
               }
               int bindex = 0;
               List<Callable<List<Map<String, Object>>>> tasks = new ArrayList<Callable<List<Map<String, Object>>>>();
               for(int i = 0; i <times ; i++){
                   Callable<List<Map<String, Object>>> qfe = new Multithreading(list, bindex, num);
                   tasks.add(qfe);
                   bindex=bindex+num;
               }
               //定义固定长度的线程池  防止线程过多
               ExecutorService execservice = Executors.newFixedThreadPool(5);
               List<Future<List<Map<String, Object>>>> futures = execservice.invokeAll(tasks);
               // 处理线程返回结果
               if (futures != null && futures.size() > 0) {
                   for(Future<List<Map<String, Object>>> future : futures) {
                       list.addAll(future.get());
                   }
               }
               //关闭线程池
               execservice.shutdown();  
               long end = System.currentTimeMillis();
               System.out.println("查询所有数据-------------"+list);
               System.out.println("查询总数据量-------------"+count);
               System.out.println("查询数据用时----------------"+(start-end)+"ms");
           }catch(SQLException se){
              se.printStackTrace();
           }catch(Exception e){
              e.printStackTrace();
           }finally{
               if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
               if(ps!=null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
               if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
           }
    }
}