Re: convert ResultSet object to CSV file
itreflects@gmail.com ha escrito:
Is there an API that can convert object implementation of
java.sql.ResultSet to a CSV file in Java?
i.e. when I execute
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE1");
can I say something like: store this rs object in "C:\result.csv" file.
public static void storeAsCSV(ResultSet rs,File f)
throws IOException, SQLException {
int ncols = rs.getMetaData().getColumnCount();
FileOutputStream fos;
Writer out =
new OutputStreamWriter(
new BufferedOutputStream(
fos=new FileOutputStream(f,false)));
for (int i=1; i<(ncols+1); i++) {
out.append(CSVQuote(rs.getMetaData().getColumnName(i)));
if (i<ncols) out.append(","); else out.append("\r\n");
}
while (rs.next()) {
for (int i=1; i<(ncols+1); i++) {
out.append(CSVQuote(rs.getString(i)));
if (i<ncols) out.append(","); else out.append("\r\n");
}
}
out.flush();
fos.close();
}
CSVQuote is left as an exercise for the reader, and depends on the
exact syntax expected by the target application; but it only needs to
examine each character of each string once.
I know I can write sequence of Java code that can achieve this, but it
becomes performance bottleneck when result set contains more than 1
million records.Is there an alternate way?
Do you mean that you think Java will inherently cause a
performance-bottleneck, or that your code seems to run slow? I'm
working on a way to invoke the perl DBI drivers and other modules from
Java; but I doubt it'll be faster than either pure Java code or pure
perl code.