ibatis에서 insert하고 id값 받기 (DB별)

출처 :

http://opensource.atlassian.com/confluence/oss/display/IBATIS/Environment+Specific+Information

Environment Specific Solutions

Oracle

BLOBs/CLOBs

As of release 2.0.9, iBATIS includes default BLOB/CLOB handlers. If you are using an older version of Oracle (pre-10g), then you might have to write your own Custom Tag Handler to access the proprietary Oracle API to work with LOBs.

Some selectKey examples

<insert id="insert" parameterClass="customer">
  <selectKey resultClass="int" keyProperty="id">
    select someSequence.NEXTVAL as "id" from dual
  </selectKey>
  insert into Customer (id, name)
  values (#id#, #name#)
</insert>

An example of using selectKey with a parameterMap.
The selectKey stanza is executed and the result is used to set the property 'pkey' of the parameter object,
then the parameter map is invoked.

<insert id="insert2" parameterClass="Entity" parameterMap="insert-paramMap">
   <selectKey resultClass="int" keyProperty="pkey"> SELECT seq.nextval FROM DUAL </selectKey>
   INSERT INTO EntityTable (PKEY, DATA) VALUES (?, ?)
</insert>

LONG

I was having trouble using an Oracle LONG column with an iBatis sqlmap.  I had defined the jdbcType as a LONGVARCHAR in the sqlmap, but iBatis was still attempting to call getCLOB on the oracle jdbc driver's LongAccessor class.  The solution to this was to explicitly use the StringTypeHandler on the column, as follows:

Using the StringTypeHandler on an Oracle LONG column - attempt #1 (failed)

<result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />
<result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>

This correctly resulted in a call to LongAccessor.getString() in the oracle jdbc driver.  However, it threw up a new problem, "Stream has already been closed". 

The solution was to change the ordering of the columns in the result map so that the LONG column was accessed first, as follows:

Using the StringTypeHandler on an Oracle LONG column - attempt #2 (succeeded)

<result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>
<result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />

So in summary, to use an Oracle LONG column, the following worked for me:

1. Declare the jdbcType attribute as "LONGVARCHAR" in the result map
2. Use a typeHandler attribute of "com.ibatis.sqlmap.engine.type.StringTypeHandler"
3. Ensure the LONG column is the first declared column in the result map.

Oracle Paging

An example of how to select a subset of an ordered list. The keys added to the parameter map over and above the abator generated ones (used by the 'Where_Clause' sql) are FIRST (0-based, hence the "+ 1") and COUNT (no of entries to return).

<select id="selectPagedIds" resultClass="int" parameterClass="java.util.Map">
 select * from (
  select a.*, ROWNUM rn from (
   select ID from Schema.MYTABLE
    <include refid="Where_Clause"/>
   order by $ABATOR_ORDER_BY_CLAUSE$) a
  WHERE ROWNUM <![CDATA[ <= ]]> (#FIRST# + #COUNT#))
 WHERE rn <![CDATA[ >= ]]> (#FIRST# + 1)
</select>

Timeout

As of release 2.2.0, iBATIS includes a query timeout funcionality.
You can specify both a global timeout

<settings defaultStatementTimeout="2" />

or a per query timeout

<statement ... timeout="2">

When the timeout expires, it throws a SQL Exception "ORA-01013 : user requested cancel of current operation".

Tested with:

• Oracle 9i release 2 drivers.

Sybase

Stored Proc/UNCHAINED mode

By default, Sybase doesn't allow procs to be run in a transactional context. You can work around this problem a few different ways:

1. Use your own Connection and .setAutoCommit(true). Pass this connection into the SqlMapClient.setUserConnection(Connection) method. You're responsible for closing the connection afterwards. If you like, you can get the Connection from the same DataSource by calling SqlMapClient.getDataSource(). Although this seems a bit "roundabout", it works.

2. In Sybase, use the following command to change all stored procedures into proper transaction mode.

sp_procxmode <stored procedure> , "anymode"

3. Use the longer approach proposed by Scott Severtson: ["Scott's Sybase Proc Solution"]

MySQL

Transactions

Older versions of MySQL don't support transactions. Make sure you're using a modern MySQL release and the latest drivers. iBATIS won't work without Transaction support (an RDBMS without TX support is kind of silly anyway).

Some selectKey Examples

<insert id="insertFolder" parameterClass="folder">
  INSERT INTO folder (parent_id, owner, foldername)
  VALUES (#parentId#, #owner#, #foldername#)
  <selectKey resultClass="int" keyProperty="folderId">
    SELECT LAST_INSERT_ID() AS folderId
  </selectKey>
</insert>

WebSphere

Container Managed Transactions (CMT) Configuration

This transaction configuration is appropriate for WebSphere when using EJBs and container managed transactions (CMT).

<transactionManager type="EXTERNAL">
    <property name="SetAutoCommitAllowed" value="false"/>
    <dataSource type="JNDI">
        <property name="DataSource" value="java:comp/env/..."/>
    </dataSource>
  </transactionManager>

Bean Managed Transactions or Servlets Without EJBs

This transaction configuration is appropriate for WebSphere when your are using WebSphere connection pooling, but not using EJBs (servlets only). It is also appropriate when using WebSphere connection pooling, EJBs, and bean managed transactions.

<transactionManager type="JTA" commitRequired="true">
    <property name="UserTransaction" value="java:comp/env/UserTransaction"/>
    <dataSource type="JNDI">
        <property name="DataSource" value="java:comp/env/..."/>
    </dataSource>
  </transactionManager>

For more information on using WAS with iBATIS read this article on the developerworks website:
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0502cline/

PostgreSQL

Some selectKey Examples

<insert id="insert">
    <selectKey keyProperty="accountId" resultClass="int">
      SELECT nextVal('account_accountid_seq')
    </selectKey>
    INSERT INTO Account (accountId, username, password)
    VALUES (#accountId#, #username#, #password#)
</insert>

If accountId is defined as a 'serial' column in your schema, you can allow the accountId to default to the next number in the sequence and use Postgres's currval function to retrieve the generated ID.

<insert id="insert">
    INSERT INTO Account (accountId, username, password)
    VALUES (#accountId#, #username#, #password#)
    <selectKey keyProperty="accountId" resultClass="long">
      SELECT currval('account_accountid_seq')
    </selectKey>
</insert>

DB400 (AS400 / iSeries)

Invalid Usages of the Parameter Marker ?

DB400 has some quirks about where the ? can go in the statement.   For instance, the ? cannot be "an operand of a scalar function."  This means that DB400 will complain about using functions like UPPER or TRIM. This is a quirk with using PreparedStatements, and therefore, SqlMaps.  Below is an example of how to get scalar functions to work, much like the example of using LIKE in the FAQ section.

For more info, see  http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzala/rzalaml.htm  Look for SQL code SQL0418.

<select id="company" resultMap="getCompanyInfo" parameterClass="java.lang.String">
    <!--
        The 400 does not allow ? in scalar functions, like UPPER(?).
        This means we must use $value$ instead of #value#.
        We must rely on Java to escape the value before passing it in to SqlMaps
        by using StringEscapeUtils.escapeSql(value) to avoid injection attacks.
    -->
    SELECT COID, NAME
      FROM USRCOM
     WHERE UPPER(USER) = UPPER('$value$')
     ORDER BY NAME
</select>