Java NetBeans:Operation not allowed after resultset closed

  • Thread starter Thread starter zak100
  • Start date Start date
  • Tags Tags
    Closed
Click For Summary
The discussion revolves around accessing a database using NetBeans and handling a ResultSet in a Java Swing application. The user initially encounters an error when trying to traverse the ResultSet after it has been closed. The solution involves restructuring the code to avoid closing the ResultSet prematurely and instead storing the results in an ArrayList for easier navigation. The user implements a method to process the SQL query and populate the ArrayList, which is called when the "Next" button is pressed. However, they face an issue where pressing "Next" after the last record causes the display to loop back to the first record. The solution proposed involves maintaining a counter to track the current position in the ArrayList and ensuring that it does not exceed the bounds of the list.Ultimately, the user resolves the issue by calling the database method from the constructor, allowing the application to load all records at once and navigate through them without re-querying the database. This approach simplifies the logic and enhances performance by reducing database calls.
zak100
Messages
462
Reaction score
11
Hi,

I am trying to access a database using netBeans.I am using the 'select' query. I have got only one record. I am displaying its only two fields in the jTextField. I have a button (next) component. I have written its handler which is used to traverse the resultset. In the constructor i am connecting with the DB. When i am pressing the 'next' button first time, its displaying the two fields of db in the jTextfield. But when i am pressing the 'next' button again, i am getting the error, "Operation not allowed after ResultSet Closed".

Java: 
package jdb1;
import java.sql.*;
/**
*
* @author HP
*/
public class Javadb1 extends javax.swing.JFrame {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost/test";
   //  Database credentials
private static final String USER = "root";
private static final String PASS = "z";
private Connection conn = null;
private Statement stmt = null;
String sql="";
ResultSet rs= null;
    /**
     * Creates new form Javadb1
     */   public Javadb1() {
     
      try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);
      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      sql = "SELECT name, owner FROM pet";
      rs = stmt.executeQuery(sql);
      }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }
   System.out.println("Goodbye!");
   
      initComponents();
    }    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                       
    private void initComponents() {
        jPanel1 = new javax.swing.JPanel();
        jNext = new javax.swing.JButton();
        jName = new javax.swing.JTextField();
        jOwner = new javax.swing.JTextField();
        jSex = new javax.swing.JTextField();
        jBirth = new javax.swing.JTextField();
        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        jNext.setText("Next");
        jNext.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jNextActionPerformed(evt);
            }
        });
        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
                .addContainerGap(155, Short.MAX_VALUE)
                .addComponent(jNext)
                .addContainerGap())
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(28, 28, 28)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jName)
                    .addComponent(jOwner)
                    .addComponent(jSex)
                    .addComponent(jBirth, javax.swing.GroupLayout.DEFAULT_SIZE, 94, Short.MAX_VALUE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(21, 21, 21)
                .addComponent(jNext)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jName, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jOwner, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jSex, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jBirth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(40, Short.MAX_VALUE))
        );
        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(67, 67, 67)
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(113, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(48, 48, 48)
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(49, Short.MAX_VALUE))
        );
        pack();
    }// </editor-fold>   
                 
    private void jNextActionPerformed(java.awt.event.ActionEvent evt) {                                   
        // TODO add your handling code here:
        try{
        if(rs.next()){
             String name = rs.getString("name");
             String owner = rs.getString("owner");
             jName.setText(name);
             jOwner.setText(owner);
          
        }
        else {
            rs.close();
            stmt.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(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
      try{
         if(rs!=null)
            rs.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end fina*/
   }
//end try
    }                                  
    /**
     * @param args the command line arguments
     */
    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(Javadb1.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Javadb1.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Javadb1.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Javadb1.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 Javadb1().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify                  
    private javax.swing.JTextField jBirth;
    private javax.swing.JTextField jName;
    private javax.swing.JButton jNext;
    private javax.swing.JTextField jOwner;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JTextField jSex;
    // End of variables declaration                
}

I am getting following errors:
Code: 
java.sql.SQLException: Operation not allowed after ResultSet closed

  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)

  at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)

  at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)

  at jdb1.Javadb1.jNextActionPerformed(Javadb1.java:149)

  at jdb1.Javadb1.access$000(Javadb1.java:12)

  at jdb1.Javadb1$1.actionPerformed(Javadb1.java:88)

  at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)

  at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)

  at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)

  at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)

  at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)

  at java.awt.Component.processMouseEvent(Component.java:6535)

  at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)

  at java.awt.Component.processEvent(Component.java:6300)

  at java.awt.Container.processEvent(Container.java:2236)

  at java.awt.Component.dispatchEventImpl(Component.java:4891)

  at java.awt.Container.dispatchEventImpl(Container.java:2294)

  at java.awt.Component.dispatchEvent(Component.java:4713)

  at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)

  at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)

  at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)

  at java.awt.Container.dispatchEventImpl(Container.java:2280)

  at java.awt.Window.dispatchEventImpl(Window.java:2750)

  at java.awt.Component.dispatchEvent(Component.java:4713)

  at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)

  at java.awt.EventQueue.access$500(EventQueue.java:97)

  at java.awt.EventQueue$3.run(EventQueue.java:709)

  at java.awt.EventQueue$3.run(EventQueue.java:703)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)

  at java.awt.EventQueue$4.run(EventQueue.java:731)

  at java.awt.EventQueue$4.run(EventQueue.java:729)

  at java.security.AccessController.doPrivileged(Native Method)

  at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)

  at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)

  at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)

  at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)

  at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)

  at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)

  at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)

  at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Some body please guide me. I am trying to view the contents of Table by clicking a button next.Zulfi.--
 
