首先说一下这个大批量，是指有上千万的数据量。
例子：
有一张短信历史表，其数据有上千万条数据，要进行数据备份到文本文件，就是执行如下SQL然后将结果集写入到文件中！
select t.msisdn,t.source,t.seq,t.area,t.send_date,t.msg,t.optcode from hnsms.SMS_SEND_10086_HIS_102 t
数据库：Oracle
下面主要列一下我写文件操作的不同实现方法，及运行结果：
第一种：-----------------------------------------------------------------------------
package wap.ftp;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import wap.util.PublicUtil;
public class FileWriteTest {
private Connection conn_a = null;
private PreparedStatement pstmt_a = null;
private ResultSet rs_a = null;
private String FilePath = "";
private File fileName = null;
private FileWriter writer;
private PrintWriter pw;
private String fileAllPath = "";
Map map = new HashMap();
public void init(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
map = PublicUtil.readConfigFile();
conn_a = DriverManager.getConnection((String)map.get("URL"),(String)map.get("USERNAME"),(String)map.get("PASSWORD"));
FilePath = PublicUtil.readServerPath();
} catch (Exception e) {
e.printStackTrace();
}
}
public void readDataToFile(){
long totalStart = System.currentTimeMillis();
init();
try {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
//SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm");
String nowDateStr = sdf.format(date);
fileAllPath = FilePath+nowDateStr+".txt";
fileName = new File(fileAllPath);
try {
writer = new FileWriter(fileAllPath);
pw=new PrintWriter(writer);
} catch (IOException e) {
e.printStackTrace();
}
//createFile("");
//读配置文件--取SQL
String sql_a= (String)map.get("SQL");
System.out.println(sql_a);
pstmt_a = conn_a.prepareStatement(sql_a);
rs_a = pstmt_a.executeQuery();
int num = 0; //记录写文件写了多少行
while(rs_a.next()){
long startTime = System.currentTimeMillis();
String size = (String)map.get("SIZE");
String s = "";
for(int i=1;i<=Integer.parseInt(size);i++){
s += rs_a.getString(i)+"|";
}
s = s.substring(0, s.length()-1);
createFile(s);
num++;
long endTime = System.currentTimeMillis();
System.out.println("写入文件第"+num+"行，耗时"+(endTime-startTime)+"毫秒.");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
finish(); //关闭输入流
closeDB();
}
long totalEnd = System.currentTimeMillis();
System.out.println("-----总耗时："+(totalEnd-totalStart)+"毫秒");
}
public void createFile(String s){
pw.println(s);
}
public void finish(){//关闭输入流，将文字从缓存写入文件
try{
writer.close();
}catch (IOException iox){
System.err.println(iox);
}
}
public void closeDB(){ //关闭数据库连接
if(rs_a!=null){
try {
rs_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(pstmt_a!=null){
try {
pstmt_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn_a!=null){
try {
conn_a.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
}
主要是通过FileWriter + PrintWriter 实现文件数据的写操作！
第二种-------------------------------------------------------------------------------------
package wap.ftp;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import wap.util.PublicUtil;
public class BuffOutPutStreamTest {
private Connection conn_a = null;
private PreparedStatement pstmt_a = null;
private ResultSet rs_a = null;
private String FilePath = "";
private File fileName = null;
private FileOutputStream fos;
private BufferedOutputStream bos;
private String fileAllPath = "";
Map map = new HashMap();
public void init(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
map = PublicUtil.readConfigFile();
conn_a = DriverManager.getConnection((String)map.get("URL"),(String)map.get("USERNAME"),(String)map.get("PASSWORD"));
FilePath = PublicUtil.readServerPath();
} catch (Exception e) {
e.printStackTrace();
}
}
public void readDataToFile(){
long totalStart = System.currentTimeMillis();
init();
try {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm");
String nowDateStr = sdf.format(date);
fileAllPath = FilePath+nowDateStr+".txt";
fileName = new File(fileAllPath);
try {
fos = new FileOutputStream(fileName);
bos = new BufferedOutputStream(fos);
} catch (IOException e) {
e.printStackTrace();
}
createFile("");
//读配置文件--取SQL
String sql_a= (String)map.get("SQL");
System.out.println(sql_a);
pstmt_a = conn_a.prepareStatement(sql_a);
rs_a = pstmt_a.executeQuery();
int num = 0; //记录写文件写了多少行
while(rs_a.next()){
long startTime = System.currentTimeMillis();
String size = (String)map.get("SIZE");
String s = "";
for(int i=1;i<=Integer.parseInt(size);i++){
s += rs_a.getString(i)+"|";
}
s = s.substring(0, s.length()-1);
createFile(s);
num++;
long endTime = System.currentTimeMillis();
System.out.println("写入文件第"+num+"行，耗时"+(endTime-startTime)+"毫秒.");
if(num>=1000000){
break;
}
//-----------定量清缓存一次，如果数据量大（上百万），请开启这个机制
if(num%100000==0){
System.out.println("===============清缓存一次===========");
try {
bos.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
//-----------清缓存机制 end--------------------------------------
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
finish(); //关闭输入流
closeDB();
}
long totalEnd = System.currentTimeMillis();
System.out.println("----总耗时："+(totalEnd-totalStart)+"毫秒");
}
public void createFile(String s){
try {
bos.write(s.getBytes());
} catch (IOException e) {
e.printStackTrace();
}
}
public void finish(){//关闭输入流，将文字从缓存写入文件
try{
bos.flush();
bos.close();
fos.close();
}catch (IOException iox){
System.err.println(iox);
}
}
public void closeDB(){ //关闭数据库连接
if(rs_a!=null){
try {
rs_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(pstmt_a!=null){
try {
pstmt_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn_a!=null){
try {
conn_a.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
}
主要用BufferedOutputStream的缓冲机制
第三种：-----------------------------------------------------------------------------
package wap.ftp;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import wap.util.PublicUtil;
public class FileWriteBufferTest {
private Connection conn_a = null;
private PreparedStatement pstmt_a = null;
private ResultSet rs_a = null;
private String FilePath = "";
private File fileName = null;
private FileWriter writer;
private PrintWriter pw;
private String fileAllPath = "";
Map map = new HashMap();
public void init(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
map = PublicUtil.readConfigFile();
conn_a = DriverManager.getConnection((String)map.get("URL"),(String)map.get("USERNAME"),(String)map.get("PASSWORD"));
FilePath = PublicUtil.readServerPath();
} catch (Exception e) {
e.printStackTrace();
}
}
public void readDataToFile(){
long totalStart = System.currentTimeMillis();
init();
try {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm");
String nowDateStr = sdf.format(date);
fileAllPath = FilePath+nowDateStr+".txt";
fileName = new File(fileAllPath);
try {
writer = new FileWriter(fileAllPath);
pw=new PrintWriter(new BufferedWriter(writer));
} catch (IOException e) {
e.printStackTrace();
}
createFile("");
//读配置文件--取SQL
String sql_a= (String)map.get("SQL");
System.out.println(sql_a);
pstmt_a = conn_a.prepareStatement(sql_a);
rs_a = pstmt_a.executeQuery();
int num = 0; //记录写文件写了多少行
while(rs_a.next()){
long startTime = System.currentTimeMillis();
String size = (String)map.get("SIZE");
String s = "";
for(int i=1;i<=Integer.parseInt(size);i++){
s += rs_a.getString(i)+"|";
}
s = s.substring(0, s.length()-1);
createFile(s);
num++;
long endTime = System.currentTimeMillis();
System.out.println("写入文件第"+num+"行，耗时"+(endTime-startTime)+"毫秒.");
if(num>=1000000){
break;
}
//-----------定量清缓存一次，如果数据量大（上百万），请开启这个机制
//-----------清缓存机制 end--------------------------------------
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
finish(); //关闭输入流
closeDB();
}
long totalEnd = System.currentTimeMillis();
System.out.println("----总耗时："+(totalEnd-totalStart)+"毫秒");
}
public void createFile(String s){
pw.println(s);
}
public void finish(){//关闭输入流，将文字从缓存写入文件
try{
pw.flush();
writer.close();
}catch (IOException iox){
System.err.println(iox);
}
}
public void closeDB(){ //关闭数据库连接
if(rs_a!=null){
try {
rs_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(pstmt_a!=null){
try {
pstmt_a.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(conn_a!=null){
try {
conn_a.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
}
主要用的是FileWriter(加缓冲）的机制。
------------------------------------------------------------------------------
在写不同量的数据的情况下运行结果：
总结，当然我这个测试只是去验证三种写文件方式的机制的在不同数据量时的效率问题！
如果想更快的去完成上千万数量的写操作，最好是启多个线程去操作，这样可以解决时间问题
但要注意数据库连接的释放，要不对数据库是有影响的，特别是我拿的生产数据库测试的。
打完收工。