MySQL十秒插入百万条数据

mysql数据库准备
private String Driver = "com.mysql.cj.jdbc.Driver"; private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
private String user = "root"; private String password = "root"; Connection
connection = null; PreparedStatement ps = null; ResultSet rs = null;
//封装与数据库建立连接的类 public void coon() throws Exception{ Class.forName(Driver);
connection = DriverManager.getConnection(url,user,password); } //封装异常类 public
void erro(){ try { if (rs!=null){ rs.close(); } if (ps!=null){ ps.close(); } if
(connection!=null){ connection.close(); } } catch (Exception e) {
e.printStackTrace(); } }
方式一：普通插入
package com.wt; import org.junit.Test; import java.sql.Connection; import
java.sql.DriverManager; import java.sql.PreparedStatement; import
java.sql.ResultSet; /** * @Author wt * @Date 2022/11/14 21:17 *
@PackageName:com.wt * @ClassName: TestAddBatch01 * @Description: TODO *
@Version 1.0 */ public class TestAddBatch01 { private String Driver =
"com.mysql.cj.jdbc.Driver"; private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai"; private String
user = "root"; private String password = "root"; Connection connection = null;
PreparedStatement ps = null; ResultSet rs = null; public void coon() throws
Exception{ Class.forName(Driver); connection =
DriverManager.getConnection(url,user,password); } public void erro(){ try { if
(rs!=null){ rs.close(); } if (ps!=null){ ps.close(); } if (connection!=null){
connection.close(); } } catch (Exception e) { e.printStackTrace(); } } @Test
public void ccc(){ long start = System.currentTimeMillis(); String sql =
"insert into a(id, name) VALUES (?,null)"; try { coon(); ps =
connection.prepareStatement(sql); for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, i);//填充sql语句种得占位符 ps.execute();//执行sql语句 } } catch (Exception
e) { e.printStackTrace(); } finally { erro(); } System.out.println("百万条数据插入用时："
+ (System.currentTimeMillis() - start)+"【单位：毫秒】"); } }
用时：62分钟多 

方式二：使用批处理插入
package com.wt; import org.junit.Test; import java.sql.Connection; import
java.sql.DriverManager; import java.sql.PreparedStatement; import
java.sql.ResultSet; /** * @Author wt * @Date 2022/11/14 20:25 *
@PackageName:com.wt.util * @ClassName: TestAddBatch * @Description: TODO *
@Version 1.0 */ public class TestAddBatch { private String Driver =
"com.mysql.cj.jdbc.Driver"; private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai"; private String
user = "root"; private String password = "root"; Connection connection = null;
PreparedStatement ps = null; ResultSet rs = null; public void coon() throws
Exception{ Class.forName(Driver); connection =
DriverManager.getConnection(url,user,password); } public void erro(){ try { if
(rs!=null){ rs.close(); } if (ps!=null){ ps.close(); } if (connection!=null){
connection.close(); } } catch (Exception e) { e.printStackTrace(); } } @Test
public void ccc(){ long start = System.currentTimeMillis(); String sql =
"insert into a(id, name) VALUES (?,null)"; try { coon(); ps =
connection.prepareStatement(sql); // connection.setAutoCommit(false);//取消自动提交
for (int i = 1; i <= 1000000; i++) { ps.setObject(1, i); ps.addBatch(); if (i %
1000 == 0) { ps.executeBatch(); ps.clearBatch(); } } ps.executeBatch();
ps.clearBatch(); // connection.commit();//所有语句都执行完毕后才手动提交sql语句 } catch
(Exception e) { e.printStackTrace(); } finally { erro(); }
System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() -
start)+"【单位：毫秒】"); } }
 方式一、二总结：到此可以看出其实其处理程序及批处理是没有起作用的，为此我们使用方式三

方式三：通过连接配置url设置【&rewriteBatchedStatements=true】（设置重写批处理语句）

