Spring Data GemFire and 'Unexpected Token' in manual OQL query

Question

I am firing a query using Spring Data GemFire and getting a ``Syntax error in query: unexpected token: timestamp.

I am not able to figure out the issue.

@Query("SELECT * FROM /Trade T WHERE T.stock.symbol =$1 ORDER BY T.timestamp desc LIMIT 15")
  public List<Trade> findAllTradeForStock(String stock);

Trade Class:

public class Trade implements Serializable {

  private static final long serialVersionUID = 3209342518270638001L;

  @Id private int tradeIdentifier;
  private Stock stock;
  private LocalDateTime timestamp;

}

Answer 1

The problem is not with Spring Data GemFire.

It would also be helpful if you included the version of both Spring Data GemFire and Pivotal GemFire that you are using (e.g. Spring Data GemFire 1.9.10.RELEASE, which pulls in Pivotal GemFire 8.2.8, or maybe you are using Spring Data GemFire 2.0.4.RELEASE, which pulls in Pivotal GemFire 9.1.1; see the version compatibility page for more details).

While this problem is pretty immediately apparent, you may also want to be mindful to include any Stack Traces or log output next time.

So, the problems you are having are directly related to this...

1) Concerning timestamp....

timestamp is a reserved word and OQL query supported literal. You cannot use "timestamp" as a field or property on your domain objects (e.g. Trade).

2) Regarding ORDER BY...

Pivotal GemFire does not support non-DISTINCT, ORDER BY queries (4th bullet).

Neither one of these problems are with Spring Data GemFire. These are Pivotal GemFire restrictions and limitations.

Please refer to Pivotal GemFire's documentation if you want to understand how to construct proper and correct OQL (query) statements.

One final tip, your query is not that complex and could have easily been expressed properly using the Repository query method abstraction, rather than resorting to using @Query, which is not necessary in this case...

interface TradeRepository extends CrudRepository<Trade, Long> {

  @Limit(15)
  List<Trade> findDistinctTradeByStockSymbolOrderByTradeTimestampDesc(String stockSymbol);

}

Here is a similarly complex Repository implementation (e.g. ContactRepository) that also includes query methods generating proper GemFire OQL using the "convention", for example, for nested types.

Look at some of the other "query methods" on this Repository interface definition (for a Contact) for other examples of what is possible.

The corresponding Integration Test for the ContactRepository can be found here.

-j