Java实现多线程数据量比较大的分页查询

 

在开发实际应用中会遇到查询比较多的数据，可以用多线程实现

UserMapper 

 

@Repository("userMapper")
@Mapper
public interface UserMapper {

    @Results(id = "userResultMap", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "name", column = "name"),
            @Result(property = "age", column = "age"),
            @Result(property = "sex", column = "sex")
    })
    @Select("select * from user where 1=1 and name=#{name} limit #{offset},#{rows}")
    List<Map<String, Object>> queryAll(Map<String , Object> map);

    @Select("select count(*) from user where 1=1 and name=#{name}")
    int count(String name);//每有条件可以不用name
}

UserServiceImpl

@Service("userService")
public class UserServiceImpl implements UserService {

    @Autowired
    UserMapper userMapper;

    @Override
    public List<Map<String, Object>> queryAll(int offset, int rows, String name) {
        Map<String , Object> map = new HashMap<String, Object>();
        map.put("offset", (offset-1)*rows);
        map.put("rows", rows);
        map.put("name",name);
        return userMapper.queryAll(map);
    }

    @Override
    public int count(String name) {
        return userMapper.count(name);
    }
}

线程类TaskThreadQuery实现Callable

public class TaskThreadQuery implements Callable<List<Map<String, Object>>> {

    private UserService userService;
    private String name;//条件可以不给

    private int offset;
    private int rows;

    public TaskThreadQuery(UserService userService,String name,int offset,int rows){
        this.userService = userService;
        this.name = name;
        this.offset = offset;
        this.rows = rows;
    }

    @Override
    public List<Map<String, Object>> call() throws Exception {
        List<Map<String, Object>>  list = userService.queryAll(offset,rows,name);
        if (list != null && list.size() > 0){
            return list;
        }
        return null;
    }
}

Controller

@RestController
@RequestMapping("tc")
public class TestController {

    @Autowired
    UserService userService;

     @RequestMapping("/queryAll")
    public List<Map<String, Object>> queryAll(String name,int offset) throws Exception {
        long startTime = System.currentTimeMillis();
        List<Map<String, Object>> list = new ArrayList<>();
        int count = userService.count(name);//根据条件查询符合的行数
        int rows = 10000;//自定义每次查询的行数
        //int times = count / rows;//计算遍历的次数
        //if (count % rows != 0){
        //    times += 1;
        //}
        int allpage = (count-1) / rows + 1;//计算遍历的次数
        System.out.println("开始查询-->" );
        //int offset = 0;
        List<Callable<List<Map<String, Object>>>> taskList = new ArrayList<Callable<List<Map<String, Object>>>>();//创建任务
        for (int i = 0; i < allpage; i++) {
            Callable<List<Map<String, Object>>> task= new TaskThreadQuery(userService,name,offset, rows);//条件查询，name没有就给null
            taskList.add(task);
            offset += rows;
        }
        int threadNum = 10;//可自定义线程数量
        ExecutorService executor = Executors.newFixedThreadPool(threadNum);
        List<Future<List<Map<String,Object>>>> futureList = executor.invokeAll(taskList);
        System.out.println(futureList.size());
        if (futureList != null && futureList.size() > 0){
            for (Future<List<Map<String,Object>>> future:futureList) {
                if(future.get() != null) {
                    list.addAll(future.get());
                }
            }
        }
        executor.shutdown();//关闭线程
        long endTime = System.currentTimeMillis();
        long s = ((endTime - startTime) / 1000);
        System.out.println(threadNum  + "个线程查询花了" + s + "秒" );
        return list;//返回结果集给前端
    }

}