portable selectKeys

[Paul Barry]

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>