数据库 java 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();    清空容器，为下一次打包做准备

精彩文章

 您阅读本篇文章共花了：