ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mathew Samuel <grandarchvi...@yahoo.com>
Subject Re: Dynamic condition/fields
Date Tue, 20 Sep 2005 20:27:21 GMT

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/ 


Mime
View raw message