Unsupported feature for JDBC 3.3 auto generated key

[Prashant Neginahal]

Hi

I am trying to use auto-generated keys feature.

My environment:
Oracle 10g
Driver: 11g JDBC Driver (ojdbc6.jar)

My mapper file looks as below.

<insert useGeneratedKeys="true" id="insert"
parameterType="test.ContactDdo" keyProperty="contactId">
insert into SR1CUST1.CONTACT_INFORMATION(CONTACT_TYPE, CONTACT_MODE,
VALUE)
values(#{contactType},#{contactMode}, #{value})
</insert>

But, i try to execute this query I get below error.

Exception in thread "main"
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause:
org.apache.ibatis.executor.ExecutorException: Error getting generated
key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
### The error may involve CONTACT_INFORMATION.insert-Inline
### The error occurred while setting parameters
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting
generated key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
100)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:
87)
at test.Test.main(Test.java:54)
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting
generated key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
at
org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:
53)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:
26)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:
51)
at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:
29)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:
72)
at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:
43)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
98)
... 2 more
Caused by: java.sql.SQLException: Unsupported feature
at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:
70)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:
133)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
199)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
263)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
271)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
445)
at
oracle.jdbc.driver.OracleReturnResultSet.findColumn(OracleReturnResultSet.java:
1341)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:
440)
at
org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:
17)
at
org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:
29)
at
org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:
38)
... 8 more


Can you please advice me, what is the problem here?

Thanks in advance.

Regards,
Prashant

[陈抒]

Hello:
   Maybe it's helpful:
   I use ojdbc14.jar and configure the SQL template in XML file as following:

<insert id="addExemption" parameterType="freebird.exemption.business.ExemptionRecord">
        <selectKey keyProperty="exemptionId" resultType="INTEGER" order="BEFORE">
            select exemption_seq.nextval from dual
        </selectKey>
        insert into exemption (id,account_id,customer_id,customer_name,address1,address2,city,state,country_code,zipcode,
        federal_id_type,federal_id,phone_number,email,active)
        values(#{exemptionId,jdbcType=DECIMAL},#{accountId,jdbcType=DECIMAL},#{customerId,jdbcType=VARCHAR},#{customerName,jdbcType=VARCHAR},
        #{address1,jdbcType=VARCHAR},#{address2,jdbcType=VARCHAR},#{city,jdbcType=VARCHAR},#{state,jdbcType=VARCHAR},
        #{country,jdbcType=VARCHAR},#{zipcode,jdbcType=VARCHAR},#{federalIdType,jdbcType=VARCHAR},#{federalId,jdbcType=VARCHAR},
        #{phoneNumber,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{active,jdbcType=INTEGER})
    </insert>

I also add a trigger into Oracle.


陈抒
Best regards
http://blog.csdn.net/sheismylife

[Prashant Neginahal]

Thank you very much for quick reply.

This approach makes double trip to database. How to achieve this in
single trip to database?

Thanks,
Prashant

On Jul 2, 5:09 pm, 陈抒 <csfreeb...@gmail.com> wrote:
> Hello:
>    Maybe it's helpful:
>    I use ojdbc14.jar and configure the SQL template in XML file as
> following:
>
> <insert id="addExemption"
> parameterType="freebird.exemption.business.ExemptionRecord">
>         <selectKey keyProperty="exemptionId" resultType="INTEGER"
> order="BEFORE">
>             select exemption_seq.nextval from dual
>         </selectKey>
>         insert into exemption

> (id,account_id,customer_id,customer_name,address1,address2,city,state,count­ry_code,zipcode,
>         federal_id_type,federal_id,phone_number,email,active)
>
> values(#{exemptionId,jdbcType=DECIMAL},#{accountId,jdbcType=DECIMAL},#{cust­omerId,jdbcType=VARCHAR},#{customerName,jdbcType=VARCHAR},
>
> #{address1,jdbcType=VARCHAR},#{address2,jdbcType=VARCHAR},#{city,jdbcType=V­ARCHAR},#{state,jdbcType=VARCHAR},
>
> #{country,jdbcType=VARCHAR},#{zipcode,jdbcType=VARCHAR},#{federalIdType,jdb­cType=VARCHAR},#{federalId,jdbcType=VARCHAR},
>
> #{phoneNumber,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{active,jdbcType­=INTEGER})

>     </insert>
>
> I also add a trigger into Oracle.
>
> 陈抒

> Best regardshttp://blog.csdn.net/sheismylife

> > org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactor­y.java:
> > 8)
> >        at
>
> > org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSessi­on.java:
> > 100)
> >        at
>
> > org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSessi­on.java:

> > 87)
> >        at test.Test.main(Test.java:54)
> > Caused by: org.apache.ibatis.executor.ExecutorException: Error getting
> > generated key or setting result to parameter object. Cause:
> > java.sql.SQLException: Unsupported feature
> >        at
>

> > org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGe­nerator.java:
> > 53)
> >        at
>
> > org.apache.ibatis.executor.statement.PreparedStatementHandler.update(Prepar­edStatementHandler.java:
> > 26)
> >        at
>
> > org.apache.ibatis.executor.statement.RoutingStatementHandler.update(Routing­StatementHandler.java:

> > 51)
> >        at
> > org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:
> > 29)
> >        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:
> > 72)
> >        at
> > org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:
> > 43)
> >        at
>

> > org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSessi­on.java:

> > 98)
> >        ... 2 more
> > Caused by: java.sql.SQLException: Unsupported feature
> >        at
> > oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:
> > 70)
> >        at
> > oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:
> > 133)
> >        at
> > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
> > 199)
> >        at
> > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
> > 263)
> >        at
> > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
> > 271)
> >        at
> > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
> > 445)
> >        at
>

> > oracle.jdbc.driver.OracleReturnResultSet.findColumn(OracleReturnResultSet.j­ava:

> > 1341)
> >        at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:
> > 440)
> >        at
>

> > org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.ja­va:

> > 17)
> >        at
> > org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:
> > 29)
> >        at
>

> > org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGe­nerator.java:

> > 38)
> >        ... 8 more
>
> > Can you please advice me, what is the problem here?
>
> > Thanks in advance.
>
> > Regards,

> > Prashant- Hide quoted text -
>
> - Show quoted text -

[陈抒]

Hi:
   Don't need to add a trigger into DB.Just need to create a sequence for generating id.
CREATE SEQUENCE exemption_seq
          INCREMENT BY 1
          START WITH 1
          NOMAXVALUE
          NOCYCLE
          NOCACHE

陈抒
Best regards
http://blog.csdn.net/sheismylife

[Guy Rouillier]

On 7/2/2010 8:29 AM, Prashant Neginahal wrote:
> Thank you very much for quick reply.
>
> This approach makes double trip to database. How to achieve this in
> single trip to database?

Unless you need to know the generated value, just put your
exemption_seq.nextval into the values clause.

--
Guy Rouillier