multiple search fields

Question

What will be the best approach if I have search fields for 20 or more and any combination should be valid. Is there any special way to do it in openJPA or native SQL is better. Any idea would be helpful.Thanks.

Answer 1

I think you might want to use something like "Hibernate Search" which brings the power of full text search engines to DB model. Thus, you can provide a google search like feature to do whatever combination of search your customers want to perform.

I had developed a demo app with a GUI to test out various query types.

Check out - http://code.google.com/p/hb-search-demo/

Answer 2

I believe that using criteria api is the best way forward.

Please read that link and check for your own:

http://openjpa.apache.org/builds/2.0.0-beta3/apache-openjpa-2.0.0-beta3/docs/manual/jpa_overview_criteria.html

Answer 3

I don't like running full text searches through SQL. For stuff like to use a search engine like Solr.

http://lucene.apache.org/solr/

an overview of its features:

http://lucene.apache.org/solr/features.html

Answer 4

You can try something like this in a stored procedure or parameterized SQL statement. This way you can pass in all of the fields even if only a few have values.

@param1 varchar(25),
@param2 int,
@param3 varchar(10),
@param4 char(1)

SELECT column1, column2, column3, column4
FROM TABLE
WHERE (column1 = @param1 OR @param1 IS NULL)
AND (column2 = @param2 OR @param2 IS NULL)
AND (column3 = @param3 OR @param3 IS NULL)
AND (column4 = @param4 OR @param4 IS NULL)