【文件上传】解析文本文件通过JDBC连接进行批处理入库(动态建表动态入库)

最新推荐文章于 2023-02-14 10:31:43 发布

没有BUG就是最大的BUG

最新推荐文章于 2023-02-14 10:31:43 发布

阅读量293

点赞数 1

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/weixin_65690086/article/details/125875614

版权

注意:我这边直接使用main函数演示,后续可整合进SpringBoot方法中

文本文件格式(字段以 !| 分割):

    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx:3306/table", "root", "root");// 创建数据连接
        LineIterator lineIterator = FileUtils.lineIterator(new File("D:\\Flink\\src\\main\\resources\\AMM_ITF_D_PRD_INFO.TXT"), "utf-8");
        conn.setAutoCommit(false);
        Integer a = 0;
        String tableName = "table_" + System.currentTimeMillis();
        String[] fields = null;
        PreparedStatement ps = null;
        String result = "";
        while (lineIterator.hasNext()) {
            result = lineIterator.next();
            if (a == 0) {
                fields = result.split("\\!\\|");
                //动态建表
                String sql = "create table " + tableName + "(";
                if (fields != null && fields.length > 0) {
                    int len = fields.length;
                    for (int i = 0; i < len; i++) {
                        //添加字段
                        sql += fields[i].trim() + " text";
                        //防止最后一个,
                        if (i < len - 1) {
                            sql += ",";
                        }
                    }
                }
                //拼凑完 建表语句 设置默认字符集
                sql += ")";
                System.out.println("建表语句是：" + sql);
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.executeUpdate(sql);
                conn.commit();
                preparedStatement.close();

                sql = "insert into " + tableName + "(";
                int length = fields.length;
                for (int i = 0; i < length; i++) {
                    sql += fields[i];
                    //防止最后一个,
                    if (i < length - 1) {
                        sql += ",";
                    }
                }
                sql += ") values(";
                for (int i = 0; i < length; i++) {
                    sql += "?";
                    //防止最后一个,
                    if (i < length - 1) {
                        sql += ",";
                    }
                }
                sql += ");";
                ps = conn.prepareStatement(sql);

                a++;
            } else {
                String[] data = result.split("\\!\\|");
                if (fields.length == data.length) {
                    //注入参数
                    for (int i = 0; i < fields.length; i++) {
                        ps.setString(i + 1, data[i]);
                    }
                    ps.addBatch();
                }
                if (a % 5 == 0) {
                    ps.executeBatch();
                    conn.commit();
                    ps.clearBatch();
                }
                a++;
            }
        }
        ps.executeBatch();
        conn.commit();
        ps.clearBatch();
        //关闭数据库连接
        conn.close();
    }