-1

I'm editing a library management system in JAVA and I'm having problems with the search functions. Each book has a BookID, Subject, Title, Author, etc and each member has a MemberID, Name, etc.

If I select the search to be by Subject, or Title or Author, it returns the correct information from the database and displays it. But when I try to search by BookID, i get an error.

 

This is the code for the form and validation logic before being passed to ListSearchBooks.java and ListSearchMembers.java, which does the query from the db and displays it.

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class SearchBooksAndMembers extends JInternalFrame {
/***************************************************************************
 ***      declaration of the private variables used in the program       ***
 ***************************************************************************/

//for creating the North Panel
private JPanel northPanel = new JPanel();
//for creating the label
private JLabel title = new JLabel("Search for Books and Members");

//for creating the center
private JPanel center = new JPanel();

//for creating the Center Panel
private JPanel centerBooksPanel = new JPanel();
//for creating an Internal Panel in the center panel
private JPanel searchBooksPanel = new JPanel();
//for creating an Internal Panel in the center panel
private JPanel searchBooksButtonPanel = new JPanel();

//for creating the table
private JLabel searchBooksLabel = new JLabel(" Search by: ");
//for creating JComboBox
private JComboBox searchBooksTypes;
//for creating String[]
private String[] booksTypes = {"BookID", "Subject", "Title", "Author", "Publisher", "ISBN"};
//for creating the label
private JLabel booksKey = new JLabel(" Write the Keyword: ");
//for cearting the text field
private JTextField booksKeyTextField = new JTextField();
//for creating the button
private JButton searchBooksButton = new JButton("Search");

//for creating the Center Panel
private JPanel centerMembersPanel = new JPanel();
//for creating an Internal Panel in the center panel
private JPanel searchMembersPanel = new JPanel();
//for creating an Internal Panel in the center panel
private JPanel searchMembersButtonPanel = new JPanel();

//for creating the table
private JLabel searchMembersLabel = new JLabel(" Search by: ");
//for creating JComboBox
private JComboBox searchMembersTypes;
//for creating String[]
private String[] membersTypes = {"MemberID", "Name", "E-Mail", "Major"};
//for creating the label
private JLabel membersKey = new JLabel(" Write the Keyword: ");
//for cearting the text field
private JTextField membersKeyTextField = new JTextField();
//for creating the button
private JButton searchMembersButton = new JButton("Search");

//for creating the south panel
private JPanel southPanel = new JPanel();
//for creating the button
private JButton cancelButton = new JButton("Cancel");

//for creating an array of string to store the data
private String[] booksData;
private String[] membersData;
//create objects from another classes for using them in the ActionListener
private ListSearchBooks listBooks;
private ListSearchMembers listMembers;
private Books book;
private Members member;

//for checking the information from the text field
public boolean isBooksDataCorrect() {
    booksData = new String[2];
    booksData[0] = searchBooksTypes.getSelectedItem().toString();
    for (int i = 1; i < booksData.length; i++) {
        if (!booksKeyTextField.getText().equals("")) {
            if (searchBooksTypes.getSelectedItem().toString().equals("BookID")) {
                booksData[i] = booksKeyTextField.getText();
            }
            else
                booksData[i] = "'%" + booksKeyTextField.getText() + "%'";
        }
        else
            return false;
    }
    return true;
}

//for checking the information from the text field
public boolean isMembersDataCorrect() {
    membersData = new String[2];
    membersData[0] = searchMembersTypes.getSelectedItem().toString();
    for (int i = 1; i < membersData.length; i++) {
        if (!membersKeyTextField.getText().equals("")) {
            if (searchMembersTypes.getSelectedItem().toString().equals("MemberID")) {
                membersData[i] = membersKeyTextField.getText();
            }
            else
                membersData[i] = "'%" + membersKeyTextField.getText() + "%'";
        }
        else
            return false;
    }
    return true;
}

//constructor of searchBooksAndMembers
public SearchBooksAndMembers() {
    //for setting the title for the internal frame
    super("Search", false, true, false, true);
    //for setting the icon
    setFrameIcon(new ImageIcon(ClassLoader.getSystemResource("images/Find16.gif")));
    //for getting the graphical user interface components display area
    Container cp = getContentPane();

    //for setting the layout
    northPanel.setLayout(new FlowLayout(FlowLayout.CENTER));
    //for setting the font
    title.setFont(new Font("Tahoma", Font.BOLD, 14));
    //for adding the label
    northPanel.add(title);
    //for adding the north panel to the container
    cp.add("North", northPanel);

    //for setting the layout
    center.setLayout(new BorderLayout());

    //for setting the layout
    centerBooksPanel.setLayout(new BorderLayout());
    //for setting the layout
    searchBooksPanel.setLayout(new GridLayout(2, 2, 1, 1));
    //for adding the label
    searchBooksPanel.add(searchBooksLabel);
    //for adding the JComboBos[]
    searchBooksPanel.add(searchBooksTypes = new JComboBox(booksTypes));
    //for adding the label
    searchBooksPanel.add(booksKey);
    //for adding the text field
    searchBooksPanel.add(booksKeyTextField);
    //for adding the internal panel to the panel
    centerBooksPanel.add("North", searchBooksPanel);

    //for setting the layout
    searchBooksButtonPanel.setLayout(new FlowLayout(FlowLayout.RIGHT));
    //for adding the button
    searchBooksButtonPanel.add(searchBooksButton);
    //for adding the internal panel to the center panel
    centerBooksPanel.add("South", searchBooksButtonPanel);
    //for setting the border
    centerBooksPanel.setBorder(BorderFactory.createTitledBorder("Search for a book:"));
    //for adding center panel to the center
    center.add("West", centerBooksPanel);

    //for setting the layout
    centerMembersPanel.setLayout(new BorderLayout());
    //for setting the layout
    searchMembersPanel.setLayout(new GridLayout(2, 2, 1, 1));
    //for adding the label
    searchMembersPanel.add(searchMembersLabel);
    //for adding the JComboBos[]
    searchMembersPanel.add(searchMembersTypes = new JComboBox(membersTypes));
    //for adding the label
    searchMembersPanel.add(membersKey);
    //for adding the text field
    searchMembersPanel.add(membersKeyTextField);
    //for adding the internal panel to the panel
    centerMembersPanel.add("North", searchMembersPanel);

    //for setting the layout
    searchMembersButtonPanel.setLayout(new FlowLayout(FlowLayout.RIGHT));
    //for adding the button
    searchMembersButtonPanel.add(searchMembersButton);
    //for adding the internal panel to the center panel
    centerMembersPanel.add("South", searchMembersButtonPanel);
    //for setting the border
    centerMembersPanel.setBorder(BorderFactory.createTitledBorder("Search for a member:"));
    //for adding center panel to the center
    center.add("East", centerMembersPanel);

    //for adding the center to the container
    cp.add("Center", center);

    /**
     *for setting the font to the lables & buttons
     */
    searchBooksLabel.setFont(new Font("Tahoma", Font.BOLD, 11));
    searchBooksTypes.setFont(new Font("Tahoma", Font.BOLD, 11));
    booksKey.setFont(new Font("Tahoma", Font.BOLD, 11));
    booksKeyTextField.setFont(new Font("Tahoma", Font.PLAIN, 11));
    searchBooksButton.setFont(new Font("Tahoma", Font.BOLD, 11));
    cancelButton.setFont(new Font("Tahoma", Font.BOLD, 11));
    searchMembersLabel.setFont(new Font("Tahoma", Font.BOLD, 11));
    searchMembersTypes.setFont(new Font("Tahoma", Font.BOLD, 11));
    membersKey.setFont(new Font("Tahoma", Font.BOLD, 11));
    membersKeyTextField.setFont(new Font("Tahoma", Font.PLAIN, 11));
    searchMembersButton.setFont(new Font("Tahoma", Font.BOLD, 11));
    cancelButton.setFont(new Font("Tahoma", Font.BOLD, 11));

    //for setting the layout
    southPanel.setLayout(new FlowLayout(FlowLayout.RIGHT));
    //for adding the button
    southPanel.add(cancelButton);
    //for setting the border
    southPanel.setBorder(BorderFactory.createEtchedBorder());
    //for adding the south panel to the container
    cp.add("South", southPanel);

    /***********************************************************************
     * for adding the action listener to the button,first the text will be *
     * taken from the JTextField and passing them to listSearchBooks object*
     ***********************************************************************/
    searchBooksButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent ae) {
            //for checking if there is a missing information
            if (isBooksDataCorrect() == true) {
                book = new Books();
                String bookQuery = "SELECT BookID, Subject, Title, Author,     Publisher," +
                        "Copyright, Edition, Pages,   NumberOfBooks,ISBN,Library,Availble,ShelfNo FROM Books" +
                        " WHERE " + booksData[0] + " LIKE " + booksData[1];
                book.connection(bookQuery);
                int bookID = book.getBookID();
                if (bookID != 0) {
                    listBooks = new ListSearchBooks(bookQuery);
                    getParent().add(listBooks);
                    try {
                        listBooks.setSelected(true);
                    }
                    catch (java.beans.PropertyVetoException e) {
                    }
                    dispose();
                }
                else {
                    JOptionPane.showMessageDialog(null, "No Match(es)", "Error", JOptionPane.ERROR_MESSAGE);
                    booksKeyTextField.setText(null);
                }
            }
            else
                JOptionPane.showMessageDialog(null, "Please enter a keyword", "Warning", JOptionPane.WARNING_MESSAGE);
        }
    });
    /***********************************************************************
     * for adding the action listener to the button,first the text will be *
     * taken from the JTextField and passing them to listSearchBooks object*
     ***********************************************************************/
    searchMembersButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent ae) {
            if (isMembersDataCorrect()) {
                member = new Members();
                String memberQuery = "SELECT MemberID, ID, Name, EMail, Major, Expired" +
                        " FROM Members WHERE " + membersData[0] + " LIKE " + membersData[1];
                member.connection(memberQuery);
                int memberID = member.getMemberID();
                if (memberID != 0) {
                    listMembers = new ListSearchMembers(memberQuery);
                    getParent().add(listMembers);
                    try {
                        listMembers.setSelected(true);
                    }
                    catch (java.beans.PropertyVetoException e) {
                    }
                    dispose();
                }
                else {
                    JOptionPane.showMessageDialog(null, "No Match(es)", "Error", JOptionPane.ERROR_MESSAGE);
                    membersKeyTextField.setText(null);
                }
            }
            else
                JOptionPane.showMessageDialog(null, "Please enter a keyword", "Warning", JOptionPane.WARNING_MESSAGE);
        }
    });
    //for adding the action listener for the button to dispose the frame
    cancelButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent ae) {
            dispose();
        }
    });
    //for setting the visible to true
    setVisible(true);
    //show the internal frame
    pack();
}
}

 

