As you've seen, iBATIS won't do this (there's only a single pass through
resolving the SQL statement). You could write your statement like this:
select * from $TABLES$ $where_clause$ #abc#
HashMap map = new HashMap();
map.put("TABLES", "USER_FORMS");
map.put("where_clause", "where FormName like");
map.put("abc", "%edit%");
List list = userSvc.dynamicSearch(map);
If you want to be completely general, then you'll need to account for many
different possibilities. Take a look at the code generated by Abator - it
is an example of how to code any arbitray where clause with dynamic SQL.
Jeff Butler
On 5/21/07, filepillow <[EMAIL PROTECTED]> wrote:
Hi,
I would like to find out if the following dynamic sql is possible using
iBatis.
The idea is to first subsititute $where_clause$ with a string which
contains another parameter #abc#. Then inclue abc as part of the parameter
map.
Thanks in advance for any help or advice. :)
With Regards
Terry.
In my Sql Map
---------------------
<select id="selectDynamic" remapResults="true" parameterClass="
java.util.HashMap" resultClass="java.util.HashMap">
select * from $TABLES$ $where_clause$
</select>
In my test codes
------------------------
HashMap map = new HashMap();
map.put("TABLES", "USER_FORMS");
map.put("where_clause", " where FormName like #abc# ");
map.put("abc", "%edit%");
List list = userSvc.dynamicSearch(map);
In the logs i noticed that the sql generated is :
{conn-100006} Preparing Statement: select * from USER_FORMS where
FormName like #abc#
where i would have expected to be:
select * from USER_FORMS where FormName like ?
Part of the Stacktrace:
---------------------------------
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation;
bad SQL grammar [];
--- The error occurred in myapp/dao/ibatis/USERTABLE_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the USERSECURITYTABLE.selectDynamic-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Invalid column name '#abc#'.
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in myapp/dao/ibatis/USERTABLE_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the USERSECURITYTABLE.selectDynamic-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Invalid column name '#abc#'.
------------------------------
Ready for the edge of your seat? Check out tonight's top
picks<http://us.rd.yahoo.com/evt=48220/*http://tv.yahoo.com/>on Yahoo! TV.
