如何获取文件数据处理之后上传到数据库

最新推荐文章于 2022-08-05 21:45:23 发布

森屿槿年汐

最新推荐文章于 2022-08-05 21:45:23 发布

阅读量340

点赞数 2

文章标签： java mysql

本文链接：https://blog.csdn.net/XIAOLIN190/article/details/110820477

版权

该博客主要介绍了如何通过Java程序批量导入省级市区数据到MySQL数据库中。首先，通过目录结构读取每个省份的文本数据，然后对文本内容进行解析，根据数据格式插入对应的SQL语句。程序实现了数据库连接、批量SQL执行以及事务处理，确保数据的完整性和一致性。内容涵盖了文件操作、数据库连接、数据处理和SQL语句的构建。

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

文档说明：

每个目录代表一个省份的数据：

每个文本代表一个一个市的数据：

数据通过io流将数据读取分离出来

连接数据库：

public class BeyondbConnection {
    public static Connection getConnection() {
        Connection con = null;
        String url = "jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        String user = "root";
        String password = "1234";

        try {
            con = DriverManager.getConnection(url, user, password);
//            System.out.println("获取链接地址" + con);

    } catch (SQLException ex) {
            Logger.getLogger(BeyondbConnection.class.getName()).log(Level.SEVERE,null, ex);
        }
        return con;

    }
}

