Java实现从数据库导出大量数据记录并保存到文件的方法
本文实例讲述了Java实现从数据库导出大量数据记录并保存到文件的方法。分享给大家供大家参考，具体如下：
数据库脚本：
--Table"t_test"DDL CREATETABLE`t_test`( `id`int(11)NOTNULLAUTO_INCREMENT, `title`varchar(255)DEFAULTNULL, `createTime`bigint(20)DEFAULTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8;
代码:
packagecom.yanek.test;
importjava.io.BufferedReader;
importjava.io.File;
importjava.io.FileOutputStream;
importjava.io.FileReader;
importjava.io.IOException;
importjava.io.OutputStreamWriter;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassTestDB{
publicstaticvoidmain(String[]args){
Test();//生成测试数据
//Exp();
//Exp(0);
//System.out.println(readText("/opt/id.txt"));
}
/**
*导出数据
*/
publicstaticvoidExp(){
ConnectionConn=null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
StringjdbcUrl="jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
StringjdbcUsername="root";
StringjdbcPassword="root";
Conn=DriverManager.getConnection(jdbcUrl,jdbcUsername,jdbcPassword);
System.out.println("conn"+Conn);
Exp(Conn);
}catch(SQLExceptione){
e.printStackTrace();
}
catch(InstantiationExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IllegalAccessExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(ClassNotFoundExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
finally
{
try{
Conn.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
publicstaticvoidExp(intstartid){
ConnectionConn=null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
StringjdbcUrl="jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
StringjdbcUsername="root";
StringjdbcPassword="root";
Conn=DriverManager.getConnection(jdbcUrl,jdbcUsername,jdbcPassword);
System.out.println("conn"+Conn);
Exp(Conn,startid);
}catch(SQLExceptione){
e.printStackTrace();
}
catch(InstantiationExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IllegalAccessExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(ClassNotFoundExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
finally
{
try{
Conn.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
/**
*导出从startid开始的数据
*@paramconn
*@paramstart_id
*/
publicstaticvoidExp(Connectionconn,intstart_id){
intcounter=0;
intstartid=start_id;
booleanflag=true;
while(flag){
flag=false;
StringSql="SELECT*FROMt_testWHEREid>"
+startid+"orderbyidascLIMIT50";
System.out.println("sql==="+Sql);
try{
Statementstmt=conn.createStatement();
ResultSetrs=stmt.executeQuery(Sql);
while(rs.next()){
flag=true;
intid=rs.getInt("id");
Stringtitle=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(SQLExceptione){
e.printStackTrace();
}
}
writeContent(""+startid,"/opt/","id.txt",false);
}
/**
*导出一小时内的数据
*@paramconn
*/
publicstaticvoidExp(Connectionconn){
intcounter=0;
//一小时内的数据
Longtimestamp=System.currentTimeMillis()-(60*60*1000);
booleanflag=true;
while(flag){
flag=false;
StringSql="SELECT*FROMt_testWHEREcreateTime>"
+timestamp+"LIMIT50";
System.out.println("sql==="+Sql);
try{
Statementstmt=conn.createStatement();
ResultSetrs=stmt.executeQuery(Sql);
while(rs.next()){
flag=true;
intid=rs.getInt("id");
Stringtitle=rs.getString("title");
Longlastmodifytime=rs.getLong("createTime");
timestamp=lastmodifytime;
counter++;
System.out.println("i="+counter+"--id--"+id+"--title-"+title);
}
rs.close();
stmt.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
publicstaticvoidTest(){
ConnectionConn=null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
StringjdbcUrl="jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
StringjdbcUsername="root";
StringjdbcPassword="root";
Conn=DriverManager.getConnection(jdbcUrl,jdbcUsername,jdbcPassword);
System.out.println("conn"+Conn);
for(inti=1;i<=10000;i++)
{
add(Conn,"testTitle"+i+"-"+System.currentTimeMillis());
}
}catch(SQLExceptione){
e.printStackTrace();
}
catch(InstantiationExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IllegalAccessExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(ClassNotFoundExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
finally
{
try{
Conn.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
publicstaticvoidadd(Connectionconn,Stringtitle)
{
PreparedStatementpstmt=null;
Stringinsert_sql="insertintot_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());
intret=pstmt.executeUpdate();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
finally{
try{
pstmt.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
/**
*写入内容到文件
*
*@paramnumber
*@paramfilename
*@return
*/
publicstaticbooleanwriteContent(Stringc,Stringdirname,Stringfilename,booleanisAppend){
Filef=newFile(dirname);
if(!f.exists())
{
f.mkdirs();
}
try{
FileOutputStreamfos=newFileOutputStream(dirname+File.separator+filename,isAppend);
OutputStreamWriterwriter=newOutputStreamWriter(fos);
writer.write(c);
writer.close();
fos.close();
}catch(IOExceptione){
e.printStackTrace();
returnfalse;
}
returntrue;
}
/**
*从文件读取内容
*
*@paramfilename
*@return
*/
publicstaticStringreadText(Stringfilename){
Stringcontent="";
try{
Filefile=newFile(filename);
if(file.exists()){
FileReaderfr=newFileReader(file);
BufferedReaderbr=newBufferedReader(fr);
Stringstr="";
Stringnewline="";
while((str=br.readLine())!=null){
content+=newline+str;
newline="\n";
}
br.close();
fr.close();
}
}catch(IOExceptione){
e.printStackTrace();
}
returncontent;
}
}
基本思想:就是通过记录开始记录id,执行多次sql来处理.由于大数据量所以不能使用一条sql语句来输出.否则会内存不足导致错误.
主要用途:可以使用在做接口开发时,给第三方提供数据增量输出的场景使用.
希望本文所述对大家Java程序设计有所帮助。