ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From TNO <tno...@free.fr>
Subject Re: Dynamic condition/fields
Date Wed, 21 Sep 2005 07:50:53 GMT
Hello,
I have an object WhereOrderBy which create a string for my dynamic 
condition to a query, then I give the string to the Ibatis Query.
Is that a good way to do like that ?

ex. :

    WhereOrderBy wob = new WhereOrderBy();
    wob.getWhere().addAnd(new Equality("TOTO", new Integer(10)));
    wob.getWhere().addAnd(new Equality("TATA", "hello"));
    wob.getWhere().addAnd(new IsTrue("TITI"));
    wob.getWhere().addOr(new Between("nomCol", new
    GregorianCalendar(1980, 9, 4), new Date(85, 4, 25)));
    wob.getOrderBy().addAsc(new String[]{"TOTO, TITI"});


wob.toString() give me :

    WHERE     TOTO = 10
     AND     TATA = 'hello'
     AND     TITI IS TRUE
     OR     nomCol BETWEEN to_timestamp('1980-10-04 00:00:00',
    'YYYY-MM-DD HH:MI:SS') AND to_timestamp('1985-05-25 00:00:00',
    'YYYY-MM-DD HH:MI:SS')
    ORDER BY TOTO, TITI ASC


and for ibatis

      <select id="getDivers" parameterClass="string"
    resultMap="diversResult">
        <![CDATA[
    SELECT     i_divers, s_code_divers, s_libelle, b_soumis
    FROM     divers
    $whereStr$
        ]]>
      </select>

thanks

Mathew Samuel a écrit :

