Spring Data JDBC and ID Generation

Every time I need to make a service in Java that works with a relational database, I can’t decide, just like that monkey who wanted to be both smart and beautiful. I would like to make queries in regular SQL, at least surrounded by various “magic” annotations, but at the same time I am too lazy to write RowMappers myself, prepare PreparedStatements or JdbcTemplate, and the like, for which they like to call Java verbose. And every time hands reach out to Spring Data JDBC, which, it seems, was conceived as something in between. But with him, too, often, you can get into some kind of nonsense out of the blue.

It was required to me to save new records in the table. It would seem, what is the question – you take CrudRepository and everything works out of the box. But in practice, there were several nuances, for example:

• First of all, we must now actively use a whole bunch of annotations to mark up the entity (@Id, @Table, @Column, @InsertOnlyProperty, etc.)

• I prefer to use records to store data, and in this case it turns out that you need to make a separate withId method for the id field, which will return a new record with the filled id.

• I want to get id from the corresponding sequence in the database

It is on the last point that I would like to dwell in more detail. accustomed to bad good thing about JPA, I expected that setting the generation of the id field in Spring Data JDBC is done with the same generation strategy settings. But no, read documentation and find out that Spring Data JDBC can only work with auto-increment columns. For everything else, it is suggested to use the BeforeConvert listener. For details, I had to go to the know-it-all Google.

Google first line gave me a link to the blog of a certain Thorben Janssen (I apologize in advance if this is someone famous, but I don’t know him – I have a bad memory for names). And after looking at the sample code, I’m honestly a little of.. surprised. Before that, all requests in SpringData JDBC looked clean and tidy, but here again JdbcTemplate and manual parsing of the result.

I did not believe and climbed to look examples from Spring itself on GitHub. Them examplealthough it looks a little cleaner, it’s still handmade with JDBC :

@Bean
BeforeConvertCallback<Customer> idGeneratingCallback(DatabaseClient databaseClient) {

    return (customer, sqlIdentifier) -> {

		if (customer.id() == null) {

			return databaseClient.sql("SELECT primary_key.nextval") //
					.map(row -> row.get(0, Long.class)) //
					.first() //
					.map(customer::withId);
		}

		return Mono.just(customer);
	};
}

The question arises – if you still need to write an additional query to the database yourself in order to get the values ​​for the id and insert it into the entity before saving, then why not do all this more clearly and in the same style with other queries? In the end, I ended up with something like this:

public record MyEntity(long id, String someData, ...) {}


@org.springframework.stereotype.Repository
public interface MyRepository extends Repository<MyEntity, Long> {

    @Query("SELECT nextval('myentity_seq')")
    long getNextMyEntityId();

    @Modifying
    @Query("INSERT INTO my_entities (id, some_data) VALUES (:#{newEntity.id}, :#{newEntity.someData})")
    boolean insert(@Param("newEntity") MyEntity newEntity);
}


@Service
public class MyService {
    private final MyRepository repository;
        
    public long saveNewEntity(String someData) {
        var entity = new MyEntity(
                repository.getNextMyEntityId(),
                someData
        );
        if (repository.insert(entity)) {
            return entity.id();
        }
        throw new RuntimeException("Can't save");
    }
}

I think this is the best option because

• the logic for the formation of a new entity in one place, and not spread across different bins;

• all requests are in one place and are made in the same style;

• all annotations related to the framework are also collected in one place (in the repository), and the class itself with data is absolutely clean.

What do you think?

PS:

Initially, I got confused by all this only because I needed to return the id of the created record. Because otherwise, the insert request would turn into something like:

INSERT INTO my_entities (id, some_data) VALUES (nextval('myentity_seq'), :someData)

And the first thought was – to use means of a DBMS by means of insert with returning. But we have a legacy base and it didn’t work there.