Java How to add new empty column to downloaded CSV

Question

I am using Java OpenCSV code to read data from one of the tables of MYSQL and writing resultset to CSV.

Now my requirement is I want to add new empty column name in the header of that downloaded csv as the 1st column.

Not sure about how and where I can make this change.

Example, of what I want is attached.Design

Code:

public void dataFetch(Connection conn,String query) {  // query is hardcoded as :  select Name,Address from dbo.Employee
    ResultSet rs = null;
    Statement stmt = null;          

    try {       
        stmt = conn.createStatement();      
        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);           
        rs = stmt.executeQuery(query); 

        CSVWriter writer = new CSVWriter(new BufferedWriter(new FileWriter(strSource)));    
        ResultSetHelperService service = new ResultSetHelperService(); 
        service.setDateTimeFormat("yyyy-MM-dd HH:mm:ss.SSS"); 
        System.out.println("**** Started writing Data to CSV **** " +  new Date());         

        writer.setResultService(service);
        int lines = writer.writeAll(rs, true, true, false);  
        writer.flush();
        writer.close();
        System.out.println("** OpenCSV -Completed writing the resultSet at " +  new Date() + " Number of lines written to the file " + lines);  

        rs.close();
        stmt.close();
    } catch(Exception e) {
        e.printStackTrace();
    }
}

Off topic but notice you're initialising your `stmt` variable twice. — Joakim Danielson, Aug 26 '18 at 14:35

Grantly · Accepted Answer · 2018-08-26T14:27:23.860

2

Can you re-construct the query string once you are inside the dataFetch function?

To add a column at the END (Last column) I would replace the phrase ' FROM ' with a new field definition so that your query string looks like this:

select Name,Address,'' AS NewColumnName from dbo.Employee

Replace ' FROM ' with ','' AS NewColumnName FROM ' - is my suggestion. (I used capitals just for emphasis)

To add a column at the start I would replace the phrase 'SELECT ' with a new field definition so that your query string looks like this:

select '' AS NewColumnName,Name,Address from dbo.Employee

Replace ' SELECT ' with 'SELECT '' AS NewColumnName, ' - is my suggestion. (I used capitals just for emphasis)

Then you effectively have added new column to your SQL output, and it will propagate into your CSV file, with a header for all the columns as it currently creates.

edited Aug 26 '18 at 14:27

answered Aug 26 '18 at 14:08

Grantly

2,546
2
21
31

@Grantly Thanks it worked. One question though why do you think this method is not recommended for Production code. When I say I have hardcoded the select query that does not mean it's in java code but in one of the sql table from there value is passed as parameter in java code. – Vikas J Aug 26 '18 at 15:47
I would be wary of this in Production code, as your query string might vary a great deal...It may have multiple SELECT statements, or DECLARE variables - all sorts of things...It depends how you 'control' the query string that enters the function. Of course you can check the query string to some degree before you replace things in it...Count the number of SELECT words (although SELECT can be used in string constants too). In my answer - you should really also ensure that SELECT is the first word in the query. Let me know if this makes it clear, as I could list many more issues with this :) – Grantly Aug 26 '18 at 15:50
This is ONE clear instance that documenting the function is probably very important in a Prod environment, so that other coders KNOW it will modify the query string – Grantly Aug 26 '18 at 15:52
Removed my comments because they are no longer needed; maybe you guys can do the same (although a the warnings above should probably be maintained). This comment will self destruct... – Maarten Bodewes Aug 26 '18 at 16:43

Java How to add new empty column to downloaded CSV

1 Answers1