You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by Paul Barry <pa...@nyu.edu> on 2005/03/10 22:17:04 UTC
portable selectKeys
As anyone tried to come up with a portable seleckKey strategy for
iBATIS? I would love to be able to easily switch between HsqlDB, MySQL,
PostgreSQL, and Oracle, but you have those pesky selectKey statements
that are very specific to the target database. If you could have the
selectKey statement refer to somewhere else based on namespace, say like
this:
<insert id="insertProduct" parameterClass="com.domain.Product">
<selectKey statement="selectKeys.product" keyProperty="id" />
insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
values (#id#,#description#)
</insert>
Then you could have this sqlmap:
<sqlMap namespace="selectKeys">
<selectKey id="product" resultClass="int">
SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
</selectKey>
</sqlMap>
Then in your sqlMap-config.xml you would have this:
<sqlMapConfig>
<sqlMap resource="sqlmaps/oracle/selectKeys.xml" />
<sqlMap resource="sqlmaps/product.xml" />
</sqlMapConfig>
Which you could easily switch to "sqlmap/mysql/selectKeys.xml" and then
everything would work the same. The only other problem is that MSSQL
and MySql need to go after the query and Oracle & PostgreSQL have to go
before the query. But you could fix that by adding another property in
the selectKey definition, like this:
<sqlMap namespace="selectKeys">
<selectKey id="product" resultClass="int" execute="before">
SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
</selectKey>
</sqlMap>
And MSSQL would be:
<sqlMap namespace="selectKeys">
<selectKey id="product" resultClass="int" execute="after">
SELECT @@IDENTITY AS ID
</selectKey>
</sqlMap>
Re: portable selectKeys
Posted by Brandon Goodin <br...@gmail.com>.
oops.. i just gathered some more info regarding executing the select
key before and after the update/insert... i guess you don't really
have any choice other than to create sql maps for your different
execution times... one for the before type (oracle) and the other for
the after type (mysql, sqlsevrver). Then you could incorporate the
substituted property value that i mentioned in the last post.
Brandon
On Thu, 10 Mar 2005 15:10:35 -0700, Brandon Goodin
<br...@gmail.com> wrote:
> well if you situation is not too complex you could just make this a
> substituted property.
>
> <sqlMap namespace="selectKeys">
> UPDATE ...
> <selectKey id="product" resultClass="int" execute="after">
> ${myKeyLookupStatement}
> </selectKey>
> </sqlMap>
>
> I realize this isn't ideal. However, we are planning support for jdbc
> 3 generated key functionality. Therefore, it would be redundant to
> expand on the select key and eventually outdated.
>
> Brandon
>
>
> On Thu, 10 Mar 2005 16:17:04 -0500, Paul Barry <pa...@nyu.edu> wrote:
> > As anyone tried to come up with a portable seleckKey strategy for
> > iBATIS? I would love to be able to easily switch between HsqlDB, MySQL,
> > PostgreSQL, and Oracle, but you have those pesky selectKey statements
> > that are very specific to the target database. If you could have the
> > selectKey statement refer to somewhere else based on namespace, say like
> > this:
> >
> > <insert id="insertProduct" parameterClass="com.domain.Product">
> > <selectKey statement="selectKeys.product" keyProperty="id" />
> > insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
> > values (#id#,#description#)
> > </insert>
> >
> > Then you could have this sqlmap:
> >
> > <sqlMap namespace="selectKeys">
> > <selectKey id="product" resultClass="int">
> > SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
> > </selectKey>
> > </sqlMap>
> >
> > Then in your sqlMap-config.xml you would have this:
> >
> > <sqlMapConfig>
> >
> > <sqlMap resource="sqlmaps/oracle/selectKeys.xml" />
> > <sqlMap resource="sqlmaps/product.xml" />
> >
> > </sqlMapConfig>
> >
> > Which you could easily switch to "sqlmap/mysql/selectKeys.xml" and then
> > everything would work the same. The only other problem is that MSSQL
> > and MySql need to go after the query and Oracle & PostgreSQL have to go
> > before the query. But you could fix that by adding another property in
> > the selectKey definition, like this:
> >
> > <sqlMap namespace="selectKeys">
> > <selectKey id="product" resultClass="int" execute="before">
> > SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
> > </selectKey>
> > </sqlMap>
> >
> > And MSSQL would be:
> >
> > <sqlMap namespace="selectKeys">
> > <selectKey id="product" resultClass="int" execute="after">
> > SELECT @@IDENTITY AS ID
> > </selectKey>
> > </sqlMap>
> >
>
Re: portable selectKeys
Posted by Brandon Goodin <br...@gmail.com>.
well if you situation is not too complex you could just make this a
substituted property.
<sqlMap namespace="selectKeys">
UPDATE ...
<selectKey id="product" resultClass="int" execute="after">
${myKeyLookupStatement}
</selectKey>
</sqlMap>
I realize this isn't ideal. However, we are planning support for jdbc
3 generated key functionality. Therefore, it would be redundant to
expand on the select key and eventually outdated.
Brandon
On Thu, 10 Mar 2005 16:17:04 -0500, Paul Barry <pa...@nyu.edu> wrote:
> As anyone tried to come up with a portable seleckKey strategy for
> iBATIS? I would love to be able to easily switch between HsqlDB, MySQL,
> PostgreSQL, and Oracle, but you have those pesky selectKey statements
> that are very specific to the target database. If you could have the
> selectKey statement refer to somewhere else based on namespace, say like
> this:
>
> <insert id="insertProduct" parameterClass="com.domain.Product">
> <selectKey statement="selectKeys.product" keyProperty="id" />
> insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
> values (#id#,#description#)
> </insert>
>
> Then you could have this sqlmap:
>
> <sqlMap namespace="selectKeys">
> <selectKey id="product" resultClass="int">
> SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
> </selectKey>
> </sqlMap>
>
> Then in your sqlMap-config.xml you would have this:
>
> <sqlMapConfig>
>
> <sqlMap resource="sqlmaps/oracle/selectKeys.xml" />
> <sqlMap resource="sqlmaps/product.xml" />
>
> </sqlMapConfig>
>
> Which you could easily switch to "sqlmap/mysql/selectKeys.xml" and then
> everything would work the same. The only other problem is that MSSQL
> and MySql need to go after the query and Oracle & PostgreSQL have to go
> before the query. But you could fix that by adding another property in
> the selectKey definition, like this:
>
> <sqlMap namespace="selectKeys">
> <selectKey id="product" resultClass="int" execute="before">
> SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
> </selectKey>
> </sqlMap>
>
> And MSSQL would be:
>
> <sqlMap namespace="selectKeys">
> <selectKey id="product" resultClass="int" execute="after">
> SELECT @@IDENTITY AS ID
> </selectKey>
> </sqlMap>
>