在应用程序开发中,执行更新或插入操作(Update-Or-Insert也称为“upsert”)的需求非常常见。此操作涉及将新记录放入数据库表(如果不存在)或更新现有记录(如果存在)。
在本教程中,我们将学习使用Spring Data JPA执行更新或插入操作的不同方法。
出于演示目的,我们将使用CreditCard实体:
@Entity @Table(name="credit_card") public class CreditCard { @Id @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "credit_card_id_seq") @SequenceGenerator(name = "credit_card_id_seq", sequenceName = "credit_card_id_seq", allocationSize = 1) private Long id; private String cardNumber; private String expiryDate; private Long customerId; // getters and setters }
|
我们将使用三种不同的方法来实现更新或插入。
1.使用存储库方法
在这种方法中,我们将使用save(entity)方法在存储库中编写事务默认方法,该方法继承自CrudRepository接口。 save (entity) 方法会插入新记录,或者根据 id 更新现有实体:
public interface CreditCardRepository extends JpaRepository<CreditCard,Long> { @Transactional default CreditCard updateOrInsert(CreditCard entity) { return save(entity); } }
|
我们将CreditCard传递给CreditCardLogic类中的updateOrInsertUsingReposiotry()方法,该方法根据实体id插入或更新实体:@Service public class CreditCardLogic { @Autowired private CreditCardRepository creditCardRepository; public void updateOrInsertUsingRepository(CreditCard creditCard) { creditCardRepository.updateOrInsert(creditCard); } }
|
这种方法的一个重要注意事项是实体是否要更新由id决定。如果我们需要根据另一列查找现有记录,例如使用cardNumber而不是id,那么这种方法将不起作用。在这种情况下,我们可以使用后面几节中讨论的方法。我们可以编写单元测试来验证我们的逻辑。首先,我们将一些测试数据保存到credit_card表中:
private CreditCard createAndReturnCreditCards() { CreditCard card = new CreditCard(); card.setCardNumber("3494323432112222"); card.setExpiryDate("2024-06-21"); card.setCustomerId(10L); return creditCardRepository.save(card); }
|
我们将使用上面保存的信用卡进行更新。让我们构建一个用于插入的CreditCard对象:private CreditCard buildCreditCard() { CreditCard card = new CreditCard(); card.setCardNumber("9994323432112222"); card.setExpiryDate("2024-06-21"); card.setCustomerId(10L); return card; }
|
我们准备好编写我们的单元测试:@Test void givenCreditCards_whenUpdateOrInsertUsingRepositoryExecuted_thenUpserted() { // insert test CreditCard newCreditCard = buildCreditCard(); CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNull(existingCardByCardNumber); creditCardLogic.updateOrInsertUsingRepository(newCreditCard); existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNotNull(existingCardByCardNumber); // update test CreditCard cardForUpdate = existingCard; String beforeExpiryDate = cardForUpdate.getExpiryDate(); cardForUpdate.setExpiryDate("2029-08-29"); existingCardByCardNumber = creditCardRepository.findByCardNumber(cardForUpdate.getCardNumber()); assertNotNull(existingCardByCardNumber); creditCardLogic.updateOrInsertUsingRepository(cardForUpdate); assertNotEquals("2029-08-29", beforeExpiryDate); CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get(); assertEquals("2029-08-29", updatedCard.getExpiryDate()); }
|
在上面的测试中,我们为updateOrInsertUsingRepository()方法断言插入和更新操作。2.使用自定义逻辑
在这种方法中,我们在CreditCardLogic类中编写自定义逻辑,该类首先检查给定的行是否已存在于表中,然后根据输出决定插入或更新记录:
public void updateOrInsertUsingCustomLogic(CreditCard creditCard) { CreditCard existingCard = creditCardRepository.findByCardNumber(creditCard.getCardNumber()); if (existingCard != null) { existingCard.setExpiryDate(creditCard.getExpiryDate()); creditCardRepository.save(creditCard); } else { creditCardRepository.save(creditCard); } }
|
根据上述逻辑,如果数据库中已存在cardNumber ,则我们根据传递的CreditCard对象更新该现有实体。否则,我们将传递的信用卡作为新实体插入updateOrInsertUsingCustomLogic()方法中。我们可以编写单元测试来验证我们的自定义逻辑:
@Test void givenCreditCards_whenUpdateOrInsertUsingCustomLogicExecuted_thenUpserted() { // insert test CreditCard newCreditCard = buildCreditCard(); CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNull(existingCardByCardNumber); creditCardLogic.updateOrInsertUsingCustomLogic(newCreditCard); existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNotNull(existingCardByCardNumber); // update test CreditCard cardForUpdate = existingCard; String beforeExpiryDate = cardForUpdate.getExpiryDate(); cardForUpdate.setExpiryDate("2029-08-29"); creditCardLogic.updateOrInsertUsingCustomLogic(cardForUpdate); assertNotEquals("2029-08-29", beforeExpiryDate); CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get(); assertEquals("2029-08-29", updatedCard.getExpiryDate()); }
|
3.使用数据库内置功能
许多数据库提供内置功能来处理插入冲突。例如,PostgreSQL 提供“ON CONFLICT DO UPDATE”,MySQL 提供“ON DUPLICATE KEY”。利用这个特性,我们可以在向数据库插入一条记录时,当出现重复键时,编写后续的更新语句。
一个示例查询如下:
String updateOrInsert = """ INSERT INTO credit_card (card_number, expiry_date, customer_id) VALUES( :card_number, :expiry_date, :customer_id ) ON CONFLICT ( card_number ) DO UPDATE SET card_number = :card_number, expiry_date = :expiry_date, customer_id = :customer_id """;
|
为了进行测试,我们使用 H2 数据库,它不提供“ON CONFLICT”功能,但我们可以使用H2 数据库提供的合并查询。让我们在CreditCardLogic类中添加合并逻辑:@Transactional public void updateOrInsertUsingBuiltInFeature(CreditCard creditCard) { Long id = creditCard.getId(); if (creditCard.getId() == null) { BigInteger nextVal = (BigInteger) em.createNativeQuery("SELECT nextval('credit_card_id_seq')").getSingleResult(); id = nextVal.longValue(); } String upsertQuery = """ MERGE INTO credit_card (id, card_number, expiry_date, customer_id) KEY(card_number) VALUES (?, ?, ?, ?) """; Query query = em.createNativeQuery(upsertQuery); query.setParameter(1, id); query.setParameter(2, creditCard.getCardNumber()); query.setParameter(3, creditCard.getExpiryDate()); query.setParameter(4, creditCard.getCustomerId()); query.executeUpdate(); }
|
在上面的逻辑中,我们使用entityManager提供的本机查询执行合并查询。现在,让我们编写单元测试来验证结果:
@Test void givenCreditCards_whenUpdateOrInsertUsingBuiltInFeatureExecuted_thenUpserted() { // insert test CreditCard newCreditCard = buildCreditCard(); CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNull(existingCardByCardNumber); creditCardLogic.updateOrInsertUsingBuiltInFeature(newCreditCard); existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber()); assertNotNull(existingCardByCardNumber); // update test CreditCard cardForUpdate = existingCard; String beforeExpiryDate = cardForUpdate.getExpiryDate(); cardForUpdate.setExpiryDate("2029-08-29"); creditCardLogic.updateOrInsertUsingBuiltInFeature(cardForUpdate); assertNotEquals("2029-08-29", beforeExpiryDate); CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get(); assertEquals("2029-08-29", updatedCard.getExpiryDate()); }
|