I am trying the following hibernate query but it always gives me a wrong result:
@SuppressWarnings("unchecked")
@Override
public List<Jcelulasmin> getAllOthers(int id, String username) {
Session session = this.sessionFactory.getCurrentSession();
List<Jcelulasmin> JcelulasList = session.createQuery("from Jcelulasmin where jgrelhas.id=? and (jconcorrentes is null or jconcorrentes.users.username <> ?) order by id").setParameter(0, id).setString(1, username).list();
for(Jcelulasmin p : JcelulasList){
logger.info("Jcelulas List::"+p);
}
return JcelulasList;
}
This query returns me 13 values, but that should be the result of "jconcorrentes.users.username <> ?"
The result of "jconcorrentes is null" is 47 values so my query should return 47+13=60...
I'm trying to achieve the following SQL query that actually returns 60 values:
SELECT * FROM `jcelulas` WHERE GrelhasId=1 and (ConcorrentesId is null or ConcorrentesId<>1)
ps:jconcorrentes.id is 1 so the sql and hql should be the same
Table/Entitiy definition:
@Entity
@Table(name = "jconcorrentes", catalog = "7jogos")
public class Jconcorrentes implements java.io.Serializable {
private Integer id;
....
private Users users;
@ManyToOne(fetch = FetchType.EAGER)
@JsonBackReference
@JoinColumn(name = "username", unique = true, nullable = false)
public Users getUsers() {
return this.users;
}
public void setUsers(Users users) {
this.users = users;
}
I tried the following and it actually worked:
session.createQuery("from Jcelulasmin where jgrelhas.id=? and (jconcorrentes is null or jconcorrentes <> 1) order by id").setParameter(0, id).list();
The only problem is that i have to get it by username which is in Users.
Users:
@Entity
@Table(name = "users", catalog = "7jogos", uniqueConstraints = @UniqueConstraint(columnNames = "username"))
public class Users implements java.io.Serializable {
@NotEmpty(message="Não se esqueça do Email")
@Email(message="Email Inválido")
private String username;
...
private Set<Jconcorrentes> jconcorrenteses = new HashSet<Jconcorrentes>(0);
@OneToMany(fetch = FetchType.EAGER, mappedBy = "users")
public Set<Jconcorrentes> getJconcorrenteses() {
return this.jconcorrenteses;
}
public void setJconcorrenteses(Set<Jconcorrentes> jconcorrenteses) {
this.jconcorrenteses = jconcorrenteses;
}
Your current query translates to inner join between Jcelulasmin and jconcorrentes (because of jconcorrentes.users.username
), thus nulls are excluded. You have to explicitly left join them:
select j from Jcelulasmin j left join j.jconcorrentes jcon ...