Java处理数据并将数据导入MySql数据库

最新推荐文章于 2023-12-27 10:36:17 发布

王卷卷.

最新推荐文章于 2023-12-27 10:36:17 发布

阅读量1.2k

点赞数 2

分类专栏： Java 文章标签： java jdbc mysql 1024程序员节

本文链接：https://blog.csdn.net/weixin_45761659/article/details/109265410

版权

Java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Java从文本文件中读取数据，处理地理坐标信息，并将处理后的数据导入MySQL数据库。在处理大数据量时，原始代码可能会遇到异常。为了解决这个问题，可以考虑使用批处理SQL语句、连接池和预编译语句来提高性能和稳定性。同时，确保数据库连接和资源及时关闭以避免内存泄漏。

摘要由CSDN通过智能技术生成

Java处理数据并将数据导入MySql数据库

原始数据集

156;2014-02-01 00:00:00.739166+01;POINT(41.8836718276551 12.4877775603346)
187;2014-02-01 00:00:01.148457+01;POINT(41.9285433333333 12.4690366666667)
297;2014-02-01 00:00:01.220066+01;POINT(41.8910686119733 12.4927045625339)
89;2014-02-01 00:00:01.470854+01;POINT(41.7931766914244 12.4321219603157)
79;2014-02-01 00:00:01.631136+01;POINT(41.90027472 12.46274618)
191;2014-02-01 00:00:02.048546+01;POINT(41.8523047579646 12.5774065771898)
343;2014-02-01 00:00:02.647839+01;POINT(41.8921718255185 12.4696996165151)
341;2014-02-01 00:00:02.709888+01;POINT(41.9102125627332 12.4770004336041)
260;2014-02-01 00:00:03.458195+01;POINT(41.8658208551143 12.4655221109313)
59;2014-02-01 00:00:03.707117+01;POINT(41.8967831636848 12.4821987021152)
122;2014-02-01 00:00:04.232912+01;POINT(41.9230874882779 12.502203541278)
311;2014-02-01 00:00:04.436445+01;POINT(41.9068137897371 12.4902083997946)
351;2014-02-01 00:00:04.487352+01;POINT(41.9100508227258 12.4966092132391)
58;2014-02-01 00:00:05.182068+01;POINT(41.9175592169096 12.5132735176741)
196;2014-02-01 00:00:05.429831+01;POINT(41.8922298239912 12.469779213474)
105;2014-02-01 00:00:06.06672+01;POINT(41.8971435606299 12.4729530904655)
331;2014-02-01 00:00:06.362172+01;POINT(41.9055040692911 12.4450642615557)
362;2014-02-01 00:00:06.508353+01;POINT(41.9101993407516 12.4770016466739)
188;2014-02-01 00:00:06.830676+01;POINT(41.9219318791848 12.4907898873581)
172;2014-02-01 00:00:07.028304+01;POINT(41.9198850823211 12.5027184760708)
352;2014-02-01 00:00:07.040664+01;POINT(41.8978325324133 12.4693947471678)
188;2014-02-01 00:00:07.122411+01;POINT(41.9226663915447 12.4871261442344)
361;2014-02-01 00:00:07.311678+01;POINT(41.9224726036191 12.487366637215)
321;2014-02-01 00:00:07.629026+01;POINT(41.89724661 12.47285113)
318;2014-02-01 00:00:07.774661+01;POINT(41.8832317105311 12.4692101233239)
188;2014-02-01 00:00:07.820636+01;POINT(41.9219318791848 12.4907898873581)
317;2014-02-01 00:00:08.452163+01;POINT(41.9004122222972 12.4728368659278)
368;2014-02-01 00:00:08.646102+01;POINT(41.8904533333333 12.4741966666667)
295;2014-02-01 00:00:09.135615+01;POINT(41.8957895596184 12.4719204209153)
197;2014-02-01 00:00:09.207596+01;POINT(41.884861232788 12.470642809741)
298;2014-02-01 00:00:09.534952+01;POINT(41.8937974842265 12.4703800398856)
232;2014-02-01 00:00:09.889075+01;POINT(41.9038292750994 12.4844517125394)
315;2014-02-01 00:00:10.098237+01;POINT(41.900065357512 12.4572887201218)
2;2014-02-01 00:00:10.168741+01;POINT(41.9081300994706 12.5043668987191)
135;2014-02-01 00:00:10.198107+01;POINT(41.9331899516031 12.5117847048568)
248;2014-02-01 00:00:10.709166+01;POINT(41.8959019919821 12.4768818873779)
132;2014-02-01 00:00:10.733119+01;POINT(41.8552883890016 12.4771413579851)
104;2014-02-01 00:00:10.855133+01;POINT(41.7915396179775 12.2502862040602)
234;2014-02-01 00:00:11.214262+01;POINT(41.897783275213 12.4693312066563)
357;2014-02-01 00:00:11.336365+01;POINT(41.8345933772036 12.4716641521793)
281;2014-02-01 00:00:11.8629+01;POINT(41.8959076848405 12.4827594572545)
341;2014-02-01 00:00:12.098922+01;POINT(41.9109088398783 12.4772815796834)
53;2014-02-01 00:00:12.23614+01;POINT(41.8912020048345 12.5025479627347)
257;2014-02-01 00:00:12.341827+01;POINT(41.9246361238323 12.4862287031811)
37;2014-02-01 00:00:12.578331+01;POINT(41.8978418925033 12.4684204086267)
224;2014-02-01 00:00:12.880149+01;POINT(41.96531436 12.45640665)
178;2014-02-01 00:00:12.926514+01;POINT(41.921767916889 12.4850695093216)
174;2014-02-01 00:00:13.311114+01;POINT(41.8898128219691 12.4745070406691)
61;2014-02-01 00:00:13.400034+01;POINT(41.9003116666667 12.4727383333333)
291;2014-02-01 00:00:13.406692+01;POINT(41.857382669784 12.491181180781)

