wood burning stoves 2.0*
The moose likes JDBC and the fly likes How to generate queries with random joins Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to generate queries with random joins" Watch "How to generate queries with random joins" New topic
Author

How to generate queries with random joins

Gaurav Chander
Greenhorn

Joined: Jan 29, 2012
Posts: 10
Hi All,

This is my first post. Sorry if it is in the wrong section.

I am working on a web application in which user will be selecting columns form different tables to be part of the final report.
My problem is that tables have joins on them and i'm not able to figure out how to generate the sql query on the base of selection criteria as user can select any number of tables and joins are to be put accordingly in run time.
Thanks for the help.

Regards
GC
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Here is an example...
Consider three tables as below
User --> UserID, Name, Age, Sex
Account --> AccountID, UserID, Balance, Type
Transaction --> TransactionID, AccountID, Amount, Type
Now if the user wants to see User.Name, Account.Balance, Transaction.Amount, Transaction.Type
Here is a sample code..considering the parameter will be like User.Name-Account.Balance-Transaction.Amount-Transaction.Type (You can use any other separator according to your wish)

You need to refine this code a bit to suit your req..but i hope this will give you a start
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19684
    
  20

That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Rob Spoor wrote:That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.

Yeah...I concentrated on the logic but security measure can be taken. Basically with this small req the parameter validation would be like (using a regular expression)

or more specifically
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Tarun Bolla wrote:
Rob Spoor wrote:That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.

Yeah...I concentrated on the logic but security measure can be taken. Basically with this small req the parameter validation would be like (using a regular expression)

or more specifically

Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less. Also, proper variables (if any) should be bound, not stuffed in as literals.

There is also a performance concern. Random queries may take very very long time to complete, if there are not proper indexes in place. If this is an OLTP system, a better solution might be providing only a few specific reports. That way a set of queries could be maintained with corresponding indexes to support them in the database. (This is often ignored by developers, who then whine that the database is slow and are tempted to try to replicate database functionality in the middle tier. )
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

have you considered using hibernate, or such things?

I THINK this would handle all that tricky stuff for you.
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Martin Vajsar wrote:Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less

Thats what the below quote is about....
Tarun Bolla wrote:

But this stuff is getting far too specific for a beginners question. Ofcourse a lot can be done. But i tried to keep the code short and simple for an idea on how to start.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Tarun Bolla wrote:
Martin Vajsar wrote:Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less

Thats what the below quote is about....
Tarun Bolla wrote:

Ahh, sorry. I just wouldn't use regular expressions for that, so I missed it.
Gaurav Chander
Greenhorn

Joined: Jan 29, 2012
Posts: 10
Tarun Bolla wrote:Here is an example...
Consider three tables as below
User --> UserID, Name, Age, Sex
Account --> AccountID, UserID, Balance, Type
Transaction --> TransactionID, AccountID, Amount, Type
Now if the user wants to see User.Name, Account.Balance, Transaction.Amount, Transaction.Type
Here is a sample code..considering the parameter will be like User.Name-Account.Balance-Transaction.Amount-Transaction.Type (You can use any other separator according to your wish)

You need to refine this code a bit to suit your req..but i hope this will give you a start




Thank you for the explanation.

I am able to assemble simple queries for example select [column list] from [table list] where [condition list]
but the problem is that I'm not sure what tables will be part of the join .

An example query :

select distinct
CAMPAIGN.LEADS_TYPE,
CAMPAIGN.CAMPAIGN_TYPE,
ADVERTISER.ACC_NAME,
USER_PROFILE.EMAIL,
ADVERTISER.ACC_TYPE,
AD_UNIT.AD_NAME
from
CAMPAIGN JOIN ADVERTISER ON CAMPAIGN.user_profile_id_fk = ADVERTISER.user_profile_id_fk
JOIN USER_PROFILE ON ADVERTISER.user_profile_id_fk = USER_PROFILE.id
JOIN AD_UNIT ON AD_UNIT.campaign_id_fk = CAMPAIGN.id;


now for the selected columns join will be as defined in the query but as the column list changes so do the joins and there are about 14 tables so I'm not sure how to handle this.

If if try to consider all the possible conditions it will be a very lengthy code and I'm not sure if that is feasible or not.

Thank you all for your time and effort.
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables
Gaurav Chander
Greenhorn

Joined: Jan 29, 2012
Posts: 10
Tarun Bolla wrote:
Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables


Thank you..
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Tarun Bolla wrote:
Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables


This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables. As the list the user selects may not include all the tables required for the select to work.
Imagine
people table -> car table -> engine size table

the user requests all the people with 1.8l engines
he needs to work out the link between people and engine size.
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Wendy Gibbons wrote:This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables.

My apologies to the OP. I was under a perception that all the tables carry single join column. It seems like a bigger picture now and a good problem to solve. See you soon. (with another solution ofcourse)
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Tarun Bolla wrote:
Wendy Gibbons wrote:This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables.

My apologies to the OP. I was under a perception that all the tables carry single join column. It seems like a bigger picture now and a good problem to solve. See you soon. (with another solution ofcourse)


yours was a very nice interface solution, now he needs to work on the back end, he has the user generated portion, but not all the key links.
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
See if this can help you.. I sooo think this will help you ..

Table And column along with foreign key mappings in a java file

PHEW.....
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

just testing it now, but the columns and tables data (in a live application) should be in a configuration file loaded at runtime, you don't want to have to change and recompile the code every time the database changes.

IT WORKED, I added another table cheque stuff linked from cheques. and selelcted qb.main("Users-Accounts", "Users.UserID-TransactionNotes.Note-ChequeStuff.ChequeID");

I also played with it a bit, made the columns a map from table to a list of columns, and passed in a list of the tables you actually want selecting, so in above select the tables users and accounts.

oops well over my lunch hour
Tarun Bolla
Ranch Hand

Joined: Jun 20, 2011
Posts: 89
Thanks for the testing Wendy! Seems like Gaurav has lost interest in this...
Gaurav Chander
Greenhorn

Joined: Jan 29, 2012
Posts: 10
Hi!

I finally made it working.

I used a properties file which has the join conditions and joins are picked on the basis of columns selected in run time.

A sample join looks like below.

ADVERTISER=1 USER_PROFILE; JOIN ADVERTISER ON USER_PROFILE.ID \= ADVERTISER.USER_PROFILE_ID_FK

so when ADVERTISER tables is selected join is with user profile and join is separated with ';'. Join tables are given numbers and joins are appended into query according to their order.

Thanks everyone for your time.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to generate queries with random joins