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(); 清空容器，为下一次打包做准备
转载：https://blog.csdn.net/weixin_56028042/article/details/127855696
查看评论