UUID id is null after saving entity with spring-data-jdbc on sql server

Question

I have following table (snippet):

create table users
(
    user_id uniqueidentifier default newid() not null primary key,
    name    varchar(192),
    ...
)

and the entity:

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Table(value = "users")
public class User {
   
    @Id
    @Column("user_id")
    private UUID userId;
}

Running a save against an h2-database with sql-server configurations works fine, however if run a save against a real sql-server db, I get

java.lang.IllegalArgumentException: After saving the identifier must not be null!
    at org.springframework.util.Assert.notNull(Assert.java:201)
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.store(JdbcAggregateTemplate.java:364)
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:161)

I saw various other posts, dealing with similar issues, however most use approaches via hibernate/JPA, which i want to avoid, if possible.

I tried various strategy settings of @GeneratedValue annotation, but none of them worked.

Is there a way to solve this issue solely using spring-data-jdbc ? How would I have to annotate my id property correctly, so that sql-server creates the uuid and pass it along to spring-data-jdbc ?

Im using SQL server 2019 with default settings.

edit:

To save an entity, I use save of UserRepository extends CrudRepository<User, UUID>

edit-2:

With this workaround, I was able to get it working:

 @Bean
 BeforeConvertCallback<AlertUser> beforeConvertCallback() {
     return (user) -> {
         if (user.getId() == null) {
             user.setId(UUID.randomUUID());
         }
         return user;
     };
 }

But it is still not the desired solution, since it relies on client side uuid generation.

I digged a bit further and kinda located the issue is happening, when spring tries to fetch the generated ids. Afaics it expects a ResultSet containing a row with property GENERATED_KEYS, but on sql-server this property is never or differently set by the dbms as it seems.

Can you show how you save your entity? – Harry Coder Jul 02 '23 at 05:27 — Harry Coder, Jul 02 '23 at 05:27

score 1 · Accepted Answer · answered Jul 03 '23 at 12:58

@GeneratedValue is a JPA annotation. It is completely unrelated to Spring Data JDBC and therefor will have no effect on your Spring Data JDBC setup.

As far as I can tell Microsoft SqlServer (or it's JDBC driver) does not return ids generated in the way you setup your table. Therefore Spring Data JDBC can't pick them up.

The correct way around this is to use the construct you already discovered:

 @Bean
 BeforeConvertCallback<AlertUser> beforeConvertCallback() {
     return (user) -> {
         if (user.getId() == null) {
             user.setId(UUID.randomUUID());
         }
         return user;
     };
 }

You probably want to drop the default newid() clause on the database side so to not confuse people where the UUID is generated. This is the "right" approach because

UUIDs are unique by construction even when generated on separate devices
This way the database load is minimised, which is good, because in high load scenarios it is easy to add 100 additional application servers. It is not so easy to scale a database once it doesn't fit on a single machine anymore.

Of course, there is always the scenario when someone higher up in the food chain told you to generate the UUID on the database. In that case there are a couple options:

Patch Spring Data JDBC to use this approach for inserting. If you do it in a way that is compatible with everything else Spring Data JDBC will probably accept a PR.
Convince Microsoft to improve there JDBC driver to also return ids generated in the way you prefer. This should Spring Data JDBC automatically pick up that id.
Use Springs NamedParameterJdbcTemplate or JdbcTemplate to insert your entity end use the approach referenced above to obtain the generated id. You can put that code into a custom method implementation in your Spring Data JDBC repository.

I was a bit concerned, that client side uuid generation might produce colliding uuids, but rethinking on that, i assume the probability will be quite low. It's not really required, that I use server side uuid generation, it was more like I had the feeling, this is such a basic scenario, cant imagine there is no 'spring' way of configuring / fixing this. — lunatikz, Jul 03 '23 at 14:33
See https://stackoverflow.com/questions/2513573/how-good-is-javas-uuid-randomuuid — Jens Schauder, Jul 04 '23 at 06:24

UUID id is null after saving entity with spring-data-jdbc on sql server

1 Answers1