Repository.findAll() returning capitalized UUIDs?

Question

I'm having a bit of a strange problem concerning Java Spring Data JPA Repositories and a SQL Server table I'm working with which has a unique identifier field (the PK field, although the fact that it's a PK is not relevant to the question).

The issue I'm coming across is that, when I insert a new item into this table, the UUID comes back as lowercase. When I do a 'findOne' (using Spring Data pre-2.0) on the table, it also comes back as lowercase, even if the 'findOne' parameter is in uppercase. However, when I do 'findAll' on this table, the IDs all come back as uppercase. I would like to be able to do something like this, for testing:

String id = repository.save(...).getField();
List<String> data = repository.findAll().map(d -> d.getField());
assertThat(data.contains(id));

The problem is that the first line will return a lowercase ID and the second line will return a list of uppercase IDs, so the 3rd line will fail.

As for using Strings for UUIDs, I am aware this is not the recommended practice; however, when I don't do this (when I use the UUID type), I run into a whole different problem which is out of scope for this question. If the recommended answer is "don't use Strings for UUIDs", then I may ask that as a separate question.

Abbreviated table definition:

CREATE TABLE [dbo].[TABLE](
    [ID] [uniqueidentifier] NOT NULL,
    <...>
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TABLE] ADD  CONSTRAINT [DF_TABLE_ID]  DEFAULT (newid()) FOR [ID]
GO

And the entity definition:

@Entity
@Table(name = "TABLE")
public class DataEntity {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "ID", updatable = false, nullable = false, unique = true)
    private String id;
    <...>
}

If you _do_ use strings for UUIDs, then you certainly are going to have to use the formatting on INSERT that the database gives you on SELECT. I don't know what your ORM is doing here, but SQL will implicitly convert UNIQUEIDENTIFIERS to string types in all caps, and will implicitly convert strings in lower or mixed case to UNIQUEIDENTIFIER. So all caps is the only format that will round-trip. — David Browne - Microsoft, Jul 26 '18 at 22:52
Could you please share the entity and table definitions? It isn't clear what to me what fare `UUID`s and what are `String`s or DB equivalents of that. Also: how are you generating the UUIDs? — Jens Schauder, Jul 27 '18 at 05:29
@DavidBrowne-Microsoft The UUIDs are automatically generated by Hibernate (the DB interaction framework that Spring Boot uses). They are generated in lowercase, although indeed stored in uppercase as you say. The weird part of your comment is that, when I do a SELECT through Hibernate (using repository.findOne) then I get the UUID back in lowercase; it's only when I do repository.findAll that I get uppercase. — Ertai87, Jul 27 '18 at 13:06
@JensSchauder I've added a brief description of the table and entity definitions to the OP. — Ertai87, Jul 27 '18 at 13:07
Either you didn't or I'm seeing a cached version of everything. The only edit I see is a changed tag. — Jens Schauder, Jul 27 '18 at 13:10
@JensSchauder Sorry, I posted that comment and then I added the additional info. You checked too fast :p — Ertai87, Jul 27 '18 at 13:12

score 0 · Answer 1 · answered Jul 27 '18 at 15:22

With the generator, you specified you generate UUIDs on the application side and send them to the database where they are stored in an efficient format which most certainly doesn't include any information about upper and lower case because it is just a number.

I suspect the difference you see in your selects probably comes from the Session cache and doesn't really have much to do with the method you use: If the entity is found in the cache it get's returned and if it was just saved it contains the UUID in the format the Hibernator generated it (so upper-lower case depends on a toString() method in Hibernate).

If the entity is not found in the cache it is returned as it comes from the database which most likely depends on an implementation detail in the JDBC driver you use.

So how to fix this:

The easy way: Use guid as the generator. It will hit the database so the UUID will always be in the format provided by the database. If performance is critical this is probably not a viable option. Note: I couldn't find this generator in the current documentation, no idea if it is deprecated or something.

The hard way: Find or create a UUID generator that creates the UUIDs in the exact format that is returned by the database.

There seems to be a surprising amount of wiggle room for the generation of UUIDs so here is a list of links for further reading:

Vlad Mihalcea about UUIDs: https://vladmihalcea.com/hibernate-and-uuid-identifiers/

A somewhat similar issue: Different representation of UUID in Java Hibernate and SQL Server

The current version of the Hibernate documentation about UUID.

score 0 · Answer 2 · answered Nov 03 '18 at 23:07

Why not just convert all the strings to the same case (which doesn't matter) before comparing them?

Still, this doesn't seem ideal; comparing two strings that are 36 characters long is going to take more time (and handling them will take more space) than 16-byte numbers, and since the database is (you hope) storing them as binary, you have the extra cost of conversions to that less efficient form. Oh, and the string comparison will be slower even for the same number of bytes because it doesn't know they're fixed length, it it probably has to compare a byte at a time rather than using wider compares.

At the time of writing this question, one of the design constraints was that converting to String was not allowed, unfortunately. — Ertai87, Nov 05 '18 at 15:03

Repository.findAll() returning capitalized UUIDs?

2 Answers2