Spring JdbcTemplate The problem of slow batch update

Because many INSERT statements (5w) are executed at a time, it is common to write native insert statements through MySQL in a similar format:

Insert into TABLEAAA (F1,F2) VALUES (F11V,F21V), (f12v,f22v) ...

Execution speed is no problem, about 5, 6 seconds. In the use of JdbcTemplate, is to use the BatchUpdate method to write batch execution statements:

String sql = "INSERT into code (ID,CODE,STATUS,TIME) VALUES (?,?,?,?)";
Jdbctemplate.batchupdate (SQL, codes, Codes.size (), new parameterizedpreparedstatementsetter<couponcode> () {
    @Override public
    void Setvalues (PreparedStatement preparedstatement, Couponcode code) throws SQLException {
        preparedstatement.setint (1, Code.getcid ());
        Preparedstatement.setstring (2, Code.getcode ());
        Preparedstatement.setint (3, Code.getstatus ());
        Preparedstatement.setdate (4, New Date (New Java.util.Date (). GetTime ()));
    }
);

When executing the statement, it takes more than 10 seconds to complete the execution, which is much worse than the native SQL statement. So consult others, and finally found that the database connection statement is less enabled rewritebatchedstatements parameters. JdbcTemplate default in the BatchUpdate method, is an execution statement of one article. This can be seen in his driver code:

So on the connection string, add

jdbc:mysql://ip:port/database?useunicode=true&characterencoding=utf-8&autoreconnect=true& Rewritebatchedstatements=true

Plus, reboot Tomcat and find that the batch execution is fast.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch