Batch operation can send a number of SQL statements to the database at one time, thus reducing the network communication with the database server and improving the execution efficiency.
When executing SQL statements in large quantities, there are three factors affecting efficiency:
1: Transactions, the more transactions, the slower the speed (transactions are best submitted once);
2: Network calls, the more network calls, the slower the speed (caching can be used here to solve);
3: Statement, Prepared Statement, because the more plans are executed, the slower the efficiency is.
As for the third point, the efficiency of using PrepareStatement is obviously higher than that of Statement, because it generates only one execution plan, and the same plan is used for subsequent reference. It's like defining a method in a java program. Dynamic information is defined as a parameter. A method can be called many times to pass in different parameters, but the method does not need to be redefined every time.
As for the first point, transaction can not be submitted automatically (default transaction is submitted automatically, such as prepareStatement.update() after execution, transaction is submitted automatically, and the problem of transaction has also been discussed, so in the case of abnormal transaction, there is no guarantee of consistency, sometimes there are big problems), the last transaction is one-time.
On the third point, caching is used to solve the problem, adding a batch operation, caching many sql statements locally, and sending the final statement to the database server at one time, which can effectively reduce the number of network calls and improve the communication efficiency with the database server.
Requirement: Insert 10,000 pieces of data into the userinfo table in the database
Automatically commit transactions without adding caching tests:
public class JDBCBatch {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
connection = DBUtil.getConnection();
statement = connection.createStatement();
long start =new Date().getTime();
System.out.println("Before the start:"+start);
for (int i = 0; i < 10000; i++) {
String sql="INSERT INTO userinfo "
+ "VALUES "
+ "(seq_userinfo_id.NEXTVAL,'test"+i+"','123456',"
+ "5000,'test"+i+"@tedu.cn')";
//Send the SQL to the database server immediately
statement.executeUpdate(sql);
}
System.out.println("End use time:"+(new Date().getTime()-start));
} catch (Exception e) {
}finally {
DBUtil.closeConnection();
}
}
}Output: Before starting: 1502871180710 End time: 10757
Use the solutions given above:
public class JDBCDemo2 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pStatement;
try {
connection = DBUtil.getConnection();
connection.setAutoCommit(false);
String sql="INSERT INTO userinfo "
+ "VALUES "
+ "(seq_userinfo_id.NEXTVAL,?,'123456',5000"
+ ",?)";
pStatement = connection.prepareStatement(sql);
long time = new Date().getTime();
for (int i = 0; i < 10000; i++) {
pStatement.setString(1, "aa"+i);
pStatement.setString(2, "scu@qq.com"+i);
pStatement.addBatch();
}
pStatement.executeBatch();
System.out.println("Use time:"+(new Date().getTime() - time));
//Clear the local cache
pStatement.clearBatch();
connection.commit();
} catch (Exception e) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
DBUtil.closeConnection();
}
}
}Output: Use time: 103
Result:
One time is 10757 MS and one time is 103 ms, which greatly improves the efficiency.
Note: Although the method of controlling variables should be used, it can be tested separately for convenience. All three schemes can provide efficiency.
Api:
connection.setAutoCommit(false):Transaction autocommit shutdown
connection.commit(): Submission of affairs
connection.rollback(): Rollback transaction
pStatement.addBatch(): Add batch operations (relative to caching to local)
pStatement.executeBatch(): Perform batch operations
pStatement.clearBatch(): Clear the local cacheDBUtil is a Self-encapsulated tool class that captures and closes connections
public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
private static String maxactive;
private static String maxwait;
//Database connection pool
private static BasicDataSource dateSource;
private static ThreadLocal<Connection> tl;
static{
try {
tl = new ThreadLocal<>();
//Loading configuration files
Properties properties = new Properties();
properties.load(new FileInputStream(new File("D:\\test\\jdbc\\src\\config.properties")));
//Read configuration files and initialize member variables
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
maxactive = properties.getProperty("maxactive");
maxwait = properties.getProperty("maxwait");
//Test the success of initialization
System.out.println(driver);
System.out.println(url);
System.out.println(username);
System.out.println(password);
System.out.println(maxactive);
System.out.println(maxwait);
dateSource = new BasicDataSource();
dateSource.setDriverClassName(driver);
dateSource.setUrl(url);
dateSource.setUsername(username);
dateSource.setPassword(password);
dateSource.setMaxActive(new Integer(maxactive));
dateSource.setMaxWait(new Integer(maxwait));
} catch (Exception e) {
//Exceptions cannot be thrown in static method blocks
e.printStackTrace();
}
}
/*
* Getting database connections from connection pools
*/
public static Connection getConnection() throws Exception{
Connection connection = dateSource.getConnection();
tl.set(connection);
return connection;
}
public static void closeConnection(){
Connection conn=tl.get();
if(conn!=null){
try {
//At this time, the connection pool is close, and the other method is oracle.
//Connection pooling is just acting as a proxy
conn.close();
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
* Close connection
*/
public static void closeConnection(Connection connection,Statement statement,ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}