You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Christine Gerstenmayer <ch...@chello.at> on 2005/07/13 22:30:03 UTC

Howto do this with OJB 1.0.1

Second trial (first was two weeks ago)

Hi all,

please can you help me with this case ?

I want OJB to generate a sqlstatement like this:

SELECT *
FROM AdminLog A0 INNER JOIN CPMUser A1 ON A0.FK_UserID=A1.PK_UserID
WHERE (((A1.FK_InstituteID = 'xxx')
AND A1.Branch LIKE 'ABC\_XYZ' ESCAPE '\')
AND A0.LoginTimestamp >= '2005-06-30 12:30:04')
AND A0.LoginTimestamp <= '2005-06-30 13:04:02'
ORDER BY 1

AdminLog has a reference-descriptor to CPMUser via UserID and CPMUser has a
reference-descriptor to Institute via InstituteID. Branch is a column of
CPMUser

The Problem ist the escapesign and the word ESCAPE

I tried it without ESCAPE:

Criteria c = new Criteria();
c.addEqualTo("ref_AdminLog.ref_Institute." +
Institute.getpk_instituteidSQLName(), instituteId);
c.addLike("ref_User." + CPMUser.getbranchSQLName(), branch);
c.addGreaterOrEqualThan(AdminLog.getlogintimestampSQLName(),fromDate);
c.addLessOrEqualThan(AdminLog.getlogintimestampSQLName(), toDate);
return AbstractPersistency.selectOrderedByCriteria(AdminLog.class,
	  c,
        new String[]{AdminLog.getlogintimestampSQLName()},
        new boolean[]{true});

Works fine, if there is no branch "ABC1XYZ" or so, but how do I get ESCAPE
into this statement ??

Please help.
Thanks in advance

Christine

 

 

          




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

AW: Howto do this with OJB 1.0.1

Posted by Christine Gerstenmayer <ch...@chello.at>.
Hi Armin,
oh, I'm so stupid !
I found the Javadoc of LikeCriteria, but I didn't understand how to use it.
Now I did it how you wrote and it works fine.
No, I didn't see the examples, thanks for the tip.
Thank you very much for your help !
Best regards,
Christine

-----Ursprüngliche Nachricht-----
Von: Armin Waibel [mailto:arminw@apache.org] 
Gesendet: Donnerstag, 14. Juli 2005 00:22
An: OJB Users List
Betreff: Re: Howto do this with OJB 1.0.1

Hi Christine,

I'm not familiar with the query part of OJB. Did you see the examples 
for escape handling in OJB test suite QueryTest#testLikeEscapedCriteria2().

This test look for Person objects containing '%' in the firstname field 
using a specific method to set the escape character:

LikeCriteria.setEscapeCharacter('|');
Criteria crit = new Criteria();
crit.addLike("firstname", "h%|%");
Query q = QueryFactory.newQuery(Person.class, crit);
Collection results = broker.getCollectionByQuery(q);

This will create a query like this:

SELECT A0.ID,A0.FIRSTNAME,A0.LASTNAME FROM PERSON A0 WHERE A0.FIRSTNAME 
LIKE 'h%|%' ESCAPE '|'

regards,
Armin


Christine Gerstenmayer wrote:
> Second trial (first was two weeks ago)
> 
> Hi all,
> 
> please can you help me with this case ?
> 
> I want OJB to generate a sqlstatement like this:
> 
> SELECT *
> FROM AdminLog A0 INNER JOIN CPMUser A1 ON A0.FK_UserID=A1.PK_UserID
> WHERE (((A1.FK_InstituteID = 'xxx')
> AND A1.Branch LIKE 'ABC\_XYZ' ESCAPE '\')
> AND A0.LoginTimestamp >= '2005-06-30 12:30:04')
> AND A0.LoginTimestamp <= '2005-06-30 13:04:02'
> ORDER BY 1
> 
> AdminLog has a reference-descriptor to CPMUser via UserID and CPMUser has
a
> reference-descriptor to Institute via InstituteID. Branch is a column of
> CPMUser
> 
> The Problem ist the escapesign and the word ESCAPE
> 
> I tried it without ESCAPE:
> 
> Criteria c = new Criteria();
> c.addEqualTo("ref_AdminLog.ref_Institute." +
> Institute.getpk_instituteidSQLName(), instituteId);
> c.addLike("ref_User." + CPMUser.getbranchSQLName(), branch);
> c.addGreaterOrEqualThan(AdminLog.getlogintimestampSQLName(),fromDate);
> c.addLessOrEqualThan(AdminLog.getlogintimestampSQLName(), toDate);
> return AbstractPersistency.selectOrderedByCriteria(AdminLog.class,
> 	  c,
>         new String[]{AdminLog.getlogintimestampSQLName()},
>         new boolean[]{true});
> 
> Works fine, if there is no branch "ABC1XYZ" or so, but how do I get ESCAPE
> into this statement ??
> 
> Please help.
> Thanks in advance
> 
> Christine
> 
>  
> 
>  
> 
>           
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 

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



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

Re: Howto do this with OJB 1.0.1

Posted by Armin Waibel <ar...@apache.org>.
Hi Christine,

I'm not familiar with the query part of OJB. Did you see the examples 
for escape handling in OJB test suite QueryTest#testLikeEscapedCriteria2().

This test look for Person objects containing '%' in the firstname field 
using a specific method to set the escape character:

LikeCriteria.setEscapeCharacter('|');
Criteria crit = new Criteria();
crit.addLike("firstname", "h%|%");
Query q = QueryFactory.newQuery(Person.class, crit);
Collection results = broker.getCollectionByQuery(q);

This will create a query like this:

SELECT A0.ID,A0.FIRSTNAME,A0.LASTNAME FROM PERSON A0 WHERE A0.FIRSTNAME 
LIKE 'h%|%' ESCAPE '|'

regards,
Armin


Christine Gerstenmayer wrote:
> Second trial (first was two weeks ago)
> 
> Hi all,
> 
> please can you help me with this case ?
> 
> I want OJB to generate a sqlstatement like this:
> 
> SELECT *
> FROM AdminLog A0 INNER JOIN CPMUser A1 ON A0.FK_UserID=A1.PK_UserID
> WHERE (((A1.FK_InstituteID = 'xxx')
> AND A1.Branch LIKE 'ABC\_XYZ' ESCAPE '\')
> AND A0.LoginTimestamp >= '2005-06-30 12:30:04')
> AND A0.LoginTimestamp <= '2005-06-30 13:04:02'
> ORDER BY 1
> 
> AdminLog has a reference-descriptor to CPMUser via UserID and CPMUser has a
> reference-descriptor to Institute via InstituteID. Branch is a column of
> CPMUser
> 
> The Problem ist the escapesign and the word ESCAPE
> 
> I tried it without ESCAPE:
> 
> Criteria c = new Criteria();
> c.addEqualTo("ref_AdminLog.ref_Institute." +
> Institute.getpk_instituteidSQLName(), instituteId);
> c.addLike("ref_User." + CPMUser.getbranchSQLName(), branch);
> c.addGreaterOrEqualThan(AdminLog.getlogintimestampSQLName(),fromDate);
> c.addLessOrEqualThan(AdminLog.getlogintimestampSQLName(), toDate);
> return AbstractPersistency.selectOrderedByCriteria(AdminLog.class,
> 	  c,
>         new String[]{AdminLog.getlogintimestampSQLName()},
>         new boolean[]{true});
> 
> Works fine, if there is no branch "ABC1XYZ" or so, but how do I get ESCAPE
> into this statement ??
> 
> Please help.
> Thanks in advance
> 
> Christine
> 
>  
> 
>  
> 
>           
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 
> 

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