Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Java NetBeans:Operation not allowed after resultset closed

  1. Jul 11, 2016 #1
    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".

    Code (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 (Text):

    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.


    --
     
  2. jcsd
  3. Jul 11, 2016 #2

    Borg

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  4. Jul 11, 2016 #3

    jedishrfu

    Staff: Mentor

    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 whats 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 [Broken]
     
    Last edited by a moderator: May 8, 2017
  5. Jul 14, 2016 #4
    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 dont 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 cant figure it out why its doing this. Somebody please guide me.
    My next button handler code is:
    Code (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 (Text):
    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){
    :
    :
    :
    }
     
  6. Jul 14, 2016 #5

    Borg

    User Avatar
    Science Advisor
    Gold Member

    What is the goal of pressing the Next button?
     
  7. Jul 15, 2016 #6
    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 cant figure it out why its doing so??

    Zulfi.
     
  8. Jul 15, 2016 #7

    Borg

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  9. Jul 16, 2016 #8
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: NetBeans:Operation not allowed after resultset closed
  1. New to NetBeans (Replies: 2)

  2. Java NetBeans Import (Replies: 6)

Loading...