opencsv creating empty csv

Question

I need to convert result set into csv for any database (not just postgres)

Empty csv file is being created when I use opencsv.

Here's the code of doGet method in the servlet:

    final String JDBC_DRIVER = "org.postgresql.Driver";
    final String DB_URL = "jdbc:postgresql://localhost:5432/postgres";

    //  Database credentials
    final String USER = "postgres";
    final String PASS = "12345";

    response.setContentType("text/html");

    PrintWriter out = response.getWriter();

    String title = "Database Result";
    String docType =
            "<!doctype html public \"-//w3c//dtd html 4.0 " +
                    "transitional//en\">\n";
    out.println(docType +
            "<html>\n" +
            "<head><title>" + title + "</title></head>\n" +
            "<body bgcolor=\"#f0f0f0\">\n" +
            "<h1 align=\"center\">" + title + "</h1>\n");

    PreparedStatement ps = null;
    Connection conn = null;

    try {
        // Register JDBC driver
        Class.forName("org.postgresql.Driver");

        // Open a connection
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        // Execute SQL query
        //stmt = conn.createStatement();
        String sql = "SELECT * FROM users";

        ps = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        ResultSet rs = ps.executeQuery();

        /*if(rs.next()){
            System.out.println("Name = "+rs.getString("first_name"));
        }*/ //prints name so rs is not empty

        //rs.first();
        CSVWriter writer = new CSVWriter(new FileWriter("Test.csv"));
        //even tried with seperator '\t' or ','
        writer.writeAll(rs, true);
        writer.close();
        out.println("</body></html>");

        // Clean-up environment
        rs.close();
        ps.close();
        conn.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (ps != null)
                    ps.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        } //end try

Not sure what's the error. Tried different way but csv is always empty.

Even tried writer.flush(), rs.beforeFirst(), rs.first() nothing works.

score 0 · Answer 1 · answered May 05 '17 at 20:33

Is your problem that you do not see data in the html - if that is the case then instead of creating a new FileWriter in the CSVWriter just pass in you out variable.

Or is it that you checked the Test.csv and file and nothing is there? if so then first check to see if there is actually data in the result set by adding the following after executeQuery:

rs.last();
long numberOfRecords = rs.getRow();
rs.beforeFirst();
System.out.println("Number of Users in table is: " + numberOfRecords);

opencsv creating empty csv

1 Answers1