Shulou(Shulou.com)06/01 Report--

This article mainly explains "MySQL JDBC Statement.executeBatch example Analysis". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "MySQL JDBC Statement.executeBatch example Analysis"!

Mport java.sql.*;/** * @ Author cherrishccl * @ Date 17:01 on 2020-9-4 * @ Version 1.0 * @ Description * perform batch submission with JDBC in the correct way: * 1. Statement stmt = conn.createStatement (); * for (int I = 0; I

< 10000; i++){ * String batchSql = "insert into t_user(name, sex, age) values('name', 'F', 22)"; * stmt.addBatch(batchSql); * } * stmt.executeBatch(); * * 2. 连接参数添加&rewriteBatchedStatements=true * PreparedStatement stmt = conn.prepareStatement("insert into t_user(name, sex, age) values (?, ?, ?)"); * for(int i = 0; i < 10000; i++){ * stmt.setString(1, "name"); * stmt.setString(2, "F"); * stmt.setString(3, 22); * stmt.addBatch(); * } */public class BatchTest1 { public static void main(String[] args) throws SQLException, ClassNotFoundException { String driver = "com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false" + "&allowMultiQueries=true&rewriteBatchedStatements=true"; String url1 = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true"; String username = "root"; String password = "123456"; /************************插入10000条数据**************************************/ // url 和 url1 效率差不多// batch2(driver, url, username, password); // 平均耗时4175// batch2(driver, url1, username, password); // 平均耗时4156 // url 和 url1 效率差不多// batch3(driver, url, username, password); // 平均耗时1951// batch3(driver, url1, username, password); // 平均耗时1873 /***********************插入100条数据***************************************/ // url 和 url1 效率差不多// batch4(driver, url, username, password); // 平均耗时7730// batch4(driver, url1, username, password); // 平均耗时6208 // url 和 url1 效率差不多// batch5(driver, url, username, password); // 平均耗时6096// batch5(driver, url1, username, password); // 平均耗时6056 // url 和 url1 效率差不多// batch6(driver, url, username, password); // 平均耗时6466// batch6(driver, url1, username, password); // 平均耗时6310 // url 和 url1 效率差不多// batch7(driver, url, username, password); // 平均耗时5969// batch7(driver, url1, username, password); // 平均耗时6201 /***********************插入10000条数据***************************************/// batch7(driver, url, username, password); // 平均耗时990// batch7(driver, url1, username, password); // 平均耗时>

> 120000 url / batch8 (driver, url, username, password); / / average time consumption 602 shock / batch8 (driver, url1, username, password); / / average time > 120000} private static Connection connect (String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {Class.forName (driver); return DriverManager.getConnection (url, username, password) } private static void batch2 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); Statement stmt = conn.createStatement (); long start = System.currentTimeMillis (); for (int I = 0; I

< 10000; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); // 4175 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start);} private static void batch3 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); Statement stmt = conn.createStatement (); conn.setAutoCommit (false); long start = System.currentTimeMillis (); for (int I = 0; I)

< 10000; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); conn.commit(); // 1398 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start);} private static void batch4 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); Statement stmt = conn.prepareStatement (" select * from dual "); long start = System.currentTimeMillis (); for (int I = 0; I)

< 100; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); // 7730 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start));} private static void batch5 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); Statement stmt = conn.prepareStatement (" select * from dual "); conn.setAutoCommit (false); long start = System.currentTimeMillis (); for (int I = 0; I

< 100; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); conn.commit(); // 7730 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start);} private static void batch6 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); PreparedStatement stmt = conn.prepareStatement (" select * from dual "); long start = System.currentTimeMillis (); for (int I = 0; I)

< 100; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); // 6162 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start));} private static void batch7 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); PreparedStatement stmt = conn.prepareStatement (" select * from dual "); conn.setAutoCommit (false); long start = System.currentTimeMillis (); for (int I = 0; I

< 100; i++){ String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" + "'name" + i + "'," + i + "," + i + "," + i + ")"; stmt.addBatch(batchSql); } stmt.executeBatch(); conn.commit(); // 6162 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start));} private static void batch7 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); PreparedStatement stmt = conn.prepareStatement (" insert into t_user (user_name, age, salary, max_size) values (?) "); long start = System.currentTimeMillis (); for (int I = 0; I

< 10000; i++){ stmt.setInt(1, i + 1); stmt.setInt(2, i + 1000); stmt.setInt(3, i + 1); stmt.setInt(4, i); stmt.addBatch(); } stmt.executeBatch(); // 6162 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start));} private static void batch8 (String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {Connection conn = connect (driver, url, username, password); PreparedStatement stmt = conn.prepareStatement (" insert into t_user (user_name, age, salary, max_size) values "); conn.setAutoCommit (false); long start = System.currentTimeMillis () For (int I = 0; I

< 10000; i++){ stmt.setInt(1, i + 1); stmt.setInt(2, i + 1000); stmt.setInt(3, i + 1); stmt.setInt(4, i); stmt.addBatch(); } stmt.executeBatch(); conn.commit(); // 6162 System.out.println("耗时======>

"+ (System.currentTimeMillis ()-start));}} at this point, I believe you have a deeper understanding of" MySQL JDBC Statement.executeBatch example Analysis ". You might as well do it in practice! here is the website, more related content can go to the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.