mysql 批处理 事务 区别_JDBC 中的事务和批处理 batch

JDBC事务处理：

事务处理一般在事务开始前把事务提交设置为false

所有DML语句执行完成后提交事务

demo:

package com.xzlf.jdbc;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* 测试事务的基本用法

* @author xzlf

*

*/

public class Demo06 {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement ps1 = null;

PreparedStatement ps2 = null;

try {

// 加载驱动类

Class.forName("com.mysql.jdbc.Driver");

// 建立连接

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456");

// JDBC中默认是true，自动提交事务

conn.setAutoCommit(false);

ps1 = conn.prepareStatement("insert into t_user (username, pwd, regTime) values(?, ?, ?)");

ps1.setObject(1, "张三");

ps1.setObject(2, "123456");

ps1.setObject(3, new Date(System.currentTimeMillis()));

ps1.execute();

System.out.println("插入一个用户，张三");

try {

Thread.sleep(5000);

} catch (InterruptedException e) {

e.printStackTrace();

}

ps2 = conn.prepareStatement("insert into t_user (username, pwd, regTime) values(?, ?, ?)");

ps2.setObject(1, "李四");

ps2.setObject(2, "123456");

ps2.setObject(3, new Date(System.currentTimeMillis()));

ps2.execute();

System.out.println("插入一个用户，李四");

conn.commit();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}finally {

try {

if(ps1 != null) {

ps1.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(ps2 != null) {

ps2.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

JDBC批处理

JDBC 进行batchc操作时：

1、设置事务自动提交为false

2、一般使用Statement接口，PreparedStatement有预编译可能出错

demo:

package com.xzlf.jdbc;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* batch批处理基本用法

* @author xzlf

*

*/

public class Demo05 {

public static void main(String[] args) {

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

try {

// 1、加载驱动类

Class.forName("com.mysql.jdbc.Driver");

///2、建立连接

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc", "root", "123456");

conn.setAutoCommit(false);

stat = conn.createStatement();

long start = System.currentTimeMillis();

for(int i = 0; i < 20000; i++) {

stat.addBatch("insert into t_user(username, pwd, regTime) values('zs" + i + "', 123456, now())");

}

stat.executeBatch();

conn.commit();// 提交事务

long end = System.currentTimeMillis();

System.out.println("插入20000条数据，耗时：" + (end - start) + " ms");

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}finally {

try {

if(rs != null) {

rs.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(stat != null) {

stat.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}