CODEWITHSUNDEEP
jakarta-eejpaeclipselinkjpql
danizmax
danizmax

Reputation: 2456

Syntax error parsing a trivial query with JPA

I created the entity bean with netbeans wizard and am trying to get data from database. No matter what SQL query do I use,it doesn't work. I tried using named query that was created by wizard:

@NamedQuery(name = "Usr.findAll", query = "SELECT u FROM Usr u")

It returns:

Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [Usr.findAll], line 1, column 0: unexpected token [Usr].

If I try; 

SELECT uid FROM usr;

I get:

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing the query [SELECT uid FROM usr;], line 0, column -1: unexpected end of query.
Internal Exception: MismatchedTokenException(-1!=78)

and even if I try :

SELECT * FROM usr

I get:

Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [SELECT * FROM usr], line 1, column 7: unexpected token [*].

My line for getting the data is:

@PersistenceContext
EntityManager em;
....

em=Persistence.createEntityManagerFactory("SchoolPU").createEntityManager();
List users = em.createQuery("SELECT * FROM usr").getResultList();

Any one can help me with this trivial problem?

The Usr entity class:

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

/**
 *
 * @author danizmax
 */
@Entity
@Table(name = "USR")
@NamedQueries({
    @NamedQuery(name = "Usr.findAll", query = "SELECT u FROM Usr u"),
    @NamedQuery(name = "Usr.findByUid", query = "SELECT u FROM Usr u WHERE u.uid = :uid"),
    @NamedQuery(name = "Usr.findByPassword", query = "SELECT u FROM Usr u WHERE u.password = :password"),
    @NamedQuery(name = "Usr.findByFistname", query = "SELECT u FROM Usr u WHERE u.fistname = :fistname"),
    @NamedQuery(name = "Usr.findByLastname", query = "SELECT u FROM Usr u WHERE u.lastname = :lastname"),
    @NamedQuery(name = "Usr.findByAddress1", query = "SELECT u FROM Usr u WHERE u.address1 = :address1"),
    @NamedQuery(name = "Usr.findByAddress2", query = "SELECT u FROM Usr u WHERE u.address2 = :address2"),
    @NamedQuery(name = "Usr.findByPostcode", query = "SELECT u FROM Usr u WHERE u.postcode = :postcode"),
    @NamedQuery(name = "Usr.findByEmail", query = "SELECT u FROM Usr u WHERE u.email = :email"),
    @NamedQuery(name = "Usr.findByPhone", query = "SELECT u FROM Usr u WHERE u.phone = :phone")})