url地址后注意添加【&rewriteBatchedStatements=true】
private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
方法三较于方法二的改变是只是url地址上的改变，其它没有任何修改 
package com.wt; import org.junit.Test; import java.sql.Connection; import
java.sql.DriverManager; import java.sql.PreparedStatement; import
java.sql.ResultSet; /** * @Author wt * @Date 2022/11/14 20:25 *
@PackageName:com.wt.util * @ClassName: TestAddBatch * @Description: TODO *
@Version 1.0 */ public class TestAddBatch { private String Driver =
"com.mysql.cj.jdbc.Driver"; private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
private String user = "root"; private String password = "root"; Connection
connection = null; PreparedStatement ps = null; ResultSet rs = null; public
void coon() throws Exception{ Class.forName(Driver); connection =
DriverManager.getConnection(url,user,password); } public void erro(){ try { if
(rs!=null){ rs.close(); } if (ps!=null){ ps.close(); } if (connection!=null){
connection.close(); } } catch (Exception e) { e.printStackTrace(); } } @Test
public void ccc(){ long start = System.currentTimeMillis(); String sql =
"insert into a(id, name) VALUES (?,null)"; try { coon(); ps =
connection.prepareStatement(sql); for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, i); ps.addBatch(); if (i % 1000 == 0) { ps.executeBatch();
ps.clearBatch(); } } ps.executeBatch(); ps.clearBatch(); } catch (Exception e)
{ e.printStackTrace(); } finally { erro(); } System.out.println("百万条数据插入用时：" +
(System.currentTimeMillis() - start)+"【单位：毫秒】"); } }
用时：【10秒左右】

 

 

 

到此批处理语句才正是生效

注意

数据库连接的url设置了【&rewriteBatchedStatements=true】时，java代码种的sql语句不能有分号【;】号，否则批处理语句打包就会出现错误，导致后面的sql语句提交出现【BatchUpdateException】异常

方式四：通过数据库连接取消自动提交，手动提交数据
package com.wt; import org.junit.Test; import java.sql.Connection; import
java.sql.DriverManager; import java.sql.PreparedStatement; import
java.sql.ResultSet; /** * @Author wt * @Date 2022/11/14 20:25 *
@PackageName:com.wt.util * @ClassName: TestAddBatch * @Description: TODO *
@Version 1.0 */ public class TestAddBatch { private String Driver =
"com.mysql.cj.jdbc.Driver"; private String url
="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
private String user = "root"; private String password = "root"; Connection
connection = null; PreparedStatement ps = null; ResultSet rs = null; public
void coon() throws Exception{ Class.forName(Driver); connection =
DriverManager.getConnection(url,user,password); } public void erro(){ try { if
(rs!=null){ rs.close(); } if (ps!=null){ ps.close(); } if (connection!=null){
connection.close(); } } catch (Exception e) { e.printStackTrace(); } } @Test
public void ccc(){ long start = System.currentTimeMillis(); String sql =
"insert into a(id, name) VALUES (?,null)"; try { coon(); ps =
connection.prepareStatement(sql); connection.setAutoCommit(false);//取消自动提交 for
(int i = 1; i <= 1000000; i++) { ps.setObject(1, i); ps.addBatch(); if (i %
1000 == 0) { ps.executeBatch(); ps.clearBatch(); } } ps.executeBatch();
ps.clearBatch(); connection.commit();//所有语句都执行完毕后才手动提交sql语句 } catch (Exception
e) { e.printStackTrace(); } finally { erro(); } System.out.println("百万条数据插入用时："
+ (System.currentTimeMillis() - start)+"【单位：毫秒】"); } }

 用时：【9秒左右】

总结：

1.使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】，以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常，

2.其他的就正常使用PreparedStatement ps;的以下三个方法即可
     *      ps.addBatch();      将sql语句打包到一个容器中
     *      ps.executeBatch();  将容器中的sql语句提交
     *      ps.clearBatch();    清空容器，为下一次打包做准备

 

• « 上一篇：Java学习，产生 10 个 100 以内的随机整数以填充一维数组，实现以下功能
• » 下一篇：ESXI之IOChain网络框架