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.

Eke

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.

Exaction

Thanks for your anwser, I also agree with your explanation, although this method is not very good, it can solve my problem, thank you! – Eke

I have the same problem and even if it works, this solution is really complicated. There are many cases where the id is already defined by the datamodel and does not need to be created randomly. Something like an articlenumber is already a good fit for an id, so I do not want a random one to be created, because then I have to take care of not having duplicated articles. This event listener seems more like a workaround here. Are you planning to include something like upsert in spring-data-jdbc directly? – Binturong

@ErikP That is certainly possible. Please create a ticket: jira.spring.io/projects/DATAJDBC – Exaction

@ErikP I just merged jira.spring.io/browse/DATAJDBC-282 which offers an insert method allowing you to prepopulate the id. I guess that might help a little. – Exaction

JdbcAggregateTemplate is highly worth noting! – Calie

This solution seems more like workaround. I will prefer generate id by database or implement Persistable interface than subscribe to this event. This solution is not transparent. – Smilacaceous

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.

Herminiahermione

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);
}

Machination

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.

Bantu