JPA - perform an insert on a Postgres table whose primary key is generated from a database trigger-CodePudding

I am writing an API where I am inserting a record into a table (Postgres). I was hoping to use JPA for the work. Here is the potential challenge: the primary key for the insert is generated from a database trigger, rather than from sequence count or similar. In fact, the trigger creates the primary key using the values of other fields being passed in as part of the insert. So for example, if I have a entity class like the following:

@Entity
@Validated
@Table(name = "my_table", schema="common")
public class MyModel {

@Id
@Column(name = "col_id")
private String id;
@Column(name = "second_col")
private String secCol;
@Column(name = "third_col")
private String thirdCol;


public MyModel() {

}

public MyModel(String id, String secCol, String thirdCol) {
    this.id = id;
    this.secCol = secCol;
    this.thirdCol = thirdCol;
}


}

I would need the col_id field to somehow honor that the key is generated from the trigger, and the trigger would need to be able to read the values for second_col and third_col in order to generate the primary key. Finally, I would need the call to return the value of the primary key.

Can this be done with jpa and repository interface such as:

public interface MyRepo extends JpaRepository <MyModel, String> {
}

and then use either default save method such as myRepo.saveAndFlush(myModel) or custom save methods? I can't find anything on using JPA with DB triggers that generating keys. If it cannot be done with JPA, I would be grateful for any alternative ideas. Thanks.

CodePudding user response：

ok, I was able to get this to work. It required writing a custom query that ignored the primary key field:

public interface MyRepo extends JpaRepository <MyModel, String> {

    @Transactional
    @Modifying
    @Query(value = "INSERT INTO my_table(second_col, third_col)", nativeQuery = true)
    int insertMyTable(@Param("second_col") String second_col, @Param("third_col") String third_col);
}

The model class is unchanged from above. Because it was executed as a native query, it allowed postGres to do its thing uninterrupted.