Java实现从数据库导出大量数据记录并保存到文件的方法

-- Table "t_test" DDLCREATE 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;

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(); // 生成测试数据   //Exp();   //Exp(0);   //System.out.println(readText("/opt/id.txt"));  }  /**   * 导出数据   */   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();     }    }   }   /**   * 导出从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);   }   /**   * 导出一小时内的数据   * @param conn   */   public static void Exp(Connection conn) {    int counter = 0;    //一小时内的数据    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();     }     }   }   /**    * 写入内容到文件    *    * @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;   }   /**    * 从文件读取内容    *    * @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;   } }