6

I have a simple entity, consisting of two UUIDs:

@Table("library")
public class LibraryDao {
    @Id
    private UUID id;
    @NonNull
    private UUID ownerId;
}

I have a corresponding table in PostgreSQL:

CREATE TABLE IF NOT EXISTS library (id UUID PRIMARY KEY, owner_id UUID NOT NULL);

I am using the correct R2DBC drivers (io.r2dbc:r2dbc-postgresql and org.postgresql:postgresql).

To this point, everything works. My applpication runs. But…

Because PostgreSQL does not – at least according to the documentation – have an auto-generating function for UUIDs, I set the id when creating a new LibraryDao instance.

However, when I call the save method in my Repository, I get an exception: Failed to update table [library]. Row with Id [0ed4d7c0-871a-4473-8997-4c9c1ec67a00] does not exist.

It appears that save is being interpretted as update, without a fallback to insert if it doesn't exist.

How am I supposed to insert a new record into my database?

Rick
  • 397
  • 3
  • 16
  • I think/am sure [this(entity-state-detection)](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.entity-persistence.saving-entites) also applies to r2dbc repositories...due to "r2dbc-limitation", you could introduce a `Version` field/column or try 2. or 3. – xerx593 Jan 27 '22 at 14:12
  • Version and Persistable attempted; both failed; there is no equivalent Factory to subclass for the EntityInformation approach (that I could find). – Rick Jan 27 '22 at 14:41

6 Answers6

4

Despite the PostgreSQL documentation, there is a way to auto-generate UUIDs using the pgcrypto extension (for v4 UUIDs). (Process based on using pgAdmin GUI.)

In the Query Tool:

  • select * from pg_extension and check that pgcrypto is not listed.
  • create extension pgcrypto to install it; it comes with the default installation.

Then, change the column definition with to ... id UUID PRIMARY KEY DEFAULT gen_random_uuid(), ...

And it works.

Rick
  • 397
  • 3
  • 16
2

However, besides my answer about doing inserts using R2DBC DatabaseClient, there IS a way to do it using reactive repository's save method - by using Persistable interface, which is pretty straight-forward.

Its isNew method can be implemented using entity's version attribut, or by adding a new dedicated attribut (something like boolean isAlreadyPersisted) that could be set explicitly before invoking save(). And maybe there are also some other ways to utilize Persistable that I'm not aware of.

denizeve
  • 78
  • 2
  • 10
  • you can edit your own answers, so that you can add this to the already existing one. – AlexT Feb 05 '22 at 03:31
  • This definitely feels the cleaner approach, as it avoids manually creating SQL; I'll give it a try when I get a chance and change my acceptance if it works. – Rick Feb 07 '22 at 20:42
  • @Rick It works? – denizeve Feb 12 '22 at 21:54
  • @denizeve Sorry it took so long. It doesn't work. The SQL in the stack trace (`SQL [INSERT INTO library (owner_id) VALUES ($1)]; null value in column "id" of relation "library" violates not-null constraint`) indicates that it's trying to insert without specifying the id; it's expecting the database to generate that itself. – Rick Mar 09 '22 at 21:14
  • 1
    We've already agreed that ID auto-generation is not (yet) possible with R2DBC, it should be provided "manually", as I suggested in the other answer. This answer is about making save() work, instead of doing update every time. – denizeve Mar 10 '22 at 22:14
  • This approach, then, only adds more code, and more places for said code to break. With db-side suto-generation, save works just fine. – Rick Mar 20 '22 at 16:30
0

Since R2DBC doesn't have (yet) auto-generation of UUIDs, and tools used inside Spring have their own mechanisms which prevent sending nulls to DB (so triggers to do the job can't be used) - maybe the optimal solution here is to generate UUIDs with Java (UUID.randomUUID();) and put them in Entities before sending the Entities to DB.

EDIT - concrete solution:

A minor problem with your solution is that the DB installation must be modyfied.

After realizing described problem with insert, I quit trying to do it using the repository save method and switched to "manual" solution using R2DBC DatabaseClient:

dbClient.sql("insert into product_price(id, product_id, price) values(:id, :product_id, :price)")
      .bind("id", UUID.randomUUID())
      .bind("product_id", 1)
      .bind("price", 10.0)
      .fetch()
      .one()
      .subscribe();

And here is the DatabaseClient, configured from ConnectionFactory and enabled to accept named parameters:

@Bean
public DatabaseClient dbClient(ConnectionFactory connectionFactory) {
    return DatabaseClient.builder()
            .connectionFactory(connectionFactory)
            .namedParameters(true)
            .build();
}

The ConnectionFactory is also a easily customisable Bean I have exposed in a config class that extends AbstractR2dbcConfiguration.

And hopefully, save() in reactive repository will be improved to behave like its counterparts in other spring-data repositories.

denizeve
  • 78
  • 2
  • 10
  • Thanks. I tried that. The internal structure of R2DBC (or some key part of the pipeline) is such that if the ID is supplied, it always does an `UPDATE`, not an `INSERT`. So the ID generation for the primary key *must* be left to the database, thereby falling back to my solution. – Rick Feb 04 '22 at 14:16
  • I have edited my answer with the solution I used. – denizeve Feb 04 '22 at 15:32
0

Because you've provided the @Id. The library needs to figure out, whether the row is new or whether it should exist.

Already answered by Mark Paluch to this question on spring-data-r2dbc issues board. See #275 for further reference. And also you can find useful information from #49.

doniadhamov
  • 97
  • 1
  • 4
0

I have read and studied the above and not only here, however, it did not solve the problem. This is not enough.

Your field (Primary key) must have a column type, for example, serial, so that the database can automatically, when inserting a record, increase the record id.

Without this, all other recipes will not work.

skyho
  • 1,438
  • 2
  • 20
  • 47
-4

The UUID can be auto-generated, if you generate it by yourself Hibernate sees the entity with an id and try to update it.

To auto-generate the uuid just use the following annotations on your fields:

@GeneratedValue(generator = "UUID")
@GenericGenerator(
    name = "UUID",
    strategy = "org.hibernate.id.UUIDGenerator",
)

source: https://thorben-janssen.com/generate-uuids-primary-keys-hibernate/

jashinhidan
  • 63
  • 1
  • 7