Java and JDBC

[jenski]

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();
}
}
});

[travishein]

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.

//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]
});