带有实体 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