How to insert a record with custom id use Spring data jdbc?

Question

For Spring Data JPA, I can use @GeneratedValue(strategy = GenerationType.AUTO) to insert a record with a custom id, but for Spring Data JDBC, how do I insert a record with custom id? I hav tried to insert with id, but no exception was thrown and the record was not inserted into the table.

Answer 1

The way to do that with Spring Data JDBC is to register a BeforeSaveEvent ApplicationListener that creates the id and sets it in the entity.

@Bean
public ApplicationListener<BeforeSaveEvent> idSetting() {

    return event -> {

        if (event.getEntity() instanceof LegoSet) {

            LegoSet legoSet = (LegoSet) event.getEntity();
            if (legoSet.getId() == null) {
                legoSet.setId(createNewId());
            }
        }
    };
}

There is an example demonstrating that in the Spring Data Examples

The reason your row wasn't inserted in the table, but you also didn't get an exception is: Spring Data JDBC concluded that the entity already existed since the ID was set and performed an update. But since it didn't exist the update failed to update any rows, so nothing happened. It might be worth creating an improvement request to check the update count against 0.

UPDATE

Since version 1.1 JdbcAggregateTemplate.insert is available allowing you do an insert without any check if an aggregate is new. You can use that to create a custom method in your repository, if you want, or you can autowire the template wherever you need it and use it directly.

Also with DATAJDBC-438 Spring Data JDBC will throw an exception if an aggregate is saved, resulting in an update but the update updates zero rows so this kind of problem doesn't get unnoticed.

Answer 2

I found another way to solve this problem although it's a little trouble.

// BaseEntity
public class BaseEntity implements Persistable, Serializable {

    @Id
    String id;

    @Transient
    @JsonIgnore
    private boolean newEntity;

    @Override
    public Object getId() {
        return id;
    }

    public void setNew(boolean newInstance) {
        this.newEntity = newInstance;
    }

    @Override
    @JsonIgnore
    public boolean isNew() {
        return newEntity;
    }
}

// User
User extends BaseEntity
...


// insert
User user = new User();
user.id = "5bffb39cc5e30ba067e86dff";
user.setName("xiangzi");
user.setNew(true);
userRepository.save(user);


// update
User user = new User();
user.id = "5bffb39cc5e30ba067e86dff";
user.setName("xiangzi2");
userRepository.save(user);

When insert needs to add the line user.setNew(true);.

Thanks!

I also added a comment here.

Answer 3

I referred to Xianghi's answer above and used it slightly differently:

@Data
@Table
public class TestDataTable implements Persistable<String> {


@Id
String id;

private String name;

@Transient
@JsonIgnore
private boolean newEntity;

@Override
public String getId() {
    return id;
}

public void setNew(boolean newInstance) {
    this.newEntity = newInstance;
}

@Override
@JsonIgnore
public boolean isNew() {
    return newEntity;
}
}

// created new record when we use task.setNew(true) in TestDataTable with String Id.
public Mono<TestDataTable> saveTestData(TestDataTable task) {
    task.setNew(true);

    return testRepository.save(task);
}

Answer 4

I also struggled with the limitation. There are some workarounds https://spring.io/blog/2021/09/09/spring-data-jdbc-how-to-use-custom-id-generation, but they're not perfect solutions for me.

I'm developing Spring Data JDBC integration for SQLite and it provides a repository helper class that exposes JdbcAggregateTemplate's insert() and update() APIs (https://github.com/komamitsu/spring-data-sqlite#repository-helper-class). The mechanism isn't SQLite specific, so I think the same way would work although some implementation will be needed.