從數(shù)據(jù)庫導出大量數(shù)據(jù)記錄保存到文件的方法和實例
[導讀]從數(shù)據(jù)庫導出大量數(shù)據(jù)記錄保存到文件的方法和實例數(shù)據(jù)庫腳本：-- Table "t_test" DDLCREATE TABLE `t_test` (? `id` int(11) NOT NULL AUT
從數(shù)據(jù)庫導出大量數(shù)據(jù)記錄保存到文件的方法和實例
數(shù)據(jù)庫腳本：
-- 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();??//?生成測試數(shù)據(jù)
//Exp();
//Exp(0);
//System.out.println(readText("/opt/id.txt"));
}
/**
?*?導出數(shù)據(jù)
?*/
?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開始的數(shù)據(jù)
??*?@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+"rn",?"/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);?
?}
?
?
?/**
??*?導出一小時內(nèi)的數(shù)據(jù)
??*?@param?conn
??*/
?public?static?void?Exp(Connection?conn)?{
?
int?counter?=?0;
//一小時內(nèi)的數(shù)據(jù)
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();
}
}
????}
?
?/**
?*?寫入內(nèi)容到文件
?*?
?*?@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;
}
?
/**
?*?從文件讀取內(nèi)容
?*?
?*?@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;
}
}
?
基本內(nèi)思想: 就是通過記錄開始記錄id,執(zhí)行多次sql來處理. 由于大數(shù)據(jù)量所以不能使用一條sql語句來輸出.否則會內(nèi)存不足導致錯誤.
?
主要用途: 可以使用在做接口開發(fā)時,給第三方提供數(shù)據(jù)增量輸出的場景使用.
?
有問題可以聯(lián)系qq: 1046011462
下載文檔
