useGeneratedKeys in postgreSQL is not setting the right property when primary key column is not the first column in the table

[GoogleCodeExporter]

What version of the MyBatis are you using?
3.0.2-SNAPSHOT

Please describe the problem.  Unit tests are best!
This is on postgreSQL 8.4 database. When using useGeneratedKeys="true", if the 
primary key column is not the first column in the table, INSERTS into the table 
fails with the following error:

### 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 long : xxx

The first column is a string in this case so setting the long value on the 
string column fails resulting in the error. Here's my mapping:

<resultMap id="workoutMap" type="Workout" >
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="description" property="description"/>
    </resultMap>

    <insert id="add" parameterType="Workout" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
            workout (
                name,
                description
            )
        VALUES (
            #{name},
            #{description}
        )
    </insert>

What is the expected output? What do you see instead?

Please provide any additional information below.
I looked at Jdbc3KeyGenerator which is doing the following (line 36):
     String colName = rsmd.getColumnName(1);
Maybe it should be trying to use the keyProperty(to look up column name) 
instead of just grabbing the first column.

Original issue reported on code.google.com by aye...@gmail.com on 17 Aug 2010 at 5:02

[GoogleCodeExporter]

I did a test of this with HSQL and didn't find a problem.

Using key property won't work because what is coming back from the database is 
the column name - not the property name and they may not necessarily be the 
same.  MyBatis assumes that the driver will only return generated columns in 
the returned ResultSet from the getGeneratedKeys() call.

Is the PostgreSql driver returning, basically, a result set with each column 
from the insert and null values for the non generated columns?

Original comment by jeffgbut...@gmail.com on 30 Dec 2010 at 12:21

[GoogleCodeExporter]

This is fixed in SVN.  It is fixed by adding a new attribute "keyColumn" to the 
@Options annotation and the <insert> tag.  This attribute can be used to 
specify a key column name in cases like PostgreSQL where the problem is as 
described above.

Original comment by jeffgbut...@gmail.com on 5 Jan 2011 at 2:02

• Changed state: Fixed
• Added labels: Component-SqlMaps, Target-Release3.0.5

[GoogleCodeExporter]

Original comment by eduardo.macarron on 3 Mar 2012 at 9:11

• Added labels: Version-Release3.x