dynamically construct SQL by user defined results and conditions within a database which contains multi related or non-related tables

Question

I encounter a j2ee project that is a system that can dynamically construct SQL by user defined results and conditions. What I propose to do like: korzh's EasyQuery: DEMO

What's a user can do in this system is:

1. config the result
2. config the condition
3. execute the auto-generated SQL

Like:

My question is: How to generate a sql statement that configured by user and these user selected result or condition fields may in different tables, I am confused that these tables maybe are related but also irrelated.

For example, as this picture shows,

• I selected Customer(table) -> [customer company name],[Order ID] (fields) as results.
• I selected Product(table) -> [Product ID = 1] as a condition.

the system will generate a SQL:

SELECT DISTINCT Customers.CompanyName AS "Customer Company name", Orders.OrderID AS "Order ID" 
FROM (((Customers AS Customers
 INNER JOIN Orders AS Orders ON (Customers.CustomerID  = Orders.CustomerID ))
 INNER JOIN Order_Details AS Order_Details ON (Orders.OrderID  = Order_Details.OrderID ))
 INNER JOIN Products AS Products ON (Products.ProductID  = Order_Details.ProductID ))
WHERE
( Products.ProductID = 1 )

How the system finds out the inner relation between these tables, like:

(((Customers AS Customers
     INNER JOIN Orders AS Orders ON (Customers.CustomerID  = Orders.CustomerID ))
     INNER JOIN Order_Details AS Order_Details ON (Orders.OrderID  = Order_Details.OrderID ))
     INNER JOIN Products AS Products ON (Products.ProductID  = Order_Details.ProductID ))

In other words, I want to know how the "ad-hoc query" works to generate a feasible SQL statement through user's configuration. Is there any library of Java exists?

I would appreciate your help.

Answer 1

You can use MySQL INFORMATION_SCHEMA to get relations between tables E.g.

select
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
  REFERENCED_TABLE_NAME = '<table>';

Got from here

Thus you can get all referenced fields for one table. Go through all your tables building graph of relations with column names.

Then in your Query just include all the necessary tables and use their relations to create INNER JOIN conditions and have FROM section filled properly (One table could be included more than once so you may need aliases)

Then just include necessary fields of the joined tables to SELECT and WHERE sections.

Answer 2

The SQL query is built solely dependent upon the tables selection, fields selection, and specific criteria. This is built in code as the specific items are selected. I'm sure you already know this but as the the selection process is carried out by the user more and more items become active and enabled.

I'm not familiar with the application you display in your post but I'm certain you can't do anything until you first select a database to work with. Once this is done a simple SQL query can gather all the table names contained within that database, 'perhaps' something like:

SELECT table_name FROM information_schema.tables WHERE table_schema='<the_database_name>';

The table names gathered are listed within the GUI as JCheckBoxes.

As the user now selects a table that table's fields are placed into a JComboBox, or JList or whatever. other tables are also checked at this time to see if there are dependencies or relationships with other tables and if there are then perhaps change the text color in the JCheckBoxes for those tables to Green so the User knows that relationships exist. There are SQL statements that can find table dependencies and relationships as well....perhaps something like:

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

(from a SO post found here: Know relationships between all the tables of database in SQL Server) Yes, this stuff is already in StackOverflow if you look.

As more items are selected then more components within your UI get filled for further selection. For every item select you keep building the SQL query string. And so on....

Your are are definitely going to need a good grasp of the SQL language to carry out this task successfully but if you do, it's not all that tough to get the job done.

Good Luck.