Technology news on Phys.org
You're opening and closing it in the constructor. Put it in a method and call it from where you need it.

Also, your jNextActionPerformed() method is trying to access the ResultSet rs that is only accessible within the constructor.
 
I would not use a SQL query this way. For one thing you have two threads running the main thread where you ran the query and the display thread that is handling the actionPerformed.

The better approach is the read in your resultset data into an array and then close the connection in the constructor. Now you can step through the array in your actionPerformed() method. It would eliminate all that extra logic you have to close the connection and place it in one place.

Also I would suggest getting familiar with log4j logging as it can tell you what's going on, which thread you're and timings as the program runs.

https://en.wikipedia.org/wiki/Log4j

Also here's an example from Oracle on using databases with java swing:

https://docs.oracle.com/javase/tutorial/jdbc/basics/jdbcswing.html

and this academic one:

http://www.cs.ubc.ca/~ramesh/cpsc304/tutorial/JDBC/Swing/jdbc_swing.html
 
Last edited by a moderator:
  • Like
Likes zak100
Hi,
Based upon your advise, i am able to do that. I am using an arraylist. But i have done one mistake. I have put the database method which executes the select query & stores results it into the arraylist inside the 'next' button handler. This is causing the db method to run each time i press the 'next' button. I think this is not correct, But then i don't know where should i put this method i.e from should i call gthe method processQuery(...). Right now its showing all the records but after the last record, it automatically comes back to the first. I can't figure it out why its doing this. Somebody please guide me.
My next button handler code is:
Java: 
private void jNextActionPerformed(java.awt.event.ActionEvent evt) {                                   
       processSelectQuery();
       String strData="";
       String[] fieldValue;
    
       //Take itemIndex as a variable & increment each time when this method is called
       //for(int i=0;i<items.size();++i)
           if (recordCnter < items.size( )){
                strData=items.get(recordCnter);
                fieldValue=strData.split("\\s+");
                jName.setText(fieldValue[0]);
                jOwner.setText(fieldValue[1]);
                jSex.setText(fieldValue[2]);
                jBirth.setText(fieldValue[3]);
                recordCnter++;
    }
        // TODO add your handling code he

and my db method (i.e. processSelectQuery() ) part which is storing resultSet into arrayList is:
Code: 
String sql;
      sql = "SELECT * FROM pet";
       rs = stmt.executeQuery(sql);
      String name="";
      String owner="";
      String sex = "";
      String dateV="";
      String record="";
      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
      
          name = rs.getString("name");
          owner = rs.getString("owner");
          sex   = " " + rs.getString("sex").charAt(0);
          dateV = " " + rs.getDate("birth");
          record = name +" " + owner + " " + sex + " " + dateV;
          items.add(record);
         //Display values
         System.out.print("name: " + name);
         System.out.print(", owner: " + owner);
      
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
:
:
:
}
 
What is the goal of pressing the Next button?
 
Hi,
It should show the next record from db & if there are no more records & we still press 'next' it should not do anything & keep showing the last record. However in my case it shows me the next record. But when it reaches the last record & i press next at this poiont, it comes back to the first. I can't figure it out why its doing so??

Zulfi.
 
There are several different ways that you could handle this. If the number of records in the database isn't that large, you could put all of them into a list of PET records the first time that the Next button is pressed. Then maintain a counter outside of the jNextActionPerformed() method that is incremented at the end of the method if it isn't larger than the size of list-1. Then just grab the petList.get(i) record when Next is pressed.

If the database is large, you will have to create a separate query that determines the total number of records, run that at least once and retrieve the ith record from the database making sure not to retrieve higher than total-1. Since this is probably a homework problem, you should be able to use the first scenario above.
 
Borg said:
There are several different ways that you could handle this. If the number of records in the database isn't that large, you could put all of them into a list of PET records the first time that the Next button is pressed. Then maintain a counter outside of the jNextActionPerformed() method that is incremented at the end of the method if it isn't larger than the size of list-1. Then just grab the petList.get(i) record when Next is pressed.

If the database is large, you will have to create a separate query that determines the total number of records, run that at least once and retrieve the ith record from the database making sure not to retrieve higher than total-1. Since this is probably a homework problem, you should be able to use the first scenario above.
Hi,
I solved the prob (same as you stated in the part-1 ) & i mentioned about it in reply#4. Your reply#7 is not related to my prob mentioned in reply# 6. Anyway thanks for your attention., I solved this problem by calling my db method from constructor.
Problem Solved.
Zulfi.
 

Similar threads

Java NetBeans:Sending multiple Messages: Erroneous sym type: Soc
  • · Replies 6 ·
Replies
6
Views
4K
Java Serialization/Deserialization of objects in Java
  • · Replies 4 ·
Replies
4
Views
2K
Comp Sci How to Build a Java GUI for Recording City Populations to a Text File?
  • · Replies 27 ·
Replies
27
Views
24K
Comp Sci How to Create a Functional Java GUI Mortgage Calculator?
  • · Replies 9 ·
Replies
9
Views
11K
Comp Sci Java Program Input from file, output to file(s)
  • · Replies 31 ·
2
Replies
31
Views
12K