error in your SQL syntax

[albtorresmo]

Hello,

I,am trying to execute a query in a stateless bean, with jboss seam

The query is

List existing = em.createQuery("select username from Users")
.getResultList();

and I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.users users0_' at line 1

My entity bean is defined as

@Entity
@Name ("users")
@Scope (SESSION)
@Table(name = "users")
public class Users implements java.io.Serializable {

private static final long serialVersionUID = 1881413500711441951L;

private String username;
private String password;
.........

What is wrong??
I have configue my app with jboss seam
seam setup
seam new-project

My database is mysql 4.1.21 and if I connect by jdbc all its ok.

thank you

[mazz]

Look at the name of the table its using:

'.users users0_'

See the "." right before users? I don't know for sure, but it looks to me like that is missing the schema name (not sure what MySQL calls it - database name? username?)

You probably just have to configure your persistence.xml a little more to tell Hibernate the schema name.

Just a thought - I have no idea if what I just said is right :) But for sure it seems like ".users" isn't right - table names don't have dots as their first character.

[waynebaylor]

You can't use "normal" SQL in the createQuery method, you have to use JPA QL.

So, try this:

List<User> users = em.createQuery("from "+User.class).getResultList();

You'll get a list of User objects and you can filter out the usernames from that.