映射hibernate SQLQuery本机在java实体中返回一个空对象

Question

我的oracle数据库中有两个表

我的表数据库

和两个实体用户和命令

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

}

和我的服务java类

@Service
@Transactional
public class MyServiceImpl implements MyService {
private static final long serialVersionUID = 8393594103219622298L;
private static final String MY_ADVANCED_REQUEST 
="select us.USER_ID, cd.CMD_DATE, us.USER_PASSWORD " +
"from Users us , Commands cd ON us.USER_ID = cd.USER_ID " +
"where us.USER_ID = cd.USER_ID " +
"AND us.USER_ID = 5";

public List<Object> findUsersCmd() {
String query1 = MY_ADVANCED_REQUEST;
SQLQuery queryObj = sessionFactory.getCurrentSession().createSQLQuery(query1);
List<Object> lstObj =queryObj.list(); 
return lstObj;
}

}

我在这行得到一个空列表list-lstObj=queryObj.list((。

我使用一个请求SQLQuery原生而不是jpa。

我想为我们获得1个对象.USER_ID=5(即使我在表命令中没有us.USER_ID=5(，为我们获得3个对象.USER_ID=1(因为命令表中有3行(；我们有1个对象。USER_ID=6(因为Commands表中有1行(

我如何解决问题，为我们返回一个valeur.USER_ID=5？

Answer 1

左外部联接返回第一个表中的所有行，只返回第二个表中匹配的行。(尽管命令表中不存在user_id=5，但可以检索它(

private static final String MY_ADVANCED_REQUEST 
="select us.USER_ID, cd.CMD_DATE, us.USER_PASSWORD " +
"from Users us LEFT JOIN Commands cd ON us.USER_ID = cd.USER_ID ";