You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Mark Egloff (Jira)" <ji...@apache.org> on 2021/07/11 13:48:00 UTC
[jira] [Updated] (IGNITE-15100) Ignite spring data -
IgniteRepository Annotated Queries with LIKE and named parameter
[ https://issues.apache.org/jira/browse/IGNITE-15100?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mark Egloff updated IGNITE-15100:
---------------------------------
Description:
When I add a method with annotated @Query to my IgniteRepository interface calss which is using a LIKE operator of a QuerySqlField or QueryTextField I get a SQL synatx error, although the query syntax itself is according JPA standards correct
{code:java}
// Example
import org.springframework.data.repository.query.Param;
@RepositoryConfig(cacheName = Item.cacheName)
public interface ItemIgniteRepository extends IgniteRepository<Item, Long>, CustomItemIngniteRepository {
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value")
public List<Item> findByTextAttribute(@Param("value") String value)
...
{code}
This returns the following error
{code:java}
log.info("search by parameter text value...");
items = itemRepoIgnite.findByTextAttribute("Road Rider");
{code}
{{org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT I._KEY, I._VAL FROM ITEM AS I JOIN ITEMATTRIBUTE AS IA ON I.ID = IA.ITEMID WHERE IA.TYPE = 'TEXT' AND IA.TEXTVALUE LIKE :[*]VALUE "; expected "SELECT, FROM, WITH"; SQL statement:}}
{{SELECT i._KEY, i._VAL from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value [42001-197]}}
When I use the same query without a named parameter "?" it works i.e.
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE ?")
public List<Item> findByTextAttribute(String value);{code}
or this is also working
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE %?1%")
public List<Item> findByTextAttribute(String value);
{code}
The named parameter is working with equals "=" operator
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue = :value")
public List<Item> findByTextAttribute(@Param("value") String value)
{code}
was:
When I add a method with annotated @Query to my IgniteRepository interface calss which is using a LIKE operator of a QuerySqlField or QueryTextField I get a SQL synatx error, although the query syntax itself is according JPA standards correct
{code:java}
// Example
import org.springframework.data.repository.query.Param;
@RepositoryConfig(cacheName = Item.cacheName)
public interface ItemIgniteRepository extends IgniteRepository<Item, Long>, CustomItemIngniteRepository {
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value")
public List<Item> findByTextAttribute(@Param("value") String value)
...
{code}
This returns the following error
{code:java}
log.info("search by parameter text value...");
items = itemRepoIgnite.findByTextAttribute("Road Rider");
{code}
{{org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT I._KEY, I._VAL FROM ITEM AS I JOIN ITEMATTRIBUTE AS IA ON I.ID = IA.ITEMID WHERE IA.TYPE = 'TEXT' AND IA.TEXTVALUE LIKE :[*]VALUE "; expected "SELECT, FROM, WITH"; SQL statement:}}
{{SELECT i._KEY, i._VAL from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value [42001-197]}}
When I sue the same query without a named parameter "?" it works i.e.
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE ?")
public List<Item> findByTextAttribute(String value);{code}
or this is also working
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE %?1%")
public List<Item> findByTextAttribute(String value);
{code}
The named parameter is working with equals "=" operator
{code:java}
@Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue = :value")
public List<Item> findByTextAttribute(@Param("value") String value)
{code}
> Ignite spring data - IgniteRepository Annotated Queries with LIKE and named parameter
> --------------------------------------------------------------------------------------
>
> Key: IGNITE-15100
> URL: https://issues.apache.org/jira/browse/IGNITE-15100
> Project: Ignite
> Issue Type: Bug
> Components: springdata
> Affects Versions: 2.9.1
> Reporter: Mark Egloff
> Priority: Major
>
> When I add a method with annotated @Query to my IgniteRepository interface calss which is using a LIKE operator of a QuerySqlField or QueryTextField I get a SQL synatx error, although the query syntax itself is according JPA standards correct
> {code:java}
> // Example
> import org.springframework.data.repository.query.Param;
> @RepositoryConfig(cacheName = Item.cacheName)
> public interface ItemIgniteRepository extends IgniteRepository<Item, Long>, CustomItemIngniteRepository {
> @Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value")
> public List<Item> findByTextAttribute(@Param("value") String value)
> ...
> {code}
> This returns the following error
> {code:java}
> log.info("search by parameter text value...");
> items = itemRepoIgnite.findByTextAttribute("Road Rider");
> {code}
> {{org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT I._KEY, I._VAL FROM ITEM AS I JOIN ITEMATTRIBUTE AS IA ON I.ID = IA.ITEMID WHERE IA.TYPE = 'TEXT' AND IA.TEXTVALUE LIKE :[*]VALUE "; expected "SELECT, FROM, WITH"; SQL statement:}}
> {{SELECT i._KEY, i._VAL from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value [42001-197]}}
> When I use the same query without a named parameter "?" it works i.e.
> {code:java}
> @Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE ?")
> public List<Item> findByTextAttribute(String value);{code}
> or this is also working
> {code:java}
> @Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE %?1%")
> public List<Item> findByTextAttribute(String value);
> {code}
> The named parameter is working with equals "=" operator
> {code:java}
> @Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue = :value")
> public List<Item> findByTextAttribute(@Param("value") String value)
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)