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