Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > convert ResultSet object to CSV file

Reply
Thread Tools

convert ResultSet object to CSV file

 
 
itreflects@gmail.com
Guest
Posts: n/a
 
      10-27-2006
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!

 
Reply With Quote
 
 
 
 
Manish Pandit
Guest
Posts: n/a
 
      10-28-2006
Hi,

http://opencsv.sourceforge.net/

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

-cheers,
Manish

 
Reply With Quote
 
 
 
 
as4109@wayne.edu
Guest
Posts: n/a
 
      10-28-2006
http://www.velocityreviews.com/forums/(E-Mail 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.

 
Reply With Quote
 
=?ISO-8859-1?Q?Arne_Vajh=F8j?=
Guest
Posts: n/a
 
      10-28-2006
(E-Mail Removed) 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
 
Reply With Quote
 
itreflects@gmail.com
Guest
Posts: n/a
 
      10-30-2006
Arne Vajh°j wrote:
> (E-Mail Removed) 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!

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to move data from a CSV file to a JTable, and from a JTable to a CSV file ? Tintin92 Java 1 02-14-2007 06:51 PM
Create CSV file from resultset in Java simon_s_li@hotmail.com Java 2 11-30-2006 09:17 PM
convert xml to resultset nileshdahiwalkar@gmail.com Java 1 02-09-2006 12:19 PM
Utilities to convert resultset to bean James Yong Java 2 08-02-2005 01:02 AM
What is the best way to convert Oracle903 ResultSet to XML ? rs207 Java 2 02-08-2005 05:02 PM



Advertisments