HQL join for Relational mapping and add extra check

@Entity
@Table(name="casehistory")
public class CaseHistory{

   	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

    @ManyToOne
	@JoinColumn(name="case_id")
	private Case case;
}

@Entity
@Table(name="cases")
public class Case {

    @Id
	@GeneratedValue (strategy = GenerationType.IDENTITY)
	private Long id;

    @OneToMany(mappedBy="case_id",cascade =  CascadeType.ALL)
	private List<CaseHistory> caseHistories;
}

I have this two entities. if I write query like this:

StringBuilder queryString=new StringBuilder("SELECT DISTINCT ch.case.id from CaseHistory ch where ch.date>=:fromDate AND ch.date<:toDate and ch.case.id=ch.case.id");
StringBuilder queryString=new StringBuilder("SELECT DISTINCT ch.case.id from CaseHistory ch,Case c where ch.date>=:fromDate AND ch.date<:toDate and c.id=ch.case.id");

both are equivalent or what?

You don’t need to explicitly add a where condition when using mapped associations: Hibernate will do that for you when translating the HQL query to SQL. You should get the result you’re expecting with:
SELECT DISTINCT ch.case.id from CaseHistory ch where ch.date>=:fromDate AND ch.date<:toDate

In your second query you’re effectively constructing an entity join which is equivalent to the ch.case association and, while valid, this is not necessary as I already said.

1 Like