No matter what, I can’t batch MySQL INSERT statements in Hibernate

by

It’s likely your queries are being rewritten but you wouldn’t know if by looking at the Hibernate SQL logs. Hibernate does not rewrite the insert statements – the MySQL driver rewrites them. In other words, Hibernate will send multiple insert statements to the driver, and then the driver will rewrite them. So the Hibernate logs only show you what SQL Hibernate sent to the driver, not what SQL the driver sent to the database.

You can verify this by enabling MySQL’s profileSQL parameter in connection url:

<b:property name="jdbcUrl" value="jdbc:mysql://server:3306/db?autoReconnect=true&amp;rewriteBatchedStatements=true&amp;profileSQL=true" />

Using an example similar to yours, this is what my output looks like:

insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
Wed Feb 05 13:29:52 MST 2014 INFO: Profiler Event: [QUERY]  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) duration: 1 ms, connection-id: 81, statement-id: 33, resultset-id: 0, message: insert into Person (firstName, lastName, id) values ('person1', 'Name', 1),('person2', 'Name', 2),('person3', 'Name', 3),('person4', 'Name', 4),('person5', 'Name', 5),('person6', 'Name', 6),('person7', 'Name', 7),('person8', 'Name', 8),('person9', 'Name', 9),('person10', 'Name', 10)

The first 10 lines are being logged by Hibernate though this not what is actually being sent to MySQL database. The last line is coming from MySQL driver and it clearly shows a single batch insert with multiple values and that is what is actually being sent to the MySQL database.

More Related Contents:

  1. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  2. Connect Java to a MySQL database
  3. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  4. MySQLSyntaxErrorException near “?” when trying to execute PreparedStatement
  5. Can we connect remote MySQL database in Android using JDBC? [closed]
  6. java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0) [closed]
  7. Hibernate 5 :- org.hibernate.MappingException: Unknown entity
  8. java.sql.SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)
  9. JDBC MySql connection pooling practices to avoid exhausted connection pool
  10. Getting java.sql.SQLException: Operation not allowed after ResultSet closed
  11. right syntax to use near ‘?’
  12. Running a .sql script using MySQL with JDBC
  13. The server time zone value ‘AEST’ is unrecognized or represents more than one time zone
  14. Hibernate batch size confusion
  15. JPA SQL Server No Dialect mapping for JDBC type: -9
  16. @GeneratedValue polymorphic abstract superclass over MySQL
  17. Passing parameters to a JDBC PreparedStatement
  18. JDBC character encoding
  19. How can I use MySQL assign operator(:=) in hibernate native query?
  20. Do you need a database transaction for reading data?
  21. Enum in Hibernate, persisting as an enum
  22. Invalid syntax error “type= MyISAM” in DDL generated by Hibernate
  23. Hibernate: Create Mysql InnoDB tables instead of MyISAM
  24. Converting mysql table to spark dataset is very slow compared to same from csv file
  25. Is there a more efficient way of making pagination in Hibernate than executing select and count queries?
  26. Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values
  27. How to setup Hibernate to read/write to different datasources?
  28. JDBC MySQL connection using Unix Socket
  29. Unable to get multiple Table entities through Stored procedure using hibernate
  30. JDBC connection error : unrecognized timezone [duplicate]

Leave a Comment