Re: convert ResultSet object to CSV file
Arne Vajh=F8j wrote:
itreflects@gmail.com wrote:
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
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!