pgp_sym_decrypt not working with JPA nativeQuery. Always throw error

Question

Hi I'm working on a Spring Boot Project using PSQL. I have a model that is implemented pgcrypto like this,

@ColumnTransformer(
     read =  "pgp_sym_decrypt( msisdn::bytea, 'mypassword' )",
     write = "pgp_sym_encrypt( ?, 'mypassword' )"
     )
@Column(name="msisdn")
private String msisdn;

In my JPArepository every function like find(), findAll(), findBySomthing(..) work perfectly. The result decoded my msisdn record correctly.

But it's not working on a navtiveQuery. For example in my JPArepository,

@Query(value = "SELECT id, pgp_sym_decrypt( msisdn::bytea, 'mypassword' ) as msisdn FROM tbl_payments ORDER BY id ASC;", nativeQuery = true)
List<MyClass> test();

It always throws error

org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"

But the query is correct because I already copied it to do a query directly on Database.

Do I need anything extra to work with JPA nativeQuery ?

Please help. Thanks a lot.

Answer 1

Your query:

@Query(value = "SELECT id, pgp_sym_decrypt( msisdn::bytea, 'mypassword' ) as msisdn FROM tbl_payments ORDER BY id ASC;", nativeQuery = true)
List<MyClass> test();

just add \:\: at :: in bytea

Improved query:

@Query(value = "SELECT id, pgp_sym_decrypt( msisdn\\:\\:bytea, 'mypassword' ) as msisdn FROM tbl_payments ORDER BY id ASC;", nativeQuery = true)
List<MyClass> test();