实体java出现Hibernate查询语法异常hql错误

Question

我有一个查询，其中我连接了两个实体java(表(Command和User。我用左联接创建了sql-hql查询，得到了一个org.hibernate.hql.ast.QuerySyntaxException：意外的令牌错误我认为我的左联接语法和第二个选择有问题。我的实体

@Entity 
@Table(name = "Users")
public class User {
private int idUser;
private String login;
private String password;
private List<Command> commands = new ArrayList<>();

public User() { }
public User( String login, String password ) {
super();
this.setLogin( login );
this.setPassword( password );
}
@Id
@Column(name = "USER_ID", unique = true, nullable = false, scale = 0)
public int getIdUser() {
return idUser;
}
@Column(name = "USER_LOGIN", nullable = false)
public String getLogin() {
return login;
}
public void setLogin(String login) {
this.login = login;
}
@Column(name = "USER_PASSWORD", nullable = false)
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}

@OneToMany(fetch = FetchType.LAZY, mappedBy = "utilisateur")
public List<Command> getCommands() {
return commands;
}
public String toString() {
return this.idUser + ": " + this.login + "/" + this.password 
;
}

}

@Entity  
@Table(name="Commands")
public class Command {

private int idCommand;
private User utilisateur;
private Date commandDate;

public Command() {}
public Command( User user, Date commandDate ) {
this.setUser( user );
this.setCommandDate( commandDate );
}
@Id
@Column(name = "CMD_Id", unique = true, nullable = false, scale = 0)
public int getIdCommand() {
return idCommand;
}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "USER_ID")
public User getUtilisateur() {
return user;
}
public void setUtilisateur(User user) {
this.user = user;
}
@Column(name = "CMD_DATE")
@Temporal(TemporalType.TIMESTAMP)
public Date getCommandDate() {
return commandDate;
}
public void setCommandDate(Date commandDate) {
this.commandDate = commandDate;
}
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append( "Commande de >> " ).append( this.user )
.append( " - " ).append( this.commandDate ).append( "n" );
return builder.toString();
}   

}

和我的hql查询

select us.login,us.password 
from User us  left join Command cmd on us.idUser = cmd.utilisateur.idUser
AND us.idUser not in (SELECT utilisateur.idUser from Command )   
ORDER BY us.idUser desc

我不知道我的hql查询出了什么问题。我在hql查询中使用实体java。我可以告诉我如何用一个好的语法hql正确地编写hql查询吗。

Answer 1

如果您需要没有命令的用户的登录名和密码，以下可能有助于

select us.login,us.password 
from User us 
where us.idUser not in (select c.user.idUser from Command c)   
order by us.idUser desc