[hibernate-dev] [ORM] Support for upsert semantics

[Steve Ebersole]

It's been discussed several times in the past.

We already have something very similar - `Session#saveOrUpdate`.  So what
exactly is the difference semantically?  There really is none.  There is,
however, a practical difference...  `Session#saveOrUpdate` handling
leverages Hibernate's "unsaved value"[1] to decided between an INSERT and
an UPDATE.  In most cases, this is an extremely efficient decision.
However, for an entity that (1) is unversioned and (2) uses assigned
identifiers, Hibernate will actually perform a SELECT to make this decision.

So it is conceivable to handle all `Session#saveOrUpdate` or maybe just
this special case using an UPSERT.

However, there are also cases where we simply cannot use UPSERT.  The main
one that comes to mind as particularly difficult is an entity which defines
one or more properties (or sub-embedded properties) as insertable=true &
updatable=false, or vice-versa.

All-in-all I am just not convinced that the effort is worth it given the
limited applicability.  But certainly, if someone wanted to implement such
a thing we should consider including it.

Now, internally there is a case where we might be able to leverage UPSERTs
for performance... Namely when updating an entity with optional secondary
tables we will first try to update the row in the secondary table and then,
if a row did not exist, insert one.  We could change this to
leverage UPSERT.  However, we still have the same limitation with regard to
insertable/updatable here as well.

[1] HBM's `<id ... unsaved-value="..."/>, don't think we have an annotation
equivalent though at the moment.  We have a pretty decent algorithm for
"guessing" the unsaved value.

On Tue, Sep 11, 2018 at 4:32 AM Gunnar Morling <gunnar at hibernate.org> wrote:

> Hi,
>
> A common requirement that comes up repeatedly is "upsert" operations, i.e.
> either insert a record or update the existing one with the same PK, if it
> already exists. Many (most?) RDBMS support it, either by implementing SQL
> 2003's MERGE keyword or via proprietary alternatives [1].
>
> I think it'd be great if there was something like Session#upsert(),
> exposing these semantics at the entity level. Perhaps the idea was already
> brought up in the past, but I wanted to throw it out in any case.
>
> Any thoughts?
>
> Cheers,
>
> --Gunnar
>
> [1]
>
> https://vladmihalcea.com/how-do-upsert-and-merge-work-in-oracle-sql-server-postgresql-and-mysql/
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>