Springboot JPA Cast numeric substring to string

Question

I have a JPA/Springboot application backed by a Postgres database. I need to get a records that is equal to a substring passed back to the server.

For example: Select * from dp1_attachments where TRIM(RIGHT(dp1_submit_date_dp1_number::text, 5)) ='00007'

This query works in PgAdmin, but not in the JPA @Query statement.

 @Query("SELECT a.attachmentsFolder as attachmentsFolder, a.attachmentNumber as attachmentNumber, a.attachmentName as attachmentName, a.dp1SubmitDateDp1Number as dp1SubmitDateDp1Number,a.attachmentType as attachmentType, a.attachmentDate as attachmentDate, a.attachmentBy as attachmentBy "
            + "FROM DP1Attachments a WHERE TRIM(SUBSTRING(a.dp1SubmitDateDp1Number::text, 5 )) = :dp1Number")

I've also tried CASTing the parameter like this:

@Query("SELECT a.attachmentsFolder as attachmentsFolder, a.attachmentNumber as attachmentNumber, a.attachmentName as attachmentName, a.dp1SubmitDateDp1Number as dp1SubmitDateDp1Number,a.attachmentType as attachmentType, a.attachmentDate as attachmentDate, a.attachmentBy as attachmentBy "
            + "FROM DP1Attachments a WHERE TRIM(SUBSTRING(CAST(a.dp1SubmitDateDp1Number as string, 5 ))) = :dp1Number")

but the application won't even run, and returns an error that the query isn't valid.

If I make no attempt to cast it, I get an error that function pg_catalog.substring(numeric, integer) does not exist

UPDATE

I've also tried creating a native query instead but that also doesn't seem to work.

  List<DP1AttachmentsProjection> results = em.createNativeQuery("Select * FROM dp1_attachments WHERE TRIM(RIGHT(CAST(dp1_submit_date_dp1_number as varchar),5)) =" + dp1Number).getResultList();

In place of varchar I have also tried string and text. Errors come back similar to ERROR: operator does not exist: text = integer. Its like the CAST is being ignored and I'm not sure why.

I also tried the following as a native query:

em.createNativeQuery("Select * FROM dp1_attachments WHERE TRIM(RIGHT(dp1_submit_date_dp1_number::varchar),5)) =" + dp1Number).getResultList();

and get ERROR: syntax error at or near ":"

FINAL SOLUTION

Thanks to @Nenad J I altered the query to get the final working solution:

@Query(value = "SELECT a.attachments_Folder as attachmentsFolder, a.attachment_Number as attachmentNumber, a.attachment_Name as attachmentName, a.dp1_Submit_Date_Dp1_Number as dp1SubmitDateDp1Number,a.attachment_Type as attachmentType, a.attachment_Date as attachmentDate, a.attachment_By as attachmentBy FROM DP1_Attachments a WHERE TRIM(RIGHT(CAST(a.dp1_Submit_Date_Dp1_Number as varchar ), 5 )) = :dp1Number", nativeQuery = true)"

Answer 1

Default substring returns a string, so substring(integer data,5) returns a string. Thus no need for the cast.

@Query("SELECT * FROM DP1Attachments a WHERE TRIM(SUBSTRING(a.dp1SubmitDateDp1Number, 5)) = :dp1Number")

But I recommend in this case use native query like this: Put this code in your attachment repository.

@Query(value="SELECT * FROM DP1Attachments AS a WHERE TRIM(SUBSTRING(a.dp1SubmitDateDp1Number, 5 )) = :dp1Number", nativeQuery=true)

Be careful with the column's name.