Comparison of Three JDBC batch insertion programming methods

Http://superjavason.iteye.com/blog/255423

 

JDBC batch insert is mainly used for data import and logging, because logs are usually written under files first.
I used the JDBC driver of MySQL 5.1.5 to test three common methods.

• Method 1: Use preparedstatement to add batch operations

Java code

2. Try {
3. Class. forname ("com. MySQL. JDBC. Driver ");
4. Conn = drivermanager. getconnection (o_url, username, password );
5. Conn. setautocommit (false );
6. String SQL = "insert adlogs (IP, website, yyyymmdd, hour, object_id) values (?,?,?,?,?) ";
7. Preparedstatement Prest = conn. preparestatement (SQL, resultset. type_scroll_sensitive, resultset. concur_read_only );
8. For (INT x = 0; x <size; X ++ ){
9. Prest. setstring (1, "192.168.1.1 ");
10. Prest. setstring (2, "localhost ");
11. Prest. setstring (3, "20081009 ");
12. Prest. setint (4, 8 );
13. Prest. setstring (5, "11111111 ");
14. Prest. addbatch ();
15. }
16. Prest.exe cutebatch ();
17. Conn. Commit ();
18. Conn. Close ();
19. } Catch (sqlexception ex ){
20. Logger. getlogger (mylogger. Class. getname (). Log (level. Severe, null, ex );
21. } Catch (classnotfoundexception ex ){
22. Logger. getlogger (mylogger. Class. getname (). Log (level. Severe, null, ex );
23. }

The meanings of the following two parameters during statement:
The first parameter specifies the type of the resultset. The options include:
Type_forward_only: Default type. Only one forward access is allowed and is not affected by changes made to the database by other users.
Type_scroll_insensitive: Allows moving forward or backward in the list, or even specific positioning, such as moving to the Fourth Record in the list or moving two records backward from the current position. It is not affected by changes made by other users to the database.
Type_scroll_sensitive: Allows location in records like type_scroll_insensitive. This type is affected by changes made by other users. If you delete a record after executing the query, the record will disappear from the resultset. Similarly, changes to data values are also reflected in the resultset.
The second parameter sets the concurrency of the resultset. This parameter determines whether the resultset can be updated. The options include:
Concur_read_only: This is the default value and cannot be updated.
Resultset concur_updatable: Indicates that the resultset can be updated.

• Method 2 Use statement to add batches

 

Java code

2. Conn. setautocommit (false );
3. Statement stmt = conn. createstatement (resultset. type_scroll_sensitive, resultset. concur_read_only );
4. For (INT x = 0; x <size; X ++ ){
5. Stmt. addbatch ("insert into adlogs (IP, website, yyyymmdd, hour, object_id) values ('2017. 168.1.3 ', 'localhost', '123', 8, '123 ')");
6. }
7. Stmt.exe cutebatch ();
8. Conn. Commit ();

• Method 3: directly use statement

Java code

1. Conn. setautocommit (false );
2. Statement stmt = conn. createstatement (resultset. type_scroll_sensitive,
3. Resultset. concur_read_only );
4. For (INT x = 0; x <size; X ++ ){
5. Stmt.exe cute ("insert into adlogs (IP, website, yyyymmdd, hour, object_id) values ('2017. 168.1.3 ', 'localhost', '123', 8, '123 ')");
6. }
7. Conn. Commit ();

The average test time for inserting 0.1 million data entries using the preceding method is as follows:
Method 1:17.844S
Method 2:18.421S
Method 3:16.359S

It can be seen that the insert of JDBC batch statements does not improve the performance, but is slower than the insert without batch. Of course, this may be related to the implementation method of the specific JDBC driver. The attachment contains the test code that can be used to run on your computer.

When executing batch inserts, the most important thing is to automatically submit and cancel the inserts, so no matter whether or not the JDBC batch syntax is used or not.

Java code

1. Conn. setautocommit (false)

I personally think the first method is the most convenient and practical.