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
PreparedStatement
Object ' 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
int
Elements 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?
- Package cyl.demo.ipsearcher;
- Import Java.io.BufferedReader;
- Import Java.io.FileInputStream;
- Import java.io.IOException;
- Import Java.io.InputStreamReader;
- Import java.sql.Connection;
- Import Java.sql.DriverManager;
- Import java.sql.PreparedStatement;
- Import java.sql.SQLException;
- Public class Dbstorehelper {
- private String Insert_sql;
- Private String CharSet;
- private Boolean debug;
- private String connectstr;
- private String username;
- private String password;
- Public Dbstorehelper () {
- Connectstr = "Jdbc:mysql://localhost:3306/db_ip";
- //Connectstr + = "? Useserverprepstmts=false&rewritebatchedstatements=true";
- Insert_sql = "INSERT into Tb_ipinfos (IPLONG1,IPLONG2,IPSTR1,IPSTR2,IPDESC) VALUES (?,?,?,?,?)";
- CharSet = "GBK";
- debug = true;
- Username = "root";
- Password = "* * *";
- }
- public void Storetodb (String srcfile) throws IOException {
- BufferedReader bfr = new BufferedReader (NewInputStreamReader (new FileInputStream (srcfile), CharSet));
- try {
- Dostore (BFR);
- } catch (Exception e) {
- E.printstacktrace ();
- } finally {
- Bfr.close ();
- }
- }
- private void Dostore (BufferedReader bfr) throws ClassNotFoundException, SQLException, IOException {
- Class.forName ("Com.mysql.jdbc.Driver");
- Connection conn = drivermanager.getconnection (Connectstr, Username,password);
- Conn.setautocommit (false); //Set manual commit
- int count = 0;
- PreparedStatement PSTs = conn.preparestatement (Insert_sql);
- String line = null;
- While (null! = (line = Bfr.readline ())) {
- string[] Infos = Line.split (";");
- if (Infos.length < 5) continue;
- if (Debug) {
- System.out.println (line);
- }
- Psts.setlong (1, long.valueof (infos[0]));
- Psts.setlong (2, long.valueof (infos[1]));
- Psts.setstring (3, infos[2]);
- Psts.setstring (4, infos[3]);
- Psts.setstring (5, infos[4]);
- Psts.addbatch (); //Join batch processing
- count++;
- }
- Psts.executebatch (); //Perform batch processing
- Conn.commit (); //Submit
- System.out.println ("All down:" + count);
- Conn.close ();
- }
- }
After execution is complete:
[Plain]View PlainCopyprint?
- All down:103498
- Convert finished.
- 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?
- All down:103498
- Convert finished.
- 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