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
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:
Code for inserting new row into
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
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,
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.
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 is to always keep serial field as the first field in the table – in this case
populateKeys function works properly.