spring data jpa中实现数据库记录的insertIfNotExists

背景

最近在业务开发中，需要通过lazy init的方式向一张table中插入数据，该table中一个column存在unique约束。

方案探索

通过catch exception

原始版本中，采用如下pseudo code所示的方式进行，其中key为unqiue id

    Record insertIfNotExists(K key) {

        Record record = recordRepo.findByKey(key)
        if (record == null) {
            Record newRecord = new Record()
            try {
                recordRepo.save(newRecord)
            } catch (DataIntegrityViolationException e) {
                //already exists
                return recordRepo.findByKey(key)
            }

        } else {
            return record
        }
    }

在不对方法添加transaction的情况下，运行正常，但在transaction中执行时，会产生如下异常：

org.hibernate.AssertionFailure: null id in entry (don't flush the Session after an exception occurs)

原因在这里：

mysql - org.hibernate.AssertionFailure: null id in entry (don't flush the Session after an exception occurs) - Stack Overflow

session中已经持有了一个未持久化的entity，该entity的id为null。

通过insert on duplicate update

该语法的说明可以参考mysql manual：

MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

遵循的是insert -> if exists -> update current的流程。

 pseudo code如下：

    Record insertIfNotExists(K key) {
        Record record = recordRepo.findByKey(key)
        if (record == null) {
            recordRepo.insertOnDuplicateUpdate(key)
            return record
        }
        return recordRepo.findByKey(key)
    }

需要注意，该function必须在一个transaction中，否则抛出异常。

该语句返回值为整数，代表了实际操作row的数量，文档中的描述是

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

实际情况下，spring data jpa的 CLIENT_FOUND_ROWS为true，所以该sql的返回值为1或2。1代表插入一行新纪录或保持不变，2代表更新了原有纪录。

绝大多数情况下，该flag设置为true，保证了其它依赖affected rows & found rows的返回正常。

对于mysql客户端，可以通过options设置，以jetbrains的datagrip为例，设置方法可参考：

https://youtrack.jetbrains.com/issue/DBE-9085

结论

在有transaction的情况下，通过insert on duplicate update实现更稳定和优雅，但需要注意返回值的问题。可以通过增加一个值为random的column，在update clause中更新该column值，即可通过返回值判断是否进行了row insert。