Justin Smith Justin Smith - 6 days ago 6
Java Question

Spring MVC JPA Native Insert With Generated Column Returned

These are the things I need to accomplish when inserting into PostgreSQL via JPA.

1) Insert to a table with the following added to the end of the insert "ON CONFLICT DO NOTHING" (Or some annotation if that exists)

2) Have the generated id column returned

Currently I am using the following method but it does not return the generated column...

@Transactional
@Modifying
@Query(nativeQuery = true, value = "INSERT INTO table_name (value1) values(?1) ON CONFLICT DO NOTHING")
int insertWithoutConflict(Long value1);


The solution I have that works without JPA involved is the following....

try(Connection conn = DatabaseUtils.getConnection()) {
Statement statement = conn.createStatement();
statement.execute(query, Statement.RETURN_GENERATED_KEYS);
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next())
return resultSet.getLong(1);
}


Only problem with this is that I then am circumventing our current process and then have to start implementing a connection pool and I do not want to get into that. I want to know if this solution is possible in JPA or Hibernate.

Answer according to @kayamane

@Transactional
@Query(nativeQuery = true, value = "INSERT INTO My_Table(value_1, value_2) values(?1, ?2) RETURNING any_column")
Long insertWithoutConflict(String value1, Long value2);


Can also return the model object I believe

@Transactional
@Query(nativeQuery = true, value = "INSERT INTO My_Table(value_1, value_2) values(?1, ?2) RETURNING column_1, columnName_2, columnName_3, columnName_4")
YourModelObject insertWithoutConflict(String value1, Long value2);

Answer

Remove the @Modifying annotation, since it the query may not modify the data.

Then add ON CONFLICT DO NOTHING RETURNING value1 (or RETURNING *), but you'll have to change that int to Integer since a conflict won't return any generated values.