使用JDBC在MySQL数据库中快速批量插入数据

使用JDBC连接MySQL数据库进行数据插入的时候,特别是大批量数据连续插入(10W+),如何提高效率呢?

在JDBC编程接口中Statement 有两个方法特别值得注意:

void addBatch() throws SQLException
Adds a set of parameters to this  PreparedStatement object's batch of commands.

int[] executeBatch() throws SQLException
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The  int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch. 

通过使用addBatch()和executeBatch()这一对方法可以实现批量处理数据。

不过值得注意的是,首先需要在数据库链接中设置手动提交, connection.setAutoCommit(false),然后在执行Statement之后执行 connection.commit()。
package cyl.demo.ipsearcher;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DbStoreHelper {

	private String insert_sql;
	private String charset;
	private boolean debug;

	private String connectStr;
	private String username;
	private String password;

	public DbStoreHelper() {
		connectStr = "jdbc:mysql://localhost:3306/db_ip";
		// connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
		insert_sql = "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)";
		charset = "gbk";
		debug = true;
		username = "root";
		password = "***";
	}

	public void storeToDb(String srcFile) throws IOException {
		BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream(srcFile), charset));
		try {
			doStore(bfr);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			bfr.close();
		}
	}

	private void doStore(BufferedReader bfr) throws ClassNotFoundException,	SQLException, IOException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection(connectStr, username,password);
		conn.setAutoCommit(false); // 设置手动提交
		int count = 0;
		PreparedStatement psts = conn.prepareStatement(insert_sql);
		String line = null;
		while (null != (line = bfr.readLine())) {
			String[] infos = line.split(";");
			if (infos.length < 5)   continue;
			if (debug) {
				System.out.println(line);
			}
			psts.setLong(1, Long.valueOf(infos[0]));
			psts.setLong(2, Long.valueOf(infos[1]));
			psts.setString(3, infos[2]);
			psts.setString(4, infos[3]);
			psts.setString(5, infos[4]);
			psts.addBatch();          // 加入批量处理
			count++;			
		}
		psts.executeBatch(); // 执行批量处理
		conn.commit();  // 提交
		System.out.println("All down : " + count);
		conn.close();
	}

}

执行完成以后:
All down : 103498
Convert finished.
All spend time/s : 47
一共10W+,执行时间一共花费 47 秒.

这个效率仍然不高,似乎没有达到想要的效果,需要进一步改进。
在MySQL JDBC连接字符串中还可以加入参数,
rewriteBatchedStatements=truemysql默认关闭了batch处理,通过此参数进行打开,这个参数可以重写向数据库提交的SQL语句,具体参见:http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html
useServerPrepStmts=false如果不开启(useServerPrepStmts=false),使用com.mysql.jdbc.PreparedStatement进行本地SQL拼装,最后送到db上就是已经替换了?后的最终SQL.

在此稍加改进,连接字符串中加入下面语句(代码构造方法中去掉注释):
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";

再次执行如下:
All down : 103498
Convert finished.
All spend time/s : 10
同样的数据量,这次执行只花费了10秒 ,处理效率大大提高.



### 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表中的方式之一。值得注意的是,这里还包含了错误处理逻辑以确保即使遇到问题也能保持数据的一致性和完整性。
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值