CODEWITHSUNDEEP
javac++sqlswingsearch
Hoody
Hoody

Reputation: 3062

Searching entire database with one word or more in java

To prevent sql injections I am trying to avoid using WHERE Surname LIKE '"search_txt.getText()"' ... however using a preparedStatement is leaving the ? blank, in other words sql statement cannot execute even a correct search value is entered in the textfield...what is the problem in the line pst1.setString(1, search_txt.getText()+"%");

private void SearchActionPerformed(java.awt.event.ActionEvent evt) {                                       
             try{
             sql = "SELECT Title, Forename, Surname, Role FROM AcademicInfo WHERE Surname LIKE ? OR Forename LIKE ?";
            PreparedStatement pst1 = conn.prepareStatement(sql); 

            ResultSet rs2 = pst1.executeQuery();
            if (rs2.next()) {
                pst1.setString(1, search_txt.getText()+"%");
                pst1.setString(2, search_txt.getText()+"%");
                System.out.println(sql);
                JOptionPane.showMessageDialog(null, "Found Academic");
                Update_table(sql);
            } else {
                System.out.println(sql);
                JOptionPane.showMessageDialog(null, "No Results Found");
            }
             }
            catch(Exception e) {
                JOptionPane.showMessageDialog(null, e);
            }



        }

Upvotes: 0

Views: 203

Answers (2)

O. Jones
O. Jones

Reputation: 108641

The easiest way to do this with MySQL is by using FULLTEXT search. It doesn't search, as you put it, the 'entire sql database,' but it can search a lot of columns. This works pretty well.

The syntax is a little different from what you're used to. This might do the trick for you.

SELECT Title, Forename, Surname, Role 
  FROM AcademicInfo 
 WHERE MATCH(Surname, Forename) AGAINST('searchterm' IN BOOLEAN MODE)

I'll leave it to you to adapt that SQL statement to Java.

Here's the documentation.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 1

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

This is a great use-case for a full-text search engine like Apache Lucene

Upvotes: 1

Related Questions