This is the code for ListBooks.Java

import javax.swing.*;
import javax.swing.table.TableColumn;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;
import java.sql.SQLException;

public class ListSearchBooks extends JInternalFrame {
/***************************************************************************
 ***      declaration of the private variables used in the program       ***
 ***************************************************************************/

//for creating the North Panel
private JPanel northPanel = new JPanel();
//for creating the Center Panel
private JPanel centerPanel = new JPanel();
//for creating the label
private JLabel label = new JLabel("List of Searched Books");
//for creating the button
private JButton printButton;
//for creating the table
private JTable table;
//for creating the TableColumn
private TableColumn column = null;
//for creating the JScrollPane
private JScrollPane scrollPane;

//for creating an object for the ResultSetTableModel class
private ResultSetTableModel tableModel;

//constructor of listSearchBooks
public ListSearchBooks(String query) {
    //for setting the title for the internal frame
    super("Searched Books", false, true, false, true);
    //for setting the icon
    setFrameIcon(new ImageIcon(ClassLoader.getSystemResource("images/List16.gif")));
    //for getting the graphical user interface components display area
    Container cp = getContentPane();

    /***********************************************************************
     *for setting the required information for the ResultSetTableModel class*
     ************************************************************************/
    final String JDBC_DRIVER ="net.ucanaccess.jdbc.UcanaccessDriver";
    final String DATABASE_URL = "jdbc:ucanaccess://JLibrary.mdb";

    final String DEFAULT_QUERY = query;

    //for bassing the required information to the ResultSetTableModel object
    try {
        tableModel = new ResultSetTableModel(JDBC_DRIVER, DATABASE_URL, DEFAULT_QUERY);
        //for setting the Query
        try {
            tableModel.setQuery(DEFAULT_QUERY);
        }
        catch (SQLException sqlException) {
        }
    }
    catch (ClassNotFoundException classNotFound) {
    }
    catch (SQLException sqlException) {
    }

    //for setting the table with the information
    table = new JTable(tableModel);
    //for setting the size for the table
    table.setPreferredScrollableViewportSize(new Dimension(990, 200));
    //for setting the font
    table.setFont(new Font("Tahoma", Font.PLAIN, 12));
    //for setting the scrollpane to the table
    scrollPane = new JScrollPane(table);

    //for setting the size for the table columns
    for (int i = 0; i < 13; i++) {
        column = table.getColumnModel().getColumn(i);
        if (i == 0) //BookID
            column.setPreferredWidth(20);
        if (i == 1) //Subject
            column.setPreferredWidth(100);
        if (i == 2) //Title
            column.setPreferredWidth(150);
        if (i == 3) //Auther
            column.setPreferredWidth(50);
        if (i == 4) //Publisher
            column.setPreferredWidth(70);
        if (i == 5) //Copyright
            column.setPreferredWidth(40);
        if (i == 6) //Edition
            column.setPreferredWidth(40);
        if (i == 7) //Pages
            column.setPreferredWidth(40);
        if (i == 8) //NumberOfBooks
            column.setPreferredWidth(80);
        if (i == 9) //ISBN
            column.setPreferredWidth(70);
        if (i == 10) //Library
            column.setPreferredWidth(30);
        if (i == 11) //Availble
            column.setPreferredWidth(30);
                    if (i == 12) //ShelfNo
            column.setPreferredWidth(30);
    }
    //for setting the font to the label
    label.setFont(new Font("Tahoma", Font.BOLD, 14));
    //for setting the layout to the panel
    northPanel.setLayout(new FlowLayout(FlowLayout.CENTER));
    //for adding the label to the panel
    northPanel.add(label);
    //for adding the panel to the container
    cp.add("North", northPanel);

    //for setting the layout to the panel
    centerPanel.setLayout(new BorderLayout());
    //for creating an image for the button
    ImageIcon printIcon = new ImageIcon(ClassLoader.getSystemResource("images/Print16.gif"));
    //for adding the button to the panel
    printButton = new JButton("print the books", printIcon);
    //for setting the tip text
    printButton.setToolTipText("Print");
    //for setting the font to the button
    printButton.setFont(new Font("Tahoma", Font.PLAIN, 12));
    //for adding the button to the panel
    centerPanel.add(printButton, BorderLayout.NORTH);
    //for adding the scrollpane to the panel
    centerPanel.add(scrollPane, BorderLayout.CENTER);
    //for setting the border to the panel
    centerPanel.setBorder(BorderFactory.createTitledBorder("Books:"));
    //for adding the panel to the container
    cp.add("Center", centerPanel);

    //for adding the actionListener to the button
    printButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent ae) {
            Thread runner = new Thread() {
                public void run() {
                    try {
                        PrinterJob prnJob = PrinterJob.getPrinterJob();
                        prnJob.setPrintable(new PrintingBooks(DEFAULT_QUERY));
                        if (!prnJob.printDialog())
                            return;
                        setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
                        prnJob.print();
                        setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
                    }
                    catch (PrinterException ex) {
                        System.out.println("Printing error: " + ex.toString());
                    }
                }
            };
            runner.start();
        }
    });
    //for setting the visible to true
    setVisible(true);
    //to show the frame
    pack();
}
}

