MySQL and JDBC with rewriteBatchedStatements=true

by

with rewriteBatchedStatements=true the JDBC will pack as many queries as possible into a single network packet, lowering this way the network overhead. Am I right?

Yes. The following code

String myConnectionString =
        "jdbc:mysql://localhost:3307/mydb?" +
        "useUnicode=true&characterEncoding=UTF-8";
try (Connection con = DriverManager.getConnection(myConnectionString, "root", "whatever")) {
    try (PreparedStatement ps = con.prepareStatement("INSERT INTO jdbc (`name`) VALUES (?)")) {
        for (int i = 1; i <= 5; i++) {
            ps.setString(1, String.format(
                    "Line %d: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.", 
                    i));
            ps.addBatch();
        }
        ps.executeBatch();
    }
}

will send individual INSERT statements even though I have created a Batch

INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...')
INSERT INTO jdbc (`name`) VALUES ('Line 2: Lorem ipsum ...')

However, if I change the connection string to include rewriteBatchedStatements=true

String myConnectionString =
        "jdbc:mysql://localhost:3307/mydb?" +
        "useUnicode=true&characterEncoding=UTF-8" +
        "&rewriteBatchedStatements=true";

then JDBC will send one or more multi-row INSERT statements

INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...'),('Line 2: Lorem ipsum ...')

does JDBC knows the value assigned to max_allowed_packet and therefore make the packet smaller than the defined value for max_allowed_packet … ?

Yes. If you enable the MySQL general log and check it you will see that MySQL Connector/J inspects a bunch of variables when it connects, one of which is max_allowed_packet. You can also set a small max_allowed_packet value and verify that JDBC splits a batch into several multi-row INSERT statements if a single such statement for the whole batch would exceed max_allowed_packet.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Multiple queries executed in java in single statement
  3. right syntax to use near ‘?’
  4. Running a .sql script using MySQL with JDBC
  5. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  6. How to use prepared statement for select query in Java?
  7. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  8. How to get the insert ID in JDBC?
  9. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  10. MySQL JDBC Driver 5.1.33 – Time Zone Issue
  11. MySQLSyntaxErrorException near “?” when trying to execute PreparedStatement
  12. How do I get the size of a java.sql.ResultSet?
  13. Using “like” wildcard in prepared statement
  14. Android JDBC not working: ClassNotFoundException on driver
  15. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  16. Variable column names using prepared statements
  17. “EEE MMM dd HH:mm:ss ZZZ yyyy” date format to java.sql.Date
  18. JDBC MySql connection pooling practices to avoid exhausted connection pool
  19. Getting java.sql.SQLException: Operation not allowed after ResultSet closed
  20. Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
  21. JDBC Pagination
  22. Java JDBC MySQL exception: “Operation not allowed after ResultSet closed”
  23. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  24. Queries returning multiple result sets
  25. how to get list of Databases “Schema” names of MySql using java JDBC
  26. Access denied for user ‘root’@’localhost’
  27. No matter what, I can’t batch MySQL INSERT statements in Hibernate
  28. How to connect to a remote MySQL database with Java?
  29. Hibernate Criteria API: get n random rows
  30. Database independence through JDBC in java

Leave a Comment