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

This article will explain in detail how the practical problems of MySQL JDBC Statement.executeBatch are, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

MySQL JDBC Statement.executeBatch practice (inefficient execution)

Native jdbc is rarely used to implement code, and recently encountered a problem when testing MySQL batch data: extremely inefficient execution of Statement.executeBatch (inserting 10000 pieces of data) and pausing here for a long time during breakpoint debugging (908712ms)

Data version:

Select version (); / 5.7.17-11-V2.0R530D002-20190816-1203-log

Database tables:

CREATE TABLE `t _ user` (`id` int (11) NOT NULL AUTO_INCREMENT, `user_ name` varchar (64) DEFAULT NULL, `age` smallint (6) DEFAULT NULL, `salary` decimal (10) DEFAULT NULL, `max_ size` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

The test code is as follows:

Public class BatchTest {public static void main (String [] args) throws SQLException, ClassNotFoundException {batch ("com.mysql.cj.jdbc.Driver", "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true", "root", "123456") } private static void batch (String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {/ / jdbc driver: mysql-connector-java:8.0.21 Class.forName (driver); Connection conn = DriverManager.getConnection (url, username, password); conn.setAutoCommit (false); PreparedStatement pstmt = conn.prepareStatement ("select * from dual"); for (int I = 1) I 908712 System.out.println ("total = >" + (System.currentTimeMillis ()-start));}}

Reason

AddBatch () is defined in PreparedStatement, and addBatch (String) is defined in Statement

By default, the precompiled MySQL JDBC driver will disregard the executeBatch () statement, break up a group of sql statements that we expect to execute in batches, and send them to the MySQL database one by one, resulting in lower performance.

BatchHasPlainStatements=true, even if Statement.addBatch (String) is used when using PreparedStatement, does not perform batch operations and sends it to the MySQL database one by one, resulting in lower performance. (exact usage: PreparedStatement + addBatch (), Statement + addBatch (sql))

JDBC driver source code:

Public class ClientPreparedStatement extends com.mysql.cj.jdbc.StatementImpl implements JdbcPreparedStatement {/ * * Does the batch (if any) contain "plain" statements added by * Statement.addBatch (String)? * * If so, we can't re-write it to use multi-value or multi-queries. * / protected boolean batchHasPlainStatements = false; / /. Protected long [] executeBatchInternal () throws SQLException {synchronized (checkClosed (). GetConnectionMutex ()) {if (this.connection.isReadOnly ()) {throw new SQLException (Messages.getString ("PreparedStatement.25") + Messages.getString ("PreparedStatement.26"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT) } if (this.query.getBatchedArgs () = = null | | this.query.getBatchedArgs () .size () = = 0) {return new long [0];} / / we timeout the entire batch, not individual statements int batchTimeout = getTimeoutInMillis (); setTimeoutInMillis (0) ResetCancelledState (); try {statementBegins (); clearWarnings () / / 1. BatchHasPlainStatements contains the original sql statement / / 2. RewriteBatchedStatements uses batch if (! this.batchHasPlainStatements & & this.rewriteBatchedStatements.getValue ()) {if (PreparedQuery) this.query). GetParseInfo (). CanRewriteAsMultiValueInsertAtSqlLevel ()) {return executeBatchedInserts (batchTimeout) } if (! this.batchHasPlainStatements & & this.query.getBatchedArgs ()! = null & & this.query.getBatchedArgs (). Size () > 3 / * cost of option setting rt-wise * /) {return executePreparedBatchAsMultiStatement (batchTimeout) }} return executeBatchSerially (batchTimeout);} finally {this.query.getStatementExecuting (). Set (false); clearBatch ();}} / /.}

Fix:

Add & rewriteBatchedStatements=true to the connection url, such as jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true

Replace Statement.addBatch (String) with Statement.addBatch ()

Public class BatchTest {public static void main (String [] args) throws SQLException, ClassNotFoundException {batch ("com.mysql.cj.jdbc.Driver", "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true", "root", "123456");} private static void batch (String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {Class.forName (driver) Connection conn = DriverManager.getConnection (url, username, password); conn.setAutoCommit (false); String pSql = "insert into t_user (user_name, age, salary, max_size) values"; PreparedStatement pstmt = conn.prepareStatement (pSql); for (int I = 1; I 2598 System.out.println ("total = >" + (System.currentTimeMillis ()-start) }} this is the end of the sharing of MySQL JDBC Statement.executeBatch practice questions. I hope the above content can help you to some extent and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.