在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_ID加SIGN_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例外，避免多執行緒重覆新增的問題。
沒有留言:
張貼留言