OpenCsv | SQL | Writes only the header and not the table content

Question

I am trying to execute an SQL statement through Java and write the results to a .csv file.

I am using OJDBC.jar (v7) to connect to Oracle 11g DB and OPENCSV.jar (v3.8) for creating and writing into the excel.

Table result is printing very well. I am using pipe to separate all the column values in a record.

However, on the generated csv file, i see only the column names of the table and no data at all! What might be the reason? Please help.

package test;

import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import com.opencsv.CSVWriter;



public class AllInOne
{

static String hostIP="255.255.255.255";
static String PortNum="1521";
static String ServiceName="SNAME";
static String un="USER";
static String pw="PWD";
static int columnCount;
static String row="";


  public static void main(String[] args) throws Exception
  {
    String addr = "jdbc:oracle:thin:@"+hostIP+":"+PortNum+":"+ServiceName;
    Class.forName("oracle.jdbc.OracleDriver");
    Connection con = DriverManager.getConnection(addr,un,pw);
    Statement stat = con.createStatement();
    ResultSet rs = stat.executeQuery("select * from t_employee_info where rownum <6");

    ResultSetMetaData resultSetMetaData = rs.getMetaData();
    columnCount = resultSetMetaData.getColumnCount();

    while (rs.next())
    {

        for (int i = 1; i <= columnCount; i++) 
        {
            row += rs.getString(i) + "|";
        }

        System.out.println(row);
        row = "";
    }

    FileWriter fw=new FileWriter("C:/Users/myName/Desktop/Folder/CSVfile.csv");
    CSVWriter writer = new CSVWriter(fw);
    writer.writeAll(rs,true);
    writer.close();  
    fw.close();
    stat.close();
    con.close();
    System.out.println("File Generated");

  }

}

score 2 · Accepted Answer · answered Aug 27 '16 at 12:11

Shreyas , you have already iterated over the result set once. So the choices you have are to store the values as you iterate over the RS to a variable (say a list of | delimited values) and pass them to a method on CSV Writer.

A second option will be to actually use rs.first() (provided you have a scrollable result set) and then pass it to the method. This is however not a great practice

score 0 · Answer 2 · answered Aug 28 '16 at 18:44

Shreyas, Ramachandran has the correct answer. Think of the ResultSet as an iterator over the results of the query. By looping over the ResultSet to log the output of the query you are passing opencsv an empty result set. That is why you are only seeing the header.

My experience with JDBC is relatively limited so I cannot tell you why rs.first() or rs.beforeFirst() is not a good practice but if that does not work and you have to both log the result set AND create a csv file then either do the first/beforeFirst or run the query a second time. Personally I would consider removing the logging and create your own custom class that inherits the Writer interface that you pass in the FileWriter and use that to log what you are writing to the file by passing that into the CSVWriter.

OpenCsv | SQL | Writes only the header and not the table content

2 Answers2

Linked