Generate UUID values by default for each row on column of UUID type in H2 Database Engine

Question

In the H2 database, on a table with a column of UUID data type, how do we specify that we want H2 to generate a UUID value by default when an INSERT omits that field?

I know how to generate a UUID. I have read the Question, How to insert a specific UUID in h2 database?.

My question is about how to ask H2 to generate the UUID value on my behalf.

Thomas Mueller · Accepted Answer · 2021-02-18T07:15:00.737

31

In SQL

You can use built-in function RANDOM_UUID():

create table test(id int primary key, data uuid default random_uuid());
insert into test(id) values(1);
select * from test;

Note that using the UUID type (or any other randomly generated data that doesn't have any natural ordering) as the primary key will result in performance problems if there are more than a few million rows (with relational databases in general, not just with H2).

edited Feb 18 '21 at 07:15

answered Dec 13 '16 at 13:00

Thomas Mueller

48,905
14
116
132

Hi, would you mind source the part about performance issue? I am working with reactive programming and I most likely have to rely on database (Postgre) to generate a random UUID. I would love to avoid performance issues after few months of release. – Vincent C. Feb 17 '21 at 06:08
2

See for example https://blog.programster.org/mysql-performance-when-using-uuid-for-primary-key Performance impact depends many things; for PostgreSQL I think the impact is lower than for H2 / MySQL / MS SQL Server, but I'm afraid I don't understand why exactly so far. The problem doesn't show up if the ids are somewhat ordered, as for example in https://github.com/anthonynsimon/timeflake – Thomas Mueller Feb 18 '21 at 07:13

score 24 · Answer 2 · edited Nov 21 '20 at 00:42

In JPA/Hibernate

After googling for hours I've created a simple solution for JPA or Hibernate, according to @Thomas Mueller.

@Id
@GeneratedValue(generator = "UUID")
@GenericGenerator(
        name = "UUID",
        strategy = "org.hibernate.id.UUIDGenerator"
)
@Column(name = "ID", updatable = false, nullable = false)
@ColumnDefault("random_uuid()")
@Type(type = "uuid-char")
@Getter
@Setter
private UUID id;

So @Type(type = "uuid-char") makes your field a VARCHAR(255) if you need it (it's binary by default)

And @ColumnDefault("random_uuid()") puts the default value for generated table DDL

Perfect answer, saves a ton of time. Thanks a lot. – WesternGun Feb 19 '23 at 16:16 — WesternGun, Feb 19 '23 at 16:16

Generate UUID values by default for each row on column of UUID type in H2 Database Engine

2 Answers2

In SQL

In JPA/Hibernate

Linked