mybatis @SelectKey 返回自增主键报错BadSqlGrammarException

Question

问题描述

springboot整合mybatis的项目。
数据库是mysql。
插入一条数据，返回自增主键。
运行过程中报错。
以前也出过这样的错误，是我没写mapper XML的原因么

错误信息

org.springframework.jdbc.BadSqlGrammarException: Error selecting key or setting result to parameter object. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE XXX.identity does not exist
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE XXX.identity does not exist

Mapper

@Insert({
        "insert into u_useruploadimg (id, userid, ",
        "imgname, filename, ",
        "url, path, upload_date)",
        "values (#{id,jdbcType=INTEGER}, #{userid,jdbcType=INTEGER}, ",
        "#{imgname,jdbcType=VARCHAR}, #{filename,jdbcType=VARCHAR}, ",
        "#{url,jdbcType=VARCHAR}, #{path,jdbcType=VARCHAR}, #{uploadDate,jdbcType=TIMESTAMP})"
    })
    @SelectKey(statement="call identity()", keyProperty="id", before=false, resultType=Integer.class)
    int insert(UploadImg record);

实际调用的时候传入的参数只有userid,imgname,filename,url,path和upload_date这几个参数，没有id。

实体类

public class UploadImg {
    private Integer id;

    ...

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    ...
}

Answer 1

要返回insert后自增的主键，可以使用@Options

Mapper:

@InsertProvider(method = "insert", type = UserInfoMapperProvider.class)
@Options(keyProperty = "userInfoId", useGeneratedKeys = true)
void insert(UserInfo userInfo);

Service:

public Long insert(UserInfo userInfo) {
    userInfoMapper.insert(userInfo);
    return userInfo.getUserInfoId();
}