網頁

2020/12/21

Spring Data JPA Repository INSERT INTO SELECT WHERE NOT EXISTS

在Spring Data JPA的Repository使用INSERT INTO .. SELECT .. WHERE NOT EXISTS避免資料重覆新增。

例如新增一筆記錄(RECORD)資料。下面是映射RECORD的entity類Record

Record

package com.abc.demo.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;


@Entity
public class Record implements Serializable {
    private static final Long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private long employeeId;

    private String signInDate;


    @Override
    public String toString() {
        return "Record{" +
                "id=" + id +
                ", employeeId=" + employeeId +
                ", signInDate='" + signInDate + '\'' +
                '}';
    }
    
    // getters and setters
}

如果EMPLOYEE_IDSIGN_IN_DATE不存在才新增。在RecordRepository撰寫INSERT INTO SELECT WHERE NOT EXISTS原生sql如下(Oracle語法)。

RecordRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Record;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface RecordRepository extends JpaRepository<Record, Long> {

    @Transactional
    @Modifying
    @Query(value =
            " insert into RECORD (EMPLOYEE_ID, SIGN_IN_DATE) " +
            " select :employeeId, :signInDate from dual " +
            " where not exists (" +
            "     select * from RECORD " +
            "     where EMPLOYEE_ID = :employeeId " +
            "     and SIGN_IN_DATE = :signInDate" +
            " )",
            nativeQuery = true)
    void insertIntoWhereNotExists(long employeeId, String signInDate);

}

測試重覆新增三次相同的資料,確實只有第一次新增成功,後兩次新增因為資料已存在所以不新增。

RecordRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Record;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.MethodOrderer;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@SpringBootTest
class RecordRepositoryTests {

    @Autowired
    private RecordRepository recordRepository;

    @Test
    void insertIntoWhereNotExists_3times_SingleThread() {

        long employeeId = 1L;
        String signInDate = new SimpleDateFormat("yyyyMMdd").format(new Date());

        recordRepository.insertIntoWhereNotExists(employeeId, signInDate); // success insert
        recordRepository.insertIntoWhereNotExists(employeeId, signInDate); // no insert
        recordRepository.insertIntoWhereNotExists(employeeId, signInDate); // no insert

        List<Record> recordList = recordRepository.findAll();

        System.out.println(recordList); // [Record{id=1, employeeId=1, signInDate='20201221'}]

        Assertions.assertEquals(1, recordList.size());

    }

}

但在多執行緒(multi-thread)併發交易的情況會失效。下面測試用三條執行緒同時新增,結果新增的筆數可能為1到3筆。

RecordRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Record;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.MethodOrderer;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@SpringBootTest
class RecordRepositoryTests {

    @Autowired
    private RecordRepository recordRepository;

    @Test
    void insertIntoWhereNotExists_twoTimes_multiThread() {

        long employeeId = 1L;
        String signInDate = new SimpleDateFormat("yyyyMMdd").format(new Date());

        int threadCount = 3;
        ExecutorService executorService = Executors.newFixedThreadPool(threadCount);

        for (int i = 0; i < threadCount; i++) {
            executorService.execute(() -> recordRepository.insertIntoWhereNotExists(employeeId, signInDate));
        }

        List<Record> recordList = recordRepository.findAll();

        System.out.println(recordList); // [Record{id=1, employeeId=1, signInDate='20201221'}, Record{id=2, employeeId=1, signInDate='20201221'}, Record{id=3, employeeId=1, signInDate='20201221'}]

        Assertions.assertTrue(recordList.size() >= 1);

    }

}

所以用INSERT INTO SELECT WHERE NOT EXISTS來確保資料不被重覆新增並非安全的做法。這種做法類似先查詢資料是否存在,若不存在才新增的交易,又稱做LBYL(Look Before You Leap),直翻為「跳下去前先看清楚」。

LYBL可能的問題是,你(thread-1)跳下去前先看清楚下面確實有塊軟墊因此就放心地跳下去,沒想到跳下去那瞬間有個渾蛋(thread-2)把軟墊拿走,所以你就摔死了(?)。所以要確保不重覆新增應該用EAFP的方式。

EAFP(it's Easier to Ask Forgiveness than Permission),直翻為「請求原諒比得到允許容易」,其思想為先做再說,錯了再補救。反映在程式中就是捕捉例外錯誤的try ... catch

以上面的例子應該從複合欄位的唯一性來避免新增重複資料,可以在entiry類前加上@Table並設定uniqueConstraints屬性來達到複合欄位的唯一性限制。

Record

package com.abc.demo.entity;

import javax.persistence.*;
import java.io.Serializable;

@Table(uniqueConstraints = @UniqueConstraint(columnNames = {"employeeId", "signInDate"}))
@Entity
public class Record implements Serializable {
    ...
}

當資料重覆新增時會因為唯一性限制拋出DataIntegrityViolationException例外,避免多執行緒重覆新增的問題。


沒有留言:

張貼留言