Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Mime-Version: 1.0 (Apple Message framework v613)
To: torque-user@db.apache.org
Message-Id: <B0D839C0-9F81-11D8-8677-000A95A0F7F4@chialin.de>
Content-Type: multipart/alternative; boundary=Apple-Mail-1-330138696
From: Chialin Tsai <tsai@chialin.de>
Subject: Problem using doDelete with multiple criteria fields
Date: Fri, 7 May 2004 01:20:37 +0800

--Apple-Mail-1-330138696
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed

I have encountered the problem of doDelete() function is only taking 
the first field and ignoring the rest of fields in the criteria.


I want to execute the following sql statement  "DELETE FROM ATABLE 
WHERE ATABLE.COLUMN1=1 OR ATABLE.COLUMN2=1" by using Criteria

Criterion Technique
By using Criterion technique, My code is like this:
Criteria criteria = new Criteria();
Criteria.Criterion criterion1 = 
criteria.getNewCriterion(ATABLE.COLUMN1, new Integer(1), 
Criteria.EQUAL);
Criteria.Criterion criterion2 = 
criteria.getNewCriterion(ATABLE.COLUMN2, new Integer(1), 
Criteria.EQUAL);
criteria.add(criterion1.or(criterion2));

However, doDelete() function in BasePeer only takes the first criterion 
as criteria for deletion and ignore the second one.  I have checked the 
Torque log file, the where closure for doDelete() function is only the 
first criteria field.

I also checked out the source code for doDelete() function in BasePeer. 
  The function goes through the keyset in Criteria object and produce 
only one criterion.  Because I used criteria.add() to add two 
criterions, so basically doDelete() function considers the criteria as 
one criteria and taking only the first part of criteria.  Therefore, 
what actually get executed was  "DELETE FROM ATABLE WHERE 
ATABLE.COLUMN1=1"

Criteria.or() Technique
Using Criteria.or() technique, I have code like:
Criteria criteria = new Criteria();
criteria.add(ATABLE.COLUMN1, 1);
criteria.or(ATABLE.COLUMN2, 1);

I have followed the example I saw on Japanese version of Jakata project 
website(http://www.ad.cyberhome.ne.jp/~milmil/java/torque-search.html)

By checking Torque log file, the result sql statement I actually get is:
"DELETE FROM ATABLE WHERE ATABLE.COLUMN1=1 AND ATABLE.COLUMN2=1"

Conclusion
My conclusion is that Torque Criteria API is not able to generate a 
simple multiple comparisons with "OR" (gate).

My current solution is to do delete separately:
Criteria criteria = new Criteria();
criteria.add(ATABLE.COLUMN1, 1);
doDelete(....)

criteria.clear();
criteria.add(ATABLE.COLUMN2, 1);
doDelete(....)


Better Solution
I would like to know if it is really a functionality that is not 
provided in Torque Criteria API or I just did not find it.  If you have 
solution or better workaround, please let me know.

Thank you.

Chialin

--Apple-Mail-1-330138696--