how to write hql join query

Question

I'm new from HQL any one Please tell me how to write this Query in HQL.

My SQL Method is

public Integer validateEditDate(int id, String date);

My SQL Query is

SELECT 
count(a.activity_task_id)
FROM activity_task_details AS a
JOIN milestone_activity_details AS b
    ON a.milestone_activity_id =  b.milestone_activity_id
WHERE a.milestone_activity_id = 17
    AND DATE(a.task_end_date) > '20161229';

Where id and date is dynamic

Please convert this sql query in Hql query

Answer 1

To convert a SQL query in HQL you must define, as first, classes about db table mapping.

You have these two tables:

activity_task_details milestone_activity_details

So you must have two classes, as follow:

ActivityTaskDetails MilestoneActivityDetails

So, you must map your fields as properties. Suppose you have done this, your query will became:

SELECT 
count(a.activity_task_id)
FROM ActivityTaskDetails a, MilestoneActivityDetails b
WHERE a.milestone_activity_id =  b.milestone_activity_id
AND a.milestone_activity_id = :paramId
AND a.task_end_date > :paramDate;

Pay attention, if possible, your task_end_date in class ActivityTaskDetails declare as date

To execute your query:

String hql = "Your HQL query write upper";
Query q = session.createQuery(hql);
q.setString("paramId", id);
q.setDate("paramDate", date);
q.list();

session is a variable to get session factory object