What is the best way of doing this?

[Jitesh Sinha]

Hi All,

I have a web page that has a few list boxes that serve a purpose of specifying filters on the data that should be shown on that web page.Something like this -

Matching conditions for URL or Title are:
• Contains
• Does not contain
• Starts with
• Ends with
• Is empty
• Is not empty
• Is exactly (or equals)
Matching conditions for Hits are:
• Is greater than
• Is less than
• Is greater than or equal to
• Is less than or equal to


I am thinking of putting the data fetch logic in a pl/sql procedure and call this procedure from my Java code.My question is what is the best way of passing these conditions to Stored procedure so that it knows what it should do.
Any ideas?

[Sudheer Bhat]

Jitesh,

Why not build the SQL query based on the conditions selected?

[Jitesh Sinha]

Sudheer Bhat wrote:Jitesh,

Why not build the SQL query based on the conditions selected?

Because I think it is better to fetch the data through Stored Procedure.I just want to tell Stored Procedure what are the conditions selected by user.

[Jitesh Sinha]

For simplification,let us assume that I construct SQL queries based on User selected rules.I need to store these rules if user clicks on a button called 'Save Filter' .
How do I store it so that user can load a specific filter if he wants?One rule can be ANDed/ORed with other rules as well.
For example one can shoose following criteria - URL contains "blah" AND Hits greater than 500.And then he can choose to store this as John_Rules.
Later when he wants to load the same filter,the conditions URL contains "blah" AND Hits greater than 500 should be implied.


It seems to be quite complicated.Any pointers?

[Paul Clapham]

Well, yes, it is quite complicated. I don't know what kind of "pointers" you're looking for, though. Perhaps you can explain what you have done so far and what you are unable to do.

[Jitesh Sinha]

I am trying to figure out the database design for this - after this is done I can think about putting logic in my Java code to construct queries.

Here is the table structure -

Table : User_Rules
Columns :-

USER_RULE_ID (just primary key)
ENTITY(e.g. URL or Hits)
CONDITION(e.g. greater than or equal to)
PARAMETER_VALUE(e.g. "somestring" or 150)

I cannot figure out how to join two rules(AND/OR).The above structure can store one rule per row but it cannot join two or more than two rules.

Is this problem outside the scope of this site(since it is currently only talking about DB design)?

[Sagar Hebra]

Let me try this -

If I understand correctly what you have is -
Rules and conditions, Filter can have combination of multiple rules and conditions.

I would suggest to have two tables with one-to-many relationship. Parent stores the rule id and for each rule you have one or more records in child table.

rule1 - url contains blah1
rule2 - hits greater than 500
rule3 - url contains blah2 and hits greater than 1000

Now if user selects two rules, rule1 and rule 3 then building of the query from the returned LIST should have below logic

for different rule id keep on adding OR i.e. (url contains blah2 AND hits greater than 1000) OR url contains blah1

I may be completely wrong to understand your scenario...

[Paul Clapham]

Jitesh Sinha wrote:I am trying to figure out the database design for this - after this is done I can think about putting logic in my Java code to construct queries.

Very good -- you're on the right path.

I cannot figure out how to join two rules(AND/OR). The above structure can store one rule per row but it cannot join two or more than two rules.

Yes, that's true. So perhaps you need another structure for the case where the user asks for more than one rule to be joined?

(You did say it seemed to be complicated, and you were right about that too.)

[chris webster]

The "best way"? Well, I'd probably want to let somebody else do the hard work, so I'd use an ORM like Hibernate which supports building query objects with criteria. But check with a Hibernate person on this, as I'm just an Oracle bloke!

But I had to do something similar (i.e. the hard way) a while ago, and the approach I came up with was to use a simple XML snippet to pass the query parameters into the query procedure. Using an XML string means you can just pass a VARCHAR2 or CLOB parameter, instead of having to mess about constructing and unpacking arrays of structured objects etc. This reduces the coupling between the Java and Oracle code, and it will be easier to change it later when you discover all the holes in your original design . Also, Java is good at XML, and there are some useful XML tools in Oracle e.g. the DBMS_XMLDOM package to help you extract info from XML on the DB side as well.

Unfortunately, I don't have a copy of the XML format or the code I used for this, so I can only offer a few tips here.

You'll need to work out what your individual conditions look like e.g. [column][operator][value] and try to simplify things so that these are fairly standard e.g. your "contains", "starts with" and "ends with" operators are all variations on LIKE. Create some kind of XML format for a single query condition that meets your needs (or see if you can find one on the web).

As for NOT, AND and OR, you can do this by wrapping the relevant conditions in the <NOT>, <AND> or <OR> tags, something like this:

<WHERE>
   <OR>
     <AND>
       <CONDITION1...../>
       <NOT>
          <CONDITION2..../>
        </NOT>
     </AND>
     <CONDITION3..../>
   </OR>
</WHERE>

This would be the equivalent of "WHERE ( ( (condition 1) AND NOT (condition 2) ) OR (condition 3) )".

So you would construct the XML snippet in your Java interface, based on the user's chosen selection criteria, then pass it into the PL/SQL as a CLOB or VARCHAR2 parameter. Inside PL/SQL you can build a generic "query-converter" function that will take the XML and return a properly formatted SQL WHERE clause that you can then use in a normal query e.g. build the complete SQL query string and execute it with a REF CURSOR.

Make sure you put in plenty of brackets around your conditions when you convert them to a WHERE clause, as shown above, as it will help you to keep track of the logic. Also, be careful of things like quotes, upper/lower case, etc.

If you keep your XML format and your queries simple, something like this will probably do what you want without being too hard to maintain.

Of course, there are also tools like XQuery, but this is aimed more at querying XML documents rather than normal DB tables.

But if I were you, I'd really think about using a standard tool like Hibernate instead.

[Jitesh Sinha]

Thanks Paul and Sagar.
Thanks Chris for detailed solution.Need to see if I can implement what you suggested.