批量导入数据的操作：

 public static void insert(List<String> sqls){
        int i =0;
        if(sqls!=null&&sqls.size()>0){
            Connection conn =  BeyondbConnection.getConnection();
            Statement stmt = null;
            try{
                stmt = conn.createStatement();
                //保存当前自动提交模式
                boolean autoCommit=conn.getAutoCommit();
                //关闭自动提交
                conn.setAutoCommit(false);
                //使用Statement同时收集多条sql语句
                //stmt.addBatch(insert_sql1);
                //stmt.addBatch(insert_sql2);

                for(String sql:sqls){
//      System.out.println(sql);
                    stmt.addBatch(sql);
                    i++;
                }
                //同时提交所有的sql语句
                stmt.executeBatch();
                //提交修改
                conn.commit();
                conn.setAutoCommit(autoCommit);
            }catch(Exception e){
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }finally{
                try {
                    stmt.close();
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
//        return i;
    }

}

最后就是我们要操作的数据了，别眨眼哦！

public class ReadDirFile {

    public static void main(String[] args) {
        List<String> sqls = readDirFile("E:\\Data\\QGQHDM\\130000","河北省",1,0);  //pid表示市级pid     aid 主键
        ReadDirFile.insert(sqls);
        System.out.println("执行成功！");
    }


    //获取某个文件夹下的所有文件
    public static ArrayList<String> getAllFileName(String path) {
        ArrayList<String> fileNameList = new ArrayList<String>();
        File file = new File(path);
        File[] tempList = file.listFiles();  //listFiles()表示获取文件的全路径名称      E:\Data\QGQHDM\130000\QHDM_130100_20200630.txt
        for (int i = 0; i < tempList.length; i++) {
            if (tempList[i].isFile()) {
//                String name = tempList[i].getPath();
                fileNameList.add(tempList[i].getPath());  //.getPath() 返回构造时传入的路径
            }
        }
        return fileNameList;
    }

    public static List<String> readDirFile(String filePath,String name1,int aid,int pid){
        ArrayList<String> fileNameList = getAllFileName(filePath);
        List<String> sqls = new ArrayList<String>();
//        String name1 = "";//省节点名称，方法中的参数已经定义引用
         String name2 = ""; //市节点名称
        String name3 = ""; //街道节点名称
        String name4 = ""; //社区节点名称

        int orderNum1 = 0;//市节点排序
        int orderNum2 = 0; //区节点排序
        int orderNum3 = 0;//街道节点排序
        int orderNum4 = 0; //社区节点排序

//        int pid = 0; //市级pid  前面参数已经引用
        int pid2 = 0; //区级pid
        int pid3 = 0; //街道pid
        int pid4 = 0; //居委会pid
        for(int a=0;a<fileNameList.size();a++){
            int inum = fileNameList.get(a).indexOf("_");  //返回所在的索引    QHDM_130100_20200630.txt
            String fileCode = fileNameList.get(a).substring(inum+1,fileNameList.get(a).indexOf("_",inum+1));  //索引从0开始，包头不包尾     int indexOf(String str, int startIndex)：从指定的索引处开始，返回第一次出现的指定子字符串在此字符串中的索引。

            try {
                String encoding = "UTF-8";
                File file = new File(fileNameList.get(a));
                if (file.isFile() && file.exists()) {  //判断文件是否存在和是否是一个目录

                    if ("00".equals(fileCode.substring(4, 6))) {  //截取的数字 “130100” 后面两位为00的都代表市级文件
                        //判断是否读到的是市
                        String lineTxt1 = null;
                        orderNum1++;
                        int count1 = 0;  //定义行数
                        //获取一行一行的文件内容
                        InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);
                        BufferedReader bufferedReader = new BufferedReader(read);
                        while ((lineTxt1 = bufferedReader.readLine()) != null) {
                            count1++;
                            if (count1 >2) {
                                String[] s = lineTxt1.split(",", 2);
                                name2 = s[0];
                                //拼sql语句
                                System.out.println(aid + ", " + pid + " ," + s[1] + ",'" + s[0] + "," + name1 + " " + name2 + "'," + orderNum1 + ",'" + now() + "'," + null + ",'" + now() + "'," + null + "," + null + "," + "0");
                                sqls.add("insert into jf_china_area value(" + aid + ", " + pid + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + "'," + orderNum1 + ",'" + now() + "'," + null + ",'" + now() + "'," + null + "," + null + "," + 0 + ")");
                                pid2 = aid;
                                aid++;

                            }
                        }
                        read.close();
                    } else {
                        //没有读到市的时候就是区
                            String lineTxt2 = null;
                            int count2 = 0;
                            InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);
                            BufferedReader bufferedReader = new BufferedReader(read);
                            while ((lineTxt2 = bufferedReader.readLine()) != null) {
                                count2++;
                                if (count2 >= 3) {
                                    String[] s = lineTxt2.split(",", 2);
                                    if ("000000".equals(s[1].substring(6, 12)) ) {  //长安区,130102 000000    市辖区,130101 000000
                                        if ("01".equals(s[1].substring(4,6))){
                                            continue;
                                        }else {
                                            orderNum2++;
                                            orderNum3 = 0; //街道节点重置
                                            name3 = s[0];
                                            //拼sql语句
                                            sqls.add("insert into jf_china_area value(" + aid + ", " + pid2 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 +" "+name3+ "'," + orderNum2 +",'"+ now() +"',"+ null +",'"+ now() +"',"+ null + "," + null + "," + 0 + ")");
                                            System.out.println(+aid + ", " + pid2 + "," + s[1] + "," + s[0] + "," + name1 + " " + name2 + " " + name3 + "," + orderNum2 + ",'" + now() + "'," + null + ",'" + now() + "'," + null + "," + null + "," + "0");
//                                            if ("".equals(name2)){
//                                                name2 = name3;
//                                            }
                                            pid3 = aid;
                                            aid++;
                                        }
                                    } else if ("000".equals(s[1].substring(9, 12))) {  //建北街道办事处,130102001 000     棉一社区居委会,130102001 001
                                        //街道的处理
                                        orderNum3++;
                                        orderNum4 = 0; //社区节点重置
                                        name4 = s[0];
                                        sqls.add("insert into jf_china_area value(" + aid + ", " + pid3 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + " " + name3 +" " + name4 + "'," + orderNum3 +",'"+now()+"'," + null +",'"+now()+"'," + null + "," + null + "," + 0 + ")");
                                        System.out.println("问题呢 "+aid + ", " + pid3 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + " " + name3 +" "+name4+ "'," + orderNum3 + ",'" + now() + "'," + null + ",'" + now() + "'," + null + "," + null + "," + "0");
                                        pid4 = aid;
                                        aid++;  //居委会自增
                                    } else {
                                        //社区的处理
                                        orderNum4++;
                                        sqls.add("insert into jf_china_area value(" + aid + ", " + pid4 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + " " + name3 + " " + name4 + " " + s[0] + "'," + orderNum4 + ",'"+now()+"'," + null + ",'"+now()+"'," + null + "," + null + "," + 0 + ")");
                                        System.out.println("insert into jf_china_area value(" + aid + ", " + pid4 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + " " + name3 + " " + name4 + " " + s[0] + "'," + orderNum4 + ",'"+now()+"'," + null + ",'"+now()+"'," + null + "," + null + "," + 0 + ")");
//                                        System.out.println("5--"+aid + ", " + pid5 + " ,'" + s[1] + "','" + s[0] + "','" + name1 + " " + name2 + " " + name3 + " " + name4 + " " + s[0] + "'," + orderNum4 + ",'" + now() + "'," + null + ",'" + now() + "'," + null + "," + null + "," + "0");
                                        aid++;
                                    }
                                }
                            }
                            read.close();
                        }
                }else{
                    System.out.println("找不到指定的文件 夹");
                }
            }catch(Exception e){
                System.out.println("读取文件内容出错");
                e.printStackTrace();
            }


        }


        return sqls;
    }