UPDATE: keyColumn is the key to the solution

Below described problem can be solved by using keyColumn property of MyBatis, as described here. Using keyColumn property it is possible to specify name of the autogenerated column, so it will be properly returned by getGeneratedKeys method.

Current version of MyBatis Generator plugin for Eclipse does not support this property, so generated xml configuration must be manually updated each time. This is not so convinient, but it is the best option so far.

Autogenerating values for primary keys

MyBatis supports using sequences in database through generated keys property for primary key fields. This means that it is possible to omit primary keys values in SQL insert statement for the fields that are autogenerated by database. Afted row is inserted, new value is assigned to autogenerated keys and MyBatis is able to properly return this value. An example of such usage on PostgreSQL will be as below:

Database table:

CREATE TABLE element_types
(
  element_type_id serial NOT NULL,
  name character varying(30),
  lastsaved timestamp without time zone,
  CONSTRAINT pk_element_types PRIMARY KEY (element_type_id)
)

Code for inserting new row into element_types table:

public ElementType addElementType(ElementType resource) {
    session = sqlSessionFactory.openSession(true);
    Integer elementTypeId = null;

    // Get mapper
    ElementTypesMapper elementTypeMapper = session.getMapper(ElementTypesMapper.class);

    // Set ID to null, will be set to new value by database
    resource.setElementTypeId(null);

    // Insert data
    elementTypeMapper.insert(resource);

    // Get new key value
    elementTypeId = resource.getElementTypeId();
    ....
    session.close();

    return resource;
}

MyBatis and PostgreSQL mismatch with autogenerated keys

The above code is working without problems; however, if the order of the columns in database table is changed or a new column is added before element_type_id column, the code will fail. Event more, elementTypeId will get wrong value, while the values in the database will be corect.

This behavior is due to the expectation MyBatis have towards jdbc function getGeneratedKeys which should return values of the keys that are generated during insert. MyBatis expect that result set returned by getGeneratedKeys contains only generated keys, while PostgeSQL jbdc driver simple return all columns! Below is a function that populates generated keys in MyBatis classJdbc3KeyGenerator.java:

private void populateKeys(ResultSet rs, MetaObject metaParam, String[] keyProperties, TypeHandler<?>[] typeHandlers) throws SQLException {
  for (int i = 0; i < keyProperties.length; i++) {
    TypeHandler<?> th = typeHandlers[i];
    if (th != null) {
      Object value = th.getResult(rs, i+1);
      metaParam.setValue(keyProperties[i], value);
    }
  }
}

ResultSet rs is returned from jdbc getGeneratedKeys method. If the database table is changed in a such way, that SERIAL field is not the first one, such as below,

CREATE TABLE element_types
(
  composite_id integer NOT NULL,
  element_type_id serial NOT NULL,
  name character varying(30),
  lastsaved timestamp without time zone,
  CONSTRAINT pk_element_types PRIMARY KEY (element_type_id)
)

ResultSet rs will contain all four fields, in the same order as in database table. However, array keyProperties contains only one field, elementTypeId and the above code will get the value fromcomposite_id field and store it to elementTypeId! No error will be produced at all.

Potential solution

The solution would be to access ResultSet using field names instead of index, this should prevent errors described above. Also, it would be interesting to see how this solution would work with two serial fields in the same table.

The expectation that MyBatis have are reasonable, there is no point in returning all columns from the getGeneratedKeys method. But, this is not forbiden – java doc for getGeneratedKeys explicetely states that JDBC driver can determine what are the columns that best represent generated keys. This is probably allowed due to the fact that in some databases it is either not possible or can cause performance problems to determine exact columns that are auto generated.

Workaround

Workaround is to always keep serial field as the first field in the table – in this case populateKeys function works properly.