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); } } }