Can't find a existing line using JPA with H2 based on UUID criteria

Question

I am writing a junit test using Spring Boot JPA. My entity has an attribute of type UUID (BaseEntity defines a Long id which is a sequence in database). My mapping is in a orm.xml file:

public class User extends BaseEntity {
    private String username;
    private UUID uuid;
...
}

I have defined a UserRepoImpl class that search for a User using a given UUID (jpaRepo being an interface extending JpaRepository<User,Long>:

public Optional<User> getUserByUuid(UUID aUuid) {
        return jpaRepo.findByUuid(aUuid);
    }

I have written a junit to test this method against a H2 database and I use a sql file to insert data before the test :

@ActiveProfiles("tu")
@ExtendWith(SpringExtension.class)
@DataJpaTest(includeFilters = @ComponentScan.Filter(type = FilterType.ANNOTATION, classes = Repository.class))
@Sql("/sql/infra/repo/user-repository.sql")
public class UserRepositoryImplTest {

    @Autowired
    private UserRepositoryImpl cut;
    
    @Test
    void should_ReturnUser_WhenUUIDExist() {
        UUID uuid = UUID.fromString("9fc1cd41-9d28-463f-94b9-542836572802");
        
        Optional<User> user = cut.getUserByUuid(uuid);

        Assertions.assertTrue(user.isPresent());
        Assertions.assertEquals(1L, user.get().getId());
        Assertions.assertEquals(uuid, user.get().getUuid());
    }
}

The SQL file inserts a user with a UUID that I have converted : INSERT INTO USERS (id, USERNAME, UUID) VALUES (3, 'user3', X'9FC1CD419D28463F94B9542836572802');

The test fails because getUserByUuid() doesn't return any user.

What I don't understand is why the UUID column is generated in H2 with a binary type when there is a UUID type in H2 :

Hibernate: create table users (id bigint not null, username varchar(255), uuid binary(255), account_id bigint, primary key (id))

I tried to use varchar for storing the UUID and that works but I don't want to use varchar to store my UUID.

I use https://www.piiatomi.org/uuid_converter.html to make conversion between UUID and hex.

Any idea ? Thank you!

Answer 1

binary(255) is a fixed-length binary string (byte array in Java) with exactly 255 bytes. This is a wrong data type for UUID values, but some versions of Hibernate ORM incorrectly choose it for UUID properties.

You can override this default with some correct type:

@Column(columnDefinition="UUID")
private UUID uuid;