You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by filepillow <fi...@yahoo.com> on 2007/05/21 11:04:09 UTC
Is this possible in Dynamic SQL
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 on Yahoo! TV.
Re: Is this possible in Dynamic SQL
Posted by filepillow <fi...@yahoo.com>.
Thanks for your help, Jeff!
=)
With Regards,
Terry
Jeff Butler <je...@gmail.com> wrote: 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
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.
Re: Is this possible in Dynamic SQL
Posted by filepillow <fi...@yahoo.com>.
Hi,
There's one more thing I would like to ask.
Is there an intention for iBatis (version 3, maybe?) to include a second pass to resolve new parameters introduced?
Having a optional 2nd pass seems like quite a useful use case. Especially if the where clause can be quite different. If 2nd pass is available, then we can have a really generic way of dealing with various conditions.
Thanks for any inputs.
With Regards,
Terry.
Jeff Butler <je...@gmail.com> wrote: 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 <fi...@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 on Yahoo! TV.
---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
Re: Is this possible in Dynamic SQL
Posted by Jeff Butler <je...@gmail.com>.
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 <fi...@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.
>
>