MYSQL LIKE query working in MYSQL database but in Spring JPA

I have a query to search for a particular row using the like operator in MYSQL, it runs perfectly on mysql workbench, but i cant seem to make it run on Spring’s JPA using nativequery.
The query in MYSQL is:

SELECT * FROM product where PRODUCT_ID LIKE '33__:modelId00000000';

It works when i tested it in workbench:

enter image description here

Wherein im using wildcards to find all products that contains a certain string in the correct character position. The productId starts with 33, followed by 0-100 and then the model id, So %:modelId% will not work here. This is my JPA query right now, but it returns null.

@Query(value = "SELECT * FROM product where PRODUCT_ID LIKE '33__:modelId00000000'", nativeQuery = true)
List<Product> getProductByModelId(String modelId);

What is allowed to be returned, if for example modelId is "99" in this case:

  • 33009900000000
  • 33019900000000
  • 33029900000000
  • 33039900000000
  • 33559900000000
  • 33899900000000
  • 33999900000000

>Solution :

Perhaps, you can try something like this:

SELECT * FROM product where PRODUCT_ID LIKE CONCAT('33%',:modelId,'00000000')

But since I’m not familiar with JPA I’m not sure if this query is safe from MySQL injection or not.

Demo fiddle

Leave a Reply