MYSQL batch data insertion implementation code page 1/3, mysql insert code page 3

Source: Internet
Author: User
Tags mysql insert

MYSQL batch data insertion implementation code page 1/3, mysql insert code page 3
@ Echo off
Cls
Set CLASSPATH = .. \ api \ jogre. jar
Set CLASSPATH = % CLASSPATH % ;.
Set CLASSPATH = % CLASSPATH %; classes
Set CLASSPATH = % CLASSPATH %; lib \ dom4j. jar
Java org. jogre. server. JogreServer
Create a table
Copy codeThe Code is as follows:
Create database con_test;
Use con_test;
Create table test (id int not null, txt varchar (70), primary key (id), index (id ));

Add an index to the id of two fields.
Use a java program to insert a record for 0.1 million cycles, id (number of cycles) and content (this record is = xx)
InsertTestMysql. java
Copy codeThe Code is as follows:
Import java. lang .*;
Import java. SQL .*;
Public class InsertTestMysql {
Public static void main (String [] args ){
Java. util. Date now_start = new java. util. Date ();
Long start_time = now_start.getTime ();
Int st = 100000;
String str, info;
String db = "org. gjt. mm. mysql. Driver ";
String host = "jdbc: mysql: // 192.168.1.35/test ";
String user = "root ";
String passwd = "root ";
Connection con = null;
Try {
Class. forName (db). newInstance ();
}
Catch (Exception e ){
System. out. println ("failed to load the driver:" + db );
}
Try {
Con = DriverManager. getConnection (host, user, passwd );
Con. setAutoCommit (false); // closes automatic transaction commit
For (int I = 1; I <= st; I ++ ){
Info = "this record is = ";
Info = info. concat (java. lang. Integer. toString (I ));
Str = "insert into test (id, txt) values (?,?); ";
PreparedStatement pstmt = con. prepareStatement (str );
Pstmt. setInt (1, I );
Pstmt. setString (2, info );
Pstmt.exe cuteUpdate ();
}
Con. commit (); // after the statement is executed, submit the transaction.
Con. close ();
}
Catch (Exception e ){
System. out. println (e );
}
Java. util. Date now_end = new java. util. Date ();
Long end_time = now_end.getTime ();
Long use_time = end_time-start_time;
System. out. println ("<--- time consumed to generate this page [" + use_time + "] Millisecond (" + (double) use_time)/1000 + "seconds) ---> ");
System. out. println ("\ n <--- insert records in total" + st + "--> ");
}
}

Different Versions of jdbc have different performance.
When this number is inserted in jdbc 3.1.7, 12778, the program exits and the Chinese language is normal.
When this number is inserted in jdbc 3.1.12 12000, the program exits and the Chinese language is normal.
The following prompt is displayed:
"Exception in thread" main "java. lang. OutOfMemoryError: Java heap space"
"Java heap space error" may be caused by insufficient memory on my machine. However, it is normal to use the 3.10 series jdbc.
Jdbc 3.0.16-ga 0.1 million records are normal and Chinese characters are normal.
Jdbc 3.0.10 0.1 million records are successful, but Chinese characters are incorrect.
With the 3.1 series jdbc, after the program runs, the remaining physical memory of the machine will soon become 40xxKB.
This may also be because the jdbc3.1 series requires a large amount of memory, and my machine has insufficient memory.
I will go to my classmate's AMD64 512 M RAM machine for testing tomorrow.
Jdbc 3.0.16-ga: the only normal one. The test result is:
Copy codeThe Code is as follows:
D: \ Program Files \ test \ db_test> java InsertTestMysql
<--- It takes [98582] milliseconds (98.582 seconds) to generate the current page --->
<--- Insert 100000 records in total -->

I tested it again a few days ago and connected ms-SQL server 2000 sp3 with jdbc of open-source jdts. The test results were terrible:
Copy codeThe Code is as follows:
D: \ dev \ java \ src \ ts \ Ms-SQL> java InsertTestMssql
<--- It takes [1746681] milliseconds (1746.681 seconds) to generate the current page --->
<--- Insert 100000 records in total -->

Current 1/3 page123. Next page

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.