SpringBoot定时任务（ClickHouse定时写入数据）

上篇文章介绍了Redis定时写入数据

本篇文章再介绍下clickHouse数据库的定时写入数据

跟redis定时任务一样 同样使用@Bean和@Scheduled注解 

@Bean
Spring的@Bean注解用于告诉方法，产生一个Bean对象，然后这个Bean对象交给Spring管理。产生这个Bean对象的方法Spring只会调用一次，随后这个Spring将会将这个Bean对象放在自己的IOC容器中。

SpringIOC 容器管理一个或者多个bean，这些bean都需要在@Configuration注解下进行创建，在一个方法上使用@Bean注解就表明这个方法需要交给Spring进行管理。

@Scheduled
需要在启动类添加@EnableScheduling，启用Spring的计划执行功能，这样可以在容器中的任何Spring管理的bean上检测@Scheduled注解，执行计划任务

//每天凌晨两点执行一次

@Scheduled(cron = "0 0 2 * * ?")

//每8秒执行一次

@Scheduled(cron = "*/8 * * * * ?")

在使用无定时任务需求 可直接在接口中调用导入方法 

@EnableAutoConfiguration
@Component
@Configuration
@Order(value = 2)
public class ScheduledTaskUtils {
    private static final Logger logger = LoggerFactory.getLogger(ClickHouseUtils.class);

    //启动初始化导入一次
    @Bean
    public void initTestClickHouse() {
        tenMinuteDataImport();
    }
 
    //每天凌晨两点自动导入数据
    @Scheduled(cron = "0 0 2 * * ?")
    public void tenMinuteDataImport() {
    List<Map<String, Object>> curve = new ArrayList<>();
    String sql = "select .. from table where ...";
    //查询数据
    curve = ClickHouseUtils.execSQL(sql);
    ...

    ClickHouseUtils.insert(curve);
    logger.warn("定时任务:" + intime + " 导入十分钟数据:" + curve.size() + "条");
    }
 
}

ClickHouseUtils

sql中字段和for循环字段顺序，数量，类型都要对应 大家根据sql调整

    /**
     * ClickHouse通用查询
     *
     */
    public synchronized static List<Map<String, Object>> execSQL(String sql) {
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            long t = System.currentTimeMillis();
            connection = ClickHousePool.getConnection();
            logger.info("init clickhouse conn use time:" + (System.currentTimeMillis() - t));
            t = System.currentTimeMillis();
            statement = connection.createStatement();
            results = statement.executeQuery(sql);
            logger.info("exec clickhouse sql use time:" + (System.currentTimeMillis() - t));
            t = System.currentTimeMillis();
            ResultSetMetaData metaData = results.getMetaData();
            int count = metaData.getColumnCount();
            ResultSetMetaData rsmd = results.getMetaData();
            Map<String, Object> map = null;
            while (results.next()) {
                map = new HashMap<String, Object>();
                for (int i = 1; i <= count; i++) {
                    map.put(rsmd.getColumnName(i), results.getObject(rsmd.getColumnName(i)));
                }
                list.add(map);
            }
            logger.info("analysis clickhouse result use time:" + (System.currentTimeMillis() - t) + " data size:" + list.size());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ClickHousePool.close(connection, statement, results);
            return list;
        }
    }

    /**
     * ClickHouse插入数据
     *
     * @param values
     * @return
     */
    public static boolean insert(List<Map<String, Object>> values) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet results = null;
        boolean isSuccess = false;
        try {
            long t = System.currentTimeMillis();
            String sql = "insert into table" +
                    "(create_time, write_time, test1, test2)" +
                    " values(?,?,?,?)";
            connection = ClickHousePool.getConnection();
            statement = connection.prepareStatement(sql);
            //字段和类型需对应
            for (Map<String, Object> valuesMap : values) {
                statement.setString(1, valuesMap.get("createTime").toString());
                statement.setString(2, valuesMap.get("writeTime").toString());
                statement.setInt(3, Integer.parseInt(valuesMap.get("test1").toString()));
                statement.setDouble(4, Double.parseDouble(valuesMap.get("test2").toString()));
                boolean execute = statement.execute();
                if (execute) {
                    logger.info("定时任务:导入数据成功");
                } else {
                    logger.info("定时任务:导入数据失败");
                }
            }
            connection.commit();
            logger.info("insert clickhouse use time {}ms,data size {}条", (System.currentTimeMillis() - t), values.size());
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ClickHousePool.close(connection, statement, results);
            return isSuccess;
        }
    }

最后大家通过需求更改sql

有遇到什么问题欢迎评论区讨论🤗