Write Multiple rows of an oracle db to a text file

Question

Hi guys I am trying to print all the transactions from a sales table (Oracle) into a text file. unfortunately, it is writing only one record while the table has many records that meet the criteria. can someone please tell me what the issue is?

here is my code:

 public void actionPerformed(ActionEvent ae) {
    Object obj = ae.getSource();

    if (obj == btnSave) {
        Connection conn = null;
        String receno = Lrecno.getText();
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "MUNGAI", "gatungo87");
            Statement st = conn.createStatement();
            String recp = "select PART_DESC, QUNTY, UNIT_SALE, TOTAL_SALE from sales where session_id = '" + receno + "'";
            ResultSet rs = st.executeQuery(recp);
            while (rs.next()) {
                File f = new File("Sale Receipts");
                f.mkdir();
                BufferedWriter bw = new BufferedWriter(new FileWriter("Sale Receipts/Sale" + Lrecno.getText() + ".TXT"));
                String receipt = rs.getString("PART_DESC") + "  " + rs.getString("QUNTY") + "  "
                        + rs.getString("UNIT_SALE") + "  " + rs.getString("TOTAL_SALE");
                String[] rece = receipt.split("\n");
                for (int i = 0; i < rece.length; i++) {
                    bw.write(receipt);
                    bw.flush();
                    bw.newLine();

                }
                JOptionPane.showMessageDialog(this, "Item sold successfully",
                        "Success", JOptionPane.PLAIN_MESSAGE);
            }
        } catch (Exception ad) {
            JOptionPane.showMessageDialog(this, ad,
                    "Error", JOptionPane.PLAIN_MESSAGE);
        }
       }

Apart from answer from Vikdor, why do you split the receipt string on "\n" (newline)? and then parts with a newline between? Also, there is seldom any need to do a bw.flush() between each line. Just remember to close the writer when you are done. — Roger Lindsjö
– Roger Lindsjö, Commented Oct 9, 2012 at 6:22

Vikdor · Accepted Answer · 2012-10-09 05:45:21Z

3

You are opening your file for every record in the ResultSet, in the while loop, and hence the file is getting overwritten and only the last record remains when you are done with processing all the rows from the ResultSet.

Move that BufferedWriter outside the while loop and close it along with the result set, statement and connection.

answered Oct 9, 2012 at 5:45

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MaVRoSCy · Accepted Answer · 2012-10-09 06:41:59Z

2

You can try this:

  File f = new File("Sale Receipts");
  f.mkdir();
  BufferedWriter bw = new BufferedWriter(new FileWriter("Sale Receipts/Sale" + Lrecno.getText() + ".TXT"));
  String receipt="";
  while (rs.next()) {
        receipt += rs.getString("PART_DESC") + "  " + rs.getString("QUNTY") + "  "
                   + rs.getString("UNIT_SALE") + "  " + rs.getString("TOTAL_SALE");
        String[] rece = receipt.split("\n");
        for (int i = 0; i < rece.length; i++) {
            bw.write(receipt);
            bw.flush();
            bw.newLine();
        }
        JOptionPane.showMessageDialog(this, "Item sold successfully",
                "Success", JOptionPane.PLAIN_MESSAGE);
    }

Your mistake was that for each row in your table you created and write it to the file, and then you kept on overwriting the same file.

The correct way is to build a String by iterating the ResultSet and then write the String to the file

UPDATE

Consider changing the String with StringBuilder Object for performance issues.

  StringBuilder  receipt = new StringBuilder();
  while (rs.next()) {
        receipt.append( rs.getString("PART_DESC") + "  " + rs.getString("QUNTY") + "  "
                   + rs.getString("UNIT_SALE") + "  " + rs.getString("TOTAL_SALE"));
        receipt.append("\n");
        JOptionPane.showMessageDialog(this, "Item sold successfully",
                "Success", JOptionPane.PLAIN_MESSAGE);
  }
  bw.write(receipt.toString());
  bw.flush();
  bw.newLine();

edited Oct 9, 2012 at 6:41

answered Oct 9, 2012 at 5:49

MaVRoSCy

17.9k15 gold badges87 silver badges129 bronze badges

2 Comments

Roger Lindsjö Over a year ago

You keep concatenating the rows to the receipt string, so if there are many records the string will grow huge. Also, for each record you read you output all records read so far. If you want to concatenate all records to a string you should move your write logic.

Matt Over a year ago

And why not just write each receipt to the writer directly? It's a waste of memory: bw.write( rs.getString... )

Collectives™ on Stack Overflow

Write Multiple rows of an oracle db to a text file

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related