Syntax error parsing JPQL: An identification variable must be provided for a range variable declaration

Question

When trying to run this code:

List<Users> list = em.createQuery("select balance b from Users where b.userName = '" + user_name.getText() +"'", Users.class).getResultList();

I encounter this error message:

Syntax error parsing [select balance b from Users where b.userName = 'a']. [28, 28] An identification variable must be provided for a range variable declaration.

I want to retrieve the Integer balance from the User whose name is typed in the user_name TextField. I am quite a beginner on this topic and the internet gives complicated info. Can someone please kindly explain what is wrong ?

Part of the Users class

@Entity
@Table(name = "users")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Users.findAll", query = "SELECT u FROM Users u"),
@NamedQuery(name = "Users.findByUserName", query = "SELECT u FROM Users u WHERE u.userName = :userName"),
@NamedQuery(name = "Users.findByBalance", query = "SELECT u FROM Users u WHERE u.balance = :balance"),
@NamedQuery(name = "Users.findByFirstName", query = "SELECT u FROM Users u WHERE u.firstName = :firstName"),
@NamedQuery(name = "Users.findByLastName", query = "SELECT u FROM Users u WHERE u.lastName = :lastName"),
@NamedQuery(name = "Users.findByIban", query = "SELECT u FROM Users u WHERE u.iban = :iban"),
@NamedQuery(name = "Users.findByCharacterSlots", query = "SELECT u     FROM     Users u WHERE u.characterSlots = :characterSlots"),
@NamedQuery(name = "Users.findByLastPayment", query = "SELECT u FROM         Users u WHERE u.lastPayment = :lastPayment"),
@NamedQuery(name = "Users.findByMonthsPayed", query = "SELECT u FROM Users u WHERE u.monthsPayed = :monthsPayed"),
@NamedQuery(name = "Users.findByPassword", query = "SELECT u FROM Users u WHERE u.password = :password"),
@NamedQuery(name = "Users.findByBanned", query = "SELECT u FROM Users u WHERE u.banned = :banned")})
public class Users implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "user_name")
private String userName;
@Column(name = "balance")
private Integer balance;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(name = "iban")
private String iban;
@Column(name = "character_slots")
private Integer characterSlots;
@Column(name = "last_payment")
@Temporal(TemporalType.DATE)
private Date lastPayment;
@Column(name = "months_payed")
private Integer monthsPayed;
@Column(name = "password")
private String password;
@Column(name = "banned")
private Boolean banned;
@ManyToMany(mappedBy = "usersCollection")
private Collection<Servers> serversCollection;
@ManyToMany(mappedBy = "usersCollection")
private Collection<Characters> charactersCollection;

public Users() {
}

public Users(String userName, Integer balance, String firstName,         String     lastName, String iban, Integer characterSlots, Date lastPayment,     Integer monthsPayed, String password, Boolean banned) {
    this.userName = userName;
    this.balance = balance;
    this.firstName = firstName;
    this.lastName = lastName;
    this.iban = iban;
    this.characterSlots = characterSlots;
    this.lastPayment = lastPayment;
    this.monthsPayed = monthsPayed;
    this.password = password;
    this.banned = banned;
}

Solution 1

You should declare an identification variable when querying against Users entity. You are querying against Users entity, therefore the variable declaration should be on User and not on the balance property.

Also, you want to retrieve the balance property of the Users entity which is of type Integer. Therefore, the use of generics should properly indicate the correct type.

Try this:

List<Integer> list = em.createQuery("select u.balance from Users u where u.userName = '" + user_name.getText() +"'", Integer.class).getResultList();

Alternatively, if only single result is expected:

Integer balance = em.createQuery("select u.balance from Users u where u.userName = '" + user_name.getText() +"'", Integer.class).getSingleResult();

A more correct implementation to prevent SQL injection attack is to use a parameterized query:

TypedQuery<Integer> query = em.createQuery("select u.balance from Users u where u.userName = :user_name", Integer.class);
query.setParameter("user_name", user_name.getText());
Integer balance = query.getSingleResult();

Solution 2

Try:

List<Integer> list = em.createQuery("select user.balance from Users user where user.userName = '" + user_name.getText() +"'").getResultList();

Comments

[Chris]

What is balance in relation to Users: the query is expecting a List of Users, while the select is specifying balance. Shouldn't it be something like "Select b from Users u join u.balance b where b.userName 'a'"?

[Y_Lakdime]

@Chris I have edited the question to show some of Users.java

[Chris]

Your query is still wrong, as it needs to be based on the Users entity. What is balance, and why would it have a userName?

[Y_Lakdime]

@Chris it now has been edited

[Y_Lakdime]

en how can I actually use that integer value to display on a Text?

[Y_Lakdime]

and how can I actually use that integer value to display on a Text?

[BalusC]

Why don't you gently remind the OP of the gaping SQL injection attack hole in his attempt?

[Ish]

Another thing, if you think the the result of your query would result to a single unique user (since you are using the userName property to filter the result and userName is declared as Users primary key), then you should use getSingleResult (instead of getResultList)

[Y_Lakdime]

if I use that variable to display the balance, as in this line of code : balancetext.setText("Your balance = " + balance);. I get a nullpointer exception

[Ish]

you should not get a NullPointerException if the balance object itself is null. It should display "Your balance = null". The most probable reason that you are getting the exception in this particularly line, is that your balancetext object is null.

[Y_Lakdime]

That was the culprit. Thanks for your help

[Ish]

you're welcome. btw, i updated the answer to prevent SQL injection attack. You might want to consider that in your code.

[Chris]

I agree it is better to use parameters , but JPQL doesn't actually leave open the SQL injection hole, as providers can parameterize the generated SQL. wiki.eclipse.org/EclipseLink/FAQ/Security