How to use JDBC to quickly process large data

In the internship, to deal with a table with 2.04 million records, because the record is taken from the Internet, there are some less ideal words, such as some words mixed with special characters, punctuation, or some words are simply punctuation and so on. I write this program is to find out these unsatisfactory words, can be modified on the modified, no need to modify the direct deletion.

[java]&http://www.aliyun.com/zixun/aggregation/37954.html ">nbsp;view plaincopy for (int i=0;i<205;i++) {                           String sql= "SELECT * from Cat_keyword the ORDER by ID limit" +i*10001+ ", 10000";                  String best= "SELECT * from Cat_keyword where id>= (select ID from Cat_keyword ORDER by ID Limit" +i*10001+ ", 1) limit 10000";                  Rs=stmt.executequery (best);              Go.filt (RS); } 

The efficiency of the first SQL statement is obviously less than that of the second one.

String best= "SELECT * from Cat_keyword where id>= (select ID from Cat_keyword ORDER by ID Limit" +i*10001+ ", 1) limit 10000";

This SQL statement is especially useful in situations where the offset is particularly large, because the 200多万条 record, in the middle and late, this offset is very large, using an optimized SQL statement to the late is getting slower.

In addition, the value of limit is also very elegant, I tried 1000,10000,70000,100000, and finally found that limit take 10000 of the time is the fastest, and the size of the data, computer and database memory allocation has a certain relationship, It can be discussed in detail according to the specific situation. I'm here just to mention the point that needs attention and can be optimized.

What I really want to say is another solution that I want to use JDBC

Let me say a few words about the construction of this program, first of all, is an implementation of the need to delete and modify the word inserted into another table method

[Java] view plaincopy //will find the offending words into the cat_garbage table      public void insert ( RESULTSET RS)  throws SQLException     {         connection  conn=null;          statement stmt=null;         try{                        conn=good.getconnection ();                  Stmt = conn.createstatement (Resultset.type_scroll_ insensitive                     ,  resultset.concur_updatable );                  string query= "Select * from cat_ Garbage where id= ' "+rs.getint (" id ") +" ";                  Resultset rst=stmt.executequery (query);                  if (!rst.next ())                   {                      string sql1= "Insert into cat_garbage (Id,cid, Name,keyword)  values (' "+rs.getint (" id ") +" ', ' "+rs.getint (" CID ") +" ', ' "+rs.getstring (" name ") +" ', ' "+ rs.getstring ("keyword") + "')";                      Stmt.executeupdate (SQL1);                 }          }          catch (sqlexception| classnotfoundexception| Ioexception e)           {              E.printstacktrace ( );         }          finally          {              if (stmt!= null)                    Stmt.close ();                     if (conn!=  null)                    conn.close ();          }     } 

And then a way to fix the word

[Java] view plaincopy public void Modify (ResultSet rs,string str,string reg) throws SQLException {