JPA - How to write correct HQL?

[Nakataa Kokuyo]

Good day,

I have a DB query as following which give me correct result, and when i try on my HQL and it provide me incorrect result.

Database:

Item table contain 1 record, 1 PK ==> GID
Item_status table contain 4 records, and 2 PK ==> SLI_ID + Start_time

SQL query :
Result : 2 rows return correctly

 
SELECT ITEM.SLA_ID,ITEM.Name,ITEM_STATUS.start_time, ITEM_STATUS.end_time, ITEM_STATUS.total_value
FROM ITEM ITEM
INNER JOIN ITEM_STATUS ITEM_STATUS ON ITEM.gid = ITEM_STATUS.ITEM_gid
WHERE EXTRACT(month FROM ITEM_STATUS.start_time) >2

My HQL:
Result: Return 2 set of 4 rows records, both set's records are same. (Question: 1- Why it show me 2 set of same record? 2- What need to change in my HQL?)

String hql = "SELECT s from Item AS s " +
		  "JOIN s.Item_Status AS status " +					  
		  "WHERE MONTH(status.startTime) > 2 ";

Below is my entity class:

@Entity
@Table(name="ITEM")
public class Item implements Serializable{

	@Id
	@Column(name="GID")
	private String id;
		
	@Column(name="SLA_ID")
	private String slaId;
	
	@Column(name="Name")
	private String name;

	@OneToMany(mappedBy="item", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
	private List<Item_Status> statusList;
}		

@Entity
@Table(name="ITEM_STATUS")
public class Item_Status implements Serializable{
	
	@Id
	@Column(name="SLI_ID")	
	private String 	sliid;
		
	@Column(name="ITEM_GID", insertable=false, updatable=false)	
	private String 	itemid;

	@Id	
	@Column(name="START_TIME")
	@Temporal(TemporalType.TIMESTAMP)
	private Date 	startTime;
	
	
	@Column(name="END_TIME")
	@Temporal(TemporalType.TIMESTAMP)
	private Date 	endtime;
	
	@Column(name="TOTAL_VALUE")
	private Double 	total_Value;
		
	@ManyToOne
    @JoinColumn(name="ITEM_GID")   
	private Item item;
}	

[Carles Gasques]

Hi,

Seems that is converted to an outer join...
What happens when you add "AND s.id = status.slaitemid' to the query?
Could you enable sql trace?


Best regards,

[Nakataa Kokuyo]

Thanks Carles for enlightenment, how do we enable the SQL trace? I'm using JPA and Hibernate with persistence.xml and Spring applicationContext.xml

Between, I already set my relationship and I'm using 'JOIN', it suppose treat as inner join right ?

[Carles Gasques]

Hi,
To enable sql trace add <property name="hibernate.show_sql" value="true"/> to your persistence unit properties (persistence xml).


Cheers,

[Nakataa Kokuyo]

Hi Carles,

My IDE show the SQL like below

Hibernate: select slaitem0_.GID as GID1_, slaitem0_.Name as Name1_, slaitem0_.SLA_ID as SLA3_1_ from REPS_ITEM slaitem0_ inner join ItemStatus ItemStatusl1_ on slaitem0_.GID=ItemStatusl1_.ITEM_GID where extract(month from ItemStatusl1_.StartTime)>2

Hibernate: select ItemStatusl0_.ITEM_GID as SLA5_1_1_, ItemStatusl0_.StartTime as REF1_0_1_, ItemStatusl0_.SLI_ID as SLI2_0_1_, ItemStatusl0_.StartTime as REF1_0_0_, ItemStatusl0_.SLI_ID as SLI2_0_0_, ItemStatusl0_.TOTAL_VALUE as CALC3_0_0_, ItemStatusl0_.REF_PER_END_TIME as REF4_0_0_, ItemStatusl0_.ITEM_GID as SLA5_0_0_ from ItemStatus ItemStatusl0_ where ItemStatusl0_.ITEM_GID=?

found there is statement show in console about "composite-id class does not override equals() and hashcode()", do i need to do something for it but how?

p/s: I modified above to avoid company data security concern.

Question :
1. Why it fire 2 query ?
2. Look like first SQL is correct, but not sure why second SQL have ITEM_GID=?

[Nakataa Kokuyo]

One more update is that, the jointcolumn ITEM_GID is not unique....not sure if that caused the issue

[James Boswell]

I think this HQL will work for you:

 select i from Item i join i.statusList status with month(status.startTime) > 2

You need to be careful not to mix property names (Java) and column names (SQL).

[Nakataa Kokuyo]

Thanks James for your guidance, I have try and the result appear the same ...I think it could be ITEM_gid duplication.

Currently, I'm pull all the record and use the if statement to filter out unnecessary return record..sound bad but working at the moment...I'll definitely try to make a simple sample after my tasks meet the timeline..

I'm just curious, our entity already define the relationship, do we still need to join again in the HQL? would it take as extra join?

[James Boswell]

How about this:

String hql = "SELECT distinct s from Item AS s " +
		  "JOIN s.Item_Status AS status " +					  
		  "WHERE MONTH(status.startTime) > 2 ";

It looks like unique entities are not returned by default. Hence, you use the distinct keyword to remove duplicates.

[Nakataa Kokuyo]

hmm...Still the same