What is the SQL/JDBC equivalent to JPA merge API? What does it exactly do?

I'm migrating JPA api's like persist,save,merge,refresh,detach and remove to plain SQL using JDBC, where iam finding it hard to understand the concept of EntityManager.merge(someTask).

I tried a SQL update query for the merge API but the explanation of merge is as follows Merge the state of the given entity into the current persistence context.But with plain SQL and JDBC its hard to understand how to do the same and i need to handle OptimisticLock as well.

The entity class which is used for JPA is as follows.

@Entity
@Table(name = "TASK", indexes = {@Index(name = "RIO", columnList = "priority", unique = false),
@Index(name = "EXP", columnList = "expiry", unique = false), 
@Index(name = "STA", columnList = "taskStatus", unique = false),
@Index(name = "CAT", columnList = "category", unique = false),
@Index(name = "NEXTTRY", columnList = "nextTry", unique = false)})

public class TaskEntity {

@Version
private int version;

@Basic
@Column(length = Integer.MAX_VALUE, columnDefinition = "varchar(" + Integer.MAX_VALUE + ")")
private String taskId;

@Basic
private String category;

@ElementCollection(fetch = FetchType.EAGER)
@MapKeyColumn(name = "KEY")
@CollectionTable(name = "TASKPROPERTIES", foreignKey = @ForeignKey(
                 name = "TASK_ID_FK",
                 foreignKeyDefinition = "FOREIGN KEY (TASKENTITY_ID) REFERENCES TASK (ID) ON DELETE CASCADE"))

@Column(length = Integer.MAX_VALUE, columnDefinition = "varchar(" + Integer.MAX_VALUE + ")")
private Map<String, String> TaskProperties;

@Basic
@Column(length = Integer.MAX_VALUE, columnDefinition = "varchar(" + Integer.MAX_VALUE + ")")
private String destination;

@Enumerated(EnumType.STRING)
private TaskStatus taskStatus;

@Basic
private String type;

@Basic
private Long expiry;

@Basic
private Long nextTry;

@Basic
private Integer retries;

@Basic
private Integer priority;

//Setters and Getters
//Equals and HashCode

}

Hence what would be the equivalent of EntityManger.merge(task) to SQL/HSQL.

Solution

Merge in essence is the process of merging an existing record in a table with what has been provided in the statement (i.e. UPDATE if the record exists else INSERT). Also known as UPSERT.

Let us say you have a table tbl_person that has primary key person_ssn and two other columns namely name and age. In case you want to insert a statement on a person_ssn that happens to exist there, DBs will throw error. Your requirement is to insert a record if the person_ssn doesn't exist else update the name and age. In such situation you will use Merge.

There are few ways to achieve this, two of them are

Issue at least two DML statements. First do a SELECT on the person_ssn and based on whether you found a record, subsequently, you will either issue an UPDATE or an INSERT statement
Use MERGE SQL statement. This is the more modern and direct way but not all databases support it. Read more information here. Further, just to get an idea, check here, on how MERGE SQL statement works in SQL Server which supports it

As far as Java JPA is concerned, implementations abstract this concept. Depending on DB's support for MERGE SQL statement, either it is used or two statements (SELECT followed by either UPDATE or INSERT) are issued to accomplish the same.

hsqldb offers MERGE SQL support as per comment provided.