欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-06 16:51:56
本文实例讲述了java实现从数据库导出大量数据记录并保存到文件的方法。分享给大家供大家参考,具体如下: 数据库脚本: -- table "t_test" ddl...

本文实例讲述了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程序设计有所帮助。