本篇内容主要讲解“MySQL JDBC Statement.executeBatch举例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL JDBC Statement.executeBatch举例分析”吧!
mport java.sql.*; /** * @Author cherrishccl * @Date 2020/9/4 17:01 * @Version 1.0 * @Description * 用JDBC执行批量提交正确方式: * 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 // batch8(driver, url, username, password); // 平均耗时602 // batch8(driver, url1, username, password); // 平均耗时>>>>>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)); } }到此，相信大家对“MySQL JDBC Statement.executeBatch举例分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
