Display duplicate entries in SQL table using Java

Question

I am currently trying to access the primary key by searching for a duplicate field in a database in java using sql statements. My database contains a primary key for the book ids and contains multiple copies of the same book. Those books have the same ISBN but different book ids. Is it possible to extract those unique ids using an SQL select statement? Whenever I run the following select statement, I can only obtain data on one of the copies:

String queryString =   
    "select bid, title, author, checked from book where isbn = " + ID;

ResultSet resultSet = stmt.executeQuery(queryString);

if (resultSet.next()){
  bid = resultSet.getString(1);
  title = resultSet.getString(2);
  author = resultSet.getString(3);
  checked = resultSet.getString(4);
}
resultSet.close();

If I run the same statement in my SQL workbench, all of the data is extracted. How can I extract the unique keys in java?

score 0 · Answer 1 · answered May 10 '16 at 12:00

0

rset2.next() moves the cursor to the next row. Since you don't seem to have any loop iterating through the resultset, it isn't going beyond the first row.

answered May 10 '16 at 12:00

Yash Capoor

346
4
14

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Change the if(resultSet.next()) for while(resultSet.next()) :

String queryString =   
    "select bid, title, author, checked from book where isbn = " + ID;

ResultSet resultSet = stmt.executeQuery(queryString);

// "while" instead of "if"
while (resultSet.next()){ 
  bid = resultSet.getString(1);
  // etc

  // do something
}
resultSet.close();

"if" only get the first element, "while" get all the element

See the answer of this question: Similar Question

edited May 23 '17 at 12:23

Community

1
1

answered May 10 '16 at 12:00

Troncador

3,356
3
23
40

I am using the while statement and I receive the following error:java.sql.SQLException: Column Index out of range, 3 > 2. – Denver0001 May 11 '16 at 00:53
That is a problem with the columns. You use resultSet.getString with a higher number than the actual number of columns – Troncador May 11 '16 at 01:19
I am able to extract the second row, but the while statement skips the first row – Denver0001 May 11 '16 at 01:31
skip the first row or column? – Troncador May 11 '16 at 01:40
the first row is skipped and the data from the second row is assigned to my variables. I am only able to retrieve data from the first row by using the if statement, then the while statement. – Denver0001 May 11 '16 at 01:49
you are re-writing the values: while(..) { foo=resultSet.getString(1);} at the end of the while you only have the last value of foo. You have to store in a list or in array all the value if you want to keep them – Troncador May 11 '16 at 01:56
while (rset2.next()){ for (int i = 0; i – Denver0001 May 11 '16 at 02:15
That is the code I am using. The last value is assigned both times even though I am using an array. – Denver0001 May 11 '16 at 02:15
you have a problem in your code, you need something like : i=0; while (rset2.next()){ book[i] = rset2.getString(1); checked[i] = rset2.getString(2); i++;} – Troncador May 11 '16 at 02:18
If my answer solved your problem, please check my answer – Troncador May 11 '16 at 03:51

score 0 · Answer 3 · answered May 10 '16 at 12:02

0

You have used if condition that allows you to get only the first record fetched from query. Rather use :

while(rs.next()) {
// TO DO
}

answered May 10 '16 at 12:02

Neha

143
4
19

Display duplicate entries in SQL table using Java

3 Answers3