CODEWITHSUNDEEP
mysqlhibernatejpajpqluuid
Nikki
Nikki

Reputation: 434

UUID Primary Key not mapped as expected with standard existsById Jpa

I am trying to introduce UUID as primary key for an entity with BINARY(16) being its representation in mysql database.

What I have is for example UserId being UUID:

    //the column definition in the Entity
    @Id
    @Column(name="user_id", columnDefinition = "BINARY(16)")
    @ColumnTransformer(
            read="bin_to_uuid(user_id)",
            write="uuid_to_bin(?)")
    private UUID userId;



        //repository methods
@Transactional
@Modifying
     @Query("UPDATE User u SET u.status = :status  WHERE u.userId= :id ")
     int updateUserStatus(@Param("id") UUID id, @Param("status") UserStatus status);
                
      @Query("SELECT count(u) from User u WHERE u.userId= bin_to_uuid(:id)")
      int existsByUserId(@Param("id") UUID id);

However notice here that in existsByUserId I have to add bin_to_uuid(:id) instead of direct comparison. I dont quite understand why I have to do this, I found this workaround by looking at queries in DB directly and came up with this. The query for update doesnt add the bin_to_uuid whereas select count does. The standard existsById implementation also doesnt work with UUID ..

Additional code I have around this:

public void updateUserStatus(UUID id, int status) throws UserNotFoundException {
        int rowsUpdated = userRepository.updateUserStatus(id, UserStatus.values()[status]);
        if (rowsUpdated == 0) {
            throw new UserNotFoundException(id);
        }
    }


public List<User> getFollowedBloggersByUser(UUID id, Pageable p) throws UserNotFoundException {
    
    if (userRepository.existsByUserId(id) == 0) {
        throw new UserNotFoundException(id);
    } else {
        return userFollowRepository.findByFollowerId(id,p);
    }
}

I do not understand the difference between these two queries. But queries in the log in DEBUG mode are:

update users set status=? where user_id=?
 binding parameter [1] as [INTEGER] - [1]
binding parameter [2] as [BINARY] - [420090c1-1a28-11eb-b98e-00155d2849a9]

select count(1) as col_0_0_ from users user0_ where bin_to_uuid(user0_.user_id)=bin_to_uuid(?)
binding parameter [1] as [BINARY] - [9e9eeb09-6a6d-405d-abd6-802c9c2c811d]

The mysql general log shows following:

update users set status=1 where user_id=x'420090C11A2811EBB98E00155D2849A9'
select count(1) as col_0_0_ from users user0_ where bin_to_uuid(user0_.user_id)=bin_to_uuid(x'9E9EEB096A6D405DABD6802C9C2C811D')

I would really appreciate if someone can point me in explanation of this or show the better way to do this. Thanks!

Upvotes: 1

Views: 531

Answers (1)

Nikki
Nikki

Reputation: 434

For future generations, it turned out that the read part of column transformer is absolutely redundant and creates problems described above. JPA was absolutely capable of displaying uuid as a string correctly. So you just need to create columntransformer only with write property:

@ColumnTransformer(write="uuid_to_bin(?)")

Upvotes: 1

Related Questions