Search code examples
javajpaeclipselinkderby

'gomobile.user u' cannot be the first declaration of the FROM clause

I'm trying to query all the data of my user table in the schema gomobile of my DerbyDB.

I've successfully established a connection to my database and created a JPA Entity, with all its columns corresponding to the database table.

@Entity
@Table(name = "user", schema = "gomobile")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    // all columns

    public static List<User> getAll() {
        String queryString = "SELECT u FROM gomobile.user u";
        EntityManager em = Persistence.createEntityManagerFactory("Eclipselink").createEntityManager();
        return em.createQuery(queryString, User.class).getResultList();
    }
}

This is the stracktrace:

Exception in thread "main" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Problem compiling [SELECT u FROM gomobile.user as u]. 
[14, 41] 'gomobile.user as u' cannot be the first declaration of the FROM clause.
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1625)
    at com.sap.sapchat.jpa.entities.User.getAll(User.java:45)
    at com.sap.sapchat.jpa.entities.InitDatabase.main(InitDatabase.java:50)
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Problem compiling [SELECT u FROM gomobile.user as u]. 
[14, 41] 'gomobile.user as u' cannot be the first declaration of the FROM clause.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:347)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
    ... 3 more

My persistence declaration in the persistence.xml looks like this:

<persistence-unit name="Eclipselink" transaction-type="RESOURCE_LOCAL">
    <class>jpa.entities.User</class>
    <properties>
        <property name="javax.persistence.jdbc.url" value="jdbc:derby://localhost:1527/gomobile;create=true" />
        <property name="javax.persistence.jdbc.user" value="gomobile" />
        <property name="javax.persistence.jdbc.password" value="mypassword" />
        <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.ClientDriver" />
    </properties>
</persistence-unit>

EDIT

If I use:

String queryString = "SELECT * FROM gomobile.user u";

I get this error:

Exception in thread "main" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [SELECT * FROM gomobile.user u]. 
[38, 38] A select statement must have a FROM clause.
[7, 7] The left expression is missing from the arithmetic expression.
[9, 38] The right expression is not an arithmetic expression.
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1625)
    at com.sap.sapchat.jpa.entities.User.getAll(User.java:75)
    at com.sap.sapchat.jpa.entities.InitDatabase.main(InitDatabase.java:64)
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [SELECT * FROM gomobile.user u]. 
[38, 38] A select statement must have a FROM clause.
[7, 7] The left expression is missing from the arithmetic expression.
[9, 38] The right expression is not an arithmetic expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
    ... 3 more
Solution

  • There is no entity called gomobile.user, so you cannot use it in your JPQL queries. JPQL is object based, and does not use the tables/schema and fields directly like you would in SQL.

    You should be using just "SELECT u FROM User u", as the Entity you are quering is named "User" by default.