Why do I keep on getting an exception-illegal operation on ResultSet?
Here is the code:
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
/*
* SearchParts.java
*
* Created on 08-Mar-2010, 12:14:31
*/
package garits;
import java.sql.*;
import javax.swing.*;
/**
*
* @author Deniz
*/
public class SearchParts extends javax.swing.JFrame {
/** Creates new form SearchParts */
public SearchParts() {
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")
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
if (!jTextField1.getText().equals("")) {
String result = "";
int Partnumber = Integer.parseInt(jTextField1.getText());
DB db = new DB();
try {
db.connect();
String query = "Select * from Stock Where Part_no =" + "'" + jTextField1.getText() + "'";
ResultSet rs = db.execSQL(query);
if (rs.equals(null)) {
PartNotFound nf = new PartNotFound();
nf.setVisible(true);
} else {
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int RowCount = 0;
for (int i = 1; i < numberOfColumns; i++) {
rs.getString(i);
result += i + "/n";
}
if (!result.equals("")) {
Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
part.setVisible(true);
while (rs.next()) {
RowCount++;
}
part.getTable().addRowSelectionInterval(0, RowCount);
} else {
PartNotFound nf = new PartNotFound();
}
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(jButton1, "More information needed for search", "Error Message", JOptionPane.ERROR_MESSAGE);
}
} else if (!jTextField2.getText().equals("")) {
String result = "";
DB db = new DB();
try {
db.connect();
String query = "Select * from Stock Where Part_name =" + "'" + jTextField2.getText() + "'";
ResultSet rs = db.execSQL(query);
if (rs.equals(null)) {
PartNotFound nf = new PartNotFound();
nf.setVisible(true);
} else {
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int RowCount = 0;
for (int i = 1; i < numberOfColumns; i++) {
rs.getString(i);
result += i + "/n";
}
// Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
// part.setVisible(true);
if (!result.equals("")) {
Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
part.setVisible(true);
while (rs.next()) {
RowCount++;
}
part.getTable().addRowSelectionInterval(0, RowCount);
} else {
PartNotFound nf = new PartNotFound();
nf.setVisible(true);
}
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(jButton1, "More information needed for search", "Error Message", JOptionPane.ERROR_MESSAGE);
}
} else if (jTextField1.getText().equals("") && jTextField2.getText().equals("")) {
String result = "";
DB db = new DB();
try {
db.connect();
String query = "Select * from Stock Where Manufacturer =" + "'" + jTextField3.getText() + "'AND Vehicle_type ='" + jTextField4.getText() + "'";
ResultSet rs = db.execSQL(query);
if (rs.equals(null)) {
PartNotFound nf = new PartNotFound();
nf.setVisible(true);
}
else{
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int RowCount = 0;
for (int i = 1; i < numberOfColumns; i++) {
rs.getString(i);
result += i + "/n";
}
// Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
// part.setVisible(true);
if (!result.equals("")) {
Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
part.setVisible(true);
while (rs.next()) {
RowCount++;
}
part.getTable().addRowSelectionInterval(0, RowCount);
} else {
PartNotFound nf = new PartNotFound();
nf.setVisible(true);
}
}
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(jButton1, "More information needed for search", "Error Message", JOptionPane.ERROR_MESSAGE);
}
} else if (jText开发者_JS百科Field3.getText().equals("") || jTextField4.getText().equals("")) {
JOptionPane.showMessageDialog(jButton1, "More information needed for search", "Error Message", JOptionPane.ERROR_MESSAGE);
}
}
/**
* @param args the command line arguments
*/
// Variables declaration - do not modify
private javax.swing.JButton jButton1;
private javax.swing.JButton jButton2;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JLabel jLabel6;
private javax.swing.JLabel jLabel7;
private javax.swing.JLabel jLabel8;
private javax.swing.JTextField jTextField1;
private javax.swing.JTextField jTextField2;
private javax.swing.JTextField jTextField3;
private javax.swing.JTextField jTextField4;
// End of variables declaration
}
I see a few things
First rs is never null. If there are no rows then rs.next() will return false
ResultSet starts position BEFORE the first row. You need to call rs.next() to move to the first row which, as above, will return false if there are no rows.
The typical pattern for ResultSet use is...
while (rs.next())
{
rs.getXXX();
}
ResultSet should ALWAYS be close()d when you are done, otherwise you may leak database resources. This also applies to Connection, Statement, PreparedStatement so check your DB code in other classes.
When looping over the column metadata you need to do..
for (int i = 1; i <= numberOfColumns; i++) { }
Note the <= rather than <. As you have correctly found that JDBC column indexes start at 1.
- It would be worth considering using a library that hides all the JDBC noise. Consider spring which has a basic JDBC abstraction while retaining all the power or all the way up to hibernate which is very powerful but has quite a learning curve. In your case I'd recommend just use the spring stuff.
See: Spring docs
You always need to call next() on the ResultSet, before accessing it. If not, the pointer points to the row before the first. You do this with while-loops in your code, but some places you dont, which generates an error:
for (int i = 1; i < numberOfColumns; i++) {
rs.getString(i);
result += i + "/n";
}
Ohh, and as another poster mentions, you dont assign the result to a variable, so the rs.getString(i) call has no effect. But this is probably your source of error.
if rs
is null, then rs.equals(null)
will throw a NullPointerException
instead of returning false
. Nevertheless if that is not the exception you are getting then the problem lies somewhere else.
Also, take a look into the javadoc/code for the DB class' execSQL(query) method. Typically, a ResultSet is never null, e.g. like when you use PreparedStatement.executeQuery. However, check into the that execSQL method to verify whether or not it will ever return a null ResultSet.
Remove the following line, it serves no purpose. Its trying to the the column value from the ResultSet but you haven't scrolled to the first row yet
rs.getString(i);
Several have pointed out some of your problems in the code. The reason for your exception is because you have not advanced to the first row using rs.next()
. If you expect that your SQL statement will only retrieve a single row then you probably should change your rs.equals(null)
statement to:
if (!rs.next()) {
...code here to set not found...
} else {
...code here to retrieve the columns...
}
Here are some other tips. In the code to retrieve the columns, your use of rs.getString(i)
doesn't do anything, the result
variable is just accumulating the column numbers with "/n" (not new-line; probably that should be "\n"). So the loop inside that section should probably become:
for (int i = 1; i <= numberOfColumns; i++) {
result += rs.getString(i) + "\n";
}
But concatenating to a immutable String
is not good and can result in slow execution for large numbers of concatenations. Use a StringBuilder
instead and initialize it to a reasonable size, something like this:
StringBuilder sb = new StringBuilder(256);
for (int i = 1; i <= numberOfColumns; i++) {
sb.append(rs.getString(i)).append("\n");
}
result = sb.toString();
Later you loop through the results set, so in the loop through the columns are you trying to get column names from the meta data? In that case:
StringBuilder sb = new StringBuilder(256);
for (int i = 1; i <= numberOfColumns; i++) {
sb.append(rsmd.getColumnName(i)).append("\n");
}
result = sb.toString();
But since you need to check for the first row the next loop will not count the number of rows correctly so you will need to adjust for that, perhaps (and there is no need to check result
at that point since it is guaranteed to no longer be an empty string since there must be at least one column in the table):
do {
RowCount++; // recommend using rowCount.
} while (rs.next());
In the end, what are you using result
for? It seems that this is just used to determine if there were some columns in the result. If that is all then you can eliminate most of this code. For what you actually have (I don't know if this is your intent), this could be reduced to (in the else if where you do the DB interaction):
DB db = new DB();
try {
db.connect();
String query = "select count(1) from Stock where Part_name =" + ...;
ResultSet rs = db.execSQL(query);
if (!rs.next()) {
PartNotFound nf = ...;
nf.setVisible(true);
} else {
Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
part.setVisible(true);
rowCount = rs.getInt(1);
part.getTable().addRowSelectionInterval(0, rowCount);
}
} catch (Exception e) {
...error handling with stack trace/JOptionPane...
}
Note that if you are just after the count of rows then it is best to let the database engine do that for you - looping through the result rows just to count them also means that all that data must be sent to your application. Instead, if you just use the aggregate function then the database only needs to send a single row and column to your application.
精彩评论