Use merge into to complete saveOrUpdate in SQL Server and HSQLDB

When we call the saveOrUpdate () of Hibernate or the Hibernate implementation of the save () method of JPA, we will do two steps: 1) press ID to query whether the record already exists; 2) No new record is inserted, and the original record is updated if it exists. This two-step operation can actually be done in a single statement in SQL Server and HSQLDB, which is the merge into statement that this article will introduce. Feel that using the database's own characteristics, and one statement will be better than saveOrUpdate () two-step operation performance, still need to be measured.

The reason for putting SQL Server and HSQLDB together is that our unit tests in the actual project are based on the HSQLDB in-memory database. Merge into, as its name suggests, should give us the convenience of merging eligible records from one table into another. We only use this feature here to implement Hibernate-like saveOrUpdate () operations.

Suppose we have a simple table.

CREATETABLE user (id INT, nameVARCHAR (32), address VARCHAR (128)

If the name and address that update the original record already exist in the record of id, insert the new record to read the full text > >