convert ResultSet object to CSV file

[itreflects]

Hello,
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.

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?

Thanks for any help!

 

[Manish Pandit]

Hi,

http://opencsv.sourceforge.net/

CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
java.sql.ResultSet myResultSet = ....
writer.writeAll(myResultSet, includeHeaders);

-cheers,
Manish

 

[as4109]

(e-mail address removed) 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.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

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.

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?

I can not see why someone else's Java code should
be faster than your code.

I can not see any algorithmic tricks.

The task should be strictly IO bound.

Arne

 

[itreflects]

I can not see why someone else's Java code should
be faster than your code.

I can not see any algorithmic tricks.

The task should be strictly IO bound.

Arne

True, I agree with Arne.
I just had hoped that,if there is an API that can make csv file out of
resultset, it would probably be faster than code I wrote. I have two
versions of program
1) using http://opencsv.sourceforge.net/ API writeAll
2) using sequence of Java code that basically iterates through the
result set in a loop and keeps building the result csv file.

After few tests, I didn't notice much difference in the execution time
of both programs. Here are test run results(from my desktop); sql was
"select a,b from TABLE1" where a & b are varchar(20).

Test OpenCSV API (ms) Traditional code (ms)
1) 100K records 516 593
2) 150K records 984 1000
3) 200K records 1590 1250

By Traditional code I mean code snippet below:

while(myResultSet.next()) {
int ncols = myResultSet.getMetaData().getColumnCount();
FileOutputStream fos=new FileOutputStream(new
File("C:\\tradCsv_200k.csv"),false);
Writer out = new OutputStreamWriter(new
BufferedOutputStream(fos));

for (int i=1; i<(ncols+1); i++) {
out.append(myResultSet.getMetaData().getColumnName(i));
if (i<ncols) out.append(","); else out.append("\r\n");
}
while (myResultSet.next()) {
for (int i=1; i<(ncols+1); i++) {
out.append(myResultSet.getString(i));
if (i<ncols) out.append(","); else out.append("\r\n");
}
}
}

Well, my intention was to process 200K records in < 100 ms. I can't
think of optimizing this code further, so I think faster hardware would
get the file ready in less time. Thanks to everyone who replied.

Good day!