ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Is this possible in Dynamic SQL
Date Mon, 21 May 2007 13:43:30 GMT
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 <filepillow@yahoo.com> 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.
>
>

Mime
View raw message