You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Gilles Schlienger <s_...@yahoo.com> on 2008/03/03 19:14:26 UTC

Dynamic request with null values and AND

Hi all,

I'm using iBATIS in my application (and I love it) and my question is about using dynamic requests for an optimized research SQL request.

I want to do the following:
- if parameters are not null have dynamic WHERE clauses merged with my join criterias
- if parameters are null, have just my join criterias

My problem is that if all my dynamic attributes are null, I get a query in the form: SELECT XXX WHERE AND YYY and the query WHERE AND does not work...

Would there be any clean way to ovoid this ?
Thanks in advance
Gilles

>>> Here is my query:

<select id="selectRechercheUtilisateurs"
        parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur" resultMap="utilisateurResultMap">
            select *
            from TABLE_1,TABLE_2
            where
            <dynamic>
                <isNotNull prepend="AND" property="cabinetId">
                    RTC_FK_CABINET_CBT_ID = #cabinetId#
                    AND RTC_FK_UTILISATEUR_USR_ID = USR_ID
                </isNotNull>
            </dynamic>
            and RCC_FK_UTILISATEUR_USR_ID = USR_ID
            and USR_FK_CRCC_CRG_ID = CRG_ID
    </select>    
    




      _____________________________________________________________________________ 
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail http://mail.yahoo.fr

Re: Dynamic request with null values and AND

Posted by Larry Meadors <la...@gmail.com>.
Why do you have the prepend="AND" there?

<dynamic>
  <isNotNull property="cabinetId">
    RTC_FK_CABINET_CBT_ID = #cabinetId# AND
    RTC_FK_UTILISATEUR_USR_ID = USR_ID AND
  </isNotNull>
</dynamic>

Larry

RE: Dynamic request with null values and AND

Posted by ivan dobosz <gh...@gmail.com>.
It is possible to do the same thing with Example Classes ?
I have two table with join and I would like to keep all the criterias in
there example class.
But I don't know how use parameterClass in this way ?
No copy paste and VIEW required ...



Vinaya Tirikkovalluru wrote:
> 
> Hi Gilles,
> 
> Try
> 
> <select id="selectRechercheUtilisateurs"
>         parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur"
> resultMap="utilisateurResultMap">
>             select *
>             from TABLE_1,TABLE_2
>            <dynamic prepend="WHERE" >
>                 <isNotNull prepend="AND" property="cabinetId">
>                     RTC_FK_CABINET_CBT_ID = #cabinetId#
>                     AND RTC_FK_UTILISATEUR_USR_ID = USR_ID
>                 </isNotNull>
>             </dynamic>
>             and RCC_FK_UTILISATEUR_USR_ID = USR_ID
>             and USR_FK_CRCC_CRG_ID = CRG_ID
>     </select>
> 
> 
> Vinaya
> 
> 
> -----Original Message-----
> From: Gilles Schlienger [mailto:s_gilou@yahoo.com] 
> Sent: Monday, March 03, 2008 1:14 PM
> To: user-java@ibatis.apache.org
> Subject: Dynamic request with null values and AND
> 
> Hi all,
> 
> I'm using iBATIS in my application (and I love it) and my question is
> about using dynamic requests for an optimized research SQL request.
> 
> I want to do the following:
> - if parameters are not null have dynamic WHERE clauses merged with my
> join criterias
> - if parameters are null, have just my join criterias
> 
> My problem is that if all my dynamic attributes are null, I get a query
> in the form: SELECT XXX WHERE AND YYY and the query WHERE AND does not
> work...
> 
> Would there be any clean way to ovoid this ?
> Thanks in advance
> Gilles
> 
>>>> Here is my query:
> 
> <select id="selectRechercheUtilisateurs"
>         parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur"
> resultMap="utilisateurResultMap">
>             select *
>             from TABLE_1,TABLE_2
>             where
>             <dynamic>
>                 <isNotNull prepend="AND" property="cabinetId">
>                     RTC_FK_CABINET_CBT_ID = #cabinetId#
>                     AND RTC_FK_UTILISATEUR_USR_ID = USR_ID
>                 </isNotNull>
>             </dynamic>
>             and RCC_FK_UTILISATEUR_USR_ID = USR_ID
>             and USR_FK_CRCC_CRG_ID = CRG_ID
>     </select>    
>     
> 
> 
> 
> 
>  
> ________________________________________________________________________
> _____ 
> Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo!
> Mail http://mail.yahoo.fr
> 
> 
> This electronic message is intended only for the use of the individual(s)
> or entity(ies) named above and may contain information which is privileged
> and/or confidential.  If you are not the intended recipient, be aware that
> any disclosure, copying, distribution, dissemination or use of the
> contents of this message is prohibited.  If you received this message in
> error, please notify the sender immediately.
> 
> 

-- 
View this message in context: http://www.nabble.com/Dynamic-request-with-null-values-and-AND-tp15810054p18870901.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.

RE: Dynamic request with null values and AND

Posted by Vinaya Tirikkovalluru <vi...@converge.com>.
Hi Gilles,

Try

<select id="selectRechercheUtilisateurs"
        parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur"
resultMap="utilisateurResultMap">
            select *
            from TABLE_1,TABLE_2
           <dynamic prepend="WHERE" >
                <isNotNull prepend="AND" property="cabinetId">
                    RTC_FK_CABINET_CBT_ID = #cabinetId#
                    AND RTC_FK_UTILISATEUR_USR_ID = USR_ID
                </isNotNull>
            </dynamic>
            and RCC_FK_UTILISATEUR_USR_ID = USR_ID
            and USR_FK_CRCC_CRG_ID = CRG_ID
    </select>


Vinaya


-----Original Message-----
From: Gilles Schlienger [mailto:s_gilou@yahoo.com] 
Sent: Monday, March 03, 2008 1:14 PM
To: user-java@ibatis.apache.org
Subject: Dynamic request with null values and AND

Hi all,

I'm using iBATIS in my application (and I love it) and my question is
about using dynamic requests for an optimized research SQL request.

I want to do the following:
- if parameters are not null have dynamic WHERE clauses merged with my
join criterias
- if parameters are null, have just my join criterias

My problem is that if all my dynamic attributes are null, I get a query
in the form: SELECT XXX WHERE AND YYY and the query WHERE AND does not
work...

Would there be any clean way to ovoid this ?
Thanks in advance
Gilles

>>> Here is my query:

<select id="selectRechercheUtilisateurs"
        parameterClass="xxx.xxx.xxx.xxx.recherche.RechercheUtilisateur"
resultMap="utilisateurResultMap">
            select *
            from TABLE_1,TABLE_2
            where
            <dynamic>
                <isNotNull prepend="AND" property="cabinetId">
                    RTC_FK_CABINET_CBT_ID = #cabinetId#
                    AND RTC_FK_UTILISATEUR_USR_ID = USR_ID
                </isNotNull>
            </dynamic>
            and RCC_FK_UTILISATEUR_USR_ID = USR_ID
            and USR_FK_CRCC_CRG_ID = CRG_ID
    </select>    
    




 
________________________________________________________________________
_____ 
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo!
Mail http://mail.yahoo.fr


This electronic message is intended only for the use of the individual(s) or entity(ies) named above and may contain information which is privileged and/or confidential.  If you are not the intended recipient, be aware that any disclosure, copying, distribution, dissemination or use of the contents of this message is prohibited.  If you received this message in error, please notify the sender immediately.