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 wang lei <wl...@yahoo.com.cn> on 2006/02/12 03:53:15 UTC
dynamic Sql and parameter together
I just build a DAO,which supports ojb ,hibernate.
Now i want to support Ibatis too.
But when i build the parameters,i got a problem.
Here is a sql statement in the xml.
<select id="doSelectUser" parameterClass="java.util.Map" resultMap="userResult">
<![CDATA[
Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T
$Where$ $condition$
]]>
</select>
Here is the java code.
Map t_Map = new HashMap();
t_Map.put("Where", "where");
t_Map.put("condition", new Integer(10));
List t_List = this.sqlMap.queryForList("doSelectUser", t_Map);
Sure,the sql for "select" is
Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where USER_ID>10
the result is right.
Now i change the red code to the following.
t_Map.put("condition", " USER_ID>#userId#");
t_Map.put("userId", new Integer(10));
I want to get a sql
Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where USER_ID>#userId#
Then the ibatis can use parameter of "userId" int the parameter map to replace the "#userId#".
Just like Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where USER_ID>10
This method is useful to build a dynamic sql.
Of course,i just get the first sql.
Who knows how to solve the problem.
---------------------------------
无限容量雅虎相册，原图等大下载，超快速度，赶快抢注！
Re: dynamic Sql and parameter together
Posted by Brandon Goodin <br...@gmail.com>.
One other thing. iBatis does not perform multiple parse sweeps. It
performs one. If you place a #property# into the sql using the literal
syntax $...$ then it will not replace it with the value of a property
in your object.
Brandon
On 2/12/06, Brandon Goodin <br...@gmail.com> wrote:
> The following was sent to me offline in response to my email. I am
> posting it back to the list along with my response. If anyone has
> assistance for Wang Lei please read through the following:
>
> In fact,I know how to use iBatis dynamic SQL capabilites with the
> dynamic tags in <select>.
>
> But i want more flexibility, when we develop a project.
> I have a tool to model the entity and generate ibatis xml and java code.
>
> Some time we need build a complicated query to get data.
> It's not easy to write in the xml, too long,too complicated.
>
> So i just use the same way as ojb and hibernate to handle the query problem.
>
> I don't want to use template to build query.
> Just the developers to build the query with java code.
>
> Does anybody has good ideas about it.
> May be i need to extend some classes of the IBatis to implement it.
>
> ----
>
> I Must add an additional explantion.
> I use "rule" to do CRUD operation instead of config.
>
> If you want insert a record of "USER" table.
> the default id of "insert statement" will be "insertUser".
>
> So the coder just use ibatis like hibernate or ojb.
> They don't know more about the config.
>
> ---
>
> The reason for dynamic sql is to avoid exactly what you are trying to
> do. We are planning to revamp the dynamic sql to provide even less
> tagging. But, for the large majority of dynamic queries out there it
> easier than doing it in java code.
>
> Also, the idea of ibatis is to allow for developers to touch the SQL.
> Not to hide them from it. So, if you do not want your developers
> touching SQL then you should not use iBatis. Do you prevent your
> developers from using HQL? I would hope not. If they are not capable
> of handing SQL or HQL then i'd find more qualified developers ;-)
>
> Anyhow, perhaps someone else will provide you the insight you need
> since you do not desire to use what is already provided.
>
> Cheers
> Brandon
>
> On 2/11/06, Brandon Goodin <br...@gmail.com> wrote:
> > You should likely be using iBatis dynamic SQL capabilites with the
> > dynamic tags in your <select>. You can learn about Dynamic SQL in the
> > manual.
> >
> > Latest Open Office Manual:
> > http://svn.apache.org/repos/asf/ibatis/trunk/java/docs/iBATIS-SqlMaps-2.sxw
> >
> > Brandon
> >
> > On 2/11/06, wang lei <wl...@yahoo.com.cn> wrote:
> > >
> > >
> > > I just build a DAO,which supports ojb ,hibernate.
> > > Now i want to support Ibatis too.
> > > But when i build the parameters,i got a problem.
> > >
> > > Here is a sql statement in the xml.
> > >
> > > <select id="doSelectUser" parameterClass="java.util.Map"
> > > resultMap="userResult">
> > > <![CDATA[
> > > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T
> > > $Where$ $condition$
> > > ]]>
> > > </select>
> > >
> > > Here is t he java code.
> > >
> > > Map t_Map = new HashMap();
> > > t_Map.put("Where", "where");
> > > t_Map.put("condition", new Integer(10));
> > >
> > > List t_List = this.sqlMap.queryForList("doSelectUser", t_Map);
> > >
> > > Sure,the sql for "select" is
> > > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > > USER_ID>10
> > > the result is right.
> > >
> > > Now i change the red code to the following.
> > > &nb sp; t_Map.put("condition", " USER_ID>#userId#");
> > > t_Map.put("userId", new Integer(10));
> > >
> > > I want to get a sql
> > >
> > > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > > USER_ID>#userId#
> > >
> > > Then the ibatis can use parameter of "userId" int the parameter map to
> > > replace the "#userId#".
> > > Just like
> > > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > > USER_ID>10
> > >
> > > This method is useful to build a dynamic sql.
> > >
> > > Of course,i just get the fi rst sql.
> > > Who knows how to solve the problem.
> > >
> > >
> > > ________________________________
> > > 无限容量雅虎相册，原图等大下载，超快速度，赶快抢注！
> > >
> > >
> >
>
Re: dynamic Sql and parameter together
Posted by Brandon Goodin <br...@gmail.com>.
The following was sent to me offline in response to my email. I am
posting it back to the list along with my response. If anyone has
assistance for Wang Lei please read through the following:
In fact,I know how to use iBatis dynamic SQL capabilites with the
dynamic tags in <select>.
But i want more flexibility, when we develop a project.
I have a tool to model the entity and generate ibatis xml and java code.
Some time we need build a complicated query to get data.
It's not easy to write in the xml, too long,too complicated.
So i just use the same way as ojb and hibernate to handle the query problem.
I don't want to use template to build query.
Just the developers to build the query with java code.
Does anybody has good ideas about it.
May be i need to extend some classes of the IBatis to implement it.
----
I Must add an additional explantion.
I use "rule" to do CRUD operation instead of config.
If you want insert a record of "USER" table.
the default id of "insert statement" will be "insertUser".
So the coder just use ibatis like hibernate or ojb.
They don't know more about the config.
---
The reason for dynamic sql is to avoid exactly what you are trying to
do. We are planning to revamp the dynamic sql to provide even less
tagging. But, for the large majority of dynamic queries out there it
easier than doing it in java code.
Also, the idea of ibatis is to allow for developers to touch the SQL.
Not to hide them from it. So, if you do not want your developers
touching SQL then you should not use iBatis. Do you prevent your
developers from using HQL? I would hope not. If they are not capable
of handing SQL or HQL then i'd find more qualified developers ;-)
Anyhow, perhaps someone else will provide you the insight you need
since you do not desire to use what is already provided.
Cheers
Brandon
On 2/11/06, Brandon Goodin <br...@gmail.com> wrote:
> You should likely be using iBatis dynamic SQL capabilites with the
> dynamic tags in your <select>. You can learn about Dynamic SQL in the
> manual.
>
> Latest Open Office Manual:
> http://svn.apache.org/repos/asf/ibatis/trunk/java/docs/iBATIS-SqlMaps-2.sxw
>
> Brandon
>
> On 2/11/06, wang lei <wl...@yahoo.com.cn> wrote:
> >
> >
> > I just build a DAO,which supports ojb ,hibernate.
> > Now i want to support Ibatis too.
> > But when i build the parameters,i got a problem.
> >
> > Here is a sql statement in the xml.
> >
> > <select id="doSelectUser" parameterClass="java.util.Map"
> > resultMap="userResult">
> > <![CDATA[
> > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T
> > $Where$ $condition$
> > ]]>
> > </select>
> >
> > Here is t he java code.
> >
> > Map t_Map = new HashMap();
> > t_Map.put("Where", "where");
> > t_Map.put("condition", new Integer(10));
> >
> > List t_List = this.sqlMap.queryForList("doSelectUser", t_Map);
> >
> > Sure,the sql for "select" is
> > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > USER_ID>10
> > the result is right.
> >
> > Now i change the red code to the following.
> > &nb sp; t_Map.put("condition", " USER_ID>#userId#");
> > t_Map.put("userId", new Integer(10));
> >
> > I want to get a sql
> >
> > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > USER_ID>#userId#
> >
> > Then the ibatis can use parameter of "userId" int the parameter map to
> > replace the "#userId#".
> > Just like
> > Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> > USER_ID>10
> >
> > This method is useful to build a dynamic sql.
> >
> > Of course,i just get the fi rst sql.
> > Who knows how to solve the problem.
> >
> >
> > ________________________________
> > 无限容量雅虎相册，原图等大下载，超快速度，赶快抢注！
> >
> >
>
Re: dynamic Sql and parameter together
Posted by Brandon Goodin <br...@gmail.com>.
You should likely be using iBatis dynamic SQL capabilites with the
dynamic tags in your <select>. You can learn about Dynamic SQL in the
manual.
Latest Open Office Manual:
http://svn.apache.org/repos/asf/ibatis/trunk/java/docs/iBATIS-SqlMaps-2.sxw
Brandon
On 2/11/06, wang lei <wl...@yahoo.com.cn> wrote:
>
>
> I just build a DAO,which supports ojb ,hibernate.
> Now i want to support Ibatis too.
> But when i build the parameters,i got a problem.
>
> Here is a sql statement in the xml.
>
> <select id="doSelectUser" parameterClass="java.util.Map"
> resultMap="userResult">
> <![CDATA[
> Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T
> $Where$ $condition$
> ]]>
> </select>
>
> Here is t he java code.
>
> Map t_Map = new HashMap();
> t_Map.put("Where", "where");
> t_Map.put("condition", new Integer(10));
>
> List t_List = this.sqlMap.queryForList("doSelectUser", t_Map);
>
> Sure,the sql for "select" is
> Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> USER_ID>10
> the result is right.
>
> Now i change the red code to the following.
> &nb sp; t_Map.put("condition", " USER_ID>#userId#");
> t_Map.put("userId", new Integer(10));
>
> I want to get a sql
>
> Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> USER_ID>#userId#
>
> Then the ibatis can use parameter of "userId" int the parameter map to
> replace the "#userId#".
> Just like
> Select USER_ID, USER_NAME, PHOTO, USER_MEMO from DEMO.USER_T where
> USER_ID>10
>
> This method is useful to build a dynamic sql.
>
> Of course,i just get the fi rst sql.
> Who knows how to solve the problem.
>
>
> ________________________________
> 无限容量雅虎相册，原图等大下载，超快速度，赶快抢注！
>
>