This is the sql that is output from Hibernate log:
Code:
/* select
count(distinct us),
count(distinct u),
count(distinct q)
from
UserSession us ,
User u,
UserSession q
where
q.question is not null */ select
count(distinct usersessio0_.USERSESSION_ID) as col_0_0_,
count(distinct user1_.USER_ID) as col_1_0_,
count(distinct usersessio2_.USERSESSION_ID) as col_2_0_
from
USERSESSION usersessio0_,
USER user1_,
USERSESSION usersessio2_
where
usersessio2_.QUESTION_ID is not null limit ?
These are the two Entities (with relevant fields included only):
Code:
@Entity
@Table(name = "USER")
@org.hibernate.annotations.Entity(selectBeforeUpdate = true)
public class User implements Serializable, Comparable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "USER_ID")
private Long id;
@Column(name = "USERNAME", length = 20, nullable = false, unique = true)
private String username;
@OneToOne(mappedBy = "user")
private UserSession usersession;
public User() {}
....etc
}
@Entity
@Table(name = "USERSESSION")
public class UserSession implements Serializable, Comparable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "USERSESSION_ID")
private Long id;
@OneToOne
@JoinColumn(name = "USER_ID")
private User user;
@OneToOne
@JoinColumn(name = "QUESTION_ID")
private Question question;
public UserSession() {}
.... etc
}
I know the query is not returning what I expect because When there is one user in the user table, and that same user has an entry in the usersession table, the query should return 1,1,0 (Because usersession.question_id will be null). But the query is always returning 0,0,0. Obviously the first two counts are not correct.
What I want is simply:
A count of all rows in the user table
A count of all rows in the usersession table
A count of all rows in the usersession table where question_id is not null
I appreciate any help you can give to help me understand this better. Thanks!
John