数据处理代码

public class ReadTxt {
    public ArrayList<String> readTxt() {
        ArrayList<String> result = new ArrayList<>();
        try {
            FileReader fr = new FileReader("E:\\taxi_test.txt");
            BufferedReader bf = new BufferedReader(fr);
            String s;
            //按行读取字符串
            while ((s = bf.readLine()) != null) {
                // System.out.println(s);
                String s1 = s.replace("POINT(", "");
                String s2 = s1.replace(")", "");
                //获取最后一个空格出现的索引
                int i = s2.lastIndexOf(" ");

                //获取第一个小数点出现的位置
                int j = s2.indexOf(".");
                //获取第一个;出现的位置。
                int k = s2.indexOf(";");
                //获取第二个;出现的位置
                k = s2.indexOf(";", k + 1);

                //将String --> StringBuffer
                StringBuffer sb = new StringBuffer(s2);
                //将最后一个空格的位置改为;
                StringBuffer s3 = sb.replace(i, i + 1, ";");
                // System.out.println(s3);
                //将第一个小数点后面数字去掉
                StringBuffer s4 = s3.replace(j, k, "");
                result.add(String.valueOf(s4));
            }
            bf.close();
            fr.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }
}

处理后的数据

156;2014-02-01 00:00:00;41.8836718276551;12.4877775603346
187;2014-02-01 00:00:01;41.9285433333333;12.4690366666667
297;2014-02-01 00:00:01;41.8910686119733;12.4927045625339
89;2014-02-01 00:00:01;41.7931766914244;12.4321219603157
79;2014-02-01 00:00:01;41.90027472;12.46274618
191;2014-02-01 00:00:02;41.8523047579646;12.5774065771898
343;2014-02-01 00:00:02;41.8921718255185;12.4696996165151
341;2014-02-01 00:00:02;41.9102125627332;12.4770004336041
260;2014-02-01 00:00:03;41.8658208551143;12.4655221109313
59;2014-02-01 00:00:03;41.8967831636848;12.4821987021152
122;2014-02-01 00:00:04;41.9230874882779;12.502203541278
311;2014-02-01 00:00:04;41.9068137897371;12.4902083997946
351;2014-02-01 00:00:04;41.9100508227258;12.4966092132391
58;2014-02-01 00:00:05;41.9175592169096;12.5132735176741
196;2014-02-01 00:00:05;41.8922298239912;12.469779213474
105;2014-02-01 00:00:06;41.8971435606299;12.4729530904655
331;2014-02-01 00:00:06;41.9055040692911;12.4450642615557
362;2014-02-01 00:00:06;41.9101993407516;12.4770016466739
188;2014-02-01 00:00:06;41.9219318791848;12.4907898873581
172;2014-02-01 00:00:07;41.9198850823211;12.5027184760708
352;2014-02-01 00:00:07;41.8978325324133;12.4693947471678
188;2014-02-01 00:00:07;41.9226663915447;12.4871261442344
361;2014-02-01 00:00:07;41.9224726036191;12.487366637215
321;2014-02-01 00:00:07;41.89724661;12.47285113
318;2014-02-01 00:00:07;41.8832317105311;12.4692101233239
188;2014-02-01 00:00:07;41.9219318791848;12.4907898873581
317;2014-02-01 00:00:08;41.9004122222972;12.4728368659278
368;2014-02-01 00:00:08;41.8904533333333;12.4741966666667
295;2014-02-01 00:00:09;41.8957895596184;12.4719204209153
197;2014-02-01 00:00:09;41.884861232788;12.470642809741
298;2014-02-01 00:00:09;41.8937974842265;12.4703800398856
232;2014-02-01 00:00:09;41.9038292750994;12.4844517125394
315;2014-02-01 00:00:10;41.900065357512;12.4572887201218
2;2014-02-01 00:00:10;41.9081300994706;12.5043668987191
135;2014-02-01 00:00:10;41.9331899516031;12.5117847048568
248;2014-02-01 00:00:10;41.8959019919821;12.4768818873779
132;2014-02-01 00:00:10;41.8552883890016;12.4771413579851
104;2014-02-01 00:00:10;41.7915396179775;12.2502862040602
234;2014-02-01 00:00:11;41.897783275213;12.4693312066563
357;2014-02-01 00:00:11;41.8345933772036;12.4716641521793
281;2014-02-01 00:00:11;41.8959076848405;12.4827594572545
341;2014-02-01 00:00:12;41.9109088398783;12.4772815796834
53;2014-02-01 00:00:12;41.8912020048345;12.5025479627347
257;2014-02-01 00:00:12;41.9246361238323;12.4862287031811
37;2014-02-01 00:00:12;41.8978418925033;12.4684204086267
224;2014-02-01 00:00:12;41.96531436;12.45640665
178;2014-02-01 00:00:12;41.921767916889;12.4850695093216
174;2014-02-01 00:00:13;41.8898128219691;12.4745070406691
61;2014-02-01 00:00:13;41.9003116666667;12.4727383333333
291;2014-02-01 00:00:13;41.857382669784;12.491181180781

将数据导入数据库。需要先建表
连接数据库需要到入jar包"mysql-connector-java:8.0.15.jar"【下载资源大家去百度搜，我不知道怎么上传资源的】

public class DemoToMySql {
    public static void main(String[] args) {
        long startTime = System.currentTimeMillis();
        ReadTxt r = new ReadTxt();
        ArrayList<String> s = r.readTxt();
        // System.out.println(s);

        //1、注册驱动
        String driver = "com.mysql.cj.jdbc.Driver";
        //2、 URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/taxi_test?useUnicode=true&useSSL=false&serverTimezone=UTC&autoReconnect=true";
        // MySQL配置时的用户�?
        String user = "root";
        // MySQL配置时的密码
        String password = "root";

        Connection conn = null;
        Statement stmt = null;
        StringBuffer sql = null;
        String[] str = null;
        //数据表字段
        String taxi_ID = null;
        String Time = null;
        String Y = null;
        String X = null;
        int count = 0;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            for (String s1 : s) {
                sql = new StringBuffer();
                str = s1.split(";");
                taxi_ID = str[0];
                Time = str[1];
                Y = str[2];
                X = str[3];
                sql.append("insert into taxi_test (taxi_ID,Time,Y,X) values('");
                sql.append(taxi_ID + "','");
                sql.append(Time + "','");
                sql.append(Y + "','");
                sql.append(X + "')");
                stmt.executeUpdate(sql.toString());
                System.out.println(count++);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println("程序运行时间：" + (endTime - startTime)/1000 + "S");
    }
}