Why is the problem only for BookID? Any help is greatly appreciated.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
El Dj
  • 385
  • 1
  • 7
  • 22
  • Which method searches the contents? `isBooksDataCorrect()`? – J. Schei Jul 19 '16 at 19:37
  • 1
    Could you include the error message you're receiving as well? This could be very helpful to those trying to help. – DeadCereal Jul 19 '16 at 19:39
  • "But when I try to search by BookID, i get an error." - What kind of error? Please post the whole stacktrace. On which line do you got the error? Did you debug your code? This is too much code posted here. You should boil it down to the relevant part. I can see two SELECT statements. Both with LIKE predicates. Is your BookID a string? If it is a number, then this might cause the error. – vanje Jul 19 '16 at 19:43
  • Also, is BookID just an INT in your database? If so it could be that you're trying to compare a string to an int and that could be causing your error. – DeadCereal Jul 19 '16 at 19:44
  • 1
    BTW it is bad practice to create your SQL statements by joining plain strings together. It is better to use prepared statements here. – vanje Jul 19 '16 at 19:45
  • I get the "No match(es)" error when I search by BookID. The error is because `isBooksDataCorrect()` returns false. – El Dj Jul 19 '16 at 19:59
  • If `isBooksDataCorrect()` returns false, then according to your code, the SQL query and the part with the "No match(es)" error is never executed. – vanje Jul 19 '16 at 20:58
  • You have empty catch blocks. So if there is an exception you will never see a error message and a stack trace. You should at least print the stack trace with `e.printStackTrace()` – vanje Jul 19 '16 at 21:01
  • Use PreparedStatement with parameters. It's far easier and type safe. – ManoDestra Jul 20 '16 at 14:00

1 Answers1

2

As mentioned in the comments to the question, your empty catch blocks are swallowing exceptions and preventing you from seeing the problem, which is:

The block of code

if (!booksKeyTextField.getText().equals("")) {
    if (searchBooksTypes.getSelectedItem().toString().equals("BookID")) {
        booksData[i] = booksKeyTextField.getText();
    }
    else
        booksData[i] = "'%" + booksKeyTextField.getText() + "%'";
}

will ultimately result in an SQL query like the following for any type of search except BookID

SELECT BookID, Subject, ... FROM Books WHERE Subject LIKE '%waffles%'

but when searching for BookID your code will try to execute

SELECT BookID, Subject, ... FROM Books WHERE BookID LIKE 1234

which will cause UCanAccess to throw the exception

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 incompatible data type in operation

You need to fix your code to generate a valid SQL query for BookID searches. That query would most likely use an equality (=) comparison instead of LIKE.

While you're at it you should also use a PreparedStatement and a parameterized query to protect against SQL injection problems, like trying to search for an Author named "O'Rourke".

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418