多线程读取数据库

方法一（第一天的小测试）

连接数据库

package com.xinyuan.ms.service;

import java.sql.*;
import java.util.*;

public class ThreadToMysql extends Thread {

    Integer cid;
    Integer num;
//    Integer page;
    public ThreadToMysql(Integer cid,Integer num){
        this.cid = cid;
        this.num = num;
//        this.page = page;
    }

    public void run(){
        String url = "jdbc:mysql://localhost:3306/wj";
        String name = "com.mysql.cj.jdbc.Driver";
        String user = "";
        String password = "";
        Connection conn = null;
        try {
            Class.forName(name);
            conn = DriverManager.getConnection(url, user, password);//获取连接
            conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (conn!=null) {
            Long startTime=System.currentTimeMillis();//开始时间
            String sql="select * from book ORDER BY  id ASC limit " + ((cid-1)*num+1) +"," + cid*num ;//SQL语句
            String result=null;
            List back = new ArrayList<>();
            try {
                Statement stmt=conn.createStatement();
                ResultSet rs=stmt.executeQuery(sql);//获取结果集
                while (rs.next()) {
                    Map map = new LinkedHashMap();
                    result=rs.getString("title");
                    map.put("title",rs.getString("title"));
                    map.put("id",rs.getString("id"));
                    map.put("date",rs.getString("date"));
                    map.put("author",rs.getString("author"));
                    map.put("cid",rs.getString("cid"));
                    back.add(map);
                }
                conn.commit();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            Long end=System.currentTimeMillis();
            System.out.println(currentThread().getName()+"  查询结果:"+back+"   开始时间:"+startTime+"  结束时间:"+end+"  用时:"+(end-startTime)+"ms");
        } else {
            System.out.println(currentThread().getName()+"数据库连接失败:");
        }
}
}

测试方法

public class TestThreadToMysql {
    public static void main(String[] args) {
        for (int i = 1; i <= 6; i++) {
            new ThreadToMysql(i).start();
        }
    }
}

方法二（第二天的继续研究，由方法一改进而来，合并多方法自制接口，还需改进成单个方法）

Controller层

	@Autowired
    private ThreadQueryService threadQueryService;    
	@RequestMapping("/ThreadToMysql")
    public List<List> test(@RequestBody String tableName) throws ExecutionException, InterruptedException {
        List<List> maxResult = threadQueryService.getMaxResult(tableName);
        return maxResult;
    }

Service层

import com.xinyuan.ms.Util.ThredQuery;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.*;

/***
 * @Author: fyz
 * @Date: 2021/8/29 16:51
 * @Param:
 * @return
 * @logic:
 */

@Service
public class ThreadQueryService {

    public List<List> getMaxResult(String table) throws InterruptedException, ExecutionException {
        long start = System.currentTimeMillis();//开始时间
        List<List> result = new ArrayList<>();//返回结果
        //查询数据库总数量
//        int count = sqlHadle.count(table);
        int count = 20;
        int num = 5;//一次查询多少条
        //需要查询的次数
        int times = count / num;
        if (count % num != 0) {
            times = times + 1;
        }
        //开始页数  连接的是orcle的数据库  封装的分页方式  我的是从1开始
        int bindex = 1;
        //Callable用于产生结果
        List<Callable<List>> tasks = new ArrayList<>();
        for (int i = 1; i <= times; i++) {
            Callable<List> qfe = new ThredQuery(i, num, table);
            tasks.add(qfe);
        }
        //定义固定长度的线程池  防止线程过多
        ExecutorService executorService = Executors.newFixedThreadPool(5);
        //Future用于获取结果
        List<Future<List>> futures=executorService.invokeAll(tasks);
        //处理线程返回结果
        if(futures!=null&&futures.size()>0){
            for (Future<List> future:futures){
                result.addAll(future.get());
            }
        }
        executorService.shutdown();//关闭线程池
        long end = System.currentTimeMillis();
        System.out.println("线程查询数据用时:"+(end-start)+"ms");
        return result;
    }
}

继承Callable的多线程类

/***
 * @Author: fyz
 * @Date: 2021/8/29 16:51
 * @Param:
 * @return
 * @logic:
 */
public class ThredQuery implements Callable<List> {
	//类自己随意创建，根据需求来

    SqlHadle sqlHadle = new SqlHadle();

    private String search;//查询条件 根据条件来定义该类的属性

    private int bindex;//当前页数

    private int num;//每页查询多少条

    private String table;//要查询的表名，也可以写死，也可以从前面传

    private List page;//每次分页查出来的数据

    public ThredQuery(int bindex, int num, String table) {
        this.bindex = bindex;
        this.num = num;
        this.table = table;
    }

    @Override
    public List call() throws Exception {
        //返回数据给Future
        //分页查询数据库数据
        page = sqlHadle.queryTest(bindex, num, table);
        return page;
    }
}

Sql处理的方式类从方法一而来

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/***
 * @Author: fyz
 * @Date: 2021/8/29 16:51
 * @Param:
 * @return
 * @logic:
 */
public class SqlHadle extends Thread{

    public List queryTest(int bindex, int num, String tableName) {
        String url = "jdbc:mysql://localhost:3306/wj";
        String name = "com.mysql.cj.jdbc.Driver";
        String user = "";
        String password = "";
        Connection conn = null;
        try {
            Class.forName(name);
            conn = DriverManager.getConnection(url, user, password);//获取连接
            conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        List back = new ArrayList<>();
        if (conn != null) {
            Long startTime = System.currentTimeMillis();//开始时间
            String sql = "select * from "+tableName+ " ORDER BY  id ASC limit " + ((bindex - 1) * num + 1) + "," + num;//SQL语句
            try {
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);//获取结果集
                while (rs.next()) {
                    Map map = new LinkedHashMap();
                    map.put("title", rs.getString("title"));
                    map.put("id", rs.getString("id"));
                    map.put("date", rs.getString("date"));
                    map.put("author", rs.getString("author"));
                    map.put("cid", rs.getString("cid"));
                    back.add(map);
                }
                conn.commit();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            Long end = System.currentTimeMillis();
            System.out.println(currentThread().getName() + "  查询结果总数:"+back.size()+"条" + back + "   开始时间:" + startTime + "  结束时间:" + end + "  用时:" + (end - startTime) + "ms");
        } else {
            System.out.println(currentThread().getName() + "数据库连接失败:");
        }
        return back;
    }
}

问题处理：先获取总数再计算得到线程数，总数获取方法

这个方法直接放在sql处理类里，int count调用此方法

public int queryCount(String tableName){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(*) allSum  FROM ").append(tableName);
        Query query =  entityManager.createNativeQuery(sql.toString());
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> resultList = query.getResultList();
        return Integer.valueOf(String.valueOf(resultList.get(0).get("allSum")));
    }