ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Curran <jonc...@gmail.com>
Subject useGeneratedKeys issue in insert statement w/patch
Date Fri, 16 Oct 2009 00:25:27 GMT
Hi all, here is what I am running into when using useGeneratedKeys in an 
insert statement:

org.apache.ibatis.exceptions.IbatisException: 
### Error updating database.  Cause: 
org.apache.ibatis.executor.ExecutorException: Error getting generated key or 
setting result to parameter object. Cause: org.postgresql.util.PSQLException: 
Bad value for type int : 2009-10-15 00:08:00.195026

Caused by: org.postgresql.util.PSQLException: Bad value for type int : 
2009-10-15 00:08:00.195026
        at 
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
        at 
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)
        at 
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2426)
        at 
com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt(NewProxyResultSet.java:2573)
        at 
org.apache.ibatis.type.IntegerTypeHandler.getNullableResult(IntegerTypeHandler.java:14)
        at 
org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:21)
        at 
org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:33)

I am currently using iBatis 3.0-beta3.

<insert id="add" parameterType="foo.bar.License" keyProperty="id" 
useGeneratedKeys="true">
  INSERT INTO license (name) VALUES (#{name})
</insert>

I use Postgresql 8.4 and the Insert statement returns the following when it is 
executed by iBatis:

created | updated | version | name | id
"2009-10-15 00:08:00.195026" | "2009-10-15 00:08:00.195026" | 0 | "asdf" | 27

As you can see, my ID column is at the end which is causing the problem -> 
iBatis is trying to convert the timestamp to an int since it is in the first 
column and thinks that this is the key it is supposed to get.

I propose the following patch which solves this issue. I have tested it by 
running my own test and all the tests in latest trunk (they all passed).

Index: ibatis-3-
core/src/main/java/org/apache/ibatis/executor/keygen/Jdbc3KeyGenerator.java
===================================================================
--- ibatis-3-
core/src/main/java/org/apache/ibatis/executor/keygen/Jdbc3KeyGenerator.java        
(revision 825397)
+++ ibatis-3-
core/src/main/java/org/apache/ibatis/executor/keygen/Jdbc3KeyGenerator.java        
(working copy)
@@ -33,7 +33,14 @@
               ResultSetMetaData rsmd = rs.getMetaData();
               int colCount = rsmd.getColumnCount();
               if (colCount > 0) {
-                String colName = rsmd.getColumnName(1);
+                int colIndex = 1;
+                for (int k=1; k<=colCount; k++) {
+                  if (rsmd.getColumnName(k).equals(keyProperty)) {
+                    colIndex = k;
+                    break;
+                  }
+                }
+                String colName = rsmd.getColumnName(colIndex);
                 while (rs.next()) {
                   Object value = th.getResult(rs, colName);
                   metaParam.setValue(keyProperty, value);

With the patch it compares the column name to the one set by keyProperty and 
if they match it will use the index of the matching column, otherwise it will 
try to extract the value out of the first column.

I hope this proves useful to someone else as well. Do let me know if there are 
any concerns regarding the patch. Hopefully it gets accepted =]

Thanks,
Jonathan Curran

P.S. I noticed that in trunk when executing a select I have to specify column 
names otherwise it throws errors (e.g. when doing select *). Just wondering 
what the deal is with that (hopefully just a side effect that will get fixed 
soon)?

---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message