db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Richards" <bren...@designuk.com>
Subject Building complex negated queries with Criteria
Date Thu, 12 Feb 2004 11:10:16 GMT

Hi, 

I posted this issue to the users list a couple of weeks ago and noone
had any ideas so I'm posting here to see if the developers have any
suggestions....

Here's my original post:

I've discovered some odd behavior exhibited by the broker criteria API
and I was wondering if anyone had come across this. 

My system is dynamically building complex queries by building criteria
objects one at a time and then pulling them together with addAndCriteria
and addOr Criteria. 

So my code looks something like this pseudocode:

Criteria criteria = new Criteria(); // create criteria object

while (myQuery.hasMoreElements()) {

	Criteria c1 = new Criteria(); // new criteria
	C1.addEqualTo("myProperty", myValue);
	if (myNegation) {
		c1.setNegative(true);
	}
	criteria.addAndCriteria(c1);
}   

So this code loops through some objects that I've created and adds a
number of criteria to a central criteria object. Each criteria may be
negated. 

My problem is that on the first call to addAndCriteria, a Criteria
object is not added to my main criteria, only a ValueCriteria. 

So say I want to do: 
WHERE  (NOT forename=brendan) AND (NOT surname IS NULL)
My code would loop twice setting values and setNegative(true) for each
sub-criteria.  

Looking at my final criteria object via getElements and printing the
types 
Using this code (with Logger being a simple class that writes output to
a file):

Enumeration critElements = criteria.getElements();
    while (critElements.hasMoreElements()) {
        Object testCrit = critElements.nextElement();
        Logger.debugLog("type "+testCrit.getClass().getName());
        if (testCrit instanceof Criteria) {
            Criteria tc = (Criteria) testCrit;
            if (tc.isNegative()) { 
			Logger.debugLog("Criteria is negative");
		}
        }
    } 

I get the following result in my logfile:
type org.apache.ojb.broker.query.ValueCriteria
type org.apache.ojb.broker.query.Criteria
Criteria is negative


Using criteria.toString() I get:

[forename = brendan, [surname IS NULL ]]




What this means is that I have completely LOST the isNegative value for
the first criteria I add. The containing criteria object is not negative
(I don't want it to be either I want to control the negativity of each
criteria I add).

Why does criteria.addAndCriteria convert a Criteria to ValueCriteria
when adding the first criteria losing the negative switch?

What I'd expect to see from criteria.toString() after the above code is:
[[forername = brendan], [surname IS NULL ]] - with both elements being
instances of Criteria.

Subsequent calls to addAndCriteria() after the first add Criteria
objects as you would expect. [ forname=brendan, [surname IS NULL],
[email IS NULL] ]

There is a simple dodgy workaround I could do where the first criteria I
add always evaluates to TRUE but this is far from ideal.


</end original post>



I've since had a look at the Criteria code and can see that the
addAndCriteria and addOrCriteria functions do indeed act very
differently for the first criteria you add. 

public void addAndCriteria(Criteria pc)
	{
		// by combining a second criteria by 'AND' the existing
criteria needs to be enclosed
		// in parenthesis
		if (!m_criteria.isEmpty())
		{
			this.setEmbraced(true);
			pc.setEmbraced(true);
			pc.setType(AND);
			addCriteria(pc);
		}
		else
		{
                        
			setEmbraced(false);
			setType(NONE); // root object
            addCriteria(pc.getCriteria());
                        //addCriteria(pc);
			orderby.addAll(pc._getOrderby());
			groupby.addAll(pc._getGroupby());
		}
	}

For the first criteria you add to another containing criteria, the
negation is lost and also the ordery and groupby settings are passed up
to the containing criteria. I'm not sure this is the behaviour I'd want
as I'll potentially want to build very complex criteria from a number of
sub-criteria to a number of levels and order-by only really applies to
the top level.

I've tried playing with this code changing the block for an empty
criteria to add a full embraced criteria object but this always fails
with an SQL error. I suspect that where a criteria resolves to SQL it
needs to have a simple criteria object (such as a ValueCriteria) as the
first element is this correct. 

I've come up with a quick-and-dirty solution to my problem by adding an
alternative addAndCriteria variant that inserts a sql criteria into the
first element:

/**
	 * ANDs two sets of criteria together but with first remaining
embraced.
         * Alternative version fixes embraced loss of first critera add
by adding dummy true clause as first element.
         * This doesn't pass up orderby settings for first entry.
         * 
	 *
	 * @param  pc criteria
	 */
	public void addAndCriteriaEmbraced(Criteria pc)
	{
		// by combining a second criteria by 'AND' the existing
criteria needs to be enclosed
		// in parenthesis
		if (!m_criteria.isEmpty())
		{
			this.setEmbraced(true);
			pc.setEmbraced(true);
			pc.setType(AND);
			addCriteria(pc);
		}
		else
		{
                        this.addSql("1>0");
			this.setEmbraced(true);
			pc.setEmbraced(true);
			pc.setType(AND);
			addCriteria(pc);
		}
	}


I'm not too sure about the elegance of this solution and what I may
potentially be breaking...
Also I haven't ruled out the case that I may be conceptually looking at
this criteria api from the wrong angle. 

If anyone has any ideas I'd be grateful for some suggestions. 

Thanks, 



-----------------------------------------
Brendan Richards
Design UK
-----------------------------------------


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message