0

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;

}

John Blum
  • 7,381
  • 1
  • 20
  • 30
Chandresh Mishra
  • 1,081
  • 3
  • 24
  • 45
  • changing the name of column from timestamp to tradeTimestamp solves the problem but getting another issue. non-distinct order-by not yet supported – Chandresh Mishra Feb 27 '18 at 22:35
  • updated query : @Query("SELECT * FROM /Trade T WHERE T.stock.symbol =$1 ORDER BY T.tradeTimestamp desc LIMIT 15") non-distinct order-by not yet supported – Chandresh Mishra Feb 27 '18 at 22:36

1 Answers1

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

John Blum
  • 7,381
  • 1
  • 20
  • 30
  • Thanks for your answer. I realized the same after reading the documentation. It seems you are quite experienced in spring data gemfire. I am using the 1.9.10.RELEASE (as I didn't get any example using higher version) . Does spring data gemfire support Java 8 LocalDateTime comparison inherently? I want to fetch the trade data in last 15 min. Could you please suggest the best way to achieve this. – Chandresh Mishra Feb 28 '18 at 18:50