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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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...
|
