You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@empire-db.apache.org by Frank Lupo <fr...@email.it> on 2009/04/08 15:41:10 UTC
Query
I have a query:
... WHERE col1=val1 and (col2=val2 or col2=val3)
In DBCommand is possible to specify the "(" ?
Thanks
--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f
Sponsor:
Diventa uno dei magnifici 8! Entra a fare parte della squadra Italiana di Poker Sportivo con PokerClub
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8804&d=8-4
AW: Query
Posted by Rainer Döbele <do...@esteam.de>.
Hi Franco,
interesting (or should I rather say "impressive") query building example.
And of course in this case my previous general answer for handling parenthesis was of no help.
Doing what you want is a bit more tricky in this case, but certainly not unfeasible.
An approach that could work for you might be as follows:
1. The current parenthesis handling is performed by the class DBCompareAndOrExpr - which is of no help for your type of problem. So based on DBCompareAndOrExpr create your own version (and possibly two classes one for AND and one for OR) that do not add any parenthesis at all (just take the code and get rid of anything that has something to do with parenthesis). You may even create a constructor that allows you to add a variable number of arguments like e.g. class MyOrExpr(DBCompareExpr... cmpexprs) and MyAndExpr(DBCompareExpr... cmpexprs).
2. Create a new class for adding only parenthesis and derive it from DBCompareExpr. The class should wrap another DBCompareExpr thus I recommend to provide a constructor like MyParenthesisExpr(DBCompareExpr expr). Then implement the function addSQL() similar to this:
@Override
public void addSQL(StringBuilder buf, long context)
{
buf.append("(");
expr.addSQL(buf, context);
buf.append(")");
}
3. Finally instead of using the and() and or() functions on DBCompareExpr, create instances your classes in your code directly. Similar to this:
cmd.where(new MyAndExpr(new MyParenthesisExpr(new MyOrExpr(col1.is(x), col2.is(y))), col3.is(z));
I know it looks a bit confusing at first, but this is just because I have written it all on one line.
This should solve all your problems with only a little extra code and without modifying any of Empire-db's classes.
(please note: I haven't tried it but I am pretty much sure it would work).
Let me know if this has been helpful to you.
Regards
Rainer
Frank Lupo wrote:
> Re: Query
>
> Hi Rainer,
>
> I implementing application using SmartGWT, using FilterBuilder grid.
> See
> http://www.smartclient.com/smartgwt/showcase/#featured_filter_builder_g
> rid
> Parsing result and trasform in query is more complicated if not exist
> Brackets "(".
> Is possible implementing method "addBrackets" in DBColumnExpr?
>
> Es.
> public DBCompareColExpr addBrackets(Object value)
> {
> return .......
> }
>
> Regards
> Frank
>
> Rainer Döbele ha scritto:
> > Hi Frank,
> >
> > (almost) everything is possible but not always sensible.
> >
> > First, an "OR" in a where clause is very rarely needed and should be
> avoided if possible. In your case e.g. it is much smarter to write:
> >
> > WHERE col1=val1 and col2 in (val2, val3)
> >
> > In Empire-db you can write it like this:
> > cmd.where(col1.is(val1).and((col2.in(new Object[] { val2, val3
> })));
> >
> > Or like this:
> > cmd.where(col1.is(val1));
> > cmd.where(col2.in(new Object[] { val2, val3 }));
> >
> > I recommend the latter since it is much better understandable and you
> can easily enable or disable certain constraints.
> >
> > But to answer your question for cases where it is not possible to
> avoid the "or":
> > The answer is that Empire-db sets parenthesis automatically depending
> on how you specify your constraints.
> >
> > So if you want something like (col1=x and (col2=y or col3=z)) you'd
> write:
> > cmd.where(col1.is(x));
> > cmd.where(col2.is(y).or(col3.is(z));
> >
> > Instead, if you want the condition to be ((col1=x and col2=y) or
> col3=z) you'd write:
> > cmd.where(col1.is(x).and(col2.is(y)).or(col3.is(z));
> >
> > In case of an "or" the parenthesis are automatically set from left to
> right.
> > Hope this has helped.
> >
> > Regards
> > Rainer
> >
> >
> > Frank Lupo wrote:
> >
> >> re: Query
> >>
> >> I have a query:
> >>
> >> ... WHERE col1=val1 and (col2=val2 or col2=val3)
> >>
> >> In DBCommand is possible to specify the "(" ?
> >>
> >> Thanks
> >>
> >>
> >> --
> >> Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3
> e SMTP
> >> autenticato? GRATIS solo con Email.it http://www.email.it/f
> >>
> >> Sponsor:
> >> Diventa uno dei magnifici 8! Entra a fare parte della squadra
> Italiana di
> >> Poker Sportivo con PokerClub
> >> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8804&d=8-4
> >> --------------------------------------------------------------------
> ----
> >>
> >>
> >> Nessun virus nel messaggio in arrivo.
> >> Controllato da AVG - www.avg.com
> >> Versione: 8.0.238 / Database dei virus: 270.11.47/2047 - Data di
> rilascio: 04/08/09 05:53:00
> >>
> >>
>
>
>
>
> --
> Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e
> SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f
>
> Sponsor:
> Servizi Internet sul tuo cellulare con m.email, installalo
> gratuitamente e fai nuove scoperte
> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8921&d=9-4
Re: Query
Posted by Frank Lupo <fr...@email.it>.
Hi Rainer,
I implementing application using SmartGWT, using FilterBuilder grid. See
http://www.smartclient.com/smartgwt/showcase/#featured_filter_builder_grid
Parsing result and trasform in query is more complicated if not exist
Brackets "(".
Is possible implementing method "addBrackets" in DBColumnExpr?
Es.
public DBCompareColExpr addBrackets(Object value)
{
return .......
}
Regards
Frank
Rainer Döbele ha scritto:
> Hi Frank,
>
> (almost) everything is possible but not always sensible.
>
> First, an "OR" in a where clause is very rarely needed and should be avoided if possible. In your case e.g. it is much smarter to write:
>
> WHERE col1=val1 and col2 in (val2, val3)
>
> In Empire-db you can write it like this:
> cmd.where(col1.is(val1).and((col2.in(new Object[] { val2, val3 })));
>
> Or like this:
> cmd.where(col1.is(val1));
> cmd.where(col2.in(new Object[] { val2, val3 }));
>
> I recommend the latter since it is much better understandable and you can easily enable or disable certain constraints.
>
> But to answer your question for cases where it is not possible to avoid the "or":
> The answer is that Empire-db sets parenthesis automatically depending on how you specify your constraints.
>
> So if you want something like (col1=x and (col2=y or col3=z)) you'd write:
> cmd.where(col1.is(x));
> cmd.where(col2.is(y).or(col3.is(z));
>
> Instead, if you want the condition to be ((col1=x and col2=y) or col3=z) you'd write:
> cmd.where(col1.is(x).and(col2.is(y)).or(col3.is(z));
>
> In case of an "or" the parenthesis are automatically set from left to right.
> Hope this has helped.
>
> Regards
> Rainer
>
>
> Frank Lupo wrote:
>
>> re: Query
>>
>> I have a query:
>>
>> ... WHERE col1=val1 and (col2=val2 or col2=val3)
>>
>> In DBCommand is possible to specify the "(" ?
>>
>> Thanks
>>
>>
>> --
>> Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
>> autenticato? GRATIS solo con Email.it http://www.email.it/f
>>
>> Sponsor:
>> Diventa uno dei magnifici 8! Entra a fare parte della squadra Italiana di
>> Poker Sportivo con PokerClub
>> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8804&d=8-4
>> ------------------------------------------------------------------------
>>
>>
>> Nessun virus nel messaggio in arrivo.
>> Controllato da AVG - www.avg.com
>> Versione: 8.0.238 / Database dei virus: 270.11.47/2047 - Data di rilascio: 04/08/09 05:53:00
>>
>>
--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f
Sponsor:
Servizi Internet sul tuo cellulare con m.email, installalo gratuitamente e fai nuove scoperte
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8921&d=9-4
re: Query
Posted by Rainer Döbele <do...@esteam.de>.
Hi Frank,
(almost) everything is possible but not always sensible.
First, an "OR" in a where clause is very rarely needed and should be avoided if possible. In your case e.g. it is much smarter to write:
WHERE col1=val1 and col2 in (val2, val3)
In Empire-db you can write it like this:
cmd.where(col1.is(val1).and((col2.in(new Object[] { val2, val3 })));
Or like this:
cmd.where(col1.is(val1));
cmd.where(col2.in(new Object[] { val2, val3 }));
I recommend the latter since it is much better understandable and you can easily enable or disable certain constraints.
But to answer your question for cases where it is not possible to avoid the "or":
The answer is that Empire-db sets parenthesis automatically depending on how you specify your constraints.
So if you want something like (col1=x and (col2=y or col3=z)) you'd write:
cmd.where(col1.is(x));
cmd.where(col2.is(y).or(col3.is(z));
Instead, if you want the condition to be ((col1=x and col2=y) or col3=z) you'd write:
cmd.where(col1.is(x).and(col2.is(y)).or(col3.is(z));
In case of an "or" the parenthesis are automatically set from left to right.
Hope this has helped.
Regards
Rainer
Frank Lupo wrote:
> re: Query
>
> I have a query:
>
> ... WHERE col1=val1 and (col2=val2 or col2=val3)
>
> In DBCommand is possible to specify the "(" ?
>
> Thanks
>
>
> --
> Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
> autenticato? GRATIS solo con Email.it http://www.email.it/f
>
> Sponsor:
> Diventa uno dei magnifici 8! Entra a fare parte della squadra Italiana di
> Poker Sportivo con PokerClub
> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=8804&d=8-4