Two Laptop Bag*
The moose likes JDBC and the fly likes App that allows users w/no SQL experience to build SQL queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "App that allows users w/no SQL experience to build SQL queries" Watch "App that allows users w/no SQL experience to build SQL queries" New topic
Author

App that allows users w/no SQL experience to build SQL queries

Paul Wam
Greenhorn

Joined: Apr 05, 2009
Posts: 20
I'm taking a small SQL server database and building a GUI that allows end users with no/SQL experiences to build their own queries. The end-user just interacts with a form composed of checkboxes and drop down boxes. I want to take that input and programmaticly form a select statements, join conditions, etc in the background.

Problem is - I've never done this before and all signs tell me that this program can get very long. My biggest sign is a coldfusion application that I have access to which is doing half of what I am doing--using fewer tables and fewer variables in it's where condition--but it still has about 1500 lines of code...that means I may have to do about 3000 lines of code...YIKES.

I'm redoing the application in Java. Does anybody have experience with this kind of application? Is their any advice you can give? Are their any third party tools that can cut down on how much wheel I have to reinvent :-)
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18708
    
    8

You've described an application, but you haven't said what your problem is with building that application. So it's pretty hard to give any advice. I would start by advising you to start with a design phase. That means to decide what you want the application to do and roughly how it's going to do it.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

Any tool that gives users enough ability to 'make their own SQL queries' tends to be poorly insulated from users creating queries that slow your system down to a crawl. Generally speaking, I find out what types of queries my clients want to run and create structures that allow them to execute them rather than giving them full access to run any query they can think of since this has the potential of killing the DBMS.


My Blog: Down Home Country Coding with Scott Selikoff
Paul Wam
Greenhorn

Joined: Apr 05, 2009
Posts: 20
Scott Selikoff wrote:Any tool that gives users enough ability to 'make their own SQL queries' tends to be poorly insulated from users creating queries that slow your system down to a crawl. Generally speaking, I find out what types of queries my clients want to run and create structures that allow them to execute them rather than giving them full access to run any query they can think of since this has the potential of killing the DBMS.


Scott, more specifically, what would create this poor insulation?

When you say you "create structures", what kind of structures do you create?

Paul Wam
Greenhorn

Joined: Apr 05, 2009
Posts: 20
Paul Clapham wrote:You've described an application, but you haven't said what your problem is with building that application. So it's pretty hard to give any advice. I would start by advising you to start with a design phase. That means to decide what you want the application to do and roughly how it's going to do it.


I have went through a design phase but since I have not done this before, I anticipate surprises during implementation which may shatter my design. So, essentially, I'm asking "has anybody done this before, if so, what would you advise for and against, if you had the opportunity to do it again what would you do differently"?
David Kilcy
Greenhorn

Joined: Aug 25, 2009
Posts: 20
Paul Wam wrote:.that means I may have to do about 3000 lines of code...YIKES.


then you are in the wrong field
Paul Wam
Greenhorn

Joined: Apr 05, 2009
Posts: 20
David Kilcy wrote:
Paul Wam wrote:.that means I may have to do about 3000 lines of code...YIKES.


then you are in the wrong field


If you love to write 3000 lines of code for something that has been done millions of times before, then you are not only in the right field but will be in SAME FIELD forever - junior programmer and witless java ranch pundit
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18708
    
    8

Paul Wam wrote:My biggest sign is a coldfusion application that I have access to which is doing half of what I am doing--using fewer tables and fewer variables in it's where condition--but it still has about 1500 lines of code...that means I may have to do about 3000 lines of code...YIKES.

I don't see why the number of lines of code should be proportional to the number of tables in the application. In fact I would go so far as to say that the number of lines of code should be essentially independent of the number of tables.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Paul Clapham wrote:I don't see why the number of lines of code should be proportional to the number of tables in the application. In fact I would go so far as to say that the number of lines of code should be essentially independent of the number of tables.

I agree!

Paul Wam wrote:Scott, more specifically, what would create this poor insulation?

Allowing users to create arbitrary SQL allows them to do random joins against huge tables producing cartesian joins that will use up all your memory/CPU/etc

Paul Wam wrote:When you say you "create structures", what kind of structures do you create?

I would create templates with the allowable queries and let them insert pieces - the columns to retrieve, values for a specific column, etc.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3712
    
    5

Paul Wam wrote:Scott, more specifically, what would create this poor insulation?

When you say you "create structures", what kind of structures do you create?


Databases aren't like file system files, its quite possible to write a query that without the right indices, would cause a database to grind to a halt. You can add indices to improve performance but you can't add an index for every possible situation therefore, given enough time a user will execute a query that will stop your system.

As for 'create structures'... all programs that access a database constitute these structures. For example, when you login to the website, it checks your username and password against a database using a SQL query. The user never enters the SQL query, they just click login and the 'structure' of the system takes care of the rest. Which, by the way, brings security into play.

Create a program that does something, not a thin layer for SQL users. For 2 reasons- in the end, they'll have to understand some SQL to make it work; SQL is one of the easier languages to pick up, especially for non-programmers.
Paul Wam
Greenhorn

Joined: Apr 05, 2009
Posts: 20
thanks Scott, it seems I'm on the right path. My design was to only give them the ability to add columns being selected, the conditions of the where clause, and a few aggregate functions. They would not have the option to run joins (thus avoiding cartesian products and teaching them SQL). I have insulated end users from overwhelming themselves w/SQL. Thanks all...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: App that allows users w/no SQL experience to build SQL queries