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

本文实例讲述了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+"\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;
  }
}

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

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

希望本文所述对大家Java程序设计有所帮助。