mybatis的BaseInsertMapper是通过传递一个和数据库表相对应的entity对象来对这个表进行插入操作的,如果是自增主键的话,则会在插入一条数据后将自增的主键值set回entity类里,这个时候只要通过get主键字段名就可以获取到新增的这条主键值,实现方式为为:
自增主键若想要获取到,首先需要添加@Id标识,然后添加@GeneratedValue(strategy = GenerationType.IDENTITY),
相当于xml的inert标签里设置的参数:useGenerateKeys和keyProperty 。然后@Column标签里需要设置insertable = false,默认是true这样解析后的sql不会忽略这个主键,就会变成如:INSERT INTO OrderError (ID ,key,value ) VALUES(? ,?,? ) 而ID是null,数据库就会抛出异常,因此需要添加。
下面给出示例
entity类:
package com.lenovo.ccb.biddingopportunityservice.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import javax.persistence.*;
import java.io.Serializable;
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
@Accessors(chain = true)
@Table(name = "OrderError")
public class OrderErrorEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", insertable = false)
private Integer id;
@Column(name = "key")
private String key;
@Column(name = "value")
private String value;
}
mapper类:
package com.lenovo.ccb.biddingopportunityservice.repository;
import com.lenovo.ccb.biddingopportunityservice.entity.OrderErrorEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.base.BaseInsertMapper;
@Mapper
@Repository
public interface OrderErrorMapper extends BaseInsertMapper<OrderErrorEntity> {
}
测试类:
package com.lenovo.ccb.biddingopportunityservice.api;
import com.lenovo.ccb.biddingopportunityservice.entity.OrderErrorEntity;
import com.lenovo.ccb.biddingopportunityservice.repository.OrderErrorMapper;
import jdk.nashorn.internal.ir.annotations.Reference;
import lombok.extern.log4j.Log4j2;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;
/**
* @param
* @author
* @return
* @date
*/
@Log4j2
@SpringBootTest
@RunWith(SpringRunner.class)
@WebAppConfiguration
public class ProjectControllerTest {
@Autowired
OrderErrorMapper orderErrorMapper;
@Test
public void testInsert() {
OrderErrorEntity orderErrorEntity = OrderErrorEntity.builder().key("orderItem").value("{'orderId':123456,'itemName':'主机'}").build();
orderErrorMapper.insert(orderErrorEntity);
log.info("========================主键为:"+orderErrorEntity.getId());
}
}
至此,以上这种方式如果是在mysql库,将会insert成功并且返回id值到orderErrorEntity这个对象里,获取是直接:orderErrorEntity.getId()即可获取到。
而如果是,sql server库,则会报错:
Error selecting key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: ‘LAST_INSERT_ID’ 不是可以识别的 内置函数名称。
; uncategorized SQLException; SQL state [S00010]; error code [195]; ‘LAST_INSERT_ID’ 不是可以识别的 内置函数名称。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: ‘LAST_INSERT_ID’ 不是可以识别的 内置函数名称。
原因:@GeneratedValue注解,在没有设置generator属性时,默认是"",而当是""时,相当于在xml中insert时添加:
<selectKey resultType="int" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID()
</selectKey>
而SELECT LAST_INSERT_ID()是获取mysql主键的,获取sqlserver主键的为:select IDENT_CURRENT(‘insert表名’),因此会报出这种错误。
解决方式:
将generator的值显示设置为JDBC,JDBC兼容绝大多数数据库也包括sql server,即,如果是sql server库的话,entity类为:
package com.lenovo.ccb.biddingopportunityservice.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import javax.persistence.*;
import java.io.Serializable;
/**
* @author 18911
*/
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
@Accessors(chain = true)
@Table(name = "OrderError")
public class OrderErrorEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY,generator = "JDBC")
@Column(name = "ID", insertable = false)
private Integer id;
@Column(name = "[key]")
private String key;
@Column(name = "value")
private String value;
}
然后再运行就可以插入成功了,并且可以返回主键的值: