convert ResultSet object to CSV file

Discussion in 'Java' started by itreflects@gmail.com, Oct 27, 2006.

  1. Guest

    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!
     
    , Oct 27, 2006
    #1
    1. Advertising

  2. Hi,

    http://opencsv.sourceforge.net/

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

    -cheers,
    Manish
     
    Manish Pandit, Oct 28, 2006
    #2
    1. Advertising

  3. Guest

    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.
     
    , Oct 28, 2006
    #3
  4. 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
     
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=, Oct 28, 2006
    #4
  5. Guest

    Arne Vajhøj wrote:
    > 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!
     
    , Oct 30, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. rs207
    Replies:
    2
    Views:
    1,895
    rs207
    Feb 8, 2005
  2. James Yong
    Replies:
    2
    Views:
    3,612
  3. Replies:
    1
    Views:
    3,321
    Roedy Green
    Feb 9, 2006
  4. Replies:
    2
    Views:
    1,573
    Martin Gregorie
    Nov 30, 2006
  5. Tintin92
    Replies:
    1
    Views:
    1,716
    Andrew Thompson
    Feb 14, 2007
Loading...

Share This Page