java使用多线程操作任务（内部类）

1、先是Java中对线程池定义的一个接口（ExecutorService）

    //创建一个可缓存线程池，如果线程池长度超过处理需要，可灵活回收空闲线程，若无可回收，则新建线程。
    private ExecutorService executorService = Executors.newCachedThreadPool();

    //创建一个定长线程池，可控制线程最大并发数，超出的线程会在队列中等待。
    private ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

    //创建一个定长线程池，支持定时及周期性任务执行。
    private ExecutorService executorService = Executors.newScheduledThreadPool(10);

    //创建一个单线程化的线程池，它只会用唯一的工作线程来执行任务，保证所有任务按照指定顺序(FIFO, LIFO, 优先级)执行。
    private ExecutorService executorService = Executors.newSingleThreadExecutor();

2、编写一个内部类实现

 class SyncDatabase implements Runnable {

        private String selectSql;

        public SyncDatabase(String selectSql) {
            this.selectSql = selectSql;
        }

        @Override
        public void run() {
            try {
                log.debug("开始任务:" + selectSql);
               } catch (Exception ex) {
                ex.printStackTrace();
            }finally {
 
              
            }
            log.debug("------------------------执行完成--------------------------------");
}

3、执行任务

 //这里执行任务
executorService.submit("selectSql");
 

--------------------------------------------------------------------------------------------------------------------------------

最后是一个完整的代码块（编写的是一个插入或更新信息的任务）

package com.gdgxkj.server.synd.datex.service.impl;

import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.gdgxkj.server.common.mvc.ServiceSupportSync;
import com.gdgxkj.server.common.utils.StringUtils;
import com.gdgxkj.server.synd.Job.TaskJob;
import com.gdgxkj.server.synd.datex.dao.ConversionTaskMapper;
import com.gdgxkj.server.synd.datex.entity.ConversionTask;
import com.gdgxkj.server.synd.datex.entity.DataSource;
import com.gdgxkj.server.synd.datex.service.ConditionsServiceI;
import com.gdgxkj.server.synd.datex.service.ConversionTaskServiceI;
import com.gdgxkj.server.synd.datex.service.DataSourceServiceI;
import com.gdgxkj.server.synd.db.datasource.DBColumn;
import com.gdgxkj.server.synd.db.datasource.DBDriver;
import com.gdgxkj.server.synd.db.datasource.JdbcTemplate;
import com.gdgxkj.server.synd.db.datasource.JdbcTemplateManager;
import com.gdgxkj.server.synd.mq.MqTag;
import com.gdgxkj.server.synd.mq.Producer;
import com.gdgxkj.server.synd.mq.handle.impl.HandleMessage;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.quartz.JobDataMap;
import org.quartz.JobKey;
import org.quartz.Scheduler;
import org.quartz.SchedulerException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.scheduling.quartz.CronTriggerFactoryBean;
import org.springframework.scheduling.quartz.JobDetailFactoryBean;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ConversionTaskService extends ServiceSupportSync<ConversionTaskMapper, ConversionTask> implements
        ConversionTaskServiceI {

    private ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
//    private ExecutorService executorService = Executors.newCachedThreadPool();

//    //创建一个可缓存线程池，如果线程池长度超过处理需要，可灵活回收空闲线程，若无可回收，则新建线程。
//    private ExecutorService executorService = Executors.newCachedThreadPool();
//
//    //创建一个定长线程池，可控制线程最大并发数，超出的线程会在队列中等待。
//    private ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
//
//    //创建一个定长线程池，支持定时及周期性任务执行。
//    private ExecutorService executorService = Executors.newScheduledThreadPool(10);
//
//    //创建一个单线程化的线程池，它只会用唯一的工作线程来执行任务，保证所有任务按照指定顺序(FIFO, LIFO, 优先级)执行。
//    private ExecutorService executorService = Executors.newSingleThreadExecutor();


    @Resource
    private Scheduler scheduler;
    @Resource
    ConditionsServiceI conditionsService;
    @Resource
    DataSourceServiceI dataSourceService;
    @Resource
    private Producer producer;

    @Override
    public void sendMqTask(Long taskId) throws Exception {
        sendMqTaskTest(taskId, false);
    }


    /**
     * @param taskId
     * @param testCase false发送mq,true：不发送mq
     * @return
     */
    @Override
    public boolean sendMqTaskTest(Long taskId, boolean testCase) {
        //1、获取数据任务信息
        ConversionTask task = getById(taskId);
        //2、获取条件，看是否需要增加条件
        List<String> condList = conditionsService.getConditionsList(task.getId());
        String dataSourceSql = task.getDataSourceSql();
        if (condList.size() > 0) {
            dataSourceSql = StringUtils.formateMark(task.getDataSourceSql(), condList);
        }


        //获取表原数据源
        DataSource dataSource = dataSourceService.getById(task.getDataSourceId());

        JdbcTemplate jdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(dataSource);
        //防止单表同步数据量过大时处理
        Integer count = findCountSql(jdbcTemplate, dataSourceSql);
        Integer pageSize = 500;
        if (count > 3000) {
            //如果数据量大雨3000条，查询语句作分页面查询处理
            Integer timeNumber = (count / 3000) + 1;
            int i = 1;
            while (i <= timeNumber) {
                String pageSql = getPageSql(dataSource.getDbType(), dataSourceSql, i, 3000);
                i++;
                sendMqMesage(jdbcTemplate, pageSql, task, count, pageSize, testCase);
                if (testCase) {
                    break;
                }
            }
        } else {
            //不分页发送mq数据
            if (count != 0) {
                sendMqMesage(jdbcTemplate, dataSourceSql, task, count, pageSize, testCase);
            }
        }
        return false;
    }


    private void sendMqMesage(JdbcTemplate jdbcTemplate, String pageSql, ConversionTask conversionTask, Integer maxSize, int pageSize) {
        sendMqMesage(jdbcTemplate, pageSql, conversionTask, maxSize, pageSize, false);
    }

    /**
     * 处理任务数据发送mq
     *
     * @param jdbcTemplate
     * @param pageSql
     * @param conversionTask
     * @param maxSize
     * @param pageSize
     */
    private void sendMqMesage(JdbcTemplate jdbcTemplate, String pageSql, ConversionTask conversionTask, Integer maxSize, int pageSize, boolean testCase) {
        List<Map<String, Object>> map = jdbcTemplate.queryForList(pageSql.toUpperCase());
        log.debug("taskId:{},size:{}", conversionTask.getId() + "", map.size() + "");
        if (testCase) {
            //如果是测试，不发送相关消息到同步去
            map = new ArrayList<>();
        }
        if (map.size() > 0) {
            Integer timeNumber = (map.size() / pageSize) + 1;
            if (timeNumber == 1) {
                sendMqMesage(map, conversionTask, 0, maxSize, pageSql);
            } else if (timeNumber > 1) {
                Integer mapIndex = 1;
                List<List<Map<String, Object>>> mapList = subList(map, pageSize);
                for (List<Map<String, Object>> maps : mapList) {
                    sendMqMesage(maps, conversionTask, mapIndex, maxSize, pageSql);
                    mapIndex++;
                }
            }
        }
    }


    /**
     * 发送数据到mq
     *
     * @param map
     * @param task
     * @param mapIndex
     */
    private void sendMqMesage(List<Map<String, Object>> map, ConversionTask task, Integer mapIndex, Integer maxSize, String dataSourceSQL) {
        if (map.size() > 0) {
//            Map<String, Object> maps = map.get(0);
//            map = new ArrayList<>();
//            map.add(maps);
            String bods = JSONObject.toJSONStringWithDateFormat(map, "yyyy-MM-dd HH:mm:ss", SerializerFeature.WriteMapNullValue);
            DataSource dbSource = dataSourceService.getById(task.getDbId());
            MqTag mqTag = MqTag.createByValue(dbSource.getSyncName().toLowerCase());
            String title = "任务：" + task.getName() + ",操作表：" + task.getTableName() + "操作记录数/总数：" + map.size() + "/" + maxSize;
            String tag = mqTag.getValue() + ":" + task.getTableName();
            if (mapIndex != 0) {
                tag += "-" + mapIndex + "(" + map.size() + "/" + maxSize + ")";
            }
            producer.sendMessage(bods, title, mqTag.getValue(), tag, task.getId(), dataSourceSQL);
        }
    }


    /**
     * 启动任务 需要自己完善逻辑，这里我用uuid作为taskCode 保证唯一
     * 启动之前要通过数据库查询是否任务已经启动，如果启动了就不能启动了
     * 启动成功了 要把数据库的任务状态改为启动中
     */
    @Override
    public void start() {
        log.error("启动同步任务.........");
        List<ConversionTask> taskList = findList(ConversionTask.builder().isEnable(ConversionTask.TASK_NOT_UPDATE).build());
        taskList.forEach(conversionTask -> {
            startTask(conversionTask);
        });
        log.error("成功作业成功，启动了：" + taskList.size() + "个任务！");
    }

    /**
     * 结束所有任务
     */
    @Override
    public void end() {
        List<ConversionTask> taskList = findList(ConversionTask.builder().isEnable(ConversionTask.TASK_NOT_UPDATE).build());
        taskList.forEach(conversionTask -> {
            endTask(conversionTask);
        });
    }


    /**
     * 开始任务调度
     *
     * @param taskId 任务名称 需要唯一标识，停止任务时需要用到
     */

    @Override
    public String startTask(String taskId) {
        return startTask(getById(taskId));
    }

    @Override
    public String startTask(ConversionTask task) {
        String msg = task.getId() + "任务启动失败";
        //任务开始的corn表达式
        String cronExpress = task.getCron();
        //一些特定的批量任务，不能启动（cronExpress == null）
        if (StringUtils.isNotBlank(cronExpress)) {
            try {
                JobDetailFactoryBean jobDetailFactoryBean = new JobDetailFactoryBean();
                jobDetailFactoryBean.setName(task.getId().toString());
                jobDetailFactoryBean.setGroup(Scheduler.DEFAULT_GROUP);
                //TaskJob.class 是任务所要执行操作的类
                jobDetailFactoryBean.setJobClass(TaskJob.class);
                //任务需要的参数可以通过map方法传递，
                jobDetailFactoryBean.setJobDataMap(getJobDataMap(objectToMap(task)));
                jobDetailFactoryBean.afterPropertiesSet();
                CronTriggerFactoryBean cronTriggerFactoryBean = new CronTriggerFactoryBean();
                cronTriggerFactoryBean.setBeanName(task.getId().toString());
                cronTriggerFactoryBean.setCronExpression(cronExpress);
                cronTriggerFactoryBean.setGroup(Scheduler.DEFAULT_GROUP);
                cronTriggerFactoryBean.setName("cron_" + task.getId());
                //先停止再启动（防止启动存在出错）
                endTask(task);
                cronTriggerFactoryBean.afterPropertiesSet();
                scheduler.scheduleJob(jobDetailFactoryBean.getObject(), cronTriggerFactoryBean.getObject());
                msg = task.getId() + "任务启动成功";
                log.error(msg);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return msg;
    }

    /**
     * 结束任务调度
     *
     * @param taskId
     */
    @Override
    public void endTask(String taskId) {
        endTask(getById(taskId));
    }

    @Override
    public void endTask(ConversionTask task) {
        try {
            scheduler.deleteJob(JobKey.jobKey(task.getId().toString(), Scheduler.DEFAULT_GROUP));
//            System.out.println(task.getId() + "任务停止成功");
            log.error("{}任务停止成功", task.getId());
        } catch (SchedulerException e) {
            e.printStackTrace();
            log.error("{}任务停止失败", task.getId());
        }
    }


    /**
     * 将HashMap转为JobDataMap
     *
     * @param params
     * @return
     */
    private JobDataMap getJobDataMap(Map<String, Object> params) {
        JobDataMap jdm = new JobDataMap();
        Set<String> keySet = params.keySet();
        Iterator<String> it = keySet.iterator();
        while (it.hasNext()) {
            String key = it.next();
//            System.out.println("key:" + key);
            jdm.put(key, params.get(key));
        }
        return jdm;
    }


    /**
     * 将Object对象里面的属性和值转化成Map对象
     *
     * @param obj
     * @return
     * @throws IllegalAccessException
     */
    private Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
        Map<String, Object> map = new HashMap<>();
        Class<?> clazz = obj.getClass();
        List<Field> fieldList = new ArrayList<>();
        //当父类为null的时候说明到达了最上层的父类(Object类)
        while (clazz != null) {
            fieldList.addAll(Arrays.asList(clazz.getDeclaredFields()));
            //得到父类,然后赋给自己
            clazz = clazz.getSuperclass();
        }
        for (Field field : fieldList) {
            field.setAccessible(true);
            String fieldName = field.getName();
            Object value = field.get(obj);
            map.put(fieldName, value);
        }
        return map;
    }

    /**
     * 把查询数据语句转换成统计语句
     *
     * @param sql 查询语句
     * @return
     */
    private String getCountSql(String sql) {
        if (sql.trim().toUpperCase().startsWith("SELECT")) {
            StringBuffer sbCount = new StringBuffer("SELECT COUNT(*) FROM ( ");
            sbCount.append(sql);
            sbCount.append(" ) TABLE_COUNT");
            return sbCount.toString();
        }
        return sql;
    }


    /**
     * 根据表名获取主键名称
     *
     * @param tableName
     * @return
     */
    private String getKeyName(String tableName) {
        StringBuffer sbKeySql = new StringBuffer(" SELECT column_name from user_cons_columns cu ");
        sbKeySql.append(",user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' ");
        sbKeySql.append(" and au.table_name ='" + tableName + "' ");
        return sbKeySql.toString();

    }


    /**
     * 设置分页查询
     *
     * @param dbType   数据库驱动类型
     * @param sql      结果集查询
     * @param pageNo   页数
     * @param pageSize 条数
     * @return
     */
    private String getPageSql(String dbType, String sql, int pageNo, int pageSize) {
        String pageSql = sql;
        if (sql.toUpperCase().indexOf("SELECT") > 0) {
            int endPage = pageNo * pageSize;
            int startPage = endPage - pageSize;

            DBDriver driver = DBDriver.createByValue(dbType);
            switch (driver) {
                case ORACLE:
                    StringBuffer sbCount = new StringBuffer(" SELECT T1.* ");
                    //这里替换相关的表数据（表名也行）
                    sbCount.append("FROM ({0}) T1,");
                    sbCount.append("(SELECT RID FROM (SELECT ROWNUM RN,T.RID FROM (SELECT ROWID RID FROM ({1}) ) T");
                    //结束行
                    sbCount.append("   WHERE ROWNUM <= {2})");
                    //开始行
                    sbCount.append("    WHERE RN >{3}) T2 WHERE T1.ROWID = T2.RID");

                    pageSql = StringUtils.formate(sbCount.toString(), sql, sql, endPage + "", startPage + "");
                    break;
                case MYSQL:
                case KINGBASE:
                    pageSql += "  LIMIT " + startPage + " OFFSET " + endPage;
                default:
            }
        }
        return pageSql;
    }


    /*
     * 将一个list均分成n个list,主要通过偏移量来实现的
     */
    public static <T> List<List<T>> subList(List<T> list, int toIndex) {
        List<List<T>> result = new ArrayList<>();
        int listSize = list.size();
        for (int i = 0; i < list.size(); i += toIndex) {
            if (i + toIndex > listSize) {
                toIndex = listSize - i;
            }
            List newList = list.subList(i, i + toIndex);
            result.add(newList);
        }
        return result;
    }


    /**
     * 获取统计行数
     *
     * @param jdbcTemplate
     * @param dataSourceSql
     * @return
     */
    private Integer findCountSql(JdbcTemplate jdbcTemplate, String dataSourceSql) {
        try {
            Integer count = jdbcTemplate.queryForObject(getCountSql(dataSourceSql), Integer.class);
            return count;
        } catch (Exception e) {

        }
        return 0;
    }


    @Override
    public void contrastDatabase(Long dataSourceId, Long dbId, String dataUser, String dbUser) {
        DataSource dataSource = dataSourceService.getById(dataSourceId);
        DataSource sdataSource = dataSourceService.getById(dbId);
//            String sourceSql = "select t.table_name from user_tables t";
        String sourceSql = "select t.OWNER,t.table_name,t.num_rows from all_tables t ";
        //获取表原数据源
        JdbcTemplate jdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(dataSource);
        //获取表原数据源（操作）
        JdbcTemplate sjdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(sdataSource);

        String dataSourceSql = sourceSql + " where t.OWNER = '" + dataSource.getDbUser() + "'";
        String sdataSourceSql = sourceSql + " where t.OWNER = '" + sdataSource.getDbUser() + "'";

        if (StringUtils.isNotBlank(dataUser)) {
            dataSourceSql = sourceSql + " where t.OWNER = '" + dataUser + "'";
        }
        if (StringUtils.isNotBlank(dataUser)) {
            sdataSourceSql = sourceSql + " where t.OWNER = '" + dbUser + "'";
        }

        List<Map<String, Object>> map = jdbcTemplate.queryForList(dataSourceSql);
        List<Map<String, Object>> smap = sjdbcTemplate.queryForList(sdataSourceSql);

//            List<String> tableAllList1 = jdbcTemplate.getAllTables();
//            List<String> tableAllList2 = sjdbcTemplate.getAllTables();

        List<String> msgSt = Lists.newArrayList();
        List<String> msgbt = Lists.newArrayList();
        String tableNames = "";

        for (Map<String, Object> objMap : map) {
            String owner = objMap.get("OWNER").toString();
            String tableName = owner + "." + objMap.get("table_name").toString();
            System.out.println("表名：" + tableName);
//                List<String> keyNames = jdbcTemplate.getPrimaryKeys(tableName);
//                if (keyNames.size() == 0) {
//                    getKeyNames(tableName, jdbcTemplate, true);
//                    continue;
//                }
//                System.out.println(tableName + "表主键：" + keyNames.toString());

            switch (tableName) {
                case "ZYC.T_TS_TZSBXX_TJY_TBJLB":
                case "ZYC.T_TS_QYXX_TJY_TBJLB":
                case "ZYC.T_TS_TZSBXXB_XGJLB":
                    System.out.println(tableName + "表数据过大，不统计！");
                    continue;
                default:

            }

            StringBuilder sbSelect = new StringBuilder("SELECT count(1) FROM ");
            sbSelect.append(tableName);
            Integer count1 = jdbcTemplate.queryForObject(sbSelect.toString(), Integer.class);
            try {
                Integer count2 = sjdbcTemplate.queryForObject(sbSelect.toString(), Integer.class);
                String msg = dataSource.getDbHost() + ",tableName:" + tableName + ",count:" + count1 + "------" + sdataSource.getDbHost() + ",tableName:" + tableName + ",count:" + count2;
                if (count1.equals(count2)) {
                    msgSt.add(msg);
                } else {
//                        StringBuilder deleteSql = new StringBuilder("DELETE FROM  ");
//                        deleteSql.append(tableName);
//                        jdbcTemplate.update(deleteSql.toString());
//                        deleteSql(tableName, sdataSource);


//                        StringBuffer sbSql = new StringBuffer();
//                        sbSql.append(" SELECT * FROM ");
//                        sbSql.append(tableName);
//                        sbSql.append(" ");
//                        String keyNameSql = getKeyName(tableName);
//                        List<String> keyNames = sjdbcTemplate.queryForList(keyNameSql, String.class);
//                        List<String> keyNames =  jdbcTemplate.getPrimaryKeys(tableName);
//                        if (keyNames.size() == 0) {
//                            getKeyNames(tableName, jdbcTemplate,true);
//                            continue;
//                        }
//                        System.out.println(tableName  +"表主键："+keyNames.toString());
//                       keyNames =  sjdbcTemplate.getPrimaryKeys(tableName);
//                        if (keyNames.size() == 0) {
//                            getKeyNames(tableName, sjdbcTemplate,true);
//                            continue;
//                        }

                    if (StringUtils.isNotBlank(tableNames)) {
                        tableNames += ",";
                    }
                    tableNames += tableName;
                    msgbt.add(msg);
                }
            } catch (Exception ex) {
                String msg = dataSource.getDbHost() + ",tableName:" + tableName + ",count:" + count1 + "------" + sdataSource.getDbHost() + ",tableName:" + tableName + "没找到表！";
                msgbt.add(msg);
            }
        }
        System.out.println("-----相同数据------");
        msgSt.parallelStream().forEach(System.out::println);
        System.out.println("-----相同数据 --end------");

        System.out.println("-----不同数据------");
        msgbt.parallelStream().forEach(System.out::println);
        System.out.println("-----不同数据 --end------");
        System.out.println("-----执行完成------");
        System.out.println(tableNames);
//            syncDatabase(taskId, tableNames);

        // 交集
//            List<String> intersection = list1.stream().filter(item -> list2.contains(item)).collect(Collectors.toList());
//            System.out.println("---交集 intersection---");
//            intersection.parallelStream().forEach(System.out :: println);

        // 差集 (list1 - list2)
//            List<String> reduce1 = list1.stream().filter(item -> !list2.contains(item)).collect(Collectors.toList());
//            System.out.println("---差集 reduce1 (list1 - list2)---");
//            reduce1.parallelStream().forEach(System.out::println);
//
//            // 差集 (list2 - list1)
//            List<String> reduce2 = list2.stream().filter(item -> !list1.contains(item)).collect(Collectors.toList());
//            System.out.println("---差集 reduce2 (list2 - list1)---");
//            reduce2.parallelStream().forEach(System.out::println);

        // 并集
//            List<String> listAll = list1.parallelStream().collect(Collectors.toList());
//            List<String> listAll2 = list2.parallelStream().collect(Collectors.toList());
//            listAll.addAll(listAll2);
//            System.out.println("---并集 listAll---");
//            listAll.parallelStream().forEachOrdered(System.out :: println);

        // 去重并集
//            List<String> listAllDistinct = listAll.stream().distinct().collect(Collectors.toList());
//            System.out.println("---得到去重并集 listAllDistinct---");
//            listAllDistinct.parallelStream().forEachOrdered(System.out :: println);
//
//            System.out.println("---原来的List1---");
//            list1.parallelStream().forEachOrdered(System.out :: println);
//            System.out.println("---原来的List2---");
//            list2.parallelStream().forEachOrdered(System.out :: println);


    }

    @Override
    public void syncTableAnnotation() {
        List<ConversionTask> taskList = findList(ConversionTask.builder().isEnable(ConversionTask.TASK_NOT_UPDATE).build());
        taskList.forEach(conversionTask -> {
            updateConversionAnnotation(conversionTask.getDataSourceId(),conversionTask,0);
        });
    }


    public void updateConversionAnnotation(Long dbId,ConversionTask conversionTask,Integer isUpdate){
        DataSource dataSource = dataSourceService.getById(dbId);
        String sourceSql = "select * from user_tab_comments WHERE TABLE_name =";
        String tableName = conversionTask.getTableName();
        if(tableName.indexOf(".")> 0){
            tableName = tableName.substring(tableName.indexOf(".")+1);
        }
        sourceSql += "'"+tableName+"'";
        //获取表原数据源
        //mysql 语句不一样处理
        switch (dataSource.getDbType()) {
            case JdbcUtils.MYSQL:
                sourceSql = "select TABLE_NAME,TABLE_COMMENT as COMMENTS  from INFORMATION_SCHEMA.TABLES ";
                sourceSql += "WHERE   TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA ='"+dataSource.getDbUser()+"'" ;
                sourceSql += " AND TABLE_NAME ='"+tableName+"'";
                break;
            default:
        }

        JdbcTemplate jdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(dataSource);
        List<Map<String, Object>> map = jdbcTemplate.queryForList(sourceSql);
        System.out.println(sourceSql);
        for (Map<String, Object> map1:  map) {
            String comments = map1.get("COMMENTS").toString();
            System.out.println(tableName+":"+comments);
            if(StringUtils.isNotBlank(comments)){
                conversionTask.setTableAnnotation(comments);
                updateById(conversionTask);
            }else{
                if(isUpdate == 0){
                    updateConversionAnnotation(conversionTask.getDbId(),conversionTask,1);
                }
            }
        }

        if(map.size() == 0 ){
            if(isUpdate == 0){
                updateConversionAnnotation(conversionTask.getDbId(),conversionTask,1);
            }
        }

        if(isUpdate == 1){
            updateConversionAnnotation(202110250000000005L,conversionTask,2);
        }

    }

    /**
     * @param
     * @Description:
     * @Author: xuzj
     * @Title: 对比两数据库数据量
     * @exception:
     * @Date: 2021-0-28 8:52
     **/
    @Override
    public void contrastDatabase(Long taskId) {
        //1、获取数据任务信息
        ConversionTask task = getById(taskId);
        //这里获取到的是配置的相应的实例名，如
        if (task.getDataSourceSql().equals("整库同步")) {
            contrastDatabase(task.getDataSourceId(), task.getDbId(), null, null);
        }
    }


    /**
     * 手动选择数据源同步任务
     * @param dataId
     * @param dbId
     * @param dataUser
     * @param dbUser
     * @param tableNames
     */
    @Override
    public void syncDatabase(Long dataId, Long dbId, String dataUser, String dbUser, String tableNames) {
        //获取表原数据源
        DataSource dataSource = dataSourceService.getById(dataId);
        //获取表原数据源（操作）
        DataSource sdataSource = dataSourceService.getById(dbId);

        String sourceSql = "select t.OWNER,t.table_name,t.num_rows from all_tables t ";
        //获取表原数据源
        JdbcTemplate jdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(dataSource);
        //获取表原数据源（操作）
        JdbcTemplate sjdbcTemplate = JdbcTemplateManager.getInstance().getJdbcTemplate(sdataSource);

        String dataSourceSql = sourceSql + " where t.OWNER = '" + dataSource.getDbUser() + "'";
        String sdataSourceSql = sourceSql + " where t.OWNER = '" + sdataSource.getDbUser() + "'";

        if (StringUtils.isNotBlank(dataUser)) {
            dataSourceSql = sourceSql + " where t.OWNER = '" + dataUser + "'";
        }
        if (StringUtils.isNotBlank(dbUser)) {
            sdataSourceSql = sourceSql + " where t.OWNER = '" + dbUser + "'";
        }


        List<Map<String, Object>> map = jdbcTemplate.queryForList(dataSourceSql);
        List<Map<String, Object>> smap = sjdbcTemplate.queryForList(sdataSourceSql);

        for (Map<String, Object> objMap : map) {
            String tableName = objMap.get("table_name").toString();
            String owner = objMap.get("OWNER").toString();
            if (StringUtils.isNotBlank(dataUser)) {
                tableName = dataUser + "." + tableName;
            }
            if (owner.equals("JCC")) {
                tableName = owner + "." + tableName;
            }

            //验证是否有指定表同步
            if (!isSyncTable(tableName, tableNames)) {
                continue;
            }
            String newTableName = tableName.replaceAll(owner + "\\.", "");
            if (!isTableThere(newTableName, smap)) {
                log.debug("表" + tableName + "没有找到表！" + sdataSource.getDbHost());
                continue;
            }
//                    Integer rows = Integer.parseInt(objMap.get("num_rows").toString());
            StringBuffer countSql = new StringBuffer();
            countSql.append(" SELECT count(*) FROM " + tableName);
            Integer rows = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
            if (rows > 0) {
                //启动多线程处理数据
                StringBuffer sbSql = new StringBuffer();
                sbSql.append(" SELECT * FROM ");
                sbSql.append(tableName);
                sbSql.append(" ");
                String keyNameSql = getKeyName(tableName);
                List<String> keyNames = Lists.newArrayList();
                try {
                    keyNames = jdbcTemplate.getPrimaryKeys(tableName);
                    if (keyNames.size() == 0) {
                        keyNames = jdbcTemplate.queryForList(keyNameSql, String.class);
                    }
                    if (keyNames.size() == 0) {
                        keyNames = getKeyNames(tableName, jdbcTemplate, false);
                    }
                } catch (DataAccessException e) {
                    keyNames = getKeyNames(tableName, jdbcTemplate, false);
                }

                //如果数据量大雨3000条，查询语句作分页面查询处理
                Integer timeNumber = (rows / 1000) + 1;
                int i = 1;
                while (i <= timeNumber) {
                    String pageSql = getPageSql(dataSource.getDbType(), sbSql.toString(), i, 1000);
                    i++;
                    //这里执行任务
                    executorService.submit(new SyncDatabase(pageSql, jdbcTemplate, sjdbcTemplate, keyNames, tableName, true, sdataSource.getDbType()));
                }
            }
        }
    }

    /**
     * 整个库同步
     *
     * @param taskId
     */
    @Override
    public void syncDatabase(Long taskId, String tableNames) {
        //1、获取数据任务信息
        ConversionTask task = getById(taskId);
        //这里获取到的是配置的相应的实例名，如
        if (task.getDataSourceSql().equals("整库同步")) {
            syncDatabase(task.getDataSourceId(),task.getDbId(),null,null,tableNames);
        }
    }


    /**
     * 验证操作数据库表是否存在
     *
     * @param tableName
     * @param smap
     * @return
     */
    public boolean isTableThere(String tableName, List<Map<String, Object>> smap) {
        //验证被操作数据是否有存在表
        for (Map<String, Object> map : smap) {
            String tableName1 = map.get("table_name").toString();
            if (tableName.equals(tableName1)) {
                return true;
            }
        }
        return false;
    }


    /**
     * 查出主键名称和创建主键
     *
     * @param tableName
     * @param jdbcTemplate
     * @return
     */
    public List<String> getKeyNames(String tableName, JdbcTemplate jdbcTemplate, boolean inserts) {
        List<String> keyNames = Lists.newArrayList();

        StringBuffer sbSql = new StringBuffer(" SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME=");
        sbSql.append("'" + tableName + "'");
        List<String> columnsList = jdbcTemplate.queryForList(sbSql.toString(), String.class);


        keyNames = jdbcTemplate.getPrimaryKeys(tableName);
        if (columnsList.size() == 0) {
            List<DBColumn> dbColumns = jdbcTemplate.getColumns(tableName);
            columnsList = dbColumns.stream().map(p -> p.getFieldName()).collect(Collectors.toList());
        }
        if (keyNames != null || keyNames.size() > 0) {
            if (columnsList.size() > 0) {
                log.debug("表" + tableName + "没有找到主键！" + columnsList.toString());
                List<String> keyList = Lists.newArrayList();
                keyList.add("ID");
                keyList.add("SBLSH");
                keyList.add("COMVEHICLEID");
                keyList.add("VESSELID");
                keyList.add("CRANEID");
                keyList.add("BOILERID");
                keyList.add("ENTERTAINMENTID");
                keyList.add("SUPREGULARINSPECTINFOID");
                keyList.add("ELEVATORID");

                for (String key : keyList) {
                    for (String key2 : columnsList) {
                        if (key.equals(key2)) {
                            keyNames.add(key2);
                            break;
                        }
                    }
                }
            }
            if (inserts) {
                StringBuffer sbKeySql = new StringBuffer();
                try {
                    //添加主键
                    sbKeySql.append(" ALTER TABLE ");
                    sbKeySql.append(tableName);
                    sbKeySql.append(" ADD CONSTRAINT ");
                    String newTableName = tableName.replaceAll("ZYC.", "");
                    sbKeySql.append("PK_" + newTableName + "_ID");

//                for (String keyName : keyNames) {
//                    sbKeySql.append("_"+keyName);
//                }
                    sbKeySql.append(" PRIMARY KEY(");
                    for (String keyName : keyNames) {
                        sbKeySql.append(keyName + ",");
                    }
                    sbKeySql.deleteCharAt(sbKeySql.length() - 1);
                    sbKeySql.append(")");
//                    jdbcTemplate.update(sbKeySql.toString());
                } catch (Exception ex) {
                    ex.printStackTrace();
                    log.debug("表" + tableName + "创建主键失败:" + sbKeySql.toString());
                }
            }
        }
        return keyNames;
    }

 

    /**
     * 验证指定表是否存在
     *
     * @param tableName
     * @param tableS
     * @return
     */
    public boolean isSyncTable(String tableName, String tableS) {
        if (StringUtils.isNotBlank(tableS)) {
            String[] tabls = tableS.split(",");
            for (String tabName : tabls) {
                if (tabName.equals(tableName)) {
                    return true;
                }
            }
        } else {
            return true;
        }
        return false;
    }

    class SyncDatabase implements Runnable {

        private String selectSql;
        //来原 数据源
        private JdbcTemplate jdbcTemplateYuan;
        //保存数据源
        private JdbcTemplate jdbcTemplateSave;

        private List<String> keyNames;

        private String tableName;

        private boolean isInsert;

        private String dbType;

        private HandleMessage handle = new HandleMessage();

        public SyncDatabase(String selectSql, JdbcTemplate jdbcTemplateYuan, JdbcTemplate jdbcTemplateSave, List<String> keyNames, String tableName, boolean isInsert, String dbType) {
            this.selectSql = selectSql;
            this.jdbcTemplateYuan = jdbcTemplateYuan;
            this.jdbcTemplateSave = jdbcTemplateSave;
            this.keyNames = keyNames;
            this.tableName = tableName;
            this.isInsert = isInsert;
            this.dbType = dbType;
        }

        @Override
        public void run() {
            try {
                log.debug("开始任务:" + selectSql);
                List<Map<String, Object>> map = jdbcTemplateYuan.queryForList(selectSql);
                log.debug("开始操作:" + map.size() + "，主键：" + keyNames.toString());
                JSONArray updateList = new JSONArray();
                JSONArray insertList = new JSONArray();
                //只做对比插入
//                isInsertList(keyNames, map, tableName, jdbcTemplateSave, handle, dbType);
                isInsert = true;
                //只插入数据
                if (isInsert) {
                    for (int i = 0; i < map.size(); i++) {
                        insertList.add(map.get(i));
                    }
                } else {
                    //更新和插入数据
                    updateInsertList(keyNames,map,tableName,jdbcTemplateSave,handle,dbType);
                }
                try {
                    handle.insertDatas(jdbcTemplateSave, insertList, tableName, dbType);
                    log.error("同步任务执行insert，执行表名：{}，共插入{}条数据", tableName, insertList.size());
                }catch (Exception e){
                    e.printStackTrace();
                    log.error("------------------------插入出错,执行更新方法--------------------------------");
                    isInsertList(keyNames,map,tableName,jdbcTemplateSave,handle,dbType);
//                    updateInsertList(keyNames,map,tableName,jdbcTemplateSave,handle,dbType);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }finally {
                try {
                    DataSourceUtils.releaseConnection(jdbcTemplateYuan.getDataSource().getConnection(), jdbcTemplateYuan.getDataSource());
                    DataSourceUtils.releaseConnection(jdbcTemplateSave.getDataSource().getConnection(), jdbcTemplateSave.getDataSource());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            log.debug("------------------------执行完成--------------------------------");
        }


        /**
         * @param keyNames         条件数据
         * @param map              源数据集合
         * @param tableName        更新表名称
         * @param jdbcTemplateSave 执行数据源
         * @param handle           操作对象
         * @param dbType           更新数据库类型
         * @throws Exception
         */
        public void updateInsertList(List<String> keyNames, List<Map<String, Object>> map, String tableName, JdbcTemplate jdbcTemplateSave, HandleMessage handle, String dbType) throws Exception {
            if (keyNames.size() > 0) {
                JSONArray updateList = new JSONArray();
                JSONArray insertList = new JSONArray();

                //end 对比插入
                //查询需要更新的数据
                for (int i = 0; i < map.size(); i++) {
                    StringBuilder sbSelect = new StringBuilder("SELECT count(1) FROM ");
                    sbSelect.append(tableName);
                    sbSelect.append(" WHERE ");
                    String whereSql = "";
                    for (String key : keyNames) {
                        if (StringUtils.isNotBlank(whereSql)) {
                            whereSql += " and ";
                        }
                        whereSql += key + " = '" + map.get(i).get(key) + "'";
                    }
                    sbSelect.append(whereSql);
                    Integer count = jdbcTemplateSave.queryForObject(sbSelect.toString(), Integer.class);
                    //                log.error(sbSelect + "是否有数据："+count);
                    if (count > 0) {
                        updateList.add(map.get(i));
                    } else {
                        //插入
                        insertList.add(map.get(i));
                    }
                }
                if (keyNames.size() > 0) {
                    handle.updateDatas(jdbcTemplateSave, updateList, tableName, keyNames, dbType);
                    log.debug("同步任务执行update，执行表名：{}，共更新{}条数据", tableName, updateList.size());
                }

                handle.insertDatas(jdbcTemplateSave, insertList, tableName, dbType);
                log.debug("同步任务执行insert，执行表名：{}，共插入{}条数据", tableName, insertList.size());
            }
        }


        /**
         * 获取插入数据源没有的数据进行插入(不更新)
         *
         * @param keyNames
         * @param map
         * @param tableName
         * @param jdbcTemplateSave
         * @param handle
         * @param dbType
         */
        public void isInsertList(List<String> keyNames, List<Map<String, Object>> map, String tableName, JdbcTemplate jdbcTemplateSave, HandleMessage handle, String dbType) throws Exception {
            //根据dwnm查询数据后，判断是否存在，如果不存在，直接插入
            JSONArray insertList = new JSONArray();
            StringBuilder sbSelectList = new StringBuilder("SELECT  " + keyNames.get(0) + "  FROM " + tableName + " where ");
            sbSelectList.append(keyNames.get(0) + " in (");
            List<String> list1 = Lists.newArrayList();

            for (int i = 0; i < map.size(); i++) {
                list1.add(map.get(i).get(keyNames.get(0)).toString());
            }

            for (String str : list1) {
                sbSelectList.append("'" + str + "',");
            }
            sbSelectList.deleteCharAt(sbSelectList.length() - 1);
            sbSelectList.append(" )");

            List<String> list2 = jdbcTemplateSave.queryForList(sbSelectList.toString(), String.class);

            // 差集 (list2 - list1)
            List<String> reduce2 = list1.stream().filter(item -> !list2.contains(item)).collect(Collectors.toList());
//            System.out.println("---差集 reduce2 (list2 - list1)---");
//            reduce2.parallelStream().forEach(System.out :: println);

            if (reduce2.size() > 0) {
                String dwnmStr = "";
                for (int i = 0; i < map.size(); i++) {
//                    String dwnmValue = map.get(i).get(keyNames.get(0)).toString();
//                    //去重
//                    if(dwnmStr.indexOf(dwnmValue) >-1){
//                        continue;
//                    }
//                    Optional<String> optional = reduce2.stream().filter(s->s.equals(dwnmValue)).findFirst();
//                    if(optional.isPresent()) {
                    StringBuilder sbSelect = new StringBuilder("SELECT count(1) FROM ");
                    sbSelect.append(tableName);
                    sbSelect.append(" WHERE ");
                    String whereSql = "";
                    for (String key : keyNames) {
                        if (StringUtils.isNotBlank(whereSql)) {
                            whereSql += " and ";
                        }
                        whereSql += key + " = '" + map.get(i).get(key) + "'";
                    }
                    sbSelect.append(whereSql);
                    Integer count = jdbcTemplateSave.queryForObject(sbSelect.toString(), Integer.class);
                    if (count == 0) {
                        insertList.add(map.get(i));
                    }
//                        dwnmStr += dwnmValue;
//                    }
                }
                if (insertList.size() > 0) {
                    handle.insertDatas(jdbcTemplateSave, insertList, tableName, dbType);
                    log.error("同步任务执行只insert，执行表名：{}，共插入{}条数据", tableName, insertList.size());
                }
            }
        }
    }





}