package com.test.mysql;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class MysqlBatchUtil {
private String sql="INSERT INTO tb_user (user_name,password,age) VALUES (?,?,?)";
private String connectStr="jdbc:mysql://localhost:3306/test";
private String username="root";
private String password="root";
private void doStore() throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.jdbc.Driver");
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";//此处是测试高效批次插入,去掉之后执行时普通批次插入
Connection conn = (Connection) DriverManager.getConnection(connectStr, username,password);
conn.setAutoCommit(false); // 设置手动提交
int count = 0;
PreparedStatement psts = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i=0;i<=1000000;i++){
psts.setString(1, i+"username1");
psts.setString(2, i+"password2");
psts.setInt(3, i);
psts.addBatch(); // 加入批量处理
count++;
}
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
System.out.println("数量="+count);
System.out.println("运行时间="+ (System.currentTimeMillis() - start)+"毫秒");
conn.close();
}
public static void main(String[] args) {
try {
new MysqlBatchUtil().doStore();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class MysqlBatchUtil {
private String sql="INSERT INTO tb_user (user_name,password,age) VALUES (?,?,?)";
private String connectStr="jdbc:mysql://localhost:3306/test";
private String username="root";
private String password="root";
private void doStore() throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.jdbc.Driver");
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";//此处是测试高效批次插入,去掉之后执行时普通批次插入
Connection conn = (Connection) DriverManager.getConnection(connectStr, username,password);
conn.setAutoCommit(false); // 设置手动提交
int count = 0;
PreparedStatement psts = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i=0;i<=1000000;i++){
psts.setString(1, i+"username1");
psts.setString(2, i+"password2");
psts.setInt(3, i);
psts.addBatch(); // 加入批量处理
count++;
}
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
System.out.println("数量="+count);
System.out.println("运行时间="+ (System.currentTimeMillis() - start)+"毫秒");
conn.close();
}
public static void main(String[] args) {
try {
new MysqlBatchUtil().doStore();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}