I want to be able to search and return results across the majority of columns in a mysql table. I considered using a myisam db and a full text search, but decided that because of the performance hit it would be better to stick with innodb. I also briefly considered using something like Solr, but a solution like that seems like overkill in my situation.
I am simply trying to add search functionality to a database of contact information. When the user enters say "Smith" I want the result to return and display all table rows where the first name, last name or even address contains the word Smith etc.
Tell me if this is the wrong approach, but I have settled on using a standard SQL string set up like this:
SELECT * FROM contacts WHERE firstName LIKE ? OR lastName LIKE ? OR email LIKE ?
Of course the actual query string will be considerably longer than this example because it will include most of the columns in the table.
1) I prefer to use prepared statements when querying the database for security, but when I set the sql string up how it is above, the code looks for a separate statement for each ?. How can I just repeat the same "LIKE ?" for each column? Or is there a way to set this up similar to:
SELECT * FROM contacts WHERE firstName, lastName, email LIKE ?
When I initially pass the string into the preparedstatement, I surround it with wildcards (%).
2) Will I be able to search both VARCHAR and INT columns?
EDIT: I ended up using a small portion of the Spring framework so I could be able to use named parameters. Unfortunately JDBC does not have this functionality. This meant including a small portion of the Spring framework in my build path so I could use the jdbc jar. Alternatively I could have passed in my one search string for each parameter in the query string, but this would result is ugly/hard to read code and I didn't want to hack around using the same string which is why I opted for using named parameters.