public class Usr implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "UID", nullable = false, length = 8)
    private String uid;
    @Basic(optional = false)
    @Column(name = "PASSWORD", nullable = false, length = 20)
    private String password;
    @Basic(optional = false)
    @Column(name = "FISTNAME", nullable = false, length = 30)
    private String fistname;
    @Basic(optional = false)
    @Column(name = "LASTNAME", nullable = false, length = 60)
    private String lastname;
    @Basic(optional = false)
    @Column(name = "ADDRESS1", nullable = false, length = 100)
    private String address1;
    @Column(name = "ADDRESS2", length = 100)
    private String address2;
    @Basic(optional = false)
    @Lob
    @Column(name = "CITY", nullable = false)
    private byte[] city;
    @Basic(optional = false)
    @Column(name = "POSTCODE", nullable = false, length = 10)
    private String postcode;
    @Column(name = "EMAIL", length = 50)
    private String email;
    @Column(name = "PHONE")
    private Integer phone;

    public Usr() {
    }

    public Usr(String uid) {
        this.uid = uid;
    }

    public Usr(String uid, String password, String fistname, String lastname, String address1, byte[] city, String postcode) {
        this.uid = uid;
        this.password = password;
        this.fistname = fistname;
        this.lastname = lastname;
        this.address1 = address1;
        this.city = city;
        this.postcode = postcode;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getFistname() {
        return fistname;
    }

    public void setFistname(String fistname) {
        this.fistname = fistname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getAddress1() {
        return address1;
    }

    public void setAddress1(String address1) {
        this.address1 = address1;
    }

    public String getAddress2() {
        return address2;
    }

    public void setAddress2(String address2) {
        this.address2 = address2;
    }

    public byte[] getCity() {
        return city;
    }

    public void setCity(byte[] city) {
        this.city = city;
    }

    public String getPostcode() {
        return postcode;
    }

    public void setPostcode(String postcode) {
        this.postcode = postcode;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getPhone() {
        return phone;
    }

    public void setPhone(Integer phone) {
        this.phone = phone;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (uid != null ? uid.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Usr)) {
            return false;
        }
        Usr other = (Usr) object;
        if ((this.uid == null && other.uid != null) || (this.uid != null && !this.uid.equals(other.uid))) {
            return false;
        }
        return true;
    }



    @Override
    public String toString() {
        return "org.danizmax.Usr[uid=" + uid + "]";
    }

}

persistance.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="SchoolPU" transaction-type="JTA">
    <jta-data-source>jdbc/school</jta-data-source>
    <properties>
    </properties>
  </persistence-unit>
</persistence>

The class where I use the entity:

import java.util.Iterator;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import javax.persistence.PersistenceContext;

/**
 *
 * @author danizmax
 */
@Stateless
public class ValidatorBean {

    @PersistenceContext
    EntityManager em;

    public ValidatorBean() {


    }

    public boolean validate(String user, String pass) {

        List users = em.createQuery("SELECT * FROM usr").getResultList();

        Iterator it = users.iterator();

        //ignore the stupid validation it's only to try out JPA
        while(it.hasNext()){
            Usr u = (Usr) it.next();

            if(u.getUid().equals(user) && u.getPassword().equals(pass)){
                return true;
            }
        }


        return false;
    }
}

UPDATE: To be fair to you guys who answered and reward your effort, now that I actually learned the technology and use it in real world, I decided to close this answer with awarding the best answer that was my most probable solution I found my self long time ago.

Upvotes: 7

Views: 16460

Answers (7)

musef
musef

Reputation: 9

I had a problem like this with my @NamedQuery. It didn't work at all. I solved the problem changing em.createQuery("anything.anotherthing") by em.createNamedQuery("anything.anotherthing") and now it's working fine.

Upvotes: 0

James
James

Reputation: 18389

Only your first query is correct, the others are not JPQL, so should cause errors. Use a @NamedNativeQuery for SQL.

For the first query, this does not appear to be what you are really using to get the error,

Exception Description: Syntax error parsing the query [Usr.findAll], line 1, column 0: unexpected token [Usr].

Note the error says "column 0" is "Usr" which seems that you put the name in the query instead of the name. My guess is you are doing,

em.createQuery("Usr.findAll").getResultList();

But should be doing,

em.createNamedQuery("Usr.findAll").getResultList();

Or,

em.createQuery("Select u from Usr u").getResultList();

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

Upvotes: 4

andand
andand

Reputation: 17507

Not sure if this will help you or not, as this question seems a bit old. However you might want to use .createNativeQuery(...) instead of .createQuery(...) as in:

List users = em.createNativeQuery("SELECT * FROM usr").getResultList();

Upvotes: 1

stew
stew

Reputation: 51

I had the same problem. In my case I used the method
em.createQuery("Usr.findAll");
instead of
em.createNamedQuery("Usr.findAll");

Upvotes: 5

j&#246;rg
j&#246;rg

Reputation: 1

I had the same problem. Strangs solution -for me - was: Toplink treats the "Tablename" case sensitive when comparing with persistence.xml. So if you use standard camel case names for your java classes you must use the same camel case names in your query. I very much like the totally misleading error message.

Upvotes: 0

Kim Burgaard
Kim Burgaard

Reputation: 3548

EclipseLink has a tendency to give pretty generic or cryptic error messages on relatively simple configuration problems.

I'm guessing you're missing a reference to the Usr class in persistence.xml. You must either add the class, or add a path reference that tells EclipseLink what classes to process for persistence. Here's an example with a direct class reference:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="SchoolPU" transaction-type="JTA">
    <jta-data-source>jdbc/school</jta-data-source>
    <class>my.package.Usr</class>
  </persistence-unit>
</persistence>

A class reference is the most straight forward approach, but can become tedious to manage if you have many classes.

Upvotes: 0

Arjan Tijms
Arjan Tijms

Reputation: 38163

What is the class name of the entity you're trying to fetch? (perhaps you need to show the class where you placed the @NamedQuery).

Is this really Usr or is it perhaps User? If the last is true, Select u from User u should definitely work.

Additionally, you don't need both @PersistenceContext and em=Persistence.createEntityManagerFactory(...). Use either one of those. If you're in a managed environment, use the injection variant. Creating an entity manager yourself gives you a so-called application managed entity manager for which your code has to do a lot more administration to keep things going right.

Upvotes: 2

Related Questions