使用Java 导出大表数据到文件的例子

主程序

package com;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;




import java.sql.CallableStatement;
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 StartApp {
/**
* Logger for this class
*/
private static final Log logger = LogFactory.getLog(StartApp.class);


public Connection getConnection() {
if (logger.isDebugEnabled()) {
logger.debug("getConnection() - start"); //$NON-NLS-1$
}


Connection conn = null;
String jdbcURL = "jdbc:oracle:thin:@localhost:1521:oracle";


String user = "scott";
String passwd = "scott";
try {
conn = DriverManager.getConnection(jdbcURL, user, passwd);
} catch (SQLException e) {
logger.error("getConnection()", e); //$NON-NLS-1$


}
if (conn == null) {
// FATAL
}


if (logger.isDebugEnabled()) {
logger.debug("getConnection() - end"); //$NON-NLS-1$
}
return conn;
}


public static void releaseConnection(Connection conn) {
if (logger.isDebugEnabled()) {
logger.debug("releaseConnection(Connection) - start"); //$NON-NLS-1$
}


if (conn != null) {


try {
conn.close();
} catch (SQLException e) {
logger.error("releaseConnection(Connection)", e); //$NON-NLS-1$


// TODO Auto-generated catch block


}


}


if (logger.isDebugEnabled()) {
logger.debug("releaseConnection(Connection) - end"); //$NON-NLS-1$
}
}


public static void closeResultSet(ResultSet rs) {
if (logger.isDebugEnabled()) {
logger.debug("closeResultSet(ResultSet) - start"); //$NON-NLS-1$
}


if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
logger.error("closeResultSet(ResultSet)", e); //$NON-NLS-1$


// TODO Auto-generated catch block


}
}


if (logger.isDebugEnabled()) {
logger.debug("closeResultSet(ResultSet) - end"); //$NON-NLS-1$
}
}


public static void closeStatement(Statement stmt) {
if (logger.isDebugEnabled()) {
logger.debug("closeStatement(Statement) - start"); //$NON-NLS-1$
}


if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
logger.error("closeStatement(Statement)", e); //$NON-NLS-1$


// TODO Auto-generated catch block


}
}


if (logger.isDebugEnabled()) {
logger.debug("closeStatement(Statement) - end"); //$NON-NLS-1$
}
}


public static void closePreparedStatement(PreparedStatement ps) {
if (logger.isDebugEnabled()) {
logger.debug("closePreparedStatement(PreparedStatement) - start"); //$NON-NLS-1$
}


if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
logger.error("closePreparedStatement(PreparedStatement)", e); //$NON-NLS-1$


// TODO Auto-generated catch block


}
}


if (logger.isDebugEnabled()) {
logger.debug("closePreparedStatement(PreparedStatement) - end"); //$NON-NLS-1$
}
}


public static void closeCallableStatement(CallableStatement cs) {
if (logger.isDebugEnabled()) {
logger.debug("closeCallableStatement(CallableStatement) - start"); //$NON-NLS-1$
}


if (cs != null) {
try {
cs.close();
} catch (SQLException e) {
logger.error("closeCallableStatement(CallableStatement)", e); //$NON-NLS-1$


}
}


if (logger.isDebugEnabled()) {
logger.debug("closeCallableStatement(CallableStatement) - end"); //$NON-NLS-1$
}
}


/**
* @param args
*/
public static void main(String[] args) {
if (logger.isDebugEnabled()) {
logger.debug("main(String[]) - start"); //$NON-NLS-1$
}


// TODO Auto-generated method stub
StartApp app = new StartApp();
Connection conn = app.getConnection();
RptControl rpt = new RptControl();
rpt.generate(conn, true);


if (logger.isDebugEnabled()) {
logger.debug("main(String[]) - end"); //$NON-NLS-1$
}
}


}

输出程序：

package com;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import oracle.jdbc.OraclePreparedStatement;


