Results 1 to 2 of 2

Thread: Java and JDBC

  1. #1
    jenski is offline Member
    Join Date
    Oct 2010
    Posts
    1
    Rep Power
    0

    Unhappy Java and JDBC

    hey guys!

    i have this program that will accept input from user via choosing from different forms like jcombobox and jradiobutton.. then after selecting, the selected item wil be added to one table of the database.. there is no syntax error for my entire program but the problem is, there comes a runtime error that states:

    SQLException: Operation not allowed after ResultSet closed

    i am using Eclipse..

    please help me :(


    here is my code for the submit button that outputs the error:




    //submit button
    JButton goAdd = new JButton("Add");

    goAdd.addActionListener(new ActionListener(){
    public void actionPerformed(ActionEvent e){
    try{
    //Register JDBC driver
    Class.forName("com.mysql.jdbc.Driver");

    //Opening a connection to the database
    String dbURL = "jdbc:mysql://localhost:3306/";
    String dbName = "irm";
    String dbUserName = "root";
    String dbPasswd = "";
    Connection connection = DriverManager.getConnection(dbURL+dbName,dbUserNam e,dbPasswd);
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery("select * from booklist");

    ResultSet getBID = statement.executeQuery("select BID from book where BName='"+titleCombo.getSelectedItem()+"'");
    ResultSet getPID = statement.executeQuery("select PID from publisher where PName='"+titleCombo3.getSelectedItem()+"'");
    {
    int BID=0, PID=0, FID=0;
    while(getBID.next()){
    BID = getBID.getInt("BID");
    }
    while(getPID.next()){
    PID = getPID.getInt("PID");
    }

    String category = null;
    if (cat1.isSelected()){
    category = category + "Novel,";}
    if (cat2.isSelected()){
    category= category + "Fiction,";}
    if (cat3.isSelected()){
    category= category + "Philosophy,";}
    if (cat4.isSelected()){
    category= category + "Comic,";}


    if(radio1.isSelected()){
    FID = 1;}
    if(radio2.isSelected()){
    FID = 2;}

    Object author = null;
    if (titleCombo2a.getSelectedItem() == "--------"){
    author= titleCombo2.getSelectedItem();}
    else{
    author= titleCombo2.getSelectedItem() +", "+ titleCombo2a.getSelectedItem();}

    while(rs.next()){
    int count = statement.executeUpdate("INSERT INTO booklist VALUES (null, BID, FID, PID, 'author', 'category')");
    JOptionPane.showMessageDialog(null, "Added to Database!");
    }
    }
    }
    catch(ClassNotFoundException cnfe)
    {
    cnfe.printStackTrace();
    }catch(SQLException evt){
    evt.printStackTrace();
    }
    }
    });

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    Generally its better to use a new statement for a new operation anyway.
    The result set object is kind of associated with the statement object, using the statement to execute a query, gets a result set, and then if you use the same statement to execute a second query, the first result set might be lost, if you have not unpacked it yet..

    Also, its usually easier to work with prepared statements. And to organize read and write operations into separate methods to see where statement objects get used and to separate them, but most importantly, its important to close the connection, statement, and result set objects when you are done with them too.
    Java Code:
    //submit button
    JButton goAdd = new JButton("Add");
    
    goAdd.addActionListener(new ActionListener(){
    public void actionPerformed(ActionEvent e){
    
    //Register JDBC driver
    Class.forName("com.mysql.jdbc.Driver");
    
    //Opening a connection to the database
    String dbURL = "jdbc:mysql://localhost:3306/";
    String dbName = "irm";
    String dbUserName = "root";
    String dbPasswd = "";
    [b]
    Connection connection = null;
    PreparedStatement statement1 = null;
    PreparedStatement statement2 = null;
    PreparedStatement statement3 = null;
    ResultSet rs = null;
    [/b]
    try {
    
      connection = DriverManager.getConnection(dbURL+dbName,dbUserNam e,dbPasswd);
    [b]
      statement1 = connection.prepareStatement("select * from booklist");
      rs = statement1.executeQuery();
      
      statement2 = connection.prepareStatement("select BID from book where BName=?");
      statement2.setString(1, titleCombo.getSelectedItem());
    
      ResultSet getBID = statement2.executeQuery();
    
      statement3 = connection.prepareStatement("select PID from publisher where PName=?");
      statement3.setString(1, titleCombo3.getSelectedItem());
        
      ResultSet getPID = statement3.executeQuery();
    [/b]  
      {
      int BID=0, PID=0, FID=0;
      while(getBID.next()){
      BID = getBID.getInt("BID");
      }
      while(getPID.next()){ 
      PID = getPID.getInt("PID");
      }
    
    String category = null;
    if (cat1.isSelected()){
    category = category + "Novel,";}
    if (cat2.isSelected()){
    category= category + "Fiction,";}
    if (cat3.isSelected()){
    category= category + "Philosophy,";}
    if (cat4.isSelected()){
    category= category + "Comic,";}
    
    
    if(radio1.isSelected()){
    FID = 1;}
    if(radio2.isSelected()){
    FID = 2;}
    
    Object author = null;
    if (titleCombo2a.getSelectedItem() == "--------"){
    author= titleCombo2.getSelectedItem();}
    else{
    author= titleCombo2.getSelectedItem() +", "+ titleCombo2a.getSelectedItem();}
    
    while(rs.next()){
      [b]Statement statement4 = connection.prepareStatement("INSERT INTO booklist VALUES (null, BID, FID, PID, 'author', 'category')");
      int count = statement4.executeUpdate();
      JOptionPane.showMessageDialog(null, "Added to Database!");
      statement4.close();
      [/b]
    }
    }
    }
    catch(ClassNotFoundException cnfe)
    {
    cnfe.printStackTrace();
    }catch(SQLException evt){
    evt.printStackTrace();
    }
    [b]finally {
      if (statement3 != null) {
      try {
      statement3.close();
      } catch (SQLException ex) {}
      }
      if (statement2 != null) {
      try {
        statement2.close();
      } catch (SQLException ex) {}
      }
      if (statement1 != null) {
      try {
        statement1.close();
      } catch (SQLException ex) {}
      }
      if (connection != null) {
      try {
        connection.close();
      } catch (SQLException ex) {}
      }
    }[/b]
    });

Similar Threads

  1. oracle JDBC java
    By silia_motor in forum JDBC
    Replies: 4
    Last Post: 05-10-2009, 09:37 AM
  2. Java JDBC/MySQL appropriate Syntax
    By thelinuxguy in forum Advanced Java
    Replies: 7
    Last Post: 02-10-2009, 07:57 PM
  3. Java JDBC help
    By nick2price in forum New To Java
    Replies: 6
    Last Post: 06-09-2008, 09:15 AM
  4. Replies: 0
    Last Post: 04-01-2008, 10:17 AM
  5. Replies: 0
    Last Post: 09-28-2007, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •