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 st;
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("/"));
}
/**
* 导出数据
*/
public static void Exp() {
Connection Conn=null;
try{
Class.forName("sql.jdbc.Driver").newInstance();
String jdbcUrl = "jdbc:";
String jdbcUsername = "root";
String jdbcPassword = "root";
Conn = Connection(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("sql.jdbc.Driver").newInstance();
String jdbcUrl = "jdbc:";
String jdbcUsername = "root";
String jdbcPassword = "root";
Conn = Connection(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 = ateStatement();
ResultSet rs = uteQuery(Sql);
()) {
flag = true;
int id = rs.getInt("id");
String title = rs.getString("title");
startid = id ;
counter++;
writeContent(counter+"--id--"+id+"--title-"+title+"\r\n", "/opt/","",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;
flag = false;
String Sql = "SELECT * FROM t_test WHERE createTime>"
+ timestamp + " LIMIT 50";
System.out.println("sql==="+ Sql);
try{
Statement stmt = ateStatement();
ResultSet rs = uteQuery(Sql);
()) {
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("sql.jdbc.Driver").newInstance();
String jdbcUrl = "jdbc:";
String jdbcUsername = "root";
String jdbcPassword = "root";
Conn = Connection(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 = uteUpdate();
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
try{
pstmt.close();
} catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
e.printStackTrace();
mkdirs方法
}
}
}
/**
* 写⼊内容到⽂件
*
* @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);
ists()) {
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语句来输出.否则会内存不⾜导致错误.主要⽤途: 可以使⽤在做接⼝开发时，给第三⽅提供数据增量输出的场景使⽤.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。