MySQL JDBC批量写入数据

本文介绍了一种从日志文件中解析特定字段并批量导入MySQL数据库的方法,对比了单行插入与批量插入的效率,后者对于大量数据处理表现更优。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一,需求
最近手头有个需求就是从日志文件中匹配出需要的一些值,然后写入MySQL数据库中,源日志

[2019-07-29 00:00:04,238] INFO  [pool-39-thread-311] c.a.c.a.f.l.AccessLog.info -  Total_time=652 Exec_time=652 Queue_time=0 - [2019-07-29 00:00:03 586] 1 select id   from tablename   where student_id = 15690 and status in (0,1)   and is_deleted = 0 and is_ex_class = 0   limit1\;process=20190729000301119424722109999543351\;CLUSTER=ay-ads-hangzhou 

仅需要匹配出Total_time,Exec_time,Queue_time,以及查询的SQL

二,分析
用正则表达式,匹配出需要的值,然后插入MySQL

Total_time=(?<total_time>\d+) Exec_time=(?<exec_time>\d+) Queue_time=(?<queue_time>\d+) - [(?<time>.*?)] \d+ (?<sql>.*?)\

三,动手实践
①,先读取日志文件,一行一行的读取,然后用List集合收集

public static List<String> readFile(String path) throws IOException {
        List<String> list = new ArrayList<String>();
        FileInputStream fis = new FileInputStream(path);
        InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
        BufferedReader br = new BufferedReader(isr);
        String line = "";
        while ((line = br.readLine()) != null) {
            list.add(line);
        }
        br.close();
        isr.close();
        fis.close();
        return list;
    }

②,配置好MySQL连接等信息
③,往MySQL写入
第一种方式比较笨的,性能低的方式,一行一行写入

        String path = "C:/Users/xxx.log";
        List<String> scanListPath = readFile(path);
        System.out.println(scanListPath.size());
        String regex = "Total_time=(?<totalTime>\\d+) Exec_time=(?<execTime>\\d+) Queue_time=(?<queueTime>\\d+) - \\[(?<time>.*?)\\] \\d+ (?<sql>.*?)\\\\";
        Pattern pattern = Pattern.compile(regex);
        Connection conn = getConn();
        System.out.println("conn:"+conn);
        scanListPath.stream().forEach(line -> {
            Matcher matcher = pattern.matcher(line);
            int toTime = 0;
            int queueTime = 0;
            int execTime = 0;
            String executeTime= "";
            String sql= "";

            if (matcher.find()) {
                toTime = Integer.parseInt(matcher.group("totalTime"));
                execTime = Integer.parseInt(matcher.group("execTime"));
                queueTime = Integer.parseInt(matcher.group("queueTime"));
                executeTime = matcher.group("time");
                sql = matcher.group("sql").replaceAll("\\t"," ").replaceAll("`","");
            }

           String statment = "insert into log_result_0731(total_time,exec_time,queue_time,execute_time,ex_sql) values("+toTime+","+execTime+","+queueTime+",'"+executeTime+"','"+sql+"')";

            try {
                PreparedStatement pste = conn.prepareStatement(statment);
                pste.execute();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println(statment);
            }
        });

        if (conn != null)
            conn.close();

上面方式60万数据耗时半小时多,不能接受,

第二种方式,拼接一条长SQL,一次性写入1000条数据、
语法:

