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程序设计有所帮助。