Primary key column is not included in insert SQL command

[sergey-morenets]

Hi

We have the following entity:

@Table("ORDERS")
public class Order {
	
	@Id
	private UUID id;
	
	private int product;
	
	@CreatedDate
	private LocalDate createdAt;

Here's database script to create this table:

CREATE TABLE ORDERS(id varchar(40) primary key, CREATED_AT timestamp, PRODUCT int);

So id is primary key but not auto-increment column. And we populate it using BeforeSaveCallback:

	@Bean
	public BeforeSaveCallback<Order> beforeSaveCallback() {
		return (order, aggregateChange) -> {
			if (order.getId() == null) {
				order.setId(UUID.randomUUID());
			}
			return order;
		};
	}

However resulting insert SQL doesn't contain id column and thus fail:

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
INSERT INTO "ORDERS" ("CREATED_AT", "PRODUCT") VALUES (?, ?) [23502-214]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:508)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.table.Column.validateConvertUpdateSequence(Column.java:365)
	at org.h2.table.Table.convertInsertRow(Table.java:926)
	at org.h2.command.dml.Insert.insertRows(Insert.java:167)
	at org.h2.command.dml.Insert.update(Insert.java:135)
	at org.h2.command.CommandContainer.executeUpdateWithGeneratedKeys(CommandContainer.java:242)
	at org.h2.command.CommandContainer.update(CommandContainer.java:163)
	at org.h2.command.Command.executeUpdate(Command.java:252)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:209)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:169)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$3(JdbcTemplate.java:992)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)

Spring Data JDBC: 3.1.0

[mp911de]

Please use BeforeConvertCallback. BeforeSaveCallback is created after converting the entity into AggregateChange that defines the SQL statements to be executed.

[sergey-morenets]

Hi @mp911de

Thank you for the quick reply. If I use BeforeConvertCallback then error disappears. However I use BeforeConvertEvent then issue still reproduces:

	@Bean
	public ApplicationListener<BeforeConvertEvent<Order>> beforeConvert() {
		return event -> {
			Order order = event.getEntity();

			if (order.getId() == null) {
				order.setId(UUID.randomUUID());
			}
		};
	}

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
INSERT INTO "ORDERS" ("CREATED_AT", "PRODUCT") VALUES (?, ?) [23502-214]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:508)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.table.Column.validateConvertUpdateSequence(Column.java:365)
	at org.h2.table.Table.convertInsertRow(Table.java:926)
	at org.h2.command.dml.Insert.insertRows(Insert.java:167)
	at org.h2.command.dml.Insert.update(Insert.java:135)
	at org.h2.command.CommandContainer.executeUpdateWithGeneratedKeys(CommandContainer.java:242)
	at org.h2.command.CommandContainer.update(CommandContainer.java:163)
	at org.h2.command.Command.executeUpdate(Command.java:252)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:209)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:169)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$3(JdbcTemplate.java:992)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)

[mp911de]

Events aren't intended for updating the aggregate/entity as events can be multicasted on threads without synchronizing with the code paths that publish those events.

Closing this one as the original issue is resolved.

[sergey-morenets]

@mp911de

Sorry, but your statement contradicts documentation:
https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.events

Here's description of BeforeConvertEvent:

Before an aggregate root gets converted into a plan for executing SQL statements, but after the decision was made if the aggregate is new or not, i.e. if an update or an insert is in order. This is the correct event if you want to set an id programmatically.

[mp911de]

Good catch. We should have updated the documentation about events. We fixed the docs via #1538.