MyBatis,如何获取insert的自动生成key?[MySql]

nszi6y05  于 2023-03-28  发布在  Mysql
关注(0)|答案(9)|浏览(158)

我怎样才能获得MyBatis的插入生成的密钥?我读了很多关于这个问题的页面,但我仍然被阻止,有人能帮助我吗?这是我的代码:
表格:

ID_ERROR long primary key
DATE timestamp
TYPE varchar
MESSAGE varchar
SOURCE varchar

道:

Long returnedId = 0L;
MyMapper myMapper = this.sqlSession.getMapper(MyMapper.class);
myMapper.insertRecord(returnedId, Utils.now(), t.getClass().getName(), t.getMessage(), c.getName());
return returnedId;

该mapper.java:

public void insertRecord(@Param("returnedId") Long returnedId, @Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source") String source);

mapper.xml

<insert id="insertRecord" parameterType="map" useGeneratedKeys="true"  keyProperty="ID_ERROR">
    INSERT INTO errors (
        DATE,
        TYPE,
        MESSAGE,
        SOURCE
    )
    VALUES (
        #{timestamp},
        #{type},
        #{message},
        #{source}
    )
    <selectKey resultType="long" order="AFTER" keyProperty="returnedId">
        SELECT LAST_INSERT_ID() as returnedId
    </selectKey>
</insert>

怎么了?我怎样才能得到这个插入的生成密钥?谢谢!

vc9ivgsu

vc9ivgsu1#

对我来说,它是这样工作的(mybatis 3.x).. id必须在mysql表中设置自动增量

<insert id="createEmpty" parameterType="Project" useGeneratedKeys="true" keyProperty="project.projectId" keyColumn="PROJECT_ID">
    INSERT INTO PROJECT (TITLE,DESCRIPTION)
    VALUES
    (#{title},#{description})
</insert>

注:keyProperty="project.projectId"useGeneratedKeys="true"
我的界面是:

public int createEmpty(@Param("project") Project project, @Param("title") String title,
    @Param("description") String description);

最后,为了获取值(将自动分配给pojo的id属性),我用途:

projectRepository.createEmpty(p, "one", "two");
System.err.print(p.getProjectId() + "\n");
flseospp

flseospp2#

你可以通过两种方式来实现这一点,
1.使用useGeneratedKeys="true", keyProperty="id", keyColumn="id"
keyProperty是指POJO变量名,keyColumn是指数据库中生成的列名
1.通过在insert标记中使用<selectKey/>

p4rjhz4m

p4rjhz4m3#

如果你看一下MyBatis的文档,至少useGeneratedKeyskeyProperty是你获取自动增量数据所需要的(对于某些数据库,你需要添加keyColumn)。
如您所见,useGeneratedKeys取决于dataBase的JDBC的getGeneretadKeys方法是否实现/如何实现。
例如,对于mysql或H2,getGeneretadKeys只支持一列。最后生成的键将是getGeneretadKeys返回的键。
总之,在您的情况下,您只需要添加useGeneratedKeys和keyProperty(使用ID_ERROR auto_increment):
Mapper.xml

<resultMap type='pathToJavaClass/Error' id='error'>
    <id property='id' column='ID_ERROR' />
    <result property='timestamp' column='DATE' />
    <result property='type' column='TYPE'/>
    <result property='message' column='MESSAGE'/>
    <result property='source' column='SOURCE'/>
</resultMap>
<insert id="insertRecord" parameterType="error" useGeneratedKeys="true" keyProperty="id">
INSERT INTO errors (
    DATE,
    TYPE,
    MESSAGE,
    SOURCE
)
VALUES (
    #{timestamp},
    #{type},
    #{message},
    #{source}
)
</insert>

Interface.java

public void insertRecord(@Param("error") Error error);

如果你仍然在检索生成的Keys时遇到一些问题,请查看mysql的JDBC文档(旧版本可能没有实现getGeneretadKeys)。

h5qlskok

h5qlskok4#

简单的解决方案:
使用KeyProperty属性作为objectName.AutoincrementId如下所示...
useGeneratedKeys="true", KeyProperty="person.id", KeyColumn="id"

6l7fqoea

6l7fqoea5#

在xml文件中,将其置于5行以下:

<insert id="createPet" parameterType="java.util.Map"
    useGeneratedKeys="true" keyProperty="id">
    INSERT INTO Pet (NAME, OWNER, SPECIES, SEX, BIRTH)
    VALUES (#{name}, #{owner}, #{species}, #{sex}, #{birth})
</insert>

在Java main类中创建这个方法,并在main方法中调用它:

public int createPet(PetDVO petDVO) throws Exception {
    HashMap<String, Object> inputMap = new HashMap<String, Object>();
    inputMap.put("name", petDVO.getName());
    inputMap.put("owner", petDVO.getOwner());
    inputMap.put("species", petDVO.getSpecies());
    inputMap.put("sex", petDVO.getSex());
    inputMap.put("birth", petDVO.getBirth());

    /**
     * Get the sql session and commit the data
     */
    SqlSession sqlSession = getSqlSession();
    sqlSession.insert("createPet", inputMap);
    sqlSession.commit();

    BigInteger newID = (BigInteger)inputMap.get("id");
    return newID.intValue();
}

但是你应该自己创建PetDVO类。就是这样。

ubbxdtey

ubbxdtey6#

在Mapper Xml下,使用查询:

<insert id="saveDemo" parameterType="com.abc.demo"
       useGeneratedKeys="true" keyProperty="demoId" keyColumn="DEMOID">
       INSERT INTO TBL_DEMO (DEMONAME,DEMODESCRIPTION)
       VALUE (#{demoName},#{demoDescription})
       <selectKey keyProperty="demoId" resultType="int" order="AFTER">
        SELECT LAST_INSERT_ID();
       </selectKey>
    </insert>

java 侧

@Override
public boolean saveDemo(Demo demo) {
    boolean status = false;
    SqlSession session = this.sqlSessionFactory.openSession();
    try {
        DemoMapper mapper = session.getMapper(DemoMapper.class);
        mapper.saveDemo(demo);
        session.commit();
        status = true;
    } catch(PersistenceException e) {
        System.out.println(e);
    } finally {
        session.close();
    }
    return status;
}
bt1cpqcv

bt1cpqcv7#

使用带有insert sql和RETURNING指示的Select注解。
下面的代码适用于Postgres,MyBatis 3.5.0。

@Select("insert into db_mutil_route(user_id, pk, instance_id, name, create_time, update_time) values(#{userId}, #{pk}, #{instanceId}, #{name}, now(), now()) RETURNING id")
    @Options(flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn="id")
    Route insert(Route dbRoute);

参考:https://github.com/mybatis/mybatis-3/issues/1293

yacmzcpb

yacmzcpb8#

请按照以下步骤操作:
1.以id作为属性创建错误POJO
1.将returnId替换为错误,如下所示,
public void insertRecord(@Param(“error”)Error error,@Param(“timestamp”)Timestamp,@Param(“type”)String type,@Param(“message”)String message,@Param(“source”)String source);
1.将keyProperty=“ID_ERROR”更改为keyProperty=”error.id“
1.移除

<selectKey resultType="long" order="AFTER" keyProperty="returnedId">
    SELECT LAST_INSERT_ID() as returnedId
</selectKey>

您将在error.id中插入id

crcmnpdw

crcmnpdw9#

如果要获取生成的主键,则应通过MapPOJO Object传递参数

public void insertRecord(Map<String, Object> map);

当调用Map方法时,将值Map。

Map<String, Object> map = new HashMap<String, Object>();
map.put("returnedId", 0);
map.put("message", message);
// other paramters
mapper.insertRecord(map);
return map.get("returnedId");

相关问题