[
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)
