Search code examples
javamysqlhibernatejpasinglestore

How to execute native memsql query using jpa

As far as I know, to interact with MemSql from a java application we use the same MySql connector.

In my application, I need to execute a query against MemSql database table. The table contains a JSON field and the query is based on some fields inside the JSON.

Here is a sample query

select  tweet::retweeted_status  from  tweet_json_original  where  posted_time > date_sub(current_date(), interval 1 day);

In my repository class, I have a method that tries to execute the previous query as following 

public List<String> getMissedTweets(){
        Query query = em.createNativeQuery(missedTweetsQuery);
        return query.getResultList();
    }

When executing this method, the application throw the following exception

Caused by: org.hibernate.QueryException: Not all named parameters have been set: [:retweeted_status] [tweet::retweeted_status  from  tweet_json_original  where  posted_time > date_sub(current_date(), interval 1 day);]
    at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:391) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.internal.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:179) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:118) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
    ... 29 common frames omitted

I understand that there is a conflict in the way I query the JSON fields and the way JPA uses to pass parameters to the query. The cause of this issue is that both operations are using the colon character (:).

how can I solve this issue and execute the previous query as it is?

Solution

  • You need to escape the colons so that JPA does not treat those as parameters that need to be set:

    select  tweet\\:\\:retweeted_status