I have the following rows in a table:
callNumber userIDNumber dateCheckedOut dateReturned
005 Jaw 104001 2003-09-07 2003-09-07
005 Jaw 104001 2003-09-07 null
006 Lie 104001 2003-09-07 null

I then have the following method:
public void checkOut()
{
 // borrowerID is a JTextField
 String borrower = borrowerID.getText();
 try
 {
 // get connection
 Connection connection = getConnection();
 // create SQL prepared statements
 PreparedStatement getBorrowedBooks = connection.prepareStatement("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = ?");
 // check to see how many books the user has currently checked out
 // where I entered 104001 into the borrowerID JTextField
 getBorrowedBooks.setString(1, borrower);
 // execute SQL query
 ResultSet rs = getBorrowedBooks.executeQuery();
 Vector values = new Vector(2);
 // create a vector to hold the results
 Vector vector = new Vector(10, 10);
 // iterate through records
 while(rs.next())
 {
  // make sure values is empty
  values.clear();
  // iterate through columns
  for(int i = 1; i <= numberOfColumns; i++)
   values.add(rs.getString(i));
  // trim vector to size
  values.trimToSize();
  // add values vector to vector
  vector.add(values);
 }
 // trim vector to size
 vector.trimToSize();
 System.out.println(vector);
 Statement stmt = connection.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = '104001'");
 Vector values = new Vector(2);
 // create a vector to hold the results
 Vector vector = new Vector(10, 10);
 // iterate through records
 while(rs.next())
 {
  // make sure values is empty
  values.clear();
  // iterate through columns
  for(int i = 1; i <= numberOfColumns; i++)
   values.add(rs.getString(i));
  // trim vector to size
  values.trimToSize();
  // add values vector to vector
  vector.add(values);
 }
 // trim vector to size
 vector.trimToSize();
 System.out.println(vector);
}

However, when I print out the results it returns three rows of the same thing, the same thing happens to be the last thing I entered.
It returns: [[006 Lie, null], [006 Lie, null], [006 Lie, null]]

The other odd thing is, if I execute the same thing with a Statement, the Statement returns the correct thing, where as the PreparedStatement does not. I think it must be some kind of cache issue, but why? and how do I go about clearing the cache?

If anyone has any ideas, please help. Thanks.

Otherwise, I guess the only alternative is to use the Statement by doing:
ResultSet rs = stmt.executeQuery("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = " + borrower);

To everyone that read and will read this post. I posted this both here and on java.sun.com's forum, and I got the following reply on java.sun.com's forum, which was the problem and fixed the problem, so I thought I would post the answer here to for those it may help. The following is the reply I received:

"The problem is not in JDBC, it's your handling of Vectors. Your are creating a Vector values, adding the reference to it to Vector vector, changing the content of Vector values and then again adding a reference to it to Vector vector.

Now you have two references to (the same) Vector values in Vector vector.

The solution is creating a new Vector before adding it. In other words, change:
values.clear();
to
values = new Vector();
Hope that helps!"

Damn vectors!

Still, I'm a little confused...

You said that the code for the second select statement was producing the correct output. How can this be if you were using the same code (other than the prepared statement) to process each loop?

I've obviously missed something obvious, so I just thought I'd ask.

Thanks in advance

Martyn

Er, well, I'm sorry, when I was testing the Statement I was outputting the results directly out to the screen instead of putting it into vectors first. So, actually that second while loop shouldn't be in my example. I guess if I had outputted the PreparedStatement to the screen before putting it in the vectors I may have realized that my vectors were the issue. My processing the ResultSet is actually a seperate method in my code but I was just trying to simplify things. I have used the method in other places without any problems, but I guess that was because I never ran across the issue, or at least never tried to do the same type of thing I was trying to do when I ran across the problem. Therefore, I thought that section of code was correct, and did not think there was a problem there, in which case there really was.