Ahmed Talha Ahmed Talha - 3 months ago 15
SQL Question

java.sql.SQLException: Closed Resultset: next on selecting item from ComboBox

I am trying to create a cheque printing application in which I have four ComboBoxes. All of them are getting data from the oracle database. ComboBox1 retrieve the names of all banks while ComboBox2 retrieve all the accounts of the selected bank. ComboBox3 retrieve all the from_cheque numbers of the range and the last one retrieve all the to_cheque numbers of the range.

Now the problem is when I select the account no. there is an exception raised "java.sql.SQLException: Closed Resultset: next" and it bring only one row in the ComboBox3 while there are more than one row stored in the database and do not bring any result in the last ComboBox. Code is mentioned below.

package frame;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.swing.*;
import java.io.*;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author talha
*/
public class print_cheque extends javax.swing.JFrame {

Connection con = null;
java.sql.Statement st = null;
java.sql.Statement st1 = null;
java.sql.Statement st2 = null;
java.sql.Statement st3 = null;

/**
* Creates new form print_cheque
*/
public print_cheque() {
initComponents();

//Statement st = null;
String query = "Select bnk_nm from d_bank_mst";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@191.1.1.220:1521:abc10g", "payablek", "payablek");
System.out.println("connection successful");

st = con.createStatement();
ResultSet rs = st.executeQuery(query);
int itemCount = jComboBox1.getItemCount();
jComboBox1.addItem("--Select Company--");
for (int i = 0; i < itemCount; i++) {
jComboBox1.removeItemAt(0);
}

while (rs.next()) {
String bank = rs.getString(1);
jComboBox1.addItem(bank);
}

//JOptionPane.showMessageDialog(this, i);
//con.close();
st.close();
rs.close();

} catch (Exception e) {
JOptionPane.showMessageDialog(this, e);
}

}
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
private void initComponents() {

jComboBox1 = new javax.swing.JComboBox<>();
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jComboBox2 = new javax.swing.JComboBox<>();
jLabel3 = new javax.swing.JLabel();
jLabel4 = new javax.swing.JLabel();
jRadioButton1 = new javax.swing.JRadioButton();
jComboBox4 = new javax.swing.JComboBox<>();
jComboBox6 = new javax.swing.JComboBox<>();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

jComboBox1.setToolTipText("");
jComboBox1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jComboBox1ActionPerformed(evt);
}
});

jLabel1.setText("Bank Name");

jLabel2.setText("Account No");

jComboBox2.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
jComboBox2ActionPerformed(evt);
}
});

jLabel3.setText("From Cheque No");

jLabel4.setText("To Cheque No");

jRadioButton1.setText("Cash");
jRadioButton1.addActionListener(new java.awt.event.ActionListener() {


private void jComboBox1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
//JOptionPane.showConfirmDialog(null, "ello"); // TODO add your handling code here:
JComboBox cb = (JComboBox) evt.getSource();
int itemCount = jComboBox2.getItemCount();
String banknm = (String) cb.getSelectedItem();// = "'%Sindh Bank Ltd%'";
JOptionPane.showMessageDialog(null, banknm);
jComboBox2.addItem("--Select Account--");
try {
st1 = con.createStatement();
} catch (Exception ex) {
Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
}

JOptionPane.showMessageDialog(null, 1);
ResultSet rs2 = null;

String query2 = "Select distinct c.ac_no "
+ "from d_chq_mst c, d_bank_mst d "
+ "where d.bnk_nm like '%" + banknm + "%'"
+ "and c.bnk_cd= d.bnk_cd ";//" + banknm + "
try {
JOptionPane.showMessageDialog(null, 2);
//int itemCount = jComboBox2.getItemCount();
for (int i = 0; i < itemCount; i++) {
jComboBox2.removeItemAt(1);
}
rs2 = st1.executeQuery(query2);
JOptionPane.showMessageDialog(null, 3);
while (rs2.next()) {
try {
//JOptionPane.showMessageDialog(null, 4);
jComboBox2.addItem(rs2.getString(1));
} catch (SQLException ex) {
Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
}
}
st1.close();
rs2.close();
} catch (SQLException ex) {
Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
}

}

private void jRadioButton1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}

private void jComboBox2ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
//int t = 0;
JComboBox cb1 = (JComboBox) evt.getSource();
jComboBox4.addItem("--Select From Cheque Number--");
String acnt = (String) cb1.getSelectedItem();
/*if((t == 0)&&!(jComboBox2.equals(null)) ){
cb1.setSelectedItem(null);
}*/

JOptionPane.showMessageDialog(null, acnt);

