Automatically generated database requests

Question

How do you implement automatically generated database (let it be SQL) requests?

Let us have offline shop with filters:

enter image description here

The database is standalone offline.

SO if I want to filter items by Price the request would be something like:

select Snowboard.Name
from Snowboard
where Snowboard.Price between 400 and 600;

And if I filter by two characteristics e.g. Price from and Camber. There would be:

select s.Name, s.Camber
from Snowboard s
where s.Price between 400 and 600
and s.Camber in ('Rocker', 'Hybrid');

The question is how could it be implemented in Java so that these requests are generated automatically from any combination of filters selected?

You'll have to generate sql query string with StringBuilder/Buffer. Based on input that is sent by UI. — Pramod S. Nikam, May 05 '14 at 09:51
You might want to look at proper implementations of `Faceted search` (http://stackoverflow.com/questions/5321595/what-is-faceted-search) — reto, May 05 '14 at 09:53

Zo Has · Accepted Answer · 2014-05-06T05:55:01.587

Quick and dirty solution #1

Generate a query at run time & make clever use of WHERE 1=1 condition as the number of where clause are unknown. (This sample is in C# but works more or less the same with JAVA as well)

string sql= @"select Snowboard.Name
from Snowboard
where 1=1";

Now you can build your query based on the UI element selections like

string whereClause="";
if(yourCheckBoxPrice.Checked) 
{
    whereClause+= " AND Price BETWEEN "+ txtPriceFrom.Text + " AND "+ txtPriceTo.Text;
}

if(yourCheckBoxCamber.Checked)
{
    whereClause+= " AND Camber IN ("+ /* your list of values go here */ +")"; 
}

sql += whereClause;

2nd Solution (Use SQL CASE)

You can use SQL CASE inside your query for each where clause to check for nulls or specific values. But beware, dynamic SQL will make your code pretty messy & hard to read (Can be done via a stored procedure as well)

SQL- CASE Statement

I advise you to use a stored procedure with a mix of both options 1 and 2. Implementing Dynamic SQL Where Clause. Keep it simple and you are good to go.

Automatically generated database requests

1 Answers1