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:
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.