Quickly bulk insert data in MySQL database using JDBC

Source: Internet
Author: User
Tags bulk insert

How to improve efficiency when using JDBC to connect to MySQL database for data insertion, especially for large-scale sequential data insertion (10w+)?

There are two statement methods in the JDBC programming interface that are particularly noteworthy:

void Addbatch () throws SQLException
Adds a set of parameters to this PreparedStatementObject ' s batch of commands.

Int[] ExecuteBatch () throws SQLException
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of up Date counts. The intElements of the array is returned be ordered to correspond to the commands in the batch, which is ordered according To the order in which they were added to the batch. by using the Addbatch () and ExecuteBatch () methods, you can implement batch processing of data. It is important to note, however, that you first need to set up manual commits in the database link. Connection.setautocommit (False), and then execute the statement after executing the Connection.commit (). [Java]View PlainCopyprint?
  1. Package cyl.demo.ipsearcher;
  2. Import Java.io.BufferedReader;
  3. Import Java.io.FileInputStream;
  4. Import java.io.IOException;
  5. Import Java.io.InputStreamReader;
  6. Import java.sql.Connection;
  7. Import Java.sql.DriverManager;
  8. Import java.sql.PreparedStatement;
  9. Import java.sql.SQLException;
  10. Public class Dbstorehelper {
  11. private String Insert_sql;
  12. Private String CharSet;
  13. private Boolean debug;
  14. private String connectstr;
  15. private String username;
  16. private String password;
  17. Public Dbstorehelper () {
  18. Connectstr = "Jdbc:mysql://localhost:3306/db_ip";
  19. //Connectstr + = "? Useserverprepstmts=false&rewritebatchedstatements=true";
  20. Insert_sql = "INSERT into Tb_ipinfos (IPLONG1,IPLONG2,IPSTR1,IPSTR2,IPDESC) VALUES (?,?,?,?,?)";
  21. CharSet = "GBK";
  22. debug = true;
  23. Username = "root";
  24. Password = "* * *";
  25. }
  26. public void Storetodb (String srcfile) throws IOException {
  27. BufferedReader bfr = new BufferedReader (NewInputStreamReader (new FileInputStream (srcfile), CharSet));
  28. try {
  29. Dostore (BFR);
  30. } catch (Exception e) {
  31. E.printstacktrace ();
  32. } finally {
  33. Bfr.close ();
  34. }
  35. }
  36. private void Dostore (BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException {
  37. Class.forName ("Com.mysql.jdbc.Driver");
  38. Connection conn = drivermanager.getconnection (Connectstr, Username,password);
  39. Conn.setautocommit (false); //Set manual commit
  40. int count = 0;
  41. PreparedStatement PSTs = conn.preparestatement (Insert_sql);
  42. String line = null;
  43. While (null! = (line = Bfr.readline ())) {
  44. string[] Infos = Line.split (";");
  45. if (Infos.length < 5) continue;
  46. if (Debug) {
  47. System.out.println (line);
  48. }
  49. Psts.setlong (1, long.valueof (infos[0]));
  50. Psts.setlong (2, long.valueof (infos[1]));
  51. Psts.setstring (3, infos[2]);
  52. Psts.setstring (4, infos[3]);
  53. Psts.setstring (5, infos[4]);
  54. Psts.addbatch (); //Join batch processing
  55. count++;
  56. }
  57. Psts.executebatch (); //Perform batch processing
  58. Conn.commit (); //Submit
  59. System.out.println ("All down:" + count);
  60. Conn.close ();
  61. }
  62. }
After execution is complete: [Plain]View PlainCopyprint?
    1. All down:103498
    2. Convert finished.
    3. All spend time/s: 47
Altogether 10w+, the execution time spends altogether 47 seconds. This efficiency is still not high, it does not seem to achieve the desired effect, need to further improve. Parameters can also be added to the MySQL JDBC connection string, rewritebatchedstatements=true, MySQL turns off batch processing by default, and opens with this parameter, which overrides the SQL statement submitted to the database, as described in: http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html Useserverprepstmts=false, if not turned on (Useserverprepstmts=false), using com.mysql.jdbc.PreparedStatement for local SQL assembly, and finally sent to the DB is replaced by the final SQL. In this little improvement, add the following statement to the connection string (uncomment the Code construction method): Connectstr + = "? Useserverprepstmts=false&rewritebatchedstatements=true"; Do the following again: [Plain]View PlainCopyprint?
    1. All down:103498
    2. Convert finished.
    3. All spend time/s: 10
The same amount of data, this implementation only took 10 seconds, processing efficiency greatly improved.

Quickly bulk insert data in MySQL database using JDBC

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.