Some solutions...choose based on how accurate your results need to be, how consistent the search data should be, and on size of data in DB.
Option 1: Use SELECT...MATCH...AGAINST full text search capability of MySQL.
Not sure if this is what you're using right now.
It orders results based on how many terms match.
No code changes required in the application - just change the SQL.
But it has lot of caveats - the table has to be MyISAM (which means no transactions), and requires a special FULLTEXT index consisting of all searchable columns.
It's a quick and simple solution, but search accuracy is not so great, it's not very tunable, places large load on db server, and becomes slow for large table sizes, or large columns.
More info here
http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
Option 2: Use Hibernate Search
This uses the proven lucene text search framework alongside Hibernate automatically.
The text indexing of Lucene is very powerful, with capabilities like word stemming, synoyms and stop words.
Each term that is present in the row will
boost the relevance score of that row, and you can get results by order of relevance.
Results will be very accurate.
Suitable if search index needs to be transactionally consistent at all times with DB. No explicit updates to seach index are needed - Hibernate automatically does that when you update table.
Very few code changes required in the application.
Database server load is reduced because actual searching is handled by lucene. Suitable for large data.
http://www.hibernate.org/subprojects/search.html
Option 3: Use a dedicated search server like Apache solr, ElasticSearch, OpenSearchServer, etc
All these are scalable solutions based over lucene, and results are exactly what plain lucene gives.
They mostly run as separate server processes, but some like Solr are also embeddable in process.
These should be deployed if data sizes are large and scalability is an issue.
Has impact on application architecture and deployment.
Explicit coding is needed to send queries and handle search results.
If you have large data sizes, you can take a look into one of these.
I usually use eventual consistency design - the index gets updated lazily after any DB update.
I've used only Solr among these, and I love it!
