How to Search from Multiple column in mysql DataBase with java

Question

I have follwinig code for search data.

public void advanceSearchMethod(String advanceName, int advanceTpNumber, String advanceAddress, String advanceDescription){
    Connection connection=null;
    try{  
        //for connect to database.
        connection=(Connection) DriverManager.getConnection("jdbc:mysql://localhost/contactbook","root","time1");
        //for communicate with database.
        Statement stmt=(Statement)connection.createStatement();
        String searchQuery="SELECT * FROM Contacts WHERE Name LIKE '%'"+advanceName+"'%' AND TelePhoneNumber LIKE '"+advanceTpNumber+"%' OR Address LIKE '%'"+advanceAddress+"'%' OR Description LIKE '%'"+advanceDescription+"'%'";
        rs=stmt.executeQuery(searchQuery);
        contactTableInDefaultForm.setModel(DbUtils.resultSetToTableModel(rs));
    }catch(Exception e){
        JOptionPane.showMessageDialog(null, "Sorry! Connection Failed");
    }
}

No errors in this code.but work catch block. I cannot imagine what I should do. How can I search them?

what is the exception you are getting .print the exception and tell — muthukumar
– muthukumar, Commented Sep 22, 2013 at 11:42

Thomas W · Accepted Answer · 2013-09-22 12:02:08Z

You have a major bug -- when you build the WHERE clause, you have spurious ' apostrophes after '% opening-quote & wildcard and before %' closing-wildcard & quote.

Your broken code:     "WHERE Name LIKE '%'"+advanceName+"'%'"
Corrected:            "WHERE Name LIKE '%"+advanceName+"%'"

But the whole code is not good code, at all -- every single thing is wrong with it.

WHERE clauses should be built up only with the conditions you actually need to search on. And should use PreparedStatement and ? bound parameters, rather than building string-literals into the SQL. (You have built a well-known security flaw.)

PhoneNumbers are strings, not integers. The LIKE pattern for TelePhoneNumber doesn't have a starting %.

DB connection should be provided from one class & method, rather than in every method in your application.

Errors in separate operations (getting the connection/ vs. executing the query and reading results) should be checked & reported separately. Exceptions and stacktraces should always be logged (use Log4J) or, at the worst case, output to the console.

The single only thing you got right here, was the variable & parameter naming.

To be honest, you ought to be using Hibernate rather than writing this rickety nonsense.

Chaitanya · Accepted Answer · 2013-10-08 13:21:32Z

2

String searchQuery="SELECT * FROM Contacts WHERE Name LIKE '%'"+advanceName+"'%' AND TelePhoneNumber LIKE '"+advanceTpNumber+"%' OR Address LIKE '%"+advanceAddress+"%' OR Description LIKE '%"+advanceDescription+"%'";

U have added addition single quatation.. Hope this is right answer..

answered Oct 8, 2013 at 13:21

Chaitanya

371 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to Search from Multiple column in mysql DataBase with java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related