Execute DISTINCT query on datastore App engine

Question

I want to fetch distinct data from Datastore of app engine . I am using below code to fetch distinct entity from DB but it throws Class name distinct could not be resolved exception on app engine server.

Code in endpoint class:

public CollectionResponse<UserMaster> searchUser(
        @Named("strPrefix1") String strPrefix1,
        @Nullable @Named("cursor") String cursorString,
        @Nullable @Named("limit") Integer limit) {

    EntityManager mgr = null;
    Cursor cursor = null;
    List<UserMaster> execute = null;
    try {
        mgr = getEntityManager();

    //here if i remove "distinct" and use "*" selector then it works properly but i want to 
    //fetch distinct records and there for i need "distinct".

        Query query = mgr
                .createQuery("select distinct from UserMaster f where f.userName >= :strPrefix1 and f.userName < :strPrefix2");

        query.setParameter("strPrefix1", strPrefix1);
        query.setParameter("strPrefix2", strPrefix1 + "\ufffd");

        if (cursorString != null && cursorString != "") {
            cursor = Cursor.fromWebSafeString(cursorString);
            query.setHint(JPACursorHelper.CURSOR_HINT, cursor);
        }
        if (limit != null) {
            query.setFirstResult(0);
            query.setMaxResults(limit);
        }
        execute = (List<UserMaster>) query.getResultList();
        cursor = JPACursorHelper.getCursor(execute);
        if (cursor != null)
            cursorString = cursor.toWebSafeString();
        for (UserMaster obj : execute)
            ;

    } finally {
        mgr.close();
    }
    return CollectionResponse.<UserMaster> builder().setItems(execute)
            .setNextPageToken(cursorString).build();
}

Exception :

 Class name distinct could not be resolved
org.datanucleus.exceptions.NucleusUserException: Class name distinct could not be resolved
    at org.datanucleus.query.expression.PrimaryExpression.bind(PrimaryExpression.java:192)
    at org.datanucleus.query.compiler.JavaQueryCompiler.compileResult(JavaQueryCompiler.java:657)
    at org.datanucleus.query.compiler.JPQLCompiler.compile(JPQLCompiler.java:83)
    at org.datanucleus.store.query.AbstractJPQLQuery.compileInternal(AbstractJPQLQuery.java:271)
    at org.datanucleus.store.query.Query.setImplicitParameter(Query.java:799)
    at org.datanucleus.api.jpa.JPAQuery.setParameter(JPAQuery.java:437)
    at org.datanucleus.api.jpa.JPAQuery.setParameter(JPAQuery.java:57)
    at com.sampleregistrationapp.UserFeedMasterEndpoint.searchUser(UserFeedMasterEndpoint.java:620)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:45)
    at com.google.api.server.spi.SystemService.invokeServiceMethod(SystemService.java:359)
    at com.google.api.server.spi.SystemServiceServlet.execute(SystemServiceServlet.java:127)
    at com.google.api.server.spi.SystemServiceServlet.doPost(SystemServiceServlet.java:85)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)

Michael Técourt Michael Técourt · Accepted Answer · 2014-04-18T13:41:30

At this point it's not even App Engine related : your JPQL query is wrong, it should be : :

select distinct f from UserMaster f where f.userName >= :strPrefix1 and f.userName < :strPrefix2

In your query, JPA was treating the distinct keyword as if it was the class name you were search for.

EDIT : I struggled to find docs about this for JPA & JDO. For the low level datastore API, distinct queries only work for projection queries, i.e. queries that only return some indexed properties of an entity, not the whole entity. There are good chances your query won't work even with the right syntax.

https://developers.google.com/appengine/docs/java/javadoc/com/google/appengine/api/datastore/Query#setDistinct(boolean) => see setDistinct(boolean distinct)

Execute DISTINCT query on datastore App engine

1 Answers