[Mybatis] useGeneratedKeys parameter usage and problems encountered

What is useGeneratedKeys?

The official statement is that the function of this parameter is: "allows JDBC to support automatic generation of primary keys, and requires driver compatibility." How do you understand the meaning of this sentence?

The original intention is to say: For databases that support automatic generation of record primary keys, such as MySQL and SQL Server, set the useGeneratedKeys parameter to true at this time, and the primary key ID automatically generated by the database can be obtained after adding records.


how to use?

The configuration can be achieved in the following ways:

  • Configure the global configuration file
  • Configure the useGeneratedKeys parameter in the xml mapper
  • Set the useGeneratedKeys parameter in the interface mapper

One, configure the global configuration file

  1. application.yml configuration file
# MyBatis配置
mybatis:
    # 搜索指定包别名
    typeAliasesPackage: com.ruoyi.**.domain
    # 配置mapper的扫描,找到所有的mapper.xml映射文件
    mapperLocations: classpath*:mapper/**/*Mapper.xml
    # 加载全局的配置文件
    configLocation: classpath:mybatis/mybatis-config.xml
  1. Configure mybatis config file
Insert picture description here


3. mybatis-config.xml

The contents of the file are as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	
	<settings>
		<setting name="cacheEnabled"             value="true" />  <!-- 全局映射器启用缓存 -->
		<setting name="useGeneratedKeys"         value="true" />  <!-- 允许 JDBC 支持自动生成主键 -->
		<setting name="defaultExecutorType"      value="REUSE" /> <!-- 配置默认的执行器 -->
		<setting name="logImpl"                  value="SLF4J" /> <!-- 指定 MyBatis 所用日志的具体实现 -->
<!--		<setting name="mapUnderscoreToCamelCase" value="true"/> &lt;!&ndash; 驼峰式命名 &ndash;&gt;-->
	</settings>
	
</configuration>
In addition,The global useGeneratedKeys parameter set in the settings element is invalid for the xml mapper. If you want to return the primary key ID after adding records in the xml mapper, you must explicitly set the useGeneratedKeys parameter value to true in the xml mapper.

Two, configure the useGeneratedKeys parameter in the xml mapper

  1. Mapper.xml file
<insert id="addBigdataGroup" parameterType="BigdataGroup" useGeneratedKeys="true" keyProperty="groupId" keyColumn="group_id">
        insert into bigdata_group (
        group_id, group_name, comment, business_line, create_by, remark, create_time)
        values(#{groupId}, #{groupName}, #{comment}, #{businessLine}, #{createBy}, #{remark}, sysdate() );
</insert>
  • parameterType Incoming parameter type
  • keyProperty JAVA attributes
  • keyColumn Database field
The useGeneratedKeys parameter configured in the xml mapper only affects the xml mapper, and the global useGeneratedKeys parameter value set in the settings element has no effect on the xml mapper.

Three, set the useGeneratedKeys parameter in the interface mapper

/Set useGeneratedKeys to true, return the primary key id of the record automatically generated by the database

@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
@Insert("insert into test(name,descr,url,create_time,update_time) values(#{name},#{descr},#{url},now(),now())")
Integer insertOneTest(Test test);
Note: The useGeneratedKeys parameter set in the interface mapper will override the corresponding parameter value set in the element.

Problems encountered

When I configured to get the primary key ID, but the returned result did not return the real data of the primary key of the newly inserted database row as expected. Instead 1.

code show as below:

  1. Mybatis层
import java.util.List;

public interface BigdataMapper {

    List<BigdataGroup> getBigdataGroup();

    int addBigdataGroup(BigdataGroup bigdataGroup);
}

  1. service层
public int addBigdataGroup(BigdataGroup bigdataGroup) {
        bigdataGroup.setCreateBy(SecurityUtils.getUsername());

        int update = bigdataMapper.addBigdataGroup(bigdataGroup);
        log.info("update: {}", update);
        return update;
    }
  1. xml文件
<insert id="addBigdataGroup" parameterType="BigdataGroup" useGeneratedKeys="true" keyProperty="groupId" keyColumn="group_id">
        insert into bigdata_group (
        group_id, group_name, comment, business_line, create_by, remark, create_time)
        values(#{groupId}, #{groupName}, #{comment}, #{businessLine}, #{createBy}, #{remark}, sysdate() );
    </insert>
  1. Print result
Insert picture description here

It stands to reason that the returned result should be the actual data inserted into the primary key, but the result is that it is returned 1.

note:It turns out that the real id has been injected into the corresponding attribute of the primary key of the passed parameter object, You only need to use the get method of the input object of the insert statement to get the correct auto-increment id.

If you get the primary key value of the newly added data here, you only need to get the primary key value corresponding to the primary key of the object.

Code modification:

public int addBigdataGroup(BigdataGroup bigdataGroup) {
        bigdataGroup.setCreateBy(SecurityUtils.getUsername());

        int update = bigdataMapper.addBigdataGroup(bigdataGroup);
        log.info("update: {}", update);
        // 新增如下代码
        int group_id = bigdataGroup.getGroupId();
        log.info("group_id: {}", group_id);
        // 到此为止
        return update;
    }

Observation results:

Insert picture description here

Successfully got the result!