try {
st2 = con.createStatement();
} catch (Exception ex) {
//Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
ResultSet rs3 = null;
String query3 = "select distinct d.chq_from "
+ "from d_chq_mst d, d_chq_dtl e "
+ " where e.ac_no= '" + acnt + "'"
+ " and d.ac_no = e.ac_no ";
try {
rs3 = st2.executeQuery(query3);
int itemCount2 = jComboBox4.getItemCount();
/*for (int i = 0; i < itemCount2; i++) {
//jComboBox4.removeItemAt(0);
JOptionPane.showMessageDialog(this, i);
}*/
while (rs3.next()) {
try {
JOptionPane.showMessageDialog(null, "item Count**" + itemCount2);
jComboBox4.addItem(rs3.getString(1));
} catch (SQLException ex) {
//Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}

st2.close();
rs3.close();

}
} catch (Exception ex) {
//Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
//t++;

}

private void jComboBox4ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
jComboBox6.addItem("--Select To Cheque Number--");
JComboBox cb2 = (JComboBox) evt.getSource();

String account = (String) cb2.getSelectedItem();

//JOptionPane.showMessageDialog(null, acnt);
try {
st3 = con.createStatement();
} catch (Exception ex) {
//Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
ResultSet rs4 = null;
String query3 = "select distinct d.chq_to "
+ "from d_chq_mst d, d_chq_dtl e "
+ "where d.ac_no= '" + account + "'"
+ "and d.ac_no= '" + account + "'";

//+ "where d.ac_no = '" + acnt + "'";
try {
rs4 = st3.executeQuery(query3);
int itemCount3 = jComboBox6.getItemCount();
/*for (int i = 0; i < itemCount3; i++) {
jComboBox6.removeItemAt(0);
}*/
while (rs4.next()) {
try {
JOptionPane.showMessageDialog(null, rs4.getString(1));
jComboBox6.addItem(rs4.getString(1));
} catch (SQLException ex) {
Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}

st3.close();
rs4.close();

}

} catch (Exception ex) {
//Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
}

private void jComboBox6ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
public static void main(String args[]) {
/* Set the Nimbus look and feel */
//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
/* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
* For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
*/
try {
for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException ex) {
java.util.logging.Logger.getLogger(print_cheque.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(print_cheque.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(print_cheque.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(print_cheque.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
}
//</editor-fold>

/* Create and display the form */
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new print_cheque().setVisible(true);
}
});
}

// Variables declaration - do not modify
private javax.swing.JComboBox<String> jComboBox1;
private javax.swing.JComboBox<String> jComboBox2;
private javax.swing.JComboBox<String> jComboBox4;
private javax.swing.JComboBox<String> jComboBox6;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JRadioButton jRadioButton1;
// End of variables declaration
}


Here is the Stack Trace.


run: connection successful java.sql.SQLException: Closed Resultset:
next at
oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:558)
at
frame.print_cheque.jComboBox2ActionPerformed(print_cheque.java:262)
at frame.print_cheque.access$100(print_cheque.java:23) at
frame.print_cheque$2.actionPerformed(print_cheque.java:103) at
javax.swing.JComboBox.fireActionEvent(JComboBox.java:1260) at
javax.swing.JComboBox.setSelectedItem(JComboBox.java:588) at
javax.swing.JComboBox.setSelectedIndex(JComboBox.java:624) at
javax.swing.plaf.basic.BasicComboBoxUI.selectNextPossibleValue(BasicComboBoxUI.java:1124)
at
javax.swing.plaf.basic.BasicComboBoxUI$Actions.actionPerformed(BasicComboBoxUI.java:1528)
at javax.swing.SwingUtilities.notifyAction(SwingUtilities.java:1662)
at javax.swing.JComponent.processKeyBinding(JComponent.java:2879) at
javax.swing.JComboBox.processKeyBinding(JComboBox.java:1425) at
javax.swing.JComponent.processKeyBindings(JComponent.java:2940) at
javax.swing.JComponent.processKeyEvent(JComponent.java:2842) at
javax.swing.JComboBox.processKeyEvent(JComboBox.java:1417) at
java.awt.Component.processEvent(Component.java:6293) at
java.awt.Container.processEvent(Container.java:2229) at
java.awt.Component.dispatchEventImpl(Component.java:4872) at
java.awt.Container.dispatchEventImpl(Container.java:2287) at
java.awt.Component.dispatchEvent(Component.java:4698) at
java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1887)
at
java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:762)
at
java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:1027)
at
java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:899)
at
java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:727)
at java.awt.Component.dispatchEventImpl(Component.java:4742) at
java.awt.Container.dispatchEventImpl(Container.java:2287) at
java.awt.Window.dispatchEventImpl(Window.java:2719) at
java.awt.Component.dispatchEvent(Component.java:4698) at
java.awt.EventQueue.dispatchEventImpl(EventQueue.java:747) at
java.awt.EventQueue.access$300(EventQueue.java:103) at
java.awt.EventQueue$3.run(EventQueue.java:706) at
java.awt.EventQueue$3.run(EventQueue.java:704) at
java.security.AccessController.doPrivileged(Native Method) at
java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at
java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:720) at
java.awt.EventQueue$4.run(EventQueue.java:718) at
java.security.AccessController.doPrivileged(Native Method) at
java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:717) at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

Answer

Your bug is here:

while (rs3.next()) {
    ...
    st2.close();
    rs3.close();
}

You close your ResultSet in the while loop instead of outside the loop

NB: Your close sequences are incorrect you are supposed to close first the result set, then the Statement and finally the connection if not needed anymore. In other words the close sequence must the inverted create sequence.

Your code should rather be something like that:

try {
    ...
} catch (Exception ex) {
    //Logger.getLogger(print_cheque.class.getName()).log(Level.SEVERE, null, ex);
    ex.printStackTrace();
} finally {
    // Close first the result set
    if (rs4 != null) {
        rs4.close();  
    }
    // The close your statement
    if (st3 != null) {
        st3.close(); 
    } 
}

Assuming that you use Java 7 or higher, you could consider using the try-with-resources statement