• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to create dynamic query builder?

 
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

I have a requirement where I need to show a report in a j2ee application(jsp/servlet) and the available columns will be shown in a pane.

The user will choose from those options. So I need to build a runtime query builder.

Please tell me the elegant way to do this.

Thanks,

Kousik
 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Kousik,

if i am not wrong, your requirement is like that user will select few fields as input fields form available field list.
And base on those selected input fields, your query should return results. right?

so, you can use COALESCE SQL Function. which return first non-Null value from expression list.

for Eg:

select *
from table1
where name = COALESCE('inputFieldFromUser',name);

here, if 'inputFieldFromUser' is null (means, user will not give any input for 'name' column) than name = name, which will not create any effect on select query.

regards,
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you familiar with DatabaseMetaData and ResultSetMetaData? They can allow you to read column names and/or filter information not known until runtime. For example, you can use them to provide the user with a list of columns in a table, thereby allowing them to select the columns they'd like to use for a query.

As a side node be extremely careful with 'dynamic query builders'. For medium and large database systems, giving users the ability to run arbitrary, possibly untested queries is *extremely* dangerous. Historically, the more open a query builder is within an application, the stronger likelihood the user can execute a query that grinds the entire database to a halt. Alternatively, I find its a better practice to offer clients a list (or report) of most desired queries that you can test and manage.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic