unable to test UUID

Question

I have a spring boot 2.6.7 app, using Liquibase, Gradle and Spock. I have a class that uses a guid format string as the ID:

@Entity
@Table(name = "devices")
public class Device {

    @Id
    @NotNull
    @Pattern(regexp = "^[{]?[0-9a-fA-F]{8}-([0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}[}]?$",
             message = "Invalid id received")
    private String deviceId;

    @NotNull
    private DeviceType deviceType;    
    @JsonFormat(shape = Shape.STRING, pattern = "yyyy-MM-dd")
    private LocalDate manufactureDate;
    @JsonFormat(shape = Shape.STRING, pattern = "yyyy-MM-dd")
    private LocalDate activationDate;
    @JsonFormat(shape = Shape.STRING, pattern = "yyyy-MM-dd")
    private LocalDate deactivationDate;
    private Status deviceStatus;

I have several endpoint that take this entity and persist/find/update it. When I look at the database, I see the columns are properly populated. So it appears to be working.

Here is my liquibase for this table:

- changeSet:
      id: 7
      preConditions:
        - onFail: MARK_RAN
          not:
            tableExists:
              tableName:
                devices
      changes:
        - createTable:
            tableName: devices
            columns:
                nullable: false
              - column:
                  name: device_id
                  type: varchar(255)
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: device_type
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: manufacture_date
                  type: date
                  constraints:
                    nullable: true
              - column:
                  name: activation_date
                  type: date
                  constraints:
                    nullable: true
              - column:
                  name: deactivation_date
                  type: date
                  constraints:
                    nullable: true
              - column:
                  name: internal_id
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: device_status
                  type: varchar(255)
                  constraints:
                    nullable: true

However, I am now trying to wrap my code in tests and I keep running into issues that a string cannot be cast as UUID. Here is the test:

given:
        def device = new Device(internalId: internalId, deviceId: deviceId,
                deviceType: deviceType, deviceStatus: deviceStatus, role: role, activationDate: activationDate, deactivationDate: deactivationDate, manufactureDate: manufactureDate)

        def result = deviceRepository.save(device)
        when:

        def isValid = deviceServices.validateDevice(result)
        then:
        isValid == testResult

        where:
        deviceId           | deviceType          | deviceStatus       | manufactureDate | activationDate  | deactivationDate | || testResult
        UUID.randomUUID() | DeviceType.EQUIPMENT | Status.ACTIVATED   | LocalDate.now() | LocalDate.now() | null               || true

And here is the error when the test starts by trying to insert into H2. The field is declared as a varchar, but for some reason the stack trace mentions NumberFormat and Long values.

SQL Error: 22018, SQLState: 22018
2022-05-12 06:24:34.083 ERROR 42198 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Data conversion error converting "faf837fa-8584-4dff-a8d1-bd4d9a8af74c"; SQL statement:
select identity0_.device_id as scanned_1_6_, identity0_.internal_id as internal2_6_ from identities identity0_ where identity0_.device_id=? [22018-212]

...

Caused by: org.h2.message.DbException: Data conversion error converting "9bbb114a-bb99-443c-9106-dd28210c4e7b" [22018-212]
    at org.h2.message.DbException.get(DbException.java:212)
    at org.h2.value.ValueStringBase.getLong(ValueStringBase.java:142)
    at org.h2.value.Value.convertToBigint(Value.java:1645)
    at org.h2.value.Value.convertTo(Value.java:1137)
    at org.h2.value.Value.convertForAssignTo(Value.java:1092)
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:369)
    ... 53 more
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "9bbb114a-bb99-443c-9106-dd28210c4e7b" [22018-212]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:506)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
    ... 59 more
Caused by: java.lang.NumberFormatException: For input string: "9bbb114a-bb99-443c-9106-dd28210c4e7b"
    at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.base/java.lang.Long.parseLong(Long.java:692)
    at java.base/java.lang.Long.parseLong(Long.java:817)
    at org.h2.value.ValueStringBase.getLong(ValueStringBase.java:140)
    ... 57 more

I feel like I have coded myself into a corner by not making the primary key a UUID, or by not having a regular ID and then using the deviceId as a UUID. However, this is production code and I am hesitant to change the table structure via Liquibase.

Is there a way to make Liquibase or Spock work around this issue? Again, it works fine in production, I just can't do an integration test on it (which might be a H2 limitation?)

Update: I have another test that has the same behavior - unable to convert a UUID to a string attribute of the class upon persistence. If I change the "UUID.randomUUID()" to "123" it works as expected.

Because I am seeing this only with my tests and the exception is SqlExceptionHelper I have to wonder if test H2 just can't handle the conversion that production Postgres does? I changed H2 for HSQL and get the same type of error.

Answer 1

The type of Device.deviceID is String, but the type of deviceId in your Spock spec is UUID. So you either need to use

def device = new Device(internalId: internalId, deviceId: deviceId.toString(), ...

or

where:
deviceId                     | ...
UUID.randomUUID().toString() | ...

It really is as trivial as that.