Why I can't use bin_to_uuid(id) in MySql JPA Native Query?

user_6847019 0
Viewed 316

Whenever I try to use this repository class in JPA/Springboot it cannot seem to find the id column that's passed as an argument to the bin_to_uuid. I've specified the mysql 8 dialect in my application.properties. This query works just fine when ran against the db directly from workbench/cli. Why can't JPA handle using this function? Running a normal SELECT id, name from CLINICS works just fine however

Exception

Hibernate: SELECT bin_to_uuid(id), name from CLINICS
2021-01-18 23:57:22.231  WARN 6064 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S0022
2021-01-18 23:57:22.232 ERROR 6064 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'id' not found.

Repository

@Repository
@Component
public interface ClinicRepository extends JpaRepository<ClinicEntity, String> {
   @Query(value = "SELECT (bin_to_uuid(id), name) from CLINICS", nativeQuery = true)
   ClinicEntity getClinicById(String uuid);
}

Entity Class

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class ClinicEntity {
   @Id
   @ColumnTransformer(read = "uuid_from_bin(id)")
   @Column(name = "id", columnDefinition = "BINARY(16)")
   private String id;
    
   private String name;
}
jpa hibernate mysql database java
1 Answers

You can correct your query in the following way:

@Query(
   value = "SELECT bin_to_uuid(id) as id, name from CLINICS where id = :id",
   nativeQuery = true
)
ClinicEntity getClinicById(@Param("id") String uuid);

and it will allow you to get rid of the error.

But I do not think that @ColumnTransformer usage for the @Id entity field is a valid use case. And I am not alone, see HHH-9808.

user_62771040
Related