0

I am implementing a search in which users need to fill different fields to do the search, but I am not sure how to search if user does not fill some of the fields.

Lets say I am asking them to fill name,family,age,city and country

User may leave any of these blank so I should make a search based on the filled one.

For example if name and family are filled just search for name AND family and if name, family and country are filled, search for records that has all of these three.

I am using prepared statement, How about if I use Persitance API?

         ps.setString(1, "Name");
         ps.setString(2, "Family");
         ps.setint(3, "age");
         ps.setString(4, "city");
         ps.setString(5,"country");

Query

 SELECT * FROM Customer WHERE name = ? AND family = ? AND age = ? AND city = ? AND country = ?
J888
  • 1,944
  • 8
  • 42
  • 76
  • 1
    This might help: http://stackoverflow.com/questions/4215135/how-to-deal-with-maybe-null-values-in-a-preparedstatement – sgeddes May 28 '13 at 01:15

3 Answers3

1

You'll want to remove the expressions that reference unspecified fields.

You can have a SortedMap<String, Object> which contains only the requested fields. Iterate over that and build the SQL where clause using a StringBuilder along with setting the parameter values.

Some ORM's will do this for you.

jspcal
  • 50,847
  • 7
  • 72
  • 76
  • would you give me an example ? I could not find one, if I use the string builder then how to do it using prepared statement? – J888 May 28 '13 at 01:08
  • Bind the values after the statement is built. The values are a list that gets appended to as you iterate over the fields. (There's also [squiggle](https://code.google.com/p/squiggle-sql/) for generating SQL.) – jspcal May 28 '13 at 01:23
1

If very complex, this sort of scenario seems to call for something like the Hibernate Criteria Query API.

Arthur Dent
  • 785
  • 3
  • 7
1

The Squiggle SQL Builder might work well for you for simpler cases.

Arthur Dent
  • 785
  • 3
  • 7