variables - 语法错误解析 JPQL : An identification variable must be provided for a range variable declaration

Question

尝试运行此代码时:

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

我遇到此错误消息:

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.

我想从在 user_name 文本字段中输入名称的用户那里检索整数余额。
我是这个主题的初学者，互联网提供了复杂的信息。有人可以请解释什么是错的吗？

用户类的一部分

@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

在查询用户实体时，您应该声明一个标识变量。您正在查询 Users 实体，因此变量声明应该在 User 而不是 balance 属性上。

此外，您还想检索整数类型的用户实体的余额属性。因此，泛型的使用应该正确地指明正确的类型。

试试这个:

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

或者，如果只预期单个结果:

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

防止 SQL 注入(inject)攻击的更正确的实现是使用参数化查询:

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