Statement and PreparedStatement batch processing

1. Why batch processing is needed

If there is a large number of sql to be executed in the database, and if it is sent one by one, the efficiency is too low, so you can add a large number of sql to a batch, send it to the database at one time, and then open the batch for execution.

2. Implementation of batch processing

Statement implements batch processing

Advantage: sql with different structure can be used in a batch

Disadvantage: injection attack cannot be prevented

PreparedStatement implements batch processing

Advantage: can prevent injection attack

Disadvantage: only sql with the same or similar structure can be executed

Example of implementation code for Statement batch:

2.1 the tool class JDBC utils provides methods for releasing resources and obtaining connections

Note: because the database needs to be created, the url does not need to specify the database name

import java.sql.*;

/**
 * JDBC Tools
 */
public class JDBCUtils {
    //How to release resources
    public static void close(Connection conn,Statement stat,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                rs = null;
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                stat = null;
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                conn = null;
            }
        }
    }

    //How to get a connection
    public static Connection getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //Because the database needs to be created, the url does not need to specify the database name
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql:///",
                    "root","root");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

2.2.Statement batch processing

Execute batch statement

No corresponding database and table information before execution

Batch code:

 

import cn.tedu.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class StateBatch {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnection();
            stat = conn.createStatement();
            System.out.println("stat::"+stat);
            //Add sql to batch
            stat.addBatch("create database mydb1");
            stat.addBatch("use mydb1");
            stat.addBatch("create table stu(id int,name varchar(20))");
            stat.addBatch("insert into stu values(1,'Li Si')");
            //Execution batch
            stat.executeBatch();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,stat,rs);
        }
    }
}

Execution successful

Looking at the database, you can see that the mydb1 database has been created, the table stu in the database has been created, and the data Li Si has been inserted, which indicates that the Statement batch processing is successful

 

2.3.PreparedStatement batch processing

The sut table has only one piece of data before execution

Before executing the program, you need to modify the url of JDBC utils and specify the database to be used

Execute program, execute successfully

Check the database, the data has been inserted successfully

The implementation code is as follows:

import cn.tedu.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class PsBatch {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement("insert into stu values(?,?)");
            for (int i=2; i<100; i++){
                ps.setInt(1,i);
                ps.setString(2,"a"+i);
                ps.addBatch();
            }
            ps.executeBatch();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }
}

 

Tags: SQL Database Java JDBC

Posted on Sun, 14 Jun 2020 22:21:44 -0400 by happyme