>Looks like I might have to do it with inline
>parameters for now.
>
>To add to Larry's comments, it WOULD be convenient to
>even have a simple iterator that feeds off a map, to
>generate the required pairs.
>
>For example,
>
>
>
>Assume FIELD1, FIELD2 & FIELD3 exists in the database
>for TABLE1, and some code just added fields FIELD3,
>FIELD4 & FIELD5 to this table.
>
>
>// First we construct the LHS and RHS query pairs.
>// The keys should iterate off of a list or something
>keyValueMap.put("FIELD4", objectA.getAttribute4());
>keyValueMap.put("FIELD5", objectA.getAttribute5());
>keyValueMap.put("FIELD6", objectB.getAttribute1());
>
>// Then, the conditions for some of the pairs
>aConditionMap.put("FIELD4", "<");
>aConditionMap.put("FIELD5", "IS NOT LIKE");
>
>// Now, we set the object that is passed to iBATIS
>someObject.setProperty1(objectA.getAttribute1());
>someObject.setProperty2(objectA.getAttribute2());
>someObject.setProperty3(objectA.getAttribute3());
>
>someObject.setKeyValueMap(keyValueMap);
>someObject.setConditionhMap(aConditionMap);
>
>queryForObject("getSomething", someObject);
>
>
>
>  <select id="getSomething" resultMap="..." 
>parameterMap="theMap">
>    select ...
>    from TABLE1
>    where FIELD1 = #property1#
>    and FIELD2 = #property2#
>    and FIELD3 = #property3#
>    <iterate prepend="and" map="aHashMap"
>condition="conditionMap" conjunction="and" />
>  </select>
>
>
>Or if you want to push it,
>    <iterate prepend="and" map="aHashMap"
>condition="conditionMap" conjunction="conjunctionMap"
>/>
>
>
>Missing entries in the map, null maps and absent XML
>attributes could attribute to a default condition of
>'=', and default a conjunction of 'and'. The framework
>would still use reflection to validate the value
>types.
>
>
>
>Just a thought...
>
>
>--- Larry Meadors <larry.meadors@gmail.com> wrote:
>
>  
>
>>Honestly, I can say in the 3 years (?!) i have been
>>using iBATIS, I
>>have never needed to do that..but at the same time,
>>I can see where it
>>might be useful.
>>
>>I can see a MUCH larger use in this way - I spent
>>last weekend writing
>>a DAO implementation for an LDAP directory...LDAP
>>queries are every
>>bit as goofy as SQL. Had I been able to use SQL maps
>>to create my
>>queres...it would have been much simpler. Not for
>>mapping parameters,
>>just for dynamic queries.
>>
>>I know when I used hibernate, this was a huge PITA,
>>using string
>>buffers and all that crap just for dynamic queries -
>>blargh! If we
>>could provide a way to build dynamic HQL for our
>>hibernate using
>>friends, they might just use it.
>>
>>I know in cases where I needed a ResultSet (damn
>>you, Oracle!) I would
>>use it to generate dynamic SQL.
>>
>>Here is what I'd like to see:
>> 1) ability to get the query (maybe with a List of
>>the parameters to the query)
>> 2) ability to stop processing (w/o blowing up)
>> 3) ability to change the query (and parameters) and
>>continue
>>
>>Larry
>>
>>
>>On 9/20/05, Mathew Samuel <grandarchville@yahoo.com>
>>wrote:
>>    
>>
>>>Problem 3 (Q3) is not for logging/debugging
>>>      
>>>
>>purposes.
>>    
>>
>>>It's to actually modify (mostly append) the
>>>      
>>>
>>generated
>>    
>>
>>>SQL text, say, to add additional SQL constructs
>>>      
>>>
>>before
>>    
>>
>>>it is sent to the execution engine.
>>>
>>>
>>>Thanks
>>>
>>>
>>>--- Ron Grabowski <rongrabowski@yahoo.com> wrote:
>>>
>>>      
>>>
>>>>Q1.
>>>>You could pass your parameters in as a Map and
>>>>        
>>>>
>>use a
>>    
>>
>>>>literal:
>>>>
>>>> <isNotNull prepend="and" property="attribute1">
>>>>  FIELD1 $likeClause$ #attribute1#
>>>> </isNotNull>
>>>>
>>>>Q2.
>>>>You could use a literal for this as well.
>>>>        
>>>>
>>Remember
>>    
>>
>>>>that ibatis isn't a
>>>>sql parser, it doesn't know anything about your
>>>>column names. It sees
>>>>everything as text so there's nothing like this:
>>>>
>>>> // FAKE SYNTAX, DOES NOT EXIST
>>>> Statement statement =
>>>>
>>>>
>>>>        
>>>>
>sqlMapper.getStatement("GetOne").addColumn("DateAdded");
>  
>
>>>> List list = statement.select();
>>>>
>>>>Q3.
>>>>All sql statements to/from the database are
>>>>        
>>>>
>>logged.
>>    
>>
>>>>You can capture
>>>>those and do whatever you want with them.
>>>>
>>>>--- Mathew Samuel <grandarchville@yahoo.com>
>>>>        
>>>>
>>wrote:
>>    
>>
>>>>>Q1. What's the best way to dynamically alter
>>>>>          
>>>>>
>>('='
>>    
>>
>>>>or
>>>>        
>>>>
>>>>>'like') to ('!=' or 'not like') in a
>>>>>          
>>>>>
>>>>pre-constructed
>>>>        
>>>>
>>>>>option ? e.g.
>>>>>
>>>>>  <isNotNull prepend="and"
>>>>>          
>>>>>
>>property="attribute1">
>>    
>>
>>>>>    FIELD1 like #attribute1#
>>>>>  </isNotNull>
>>>>>
>>>>>can become
>>>>>
>>>>>  <isNotNull prepend="and"
>>>>>          
>>>>>
>>property="attribute1">
>>    
>>
>>>>>    FIELD1 not like #attribute1#
>>>>>  </isNotNull>
>>>>>
>>>>>or vice-versa.
>>>>>
>>>>>
>>>>>
>>>>>Q2. Is there a way to dynamically add fields
>>>>>          
>>>>>
>>to an
>>    
>>
>>>>SQL
>>>>        
>>>>
>>>>>statement (select/update/insert/delete) ? e.g.
>>>>>          
>>>>>
>>A
>>    
>>
>>>>UI
>>>>        
>>>>
>>>>>customization that indirectly allows adding a
>>>>>          
>>>>>
>>>>field to
>>>>        
>>>>
>>>>>an existing table, could then have statements
>>>>>          
>>>>>
>>that
>>    
>>
>>>>>operate on those new fields.
>>>>>
>>>>>
>>>>>
>>>>>Q3. Does Ibatis allow an API call to retrieve
>>>>>          
>>>>>
>>the
>>    
>>
>>>>>constructed SQL right before the execution ?
>>>>>          
>>>>>
>>>>(Perhaps
>>>>        
>>>>
>>>>>for achieving the problem above)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>          
>>>>>
>>__________________________________________________
>>    
>>
>>>>>Do You Yahoo!?
>>>>>Tired of spam?  Yahoo! Mail has the best spam
>>>>>          
>>>>>
>>>>protection around
>>>>        
>>>>
>>>>>http://mail.yahoo.com
>>>>>
>>>>>          
>>>>>
>>>>        
>>>>
>>>
>>>
>>>__________________________________
>>>Yahoo! Mail - PC Magazine Editors' Choice 2005
>>>http://mail.yahoo.com
>>>
>>>      
>>>
>
>
>
>	
>		
>______________________________________________________ 
>Yahoo! for Good 
>Donate to the Hurricane Katrina relief effort. 
>http://store.yahoo.com/redcross-donate3/ 
>
>
>
>
>  
>


-- 
Il n'y a pas de mauvais langage, il n'y a que des bons programmeurs...


Mime
View raw message