Sql query with optional fields for Search Web Application

Question

Not an SQL expert:

I am currently implementing a Searching Web Application. The user will send a GET request to a particular Endpoint.

The Endpoint will accept a set of URL Params and the request can come with optional fields. Such as 1, 2 or 3 fields.

My Database table for USER looks like this.

+--------------+---------+------+-----+---------+----------------+
| id        | firstName    | lastName | email | zip | phone      |
+--------------+---------+------+-----+---------+----------------+

Now the web application will get a GET Request with either Phone or Zip or Email.

I have two solutions for doing this but both look bad:

Solution 1:

Have multiple SQL Queries and Execute them according to the URL Params, I receive.

Select * from User where phone=1111111111 and zip=12345 and [email protected];

Select * from User where phone=1111111111 and zip=12345;

... ...

And so on........I will end up having many queries and this will be a bad implementation. Also will be bad to maintain.

Solution 2:

Other solution that I am thinking of is to have a method, which will build an SQL query based on the URL Params I receive.

Example:

buildSQLQuery(phone,zip,email){

String sql = "Select * from User where "

if(phone!=null  && email!=null && zip!=null ){
sql = sql   + "phone = " + phone + " and zip = " + zip + " and email = " + email;
}else if (phone!=null  && email!=null && zip==null){
sql = sql + "phone = " + phone + " and email = " + email;
}
.......
......
And so on have all conditions and build that particular query.
}

I don't like both these solutions.

Is there a way to write a Single SQL query and that will handle all the above conditions.

Something like if the URL Param value is NULL then that should not affect the query and I will get my expected results.

As in my case the optional values, which don't come in are set to NULL.

Can I implement something like this?

Select * from User where (if notNull (phone))(phone = phone ) and (if notNull (email))(email = email ) and (if notNull (zip))(zip = zip )

If any of the above one value is null then don't use that part in where Condition.

Also I will always have one field present, so there will be no case where all values are null.

I am implementing this web application in Java and Spring MVC. If anyone can guide me in the correct direction.

Thank you.

Yes you can do that with if() if you provide fiddle for your table here it will be easy to find solution. — Mobasher Fasihy
– Mobasher Fasihy, Commented Jun 29, 2015 at 5:57
You are using Spring MVC. I highly recommend you usage of Spring Data JPA and Hibernate framework. projects.spring.io/spring-data-jpa Then you will not have to write SQL boilerplate code like this. — Gondy
– Gondy, Commented Jun 29, 2015 at 6:33
Can you give me an example for using spring-data-jpa? in my case. — Aniks
– Aniks, Commented Jun 29, 2015 at 19:37

Yogendra · Accepted Answer · 2015-06-29 06:25:29Z

1

I think one more possible solution like:

String sql = "Select * from User where 1=1 "

    if(phone!=null){
    sql +=  " and phone = " + phone ;}

    if(email!=null){
    sql +=  " and email = " + email ;}

    if(zip!=null){
    sql +=  " and zip = " + zip ;}

OR You can try following single query:

select * from User 
where (@phone is null OR phone = @phone) AND (@email is null OR email = @email) AND (@zip is null OR zip = @zip)

edited Jun 29, 2015 at 6:25

answered Jun 29, 2015 at 6:02

Yogendra

2,2843 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gondy Over a year ago

Be careful, SQL injection is strongly possible here.

Tim Biegeleisen Over a year ago

I would be curious to see if this answer could be updated in such a way that SQL injection is not possible.

Mobasher Fasihy · Accepted Answer · 2015-06-29 06:28:26Z

0

You can do like this:

SELECT * FROM User where(
   IF($phone != 0, IF(phone = $phone,1,0), 0) AND 
   IF($email != 0, IF(email = $email,1,0),0) AND 
   IF($zip != 0, IF(zip = $zip,1,0),0)
)

assumed for PHP you can change the syntax, may be this query not do the job completely but if you provide fiddle i will modify this to give the proper result.

edited Jun 29, 2015 at 6:28

answered Jun 29, 2015 at 6:12

Mobasher Fasihy

1,0612 gold badges10 silver badges19 bronze badges

Collectives™ on Stack Overflow

Sql query with optional fields for Search Web Application

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related