jpa native Query regexp like with querydsl

Question

I have i query statement like this:

select t.*
  from T_ex_table t
 where regexp_like(t.note,
                   '^(.*[^[:digit:]]+)?([condition])([^[:digit:]]+.*)?$',
                   'n')

And if I use it in jpa with querydsl(com.querydsl) like(this is scala, and it doesn't important):

 @Query(value =
    "select t.*" +
      "  from T_PURCHASE t" +
      " where regexp_like(t.note," +
      "                   '^(.*[^[:digit:]]+)?([?1])([^[:digit:]]+.*)?$'," +
      "                   'n')", nativeQuery = true)
 def getByTrackingNo(trackingNo: String): Purchase

While i debug test, it always throw

Using named parameters for method public abstract Purchase PurchaseRepository.getByTrackingNo(java.lang.String) but parameter 'trackingNo' not found in annotated query 'select t.pt_note, t.tracking_no from T_EC_PURCHASE t where regexp_like(t.pt_note, '^(.[^[:digit:]]+)?({?1})([^[:digit:]]+.)?$', 'n')'!

Did i missed something, and how can i fix it.

Please provide a little bit more logs and full method annotated by `@Query`, i tried your query on my project and it works without error, also describe what data base do you use. — borino, Apr 28 '18 at 11:29
please check documentation [Using named parameters](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.named-parameters). Spring-data waits for a parameter in the query (in your case `trackingNo`), or make a method with no parameters — borino, May 02 '18 at 05:28
I have tried it, but It throw Parameter with that position [1] did not exist. — xmcx, May 02 '18 at 10:24
It seem like jpa treat it as an ordinary string. And I cann't use a no parameters method, because users will get data through this parameter. — xmcx, May 02 '18 at 10:35
Maybe try to move full regexp to the param method? and build it before. for example : `@Query(value = "select t.* from T_PURCHASE t where regexp_like(t.note, ?1, 'n')", nativeQuery = true)`. Where `?1` - yours fully build regexp with required parameters. — borino, May 04 '18 at 07:35
Yes. It work. Thank you very much. And can i just return one or many column(like t.note[,...] instead of t.*)? — xmcx, May 08 '18 at 03:30
Of course just search [spring data and projections](https://stackoverflow.com/questions/47420600/spring-data-projection-and-error-no-aliases-found-in-result-tuple-make-sure-y) for example.. Do not forget +++ ) — borino, May 08 '18 at 05:55

score 2 · Accepted Answer · answered May 08 '18 at 05:57

2

Maybe try to move full regexp to the param method? and build it before. for example :
@Query(value = "select t.* from T_PURCHASE t where regexp_like(t.note, ?1, 'n')", nativeQuery = true)
Where ?1 - yours fully build regexp with required parameters.

answered May 08 '18 at 05:57

borino

1,720
1
16
24

jpa native Query regexp like with querydsl

1 Answers1

Linked