Search code examples
springjpauuid

JPA: How to select a binary(16) UUID by a String UUID?

I have the following Entity:

@Data
@Entity
public class Comment implements Serializable {

    @Id
    @GeneratedValue(generator = "uuid4")
    @GenericGenerator(name = "UUID", strategy = "uuid4")
    @Column(columnDefinition = "BINARY(16)")
    private UUID id;

    @Column(columnDefinition = "BINARY(16)")
    private UUID imageId;

    private Instant creationTime;

    private String text;
}

And a CRUD repo:

public interface CommentsRepository extends CrudRepository<Comment, UUID> {

    List<Comment> findAllByImageId(final UUID imageId);
}

I add some example data:

@Component
@Slf4j
public class CommentsSampleData implements CommandLineRunner {

    private final CommentsRepository repository;

    @Autowired
    public CommentsSampleData(final CommentsRepository repository) {
        this.repository = repository;
    }

    @Override
    public void run(String... args) {
        createComment("617220ff-1642-4490-b589-869e7978c5e0", Instant.now(), "comment1");
        createComment("617220ff-1642-4490-b589-869e7978c5e0", Instant.now(), "comment2");
        createComment("617220ff-1642-4490-b589-869e7978c5e0", Instant.now(), "comment3");
        createComment("e3a8aa57-6937-4f9e-b117-78bafe61b718", Instant.now(), "comment1");
    }

    private void createComment(
            final String imageId,
            final Instant creationTime,
            final String text) {
        final Comment comment = new Comment();
        comment.setImageId(UUID.fromString(imageId));
        comment.setCreationTime(creationTime);
        comment.setText(text);

        log.info("save comment: {}", comment);

        repository.save(comment);
    }
}

So the data in my table looks like the following:

enter image description here

So what is the best way now to select by those binary UUID's? I will get string UUID's from the frontend So I guess I somehow need to convert those Strings to Binaryies. Whats the best way to do so so that it also works with ids and primary keys.

Example endpoint:

@Slf4j
@RestController
public class CommentsController {

    private final CommentsService service;

    public CommentsController(final CommentsService service) {
        this.service = service;
    }

    @GetMapping(value = "/comments", produces = MediaType.APPLICATION_JSON_VALUE)
    public List<Comment> getComments(@RequestParam("imageId") final UUID imageId) {
        log.info("get comments by imageId: {}", imageId);

        String existingIds = service.findAll().stream()
                .map(Comment::getImageId)
                .map(UUID::toString)
                .collect(Collectors.joining(","));

        log.info("Image Id Passed: {}", imageId);
        log.info("Existing image ids: {}", existingIds);

        String resultIds = service.findAllByImageId(imageId).stream()
                .map(Comment::getImageId)
                .map(UUID::toString)
                .collect(Collectors.joining(","));

        log.info("Result image ids: {}", resultIds);

        return service.findAllByImageId(imageId);
    }
}

When I now do a request:

localhost:8080/comments?imageId=617220ff-1642-4490-b589-869e7978c5e0

I get no result even though the UUID exists but not as string, it exists as binary(16) in the database:

d.f.a.c.service.CommentsController       : Image Id Passed: 617220ff-1642-4490-b589-869e7978c5e0
d.f.a.c.service.CommentsController       : Existing image ids: 617220ff-1642-4490-b589-869e7978c5e0,617220ff-1642-4490-b589-869e7978c5e0,617220ff-1642-4490-b589-869e7978c5e0,e3a8aa57-6937-4f9e-b117-78bafe61b718
d.f.a.c.service.CommentsController       : Result image ids:
Solution

    • It is working as expected without any issue and it is auto converted between UUID and binary.

    • I recommend trying the following to make sure that id genuinely exists in database.

        @GetMapping(value = "/comments", produces = MediaType.APPLICATION_JSON_VALUE)
    public Iterable<Comment> getComments(@RequestParam("imageId") 
                                          final UUID imageId) {
        log.info("get comments by imageId: {}", imageId);

        String existingIds = service.findAll()
                .map(Comment::getImageId)
                .map(UUID::toString)
                .collect(Collectors.joining(","));
        
        log.info("Image Id Passed : {}", imageId);
        log.info("Existing image ids : {}", existingIds);

        return service.findAllByImageId(imageId);
    }