JTable shows duplicate values while trying to populate it

Question

I am implementing the search book frame in my library management system project. In this frame, I want to check the availability of the book. I have two database tables:

1 Book:-which keeps the records of all the books in the library 2 Issuebook;-which keeps the record of issued book

When I am running the frame, the JTable gets populated with the same value thrice. The output is attached after the code. I am unable to find the problem. Here's my code:

public void actionPerformed(ActionEvent e) {
        String bookname=tfBookName.getText();
        int count=0;
        try{
            con=DemoConnection.getConnection();
            ps=con.prepareStatement("select book.bookid from book,issuebook where book.bookid!=issuebook.bookid and bookname=?");
            ps.setString(1, bookname);
            rs=ps.executeQuery();
            while(rs.next())
            {   
                count++;
                String bookid =rs.getString(1);
                String availability="Available";

                Object[] row = { bookid, bookname, availability};

                DefaultTableModel model = (DefaultTableModel)table.getModel();

                model.insertRow(0,row);

            }
            ps1=con.prepareStatement("select book.bookid from book,issuebook where book.bookid=issuebook.bookid and bookname=?");
            ps1.setString(1, bookname);
            rs1=ps1.executeQuery();
            while(rs1.next())
            {   
                String bookid =rs1.getString(1);
                String availability="Issued";

                Object[] row = { bookid, bookname, availability};

                DefaultTableModel model = (DefaultTableModel)table.getModel();

                model.insertRow(0,row);

            }
            if(rs==null&&rs1==null)
            {
                JOptionPane.showMessageDialog(frame, "Book named"+bookname+"does not exist!!");
            }
            else if(rs==null&&rs!=null)
            {
                JOptionPane.showMessageDialog(frame, "All the copies of"+bookname+" book are issued!!");    
            }
            else if(rs!=null&&rs==null)
            {
                JOptionPane.showMessageDialog(frame, "All the copies of"+bookname+" book are avaialble!!"); 
            }
            else if(rs!=null&&rs!=null)
            {
                JOptionPane.showMessageDialog(frame, count+" copies of"+bookname+" book is avaliable!!");   
            }   
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
    }
});

Here's output. When the book which is issued, it is shown in the table; no duplicate entries were filled up, but duplicate entries get filled in table for the available books.

Book table

Issuebook table

could you upload the structure of the tables that are used here with some sample data — Blip, Oct 20 '16 at 08:31
I have attached the images of my database tables in post edit @Blip — SanjotKaur, Oct 20 '16 at 11:20
Post executable code, for [example](http://stackoverflow.com/a/34742409/230513), not pictures of listings. — trashgod, Oct 20 '16 at 12:13

score 0 · Answer 1 · answered Oct 20 '16 at 12:47

As @Blip stood out, the problem lies in your SQL query, especially the first one

select book.bookid from book,issuebook where book.bookid!=issuebook.bookid and bookname=?

In this query, you get the book.bookid each time it is different from an issuebook.bookid. So as you have 3 records in your issuebook table, you get 3 results when a book is not present in this table. Directly trying this query on your console should point this out.

The solution can be to modify your query as

select book.bookid, IF(issuebook.bookid IS NULL, 'Available', 'Issued') as availability from book,issuebook where LEFT JOIN availability ON (book.bookid, issuebook.bookid) and bookname=?

I'm not very used to MySQL and the "LEFT JOIN" notation, but the idea is to automatically get the availability status depending on weither or not the bookid is present in the issuebook table or not. So maybe there is some syntax flaws in my sample query...

Anyway like this you just have to do ONE SQL query (get rid of this ps1, res1...) and get the availability with

String availability=rs.getString(2);

If you are not familiar with SQL JOIN, I advise to document yourself on the subject :).

Hope this will help ;)

JTable shows duplicate values while trying to populate it

1 Answers1