Home>

Database script:

-table "t_test" ddl
create table `t_test` (
 `id` int (11) not null auto_increment, `title` varchar (255) default null, `createtime` bigint (20) default null, primary key (`id`)
) engine=innodb default charset=utf8;

Code:

package com.yanek.test;
import java.io.bufferedreader;
import java.io.file;
import java.io.fileoutputstream;
import java.io.filereader;
import java.io.ioexception;
import java.io.outputstreamwriter;
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.preparedstatement;
import java.sql.resultset;
import java.sql.sqlexception;
import java.sql.statement;
public class testdb {
 public static void main (string [] args) {
  test ();//Generate test data
  //exp ();
  //exp (0);
  //system.out.println(readtext("/opt/id.txt "));
 }
 /**
  * export data
  * /
  public static void exp () {
   connection conn=null;
   try {
    class.forname ("com.mysql.jdbc.driver"). newinstance ();
    string jdbcurl="jdbc:mysql://127.0.0.1:3306/testcms?characterencoding=gbk";
    string jdbcusername="root";
    string jdbcpassword="root";
    conn=drivermanager.getconnection (jdbcurl, jdbcusername, jdbcpassword);
    system.out.println ("conn" + conn);
    exp (conn);
   } catch (sqlexception e) {
    e.printstacktrace ();
   }
   catch (instantiationexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (illegalaccessexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (classnotfoundexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   }
   finally
   {
    try {
     conn.close ();
    } catch (sqlexception e) {
     //todo auto-generated catch block
     e.printstacktrace ();
    }
   }
  }
  public static void exp (int startid) {
   connection conn=null;
   try {
    class.forname ("com.mysql.jdbc.driver"). newinstance ();
    string jdbcurl="jdbc:mysql://127.0.0.1:3306/testcms?characterencoding=gbk";
    string jdbcusername="root";
    string jdbcpassword="root";
    conn=drivermanager.getconnection (jdbcurl, jdbcusername, jdbcpassword);
    system.out.println ("conn" + conn);
    exp (conn, startid);
   } catch (sqlexception e) {
    e.printstacktrace ();
   }
   catch (instantiationexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (illegalaccessexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (classnotfoundexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   }
   finally
   {
    try {
     conn.close ();
    } catch (sqlexception e) {
     //todo auto-generated catch block
     e.printstacktrace ();
    }
   }
  }
  /**
  * Export data starting from startid
  * @param conn
  * @param start_id
  * /
  public static void exp (connection conn, int start_id) {
   int counter=0;
   int startid=start_id;
   boolean flag=true;
   while (flag) {
    flag=false;
    string sql="select * from t_test where id>"
      + startid + "order by id asc limit 50";
    system.out.println ("sql ===" + sql);
    try {
     statement stmt=conn.createstatement ();
     resultset rs=stmt.executequery (sql);
      while (rs.next ()) {
       flag=true;
       int id=rs.getint ("id");
       string title=rs.getstring ("title");
       startid=id;
       counter ++;
       writecontent (counter + "-id-" + id + "-title-" + title + "\ r \ n", "/opt/","log.txt",true);
       system.out.println ("i =" + counter + "-id-" + id + "-title-" + title);
      }
     rs.close ();
     stmt.close ();
    } catch (sqlexception e) {
     e.printstacktrace ();
    }
   }
   writecontent ("" + startid, "/opt/","id.txt",false);
  }
  /**
  * Export data within one hour
  * @param conn
  * /
  public static void exp (connection conn) {
   int counter=0;
   //data within one hour
   long timestamp=system.currenttimemillis ()-(60 * 60 * 1000);
   boolean flag=true;
   while (flag) {
    flag=false;
    string sql="select * from t_test where createtime>"
      + timestamp + "limit 50";
    system.out.println ("sql ===" + sql);
    try {
     statement stmt=conn.createstatement ();
     resultset rs=stmt.executequery (sql);
     while (rs.next ()) {
      flag=true;
      int id=rs.getint ("id");
      string title=rs.getstring ("title");
      long lastmodifytime=rs.getlong ("createtime");
      timestamp=lastmodifytime;
      counter ++;
      system.out.println ("i =" + counter + "-id-" + id + "-title-" + title);
     }
     rs.close ();
     stmt.close ();
    } catch (sqlexception e) {
     e.printstacktrace ();
    }
   }
  }
  public static void test () {
   connection conn=null;
   try {
    class.forname ("com.mysql.jdbc.driver"). newinstance ();
    string jdbcurl="jdbc:mysql://127.0.0.1:3306/testcms?characterencoding=gbk";
    string jdbcusername="root";
    string jdbcpassword="root";
    conn=drivermanager.getconnection (jdbcurl, jdbcusername, jdbcpassword);
    system.out.println ("conn" + conn);
    for (int i=1;i<= 10000;i ++)
    {
     add (conn, "testtitle" + i + "-" + system.currenttimemillis ());
    }
   } catch (sqlexception e) {
    e.printstacktrace ();
   }
   catch (instantiationexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (illegalaccessexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   } catch (classnotfoundexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   }
   finally
   {
    try {
     conn.close ();
    } catch (sqlexception e) {
     //todo auto-generated catch block
     e.printstacktrace ();
    }
   }
  }
  public static void add (connection conn, string title)
  {
   preparedstatement pstmt=null;
   string insert_sql="insert into t_test (title, createtime) values ​​(?,?)";
   system.out.println ("sql =" + insert_sql);
   try {
    pstmt=conn.preparestatement (insert_sql);
    pstmt.setstring (1, title);
    pstmt.setlong (2, system.currenttimemillis ());
    int ret=pstmt.executeupdate ();
   } catch (sqlexception e) {
    //todo auto-generated catch block
    e.printstacktrace ();
   }
   finally {
    try {
     pstmt.close ();
    } catch (sqlexception e) {
     //todo auto-generated catch block
     e.printstacktrace ();
    }
   }
  }
  /**
   * Write content to file
   *
   * @param number
   * @param filename
   * @return
   * /
  public static boolean writecontent (string c, string dirname, string filename, boolean isappend) {
   file f=new file (dirname);
   if (! f.exists ())
   {
     f.mkdirs ();
   }
   try {
    fileoutputstream fos=new fileoutputstream (dirname + file.separator + filename, isappend);
    outputstreamwriter writer=new outputstreamwriter (fos);
    writer.write (c);
    writer.close ();
    fos.close ();
   } catch (ioexception e) {
    e.printstacktrace ();
    return false;
   }
   return true;
  }
  /**
   * Read content from a file
   *
   * @param filename
   * @return
   * /
  public static string readtext (string filename) {
   string content="";
   try {
    file file=new file (filename);
    if (file.exists ()) {
     filereader fr=new filereader (file);
     bufferedreader br=new bufferedreader (fr);
     string str="";
     string newline="";
     while ((str=br.readline ())!=null) {
      content +=newline + str;
      newline="\ n";
     }
     br.close ();
     fr.close ();
    }
   } catch (ioexception e) {
    e.printstacktrace ();
   }
   return content;
  }
}

The basic idea is to start by recording the id, and execute multiple sql processing. Due to the large amount of data, you cannot use a sql statement to output.Otherwise, insufficient memory will cause errors.

Main use:Can be used when doing interface development,Use in scenarios that provide incremental data output to third parties.

  • Previous Example of a tool class for implementing image cropping in java
  • Next Android programming method for SMS reading and saving to SQLite