java多线程查询数据库

package com.ieslab.util; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.concurrent.CountDownLatch; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import org.springframework.jdbc.core.JdbcTemplate; /** * 这个类存在的意义是，之前查询历史事项数据当时查询了3万多条，效率很慢 * 所以，我想搞出一个工具类，提高查询的效率 * 1、先获取全部的结果的条数 * 2、循环多次查询条数 * 3、组织数据，判断一下效率 */ public class BatchDataUtil { private JdbcTemplate jdbcTemplate; private String sql; public BatchDataUtil(JdbcTemplate jdbc, String sql_t){ jdbcTemplate = jdbc; sql = sql_t; } /** * 获取全部查询结果的长度 * @return */ private int getQueryResultSize(){ String sql_t = " select count(*) from ("+sql+") "; return this.jdbcTemplate.queryForInt(sql_t); } /** * 查询 * @return */ public List<Map<String, Object>> getResult(){ List<Map<String, Object>> list = new ArrayList<>(); Map<String, List<Map<String, Object>>> map = new HashMap<String, List<Map<String,Object>>>(); int allSize = getQueryResultSize(); //计算一下应该需要多少个线程执行 int number = allSize/3000 + 1; System.out.println("一共有" + allSize); ExecutorService fixedThreadPool = Executors.newFixedThreadPool(10);//创建5个线程池 CountDownLatch countUtil = new CountDownLatch(number); int index = 1; for(int i = 1; i <= allSize; i = i + 3000){ String sql_t = getResultPageSql(i, i + 2999); BatchDataRunnable runnable = new BatchDataRunnable(jdbcTemplate, sql_t, "index" + index, map, countUtil); Thread thread = new Thread(runnable); fixedThreadPool.execute(thread); index++; } try { countUtil.await(); } catch (InterruptedException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("所有线程结束"); for(int i = 1; i < index; i++){ System.out.println("111111111============" + i); list.addAll(map.get("index" + i)); System.out.println("222222222222============" + i); } System.out.println("wocao"); System.out.println("==============查询出来的结果集：" + list.size()); return list; } /** * 分页查询 * @param start * @param end * @return */ private List<Map<String, Object>> getResultPage(int start, int end){ StringBuffer sb = new StringBuffer(); sb.append(" select * from ( "); sb.append(" SELECT A.*, ROWNUM RN "); sb.append(" FROM (" + sql + ") A "); sb.append(" WHERE ROWNUM <= " + end + " "); sb.append(" ) WHERE RN >= " + start); List<Map<String, Object>> res = this.jdbcTemplate.queryForList(sb.toString()); return res; } private String getResultPageSql(int start, int end){ StringBuffer sb = new StringBuffer(); sb.append(" select * from ( "); sb.append(" SELECT A.*, ROWNUM RN "); sb.append(" FROM (" + sql + ") A "); sb.append(" WHERE ROWNUM <= " + end + " "); sb.append(" ) WHERE RN >= " + start); return sb.toString(); } }