2

Build Oracle SQL Application dynamically from the java application

 2 years ago
source link: https://www.codesd.com/item/build-oracle-sql-application-dynamically-from-the-java-application.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Build Oracle SQL Application dynamically from the java application

advertisements

How do I build oracle pl/sql query dynamically from a java application? The user will be presented with a bunch of columns that are present in different tables in the database. The user can select any set of column and the application should build the complete select query using only the tables that contain the selected columns. For example, lets consider that there are 3 tables in the database. The user selects col11, col22. In this case, the application should build the query using Tabl1 and Tabl2 only. How do I achieve this?

Tabl1
 - col11
 - col12
 - col13

Tabl2
 - fkTbl1
 - col21
 - col22
 - col23

Tabl3
 - col31
 - col32
 - col33
 - fkTbl1

Ad hoc reporting is an old favourite. It frequently appears as a one-liner at the end of the Reports Requirements section: "Users must be able to define and run their own reports". The only snag is that ad hoc reporting is an application in its own right.

You say

"The user will be presented with a bunch of columns that are present in different tables in the database."

You can avoid some of the complexities I discuss below if the "bunch of columns" (and the spread of tables) is preselected and tightly controlled. Alas, it is in the nature of ad hoc reporting that users will want pretty much all columns from all tables.

Let's start with your example. The user has selected col11 and col22, so you need to generate this query:

SELECT tabl1.col11
       , tabl2.col22
FROM tabl1 JOIN tabl2
     ON  (TABL1.ID = TABL2.FKTABL1)
/

That's not too difficult. You just need to navigate the data dictionary views USER_CONSTRAINTS and USER_CONS_COLUMNS to establish the columns in the join condition - providing you have defined foreign keys (please have foreign keys!).

Things become more complicated if we add a fourth table:

Tabl4
 - col41
 - col42
 - col43
 - fkTbl2

Now when the user choose col11 and col42 you need to navigate the data dictionary to establish that Tabl2 acts as an intermediary table to join Tabl4 and Tabl1 (presuming you are not using composite primary keys, as most people don't). But suppose the user selects col31 and col41. Is that a legitimate combination? Let's say it is. Now you have to join Tabl4 to Tabl2 to Tabl1 to Tabl3. Hmmm...

And what if the user selects columns from two completely unrelated tables - Tabl1 and Tabl23? Do you blindly generate a CROSS JOIN or do you hurl an exception? The choice is yours.

Going back to that first query, it will return all the rows in both tables. Almost certainly your users will want the option to restrict the result set. So you need to offer them the ability to add to filters to the WHERE clause. Gotchas here include:

  • ensuring that supplied values are of an appropriate data-type (no strings for a number, no numbers for a date)
  • providing look-ups to reference data values
  • handling multiple values (IN list rather than equals)
  • ensuring date ranges are sensible (opening bound before closing bound)
  • handling free text searches (are you going to allow it? do you need to use TEXT indexes or will you run the risk of users executing LIKE '%whatever%' against some CLOB column?)

The last point highlights one risk inherent in ad hoc reporting: if the users can assemble a query from any tables with any filters they can assemble a query which can drain all the resources from your system. So it is a good idea to apply profiles to prevent that happening. Also, as I have already mentioned, it is possible for the users to build nonsensical queries. Bear in mind that you don't need very many tables in your schema to generate too many permutations to test.

Finally there is the tricky proposition of security policies. If users are restricted to seeing subsets of data on the basis their department or their job role, then you will need to replicate those rules. In such cases the automatic application of policies through Row Level Security is a real boon

All of which might lead you to conclude that the best solution would be to pursuade your users to acquire an off-the-shelf product instead. Although that approach isn't without its own problems.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK