How to get a field value, which is default to CURRENT_TIMESTAMP, in an insert statement in MySQL with Mybatis

Question

I have a table in MySQL as below:

CREATE TABLE `mytable` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `phone` VARCHAR(128) NULL DEFAULT NULL,
    `createTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=25
;

I have a Mybatis mapper as below

@Mapper
public interface MyMapper {
    Integer insertRecord(List<PhoneModel> list);
}


<insert id="insertRecord" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into mytable(phone) values
    <foreach item="item" index="index" collection="list" separator=",">
        (#{item.phone})
    </foreach>
</insert>

There are three properites in PhoneModel: id,phone,createTime. Now I can get id after calling insertRecord, but I want to get createTime as well.

I've gone through the documents on Mybatis. It mentions that I can add multiple property names (such as createTime) to keyProperty and keyColumn.

keyProperty (insert and update only) Identifies a property into which MyBatis will set the key value returned by getGeneratedKeys, or by a selectKey child element of the insert statement. Default: unset. Can be a comma separated list of property names if multiple generated columns are expected.

keyColumn (insert and update only) Sets the name of the column in the table with a generated key. This is only required in certain databases (like PostgreSQL) when the key column is not the first column in the table. Can be a comma separated list of columns names if multiple generated columns are expected.

I'm not clear about what a comma separated list of property/column names means. I've tried keyProperty="id, createTime" but it doesn't seem to work.

Thanks.

score 1 · Answer 1 · edited May 23 '17 at 11:53

If keyProperty="id, createTime" is a copy from your code, then clear the space after the comma. It might not be the (only) culprit anyway.

I guess the auto_increment internally operates like a sequence. Whereas CURRENT_TIMESTAMP is a function. Then you may check the multiple keyProperty support behavior: for test purpose, can you add another column (auto_increment , serial ?) so that you check you can get generated keys.

It seems PostgreSQL allows RETURNING value from an Insert, but I' have not found equivalent in MySQL.

You execute a single multi row insert statement, how the CURRENT_TIMESTAMP behaves? exactly the same timestamp is inserted for all records? or the values evolves? this can be observed when inserting a large number of record, that will take more than 1 second.

An why not inserting the timestamp yourself? just bulk insert instead of multi row insert, iterate the list in the java, the session must use an ExecutorTYPE.REUSE to prepare the statement only once.

for(PhoneModel model : list) {
    model.setCreateTime(new Date());
    mapper.insertRecord(model);
}

How to get a field value, which is default to CURRENT_TIMESTAMP, in an insert statement in MySQL with Mybatis

1 Answers1