With Micronaut Data JDBC, how do I transform a value for WHERE clauses?

Question

I'm using Micronaut Data JDBC 1.1.3 with a MySQL 8.0 database. I'd like to have my id columns use a BINARY(16) storage and use BIN_TO_UUID()/UUID_TO_BIN() to convert the values. I though I could do this with Micronaut Data's @ColumnTransformer, like this:

public class User {
    @Id
    @ColumnTransformer(read = "BIN_TO_UUID(user_.id)", write = "UUID_TO_BIN(?)")
    private UUID id;

    // ...
}

I then use a UserRepository like this:

@JdbcRepository(dialect = Dialect.MYSQL)
public interface UserRepository extends RxJavaCrudRepository<User, UUID> {
}

It works great in that the id column is correctly tranformed in query results. But when I try to find something by ID, using userRepository.findById(id).toSingle(), I get an error ("the MaybeSource is empty"), and I can see in the trace output the it is not tranforming the value:

15:17:04.878 [pool-1-thread-76] DEBUG io.micronaut.data.query - Executing Query: SELECT BIN_TO_UUID(user_.id) AS id,user_.`username` FROM `user` user_ WHERE (user_.`id` = ?)

---

Is this expected? Is there anything else I can do transform the query? Do I have to write an explicit query?

Answer 1

I believe this is a bug in Micronaut-Data 1.1.3.

As a workaround I would override the query like:

@ReadOnly
@Override
@Query(
   "SELECT * FROM user WHERE user.id = :id")
Optional<User> findById(UUID id);

in the UserRepository interface.

BTW Query needs of cause to be customized for your needs.