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

数据库脚本：

-- 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; 

代码：

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+" ", "/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 = " "; 

     } 

     br.close(); 

     fr.close(); 

    } 

   } catch (IOException e) { 

    e.printStackTrace(); 

   } 

   return content; 

  } 

}

基本思想: 就是通过记录开始记录id,执行多次sql来处理. 由于大数据量所以不能使用一条sql语句来输出.否则会内存不足导致错误.

主要用途: 可以使用在做接口开发时,给第三方提供数据增量输出的场景使用.