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
– borino, Commented Apr 28, 2018 at 11:29
please check documentation Using named parameters. Spring-data waits for a parameter in the query (in your case trackingNo), or make a method with no parameters — borino
– borino, Commented May 2, 2018 at 5:28
I have tried it, but It throw Parameter with that position [1] did not exist. — xmcx
– xmcx, Commented May 2, 2018 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
– xmcx, Commented May 2, 2018 at 10:35

borino · Accepted Answer · 2018-05-08 05:57:11Z

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 8, 2018 at 5:57

borino

1,7601 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

jpa native Query regexp like with querydsl

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related