insert into table(col1,col2,col3) values (a,b,c),(d,e,f),(h,i,j)...
        String path = "C:/Users/xxx.log";
        List<String> scanListPath = readFile(path);
        System.out.println(scanListPath.size());
        String regex = "Total_time=(?<totalTime>\\d+) Exec_time=(?<execTime>\\d+) Queue_time=(?<queueTime>\\d+) - \\[(?<time>.*?)\\] \\d+ (?<sql>.*?)\\\\";
        Pattern pattern = Pattern.compile(regex);
       Connection conn = getConn();
        System.out.println("conn:"+conn);
        List<Tuple5> list = new ArrayList<>();

        scanListPath.stream().forEach(line -> {

            Matcher matcher = pattern.matcher(line);
            int toTime = 0;
            int queueTime = 0;
            int execTime = 0;
            String executeTime= "";
            String sql= "";

            if (matcher.find()) {
                toTime = Integer.parseInt(matcher.group("totalTime"));
                execTime = Integer.parseInt(matcher.group("execTime"));
                queueTime = Integer.parseInt(matcher.group("queueTime"));
                executeTime ="\'" + matcher.group("time")+"'";
                sql = "\'"+matcher.group("sql").replaceAll("`","")+"'";
                Tuple5<Integer,Integer,Integer,String,String> tuple5 = new Tuple5(toTime,execTime,queueTime,executeTime,sql);//将结果存入五元组
                list.add(tuple5);
            }

            String sql_prefix = "insert into log_result_0805(total_time,exec_time,queue_time,execute_time,ex_sql) values ";

            String statmentsql = "";
			//集合数据到达一千就开始写入
            if (list.size() >= 1000){
                StringBuffer sb = new StringBuffer();
                sb.append(sql_prefix );
                //拼接SQL
                list.stream().forEach(tup ->{
                    sb.append("(").append(tup._1()).append(",").append(tup._2()).append(",").append(tup._3()).append(",").append(tup._4()).append(",").append(tup._5()).append("),");
                });

                String stat = sb.toString();
                boolean suffix = stat.endsWith(",");
                if (suffix) {
                    int length = stat.length();
                    statmentsql = stat.substring(0,length - 1);//去除最后一个逗号
                }

               // System.out.println("sql:"+statmentsql);
                try {
                    PreparedStatement pste = conn.prepareStatement(statmentsql);
                   // pste.executeBatch();
                     pste.execute();

                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println(statmentsql);
                }
                list.clear();
            }
            });
		//最后不足一千的数据写入库
        if (!(null == list)){
            String sql_preefix = "insert into log_result_0805(total_time,exec_time,queue_time,execute_time,ex_sql) values ";
            String statmentsql = "";
            StringBuffer sb = new StringBuffer();
            sb.append(sql_preefix);
            list.stream().forEach(tup ->{
                sb.append("(").append(tup._1()).append(",").append(tup._2()).append(",").append(tup._3()).append(",").append(tup._4()).append(",").append(tup._5()).append("),");
            });
            String stat = sb.toString();
            boolean suffix = stat.endsWith(",");
            if (suffix) {
                int length = stat.length();
                statmentsql = stat.substring(0,length - 1);            
            }
            list.clear();
            try {
                PreparedStatement pste = conn.prepareStatement(statmentsql);
                pste.execute();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println(statmentsql);
            }
        }
        if (conn != null)
            conn.close();

第二种方式60万数据大约一分钟之内搞定,前后两种方式,差距可想而知

### JDBC连接MySQL数据库批量插入数据 #### 准备工作 为了实现JDBC连接到MySQL并进行批量插入操作,需先加载相应的驱动程序,并建立与目标数据库的连接。这通常涉及设置正确的JDBC URL来指定要访问的具体数据库实例[^2]。 #### 创建批处理对象 在Java应用程序中准备Statement或PreparedStatement接口的一个实例,用于发送SQL语句至数据库服务器。对于批量插入来说,推荐使用`PreparedStatement`因为它能有效防止SQL注入攻击的同时提高性能[^3]。 #### 设置自动提交属性 默认情况下,每次执行更新都会立即生效;然而,在批量插入场景里,关闭事务的自动提交功能可以让所有更改在一个单独的事务内完成,从而减少磁盘I/O次数,提升效率。可以通过调用Connection对象上的方法来改变此行为: ```java connection.setAutoCommit(false); ``` #### 添加批处理指令 利用`addBatch()`函数向预编译好的SQL命令添加参数化值集,之后再一次性提交整个批次的数据数据库引擎处理。下面是一个具体的例子展示怎样构建这样的流程[^1]: ```java String sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?)"; try(PreparedStatement pstmt = connection.prepareStatement(sql)){ for(int i=0; i<list.size(); ++i){ Object[] row = list.get(i); // 假设每条记录存储于数组形式的对象列表之中 pstmt.setObject(1, row[0]); pstmt.setObject(2, row[1]); pstmt.addBatch(); if((i+1)%batchSize==0 || i==(list.size()-1)){// 当达到设定大小或是最后一组时执行批处理 pstmt.executeBatch(); connection.commit(); // 清除之前的批处理以防内存溢出 pstmt.clearBatch(); } } }catch(Exception e){ try{ connection.rollback();// 发生异常则回滚交易 }catch(SQLException ex){} } finally { connection.setAutoCommit(true);// 操作完成后恢复初始状态 } ``` 上述代码片段展示了如何高效地将大量数据分批写入MySQL表中的方式之一。值得注意的是,这里还包含了错误处理逻辑以确保即使遇到问题也能保持数据的一致性和完整性。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

冬瓜螺旋雪碧

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值