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;
}

Answer 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();