Hibernate insert doesn't work with custom native query

Question

In a spring boot project and database SQL Server, I'm doing some inserts where I need to return the id of the record I have a Entity with few fields:

public class PackGroupEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long group_id;
    private String group;
    private String remark;
    private String description;
    ....
}

I simplified the insert just to ask the question.

I have an insert statement and I need to retrieve the id from the inserted record.

String query = String.format(
                "insert into pack_group(group, remark, description ) " +
                "values ( %s, %s, %s)", "a","b","c"  );
Query q = entityManager.createNativeQuery(query );
BigInteger biid = (BigInteger) q.getSingleResult();
long id = biid.longValue();

And I get this error com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

Based on this answer here that I can use returning id so I tried but:

String query = String.format(
                "insert into pack_group(group, remark, description ) " +
                "values ( %s, %s, %s ) returning group_id;", "a","b","c"  );
Query q = entityManager.createNativeQuery(query );
BigInteger biid = (BigInteger) q.getSingleResult();
long id = biid.longValue();

but it throws an error

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'returning'.

Can someone help me, please?

Do not EVER put in user strings like this. It's a major security vulnerability. Learn how to avoid it from [Bobby Tables](https://bobby-tables.com/java). — coladict, Aug 12 '21 at 10:35

score 1 · Answer 1 · answered Aug 11 '21 at 19:49

This things is SQL Server does not support returning syntax.

If you want get id for inserted row - you should use keyword output

This is official docs for using output with INSERT statement: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15#a-using-output-into-with-a-simple-insert-statement

You can also see this answer with examples for different SQL Server version for C# (but query does not different for Java): https://stackoverflow.com/a/18373524/16644196

score 0 · Answer 2 · answered Aug 11 '21 at 19:57

SQLServer doesn't support the returning clause. Instead, use the output clause.

String query = String.format("INSERT INTO pack_group(group, remark, description) " 
                + "OUTPUT INSERTED.group_id " 
                + "VALUES( %s, %s, %s )", "a","b","c");

Hibernate insert doesn't work with custom native query

2 Answers2