spring-projects / spring-data-r2dbc

Provide support to increase developer productivity in Java when using Reactive Relational Database Connectivity. Uses familiar Spring concepts such as a DatabaseClient for core API usage and lightweight repository style data access.
Apache License 2.0
708 stars 133 forks source link

Add support for SQL Server's `INSERT … OUTPUT` clause #577

Open vasilhsfoto opened 3 years ago

vasilhsfoto commented 3 years ago

Current setup

dependencies spring boot v 2.4.3 `

org.springframework.data spring-data-r2dbc
    <dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-mssql</artifactId>
    </dependency>
    <dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-pool</artifactId>
    </dependency>

` Repository ItemRepository extends ReactiveCrudRepository<Item, UUID>

DB schema in mssql server [id] UNIQUEIDENTIFIER default NEWID(), ... code var itemToSave = new Item().setName(); RegistrationRoute newSavedEntity = itemRepo.save(itemToSave) .log() .block(); The entity is the same instance and the id is not populated.

Notes: The above works as expected when running on H2 data base. Although the instance is mutated as opposed a brand new one to be created which I would expect.

The type of the id has the same behaviour declared either UUID or String. Which is as expected since the mapping between DB type and Java type comes from the mssql r2dbc driver which supports UUID/String -> uniqueIdentifier

Trying the varchar() (with auto generated GUID) -> String mapping for key. Still doesn't work Trying the INT (with IDENTITY => autogenerate sequence)-> Integer/int mapping for key it does work as expected

mp911de commented 3 years ago

This is by design. SQL Server doesn't support by default echoing of inserted values through select SCOPE_IDENTITY() AS GENERATED_KEYS.

The R2DBC driver aligns with the JDBC driver, see:

https://github.com/microsoft/mssql-jdbc/blob/89bb744675941113a0ca9bcd7dc3c8f1310bb23c/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerStatement.java#L860-L863

Capturing output values requires a different INSERT statement that declares an OUTPUT clause:

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15#t-using-output-with-an-insert-statement

vasilhsfoto commented 3 years ago

My goal is to use the repository. AFAIU, I can't generate the id of the entity in the java code. there is no generated ID annotation or something like that. At the same time, the Insert statement generated by repository doesn't return the generated id as per your comment. So looks like for now the only option is to use template or DatabaseClient. And since there is a bug currently with using template on insert (DB errors are not thrown as exception). Looks like the only option is to use DatabaseClient for the insert. could you please advice