To write the value from resultset to text file (.txt file)

Question

Please help me on the below code as i want to write the values from the resultset to a txt file

Code

while (rs.next()){
    FileWriter fstream = new FileWriter(file);
    BufferedWriter out = new BufferedWriter(fstream);

    out.write(Integer.toString(rs.getInt("SBL_PRODUCT_ID")) + ", ");
    out.write(Integer.toString(rs.getInt("SBL_TARIFF_ID")) + ", ");
    out.write(rs.getString("PRODUCT_DESCRIPTION") + ", ");
    out.write(rs.getString("SERVICE_TYPE") + ", ");
    out.write(Integer.toString(rs.getInt("MARKET_CLASS")) + ", ");
    out.write(rs.getString("ENTITY_TYPE") + ", ");
    out.newLine();
    /*out.write(System.getProperty("line.separator"));*/

    System.out.println("Completed writing into text file");
    out.close();
}

Required Output in the txt file

4087, 98, POSTE SIM, Deal [SoHo Flat Tariff Recurring Charge, Prepaid], /service/telco/SIM, 101, DEAL

4087, 99, POSTE SIM, Deal [SoHo Flat Tariff Recurring Charge, Prepaid], /service/telco/SIM, 101, DEAL

4087, 100, POSTE SIM, Deal [SoHo Flat Tariff Recurring Charge, Prepaid], /service/telco/SIM, 101, DEAL

4087, 101, POSTE SIM, Deal [SoHo Flat Tariff Recurring Charge, Prepaid], /service/telco/SIM, 101, DEAL

Current output which i am getting is only one line that too the last value from the resultset ie below

Current output

4087, 101, POSTE SIM, Deal [SoHo Flat Tariff Recurring Charge, Prepaid], /service/telco/SIM, 101, DEAL

Kindly help me on this :(

Open the file once write ALL the content, then close it, once — MadProgrammer, Jul 03 '15 at 05:39
You're opening the file, writing to and closing on EACH iteration of the `ResultSet` (for each row in the `ResultSet`), not only are you not appending to the file, that's a lot of unnecessary overhead — MadProgrammer, Jul 03 '15 at 05:43
Possible duplicate of [Writing a large ResultSet to a File](http://stackoverflow.com/questions/7195048/writing-a-large-resultset-to-a-file) — OhadR, Apr 03 '17 at 08:12

score 1 · Answer 1 · answered Jul 03 '15 at 05:41

Open the file once, write the contents to it and THEN close once ALL the content is written, for example

try (BufferedWriter out = new BufferedWriter(new FileWriter(file))) {
    while (rs.next()) {
        out.write(Integer.toString(rs.getInt("SBL_PRODUCT_ID")) + ", ");
        out.write(Integer.toString(rs.getInt("SBL_TARIFF_ID")) + ", ");
        out.write(rs.getString("PRODUCT_DESCRIPTION") + ", ");
        out.write(rs.getString("SERVICE_TYPE") + ", ");
        out.write(Integer.toString(rs.getInt("MARKET_CLASS")) + ", ");
        out.write(rs.getString("ENTITY_TYPE") + ", ");
        out.newLine();
    }
    System.out.println("Completed writing into text file");
}

Have a look at The try-with-resources Statement for more details

Thank You so very much :) :) – Neethu Shaji Jul 03 '15 at 05:49 — Neethu Shaji, Jul 03 '15 at 05:49

Mike Laren · Accepted Answer · 2015-07-03T05:44:33.040

Every time you create a file writer you're overwriting the file. Change it to:

FileWriter fstream = new FileWriter(file);
BufferedWriter out = new BufferedWriter(fstream);
while (rs.next()) {            
        out.write(Integer.toString(rs.getInt("SBL_PRODUCT_ID")) + ", ");
        out.write(Integer.toString(rs.getInt("SBL_TARIFF_ID")) + ", ");
        out.write(rs.getString("PRODUCT_DESCRIPTION") + ", ");
        out.write(rs.getString("SERVICE_TYPE") + ", ");
        out.write(Integer.toString(rs.getInt("MARKET_CLASS")) + ", ");
        out.write(rs.getString("ENTITY_TYPE") + ", ");
        out.newLine();
        /*out.write(System.getProperty("line.separator"));*/
}
System.out.println("Completed writing into text file");
out.close();

Alternatively, you could had set the append flag in your FileWriter:

FileWriter fstream = new FileWriter(file, true);

although this is not as efficient as opening the file just once.

Precisely. Was about to suggest same. Another better way of coding is the one suggested by @MadProgrammer using Java 7 feature, try-with-resources — JavaHopper, Jul 03 '15 at 05:47

score 0 · Answer 3 · answered Apr 03 '17 at 08:10

From GitHub: https://github.com/OhadR/ohadr.common/blob/master/src/main/java/com/ohadr/common/utils/resultset/ResultSetConverters.java

public static void writeResultSetToWriter(ResultSet resultSet, PrintWriter writer) throws SQLException
{
ResultSetMetaData metadata = resultSet.getMetaData();
int numColumns = metadata.getColumnCount();
int numRows = 0;

while(resultSet.next())             //iterate rows
{
    ++numRows;
    JSONObject obj = new JSONObject();      //extends HashMap
    for (int i = 1; i <= numColumns; ++i)           //iterate columns
    {
        String column_name = metadata.getColumnName(i);
        obj.put(column_name, resultSet.getObject(column_name));
    }
    writer.println(obj.toJSONString());

    if(numRows % 1000 == 0)
        writer.flush();
}

}

To write the value from resultset to text file (.txt file)

3 Answers3

Linked

Related