useGeneratedKeys with Oracle appears to require keyColumns, is this correct?

241 views

Skip to first unread message

Kevin Schmidt

unread,

Mar 23, 2017, 1:31:12 PM3/23/17

to mybatis-user

Using MyBatis 3.3.0 (and behaves the same with 3.4.2) I'd implemented inserting rows into a table with useGeneratedKeys=true and specifying just keyProperty for my key column. This worked fine with MySQL.

But when I tried to use this as-is with Oracle it didn't work, getting:

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: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set. Cause: java.sql.SQLException: Invalid conversion requested

...

### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set. Cause: java.sql.SQLException: Invalid conversion requested

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)

...

Caused by: java.lang.NumberFormatException

at java.math.BigDecimal.<init>(BigDecimal.java:494)[:1.8.0_92]

at java.math.BigDecimal.<init>(BigDecimal.java:383)[:1.8.0_92]

at java.math.BigDecimal.<init>(BigDecimal.java:806)[:1.8.0_92]

at oracle.jdbc.driver.T4CVarcharAccessor.StringToNUMBER(T4CVarcharAccessor.java:825)

Searching the MyBatis docs, there was nothing indicating what might be a problem, it was only digging around in some Oracle documentation that I found this:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.

Based on that, I specified keyColumns as well and that got it to work. So is this the correct approach? Or is there another way to get this working with Oracle?

The MyBatis docs mention keyColumns being needed for PostgreSQL but nothing about Oracle. So if this is correct the docs should probably be updated.

Guy Rouillier

unread,

Mar 24, 2017, 2:42:02 AM3/24/17

to mybatis-user

I've always specified the keyColumn when employing useGeneratedKeys with Oracle.

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.