java多线程批量更新数据库的数据

需求：更新机构表的字段，该字段的内容为包括当前机构的机构号和所有父机构的机构号以逗号拼接的形式保存到数据库

该表的数据量大约为10万条（数据库类型：postgresql）

10万条数据每1000条跑一次事务(机构表：organization，机构号：brh_code，父机构号：up_brh_code)

1.最开始的用法

public List<Record> findAllParentData(String orgCode){
    String sql = "WITH RECURSIVE r AS (  " +
                " SELECT brh_code, up_brh_code FROM organization WHERE brh_code = ? " +
                " UNION ALL " + 
                " SELECT o.brh_code, o.up_brh_code FROM organization o,r " +
                " WHERE o.brh_code = r.up_brh_code) " +
                " SELECT r.brh_code FROM r ";
    return Db.find(sql, orgCode);
}

这个方法会查当前机构的所有的父级机构，然后遍历拼接完后再执行update

更新10万条数据本地运行了5个多小时，生产环境运行了2个多小时，这样肯定是不行的，主要是这个循环的sql运行时间比较长

2.采用多线程更新

控制层代码（一个定时任务:每1000跑一个事务）

public class UpdateOrgFamilyField implements Job {
    private final int THREAD_COUNT = 10;
    private final CountDownLatch latch = new CountDownLatch(THREAD_COUNT);
    private static Logger log = Logger.getLogger(UpdateOrgFamilyField.class);

    @Override
    publi void execute (JobExecutionContext jobExecutionContext ) {
        int startLimit = 0;
        int limitLength = 1000;
        boolean next = true;
        ExecutorService es = Executors.newFixedThreadPool(THREAD_COUNT);
        try {
            log.info("开始执行任务");
            while (next) {
                //每次获取1000条数据
                List<Record> list = OrganizationJobService.me.queryOrgTable(startLimit, limitLength);
                //每list.size()跑一次事务
                OrganizationJobService.me.updateOrgFamilyField(list, es);
                startLimit += limitLength;
                if (list.size() < limitLength) {
                    next = false;
                }
            }
            log.info("任务执行完成");
            latch.await();
            es.shutdown();
        } catch (Exception e) {
            log.error("执行更新任务出错");
        }

    }

}

业务层代码

public class OrganizationJobService {

    public static final OrganizationJobService me = Duang.duang(OrganizationJobService.class, Tx.class);
    private static final Logger log = Logger.getLogger(OrganizationJobService.class);

     /**
      *查询机构表，每次查询limitLength条数据
      */
    public list<Record> queryOrgTable(int startlimit, int limitLength) {
        return Db.find("select id, brh_code, up_brh_code, org_family " + 
            " from organization order by id asc limit ? offset ? ", limitLength, startlimit);
    }


    /**
      *更新机构表的org_family字段
      */
    public void updateOrgFamilyField(List<Record> list, ExecutorService es){
        int count = 0;
        for (final Record record : list) {
            es.submit(new Runnable() {
                @Override
                public void run () {
                    StringBuilder sb = new StringBuilder();
                    String brhCode = record.getStr("brh_code");//机构号
                    String upCode = record.getStr("up_brh_code");//父机构号
                    sb.append(brhCode).append(",").append(upCode);
                    //获取父级机构号的字符串
                    String codeStr = queryBrhCodeByUpCode(upCode, sb);
                    record.set("org_family", codeStr);
                    Db.update("organization", "id", record);
                }
            }); 
            count++;
            log.info("更新了" + count + "条数据");
        }
        log.info("成功更新了" + list.size() + "条");
    }


    /**
      *轮询获取父机构的字符串
      */
    private String queryBrhCodeByUpCode (String brhCode, StringBuilder sb){
        String sql = "select up_brh_code from organization where brh_code = ?";
        Record first = Db.findFirst(sql, brhCode);
        if (first == null) {
            return sb.toString();
        }
        String code = first.getStr("up_brh_code");
        if (StringUtils.isNotEmpty(code)) {
            sb.append(",").append(code);
        }
        queryBrhCodeByUpCode(code, sb);
        return sb.toString();
    }
}

 

更新10万条数据生产环境仅仅用了20秒，效率还是非常明显的

开启线程数量：https://www.cnblogs.com/warehouse/p/10810338.html