public class RptControl {
/**
* Logger for this class
*/
private static final Log logger = LogFactory.getLog(RptControl.class);


private String destinationFile = "D://buffer.tmp";


Byte[] fixedLengthByte = new Byte[8 * 1024 * 1024];
FileChannel writer = null;
String fileHeader = "                     " + " file head" + "\n";
boolean writtenFileHeader = false;


public void generate(Connection conn, boolean releaseConnection) {
if (logger.isDebugEnabled()) {
logger.debug("generate(Connection, boolean) - start"); //$NON-NLS-1$
}


try {
generate(conn);
} finally {
if (releaseConnection) {
try {
conn.close();
} catch (SQLException e) {
logger.error("generate(Connection, boolean)", e); //$NON-NLS-1$


// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


if (logger.isDebugEnabled()) {
logger.debug("generate(Connection, boolean) - end"); //$NON-NLS-1$
}
}


private void generate(Connection conn) {
if (logger.isDebugEnabled()) {
logger.debug("generate(Connection) - start"); //$NON-NLS-1$
}


if (conn != null) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement("select * from emp where deptno=?");
OraclePreparedStatement ops = ((OraclePreparedStatement) ps);
ops.setInt(1, 30);
rs = ps.executeQuery();
while (rs.next()) {
generateFileHeader();
String name = rs.getString("ENAME");
String job = rs.getString("JOB");
StringBuffer sb = new StringBuffer();
// sb.append(fixedLengthByte.toString());
sb.append("\"");
sb.append(name);
sb.append("\"");
sb.append("\"");
sb.append(job);
sb.append("\"");
sb.append("\n");


for (int i = 0; i < 1000; i++) {
generate(sb.toString(), false);
}
closeWriter();
}
} catch (SQLException e) {
logger.error("generate(Connection)", e); //$NON-NLS-1$


// TODO Auto-generated catch block
e.printStackTrace();
} finally {
StartApp.closeResultSet(rs);
StartApp.closePreparedStatement(ps);
}


} else {


}


if (logger.isDebugEnabled()) {
logger.debug("generate(Connection) - end"); //$NON-NLS-1$
}
}


public void generate(String line, boolean forceCloseWriter) {
if (logger.isDebugEnabled()) {
logger.debug("generate(String, boolean) - start"); //$NON-NLS-1$
}


try {
if (writer == null) {
File file = new File(destinationFile);
if (!file.canWrite()) {
try {
file.createNewFile();
} catch (IOException e) {
logger.error("generate(String, boolean)", e); //$NON-NLS-1$


// TODO Auto-generated catch block
e.printStackTrace();
}
}
openWriter();
}


writeFileByLine(writer, line);
} finally {
if (writer != null && forceCloseWriter) {
try {
writer.close();
} catch (IOException e) {
logger.error("generate(String, boolean)", e); //$NON-NLS-1$


e.printStackTrace();
}
}
}


if (logger.isDebugEnabled()) {
logger.debug("generate(String, boolean) - end"); //$NON-NLS-1$
}
}


public void generateFileHeader() {
if (logger.isDebugEnabled()) {
logger.debug("generateFileHeader() - start"); //$NON-NLS-1$
}


if (!writtenFileHeader) {
openWriter();
generate(fileHeader, true);
closeWriter();
writtenFileHeader = true;
}


if (logger.isDebugEnabled()) {
logger.debug("generateFileHeader() - end"); //$NON-NLS-1$
}
}


public void openWriter() {
if (logger.isDebugEnabled()) {
logger.debug("openWriter() - start"); //$NON-NLS-1$
}


if (writer == null) {
try {
writer = new RandomAccessFile(destinationFile, "rw")
.getChannel();
} catch (FileNotFoundException e) {
logger.error("openWriter()", e); //$NON-NLS-1$


e.printStackTrace();
}
} else {


}


if (logger.isDebugEnabled()) {
logger.debug("openWriter() - end"); //$NON-NLS-1$
}
}


public void closeWriter() {
if (logger.isDebugEnabled()) {
logger.debug("closeWriter() - start"); //$NON-NLS-1$
}


if (this.writer != null) {
try {
this.writer.close();
this.writer = null;
} catch (IOException e) {
logger.error("closeWriter()", e); //$NON-NLS-1$


e.printStackTrace();
}
}


if (logger.isDebugEnabled()) {
logger.debug("closeWriter() - end"); //$NON-NLS-1$
}
}


private void writeFileByLine(FileChannel fileChannel, String line) {
if (logger.isDebugEnabled()) {
logger.debug("writeFileByLine(FileChannel, String) - start"); //$NON-NLS-1$
}


try {
fileChannel.write(ByteBuffer.wrap(line.getBytes()),
fileChannel.size());
} catch (IOException e) {
logger.error("writeFileByLine(FileChannel, String)", e); //$NON-NLS-1$


e.printStackTrace();
}


if (logger.isDebugEnabled()) {
logger.debug("writeFileByLine(FileChannel, String) - end"); //$NON-NLS-1$
}
}

}