Syntax error parsing trivial query with JPA

Question

I created a bean using the netbeans wizard and am trying to get data from a database. No matter what SQL query I use, it does not work. I tried using the named query created by the 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 data:

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

Can anyone help me with this trivial issue?

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 in which I use the object:

 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 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 honest with you guys who responded and rewarded your efforts, now that I have learned technology and used it in the real world, I decided to close this answer, getting the best answer, it was my most likely solution that I found for myself myself a long time ago.

Answer 1

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

For the first request, this is not like what you really use to get the error,

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

Note that the error says that “column 0” is “Usr”, which seems like you are putting the name in the query instead of the name. I guess what you do

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

But you have to do

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

Or

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

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

Answer 2

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

Answer 3

What is the class name of the object you are trying to retrieve? (maybe you need to show the class in which you posted @NamedQuery).

Is it really Usr or is it possible User ? If the latter is true, Select u from User u should definitely work.

Furthermore, you do not need both @PersistenceContext and em=Persistence.createEntityManagerFactory(...) . Use one of them. If you are in a controlled environment, use the injection option. Creating an application managed entity manager on your own gives you the so-called application managed entity manager , for which your code should do much more administration so that everything is in order.

Answer 4

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

 List users = em.create Native Query("SELECT * FROM usr").getResultList(); 

Answer 5

EclipseLink tends to give fairly general or cryptic error messages regarding relatively simple configuration problems.

I assume that you did not Usr reference to the Usr class in persistence.xml . You must either add a class or add a link to the path that tells EclipseLink which classes to process to save. Here is an example with a direct reference to the class:

 <?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> 

Linking to a class is the easiest approach, but can be tedious to manage if you have many classes.

Answer 6

I had the same problem. The Strangs solution - for me - was: Toplink handles the table name register when it is compared to persistence.xml. Therefore, if you use the standard camel names for your java classes, you must use the same camel clone names in your request. I really like the completely misleading error message.

Answer 7

I am having a problem with my @NamedQuery. This did not work. I solved the problem with changing em.createQuery("anything.anotherthing") to em.createNamedQuery("anything.anotherthing") and now it works fine.