一个Java数据处理题目，并将结果使用JDBC输出到MySQL数据库

题目：

1. 这里有几天的数据，求 白天 9:00 - 18:00的top10
2. 将每天的有效数据排序后存入数据库

数据预览（下载链接在文末）：

time	value
2020-08-01T20:16:33+08:00	126
2020-08-01T20:18:33+08:00	766
2020-08-01T20:20:33+08:00	95
2020-08-01T20:21:33+08:00	95
2020-08-01T20:22:33+08:00	111
2020-08-01T20:23:33+08:00	80
2020-08-01T20:24:33+08:00	93
2020-08-01T20:25:33+08:00	90
2020-08-01T20:26:33+08:00	120
2020-08-01T20:27:33+08:00	112
2020-08-01T20:28:33+08:00	90
2020-08-01T20:29:33+08:00	101
2020-08-01T20:30:33+08:00	94
2020-08-01T20:31:33+08:00	88
2020-08-01T20:32:33+08:00	195
2020-08-01T20:33:33+08:00	84
2020-08-01T20:34:33+08:00	290
2020-08-01T20:35:33+08:00	76
2020-08-01T20:36:33+08:00	88
2020-08-01T20:37:33+08:00	67
2020-08-01T20:38:33+08:00	85
2020-08-01T20:39:33+08:00	94
2020-08-01T20:40:33+08:00	64
2020-08-01T20:41:33+08:00	94
2020-08-01T20:42:33+08:00	94

 代码实现：

package com.CaseAnalyze;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class DateTest {
    public static void main(String[] args) throws IOException, ParseException, ClassNotFoundException, SQLException {
        File f = new File("/Users/username/Desktop/query.csv");
        SimpleDateFormat timeSdf = new SimpleDateFormat("HH:mm:ss");
        SimpleDateFormat dateSdf = new SimpleDateFormat("yyyy-MM-dd");
        BufferedReader br = new BufferedReader(new FileReader(f));
        //使用TreeMap对key自然排序
        Map<Date, ArrayList<Integer>> map = new TreeMap<>();
        //去除首行
        String line = br.readLine();
        while ((line = br.readLine()) != null) {
            //提取日期
            String[] split = line.split(",");
            int value = Integer.parseInt(split[1].trim());
            //日期和时间的各自字符串
            String dateStr = split[0].split("T")[0];
            String timeStr = split[0].split("T")[1].split("\\+")[0];
            //转换为date格式
            Date time = timeSdf.parse(timeStr);
            Date date = dateSdf.parse(dateStr);
            //设置起始时间点
            Date begin = timeSdf.parse("8:00:00");
            Date end = timeSdf.parse("18:00:00");
            //如果时间在时间区间内,保存进集合中
            if (time.after(begin) && time.before(end)) {
                ArrayList<Integer> array = map.getOrDefault(date, new ArrayList<Integer>());
                array.add(value);
                map.put(date, array);
            }
        }
        Set<Map.Entry<Date, ArrayList<Integer>>> entries = map.entrySet();
        //遍历map集合取出所有键值对
        for (Map.Entry<Date, ArrayList<Integer>> entry : entries) {
            Date key = entry.getKey();
            ArrayList<Integer> value = entry.getValue();
            String date = dateSdf.format(key);
            System.out.println(date);
            //使用Stream流筛选
            value.stream().sorted((s1, s2) -> s2 - s1).limit(10).forEach(System.out::println);
        }
        //释放资源
        br.close();
        br = null;
        //将结果储存到MySQL数据库中
        Properties properties = new Properties();
        //加载配置文件
        properties.load(DateTest.class.getClassLoader().getResourceAsStream("JDBC.properties"));
        String drive = properties.getProperty("drive");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        Class.forName(drive);
        //建立连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //建立处理sql语句的对象
        Statement statement = connection.createStatement();
        //创建表格
//        statement.execute("drop table TheDayDateList");
        statement.execute("create table TheDayDateList(date varchar(20),count varchar(10) )");

        Set<Map.Entry<Date, ArrayList<Integer>>> entries1 = map.entrySet();
        for (Map.Entry<Date, ArrayList<Integer>> dateArrayListEntry : entries1) {
            Date key = dateArrayListEntry.getKey();
            String date = dateSdf.format(key);
            ArrayList<Integer> value = dateArrayListEntry.getValue();
            //对集合排序
            Collections.sort(value, new Comparator<Integer>() {
                @Override
                public int compare(Integer o1, Integer o2) {
                    return o2-o1;
                }
            });
            //遍历存入数据库
            for (Integer integer : value) {
                if (integer != 0) {
                    //生成sql语句
                    String sql = "insert into TheDayDateList values (" + date + "," + String.valueOf(integer) + ")";
                    //System.out.println(sql);
                    statement.executeUpdate(sql);
                }
            }
        }
        //关闭资源
        statement.close();
        statement = null;
        connection.close();
        connection = null;
    }
}

文件下载：

链接: https://pan.baidu.com/s/1QXqsDY4jtoUxxXuOk49bHA  密码: b9vl