Stack Exchange
Hibernate criteria query with INNER JOIN, LEFT JOIN, GROUP_CONCAT and DISTINCT
I am working with hibernate CRITERIA with last few months, I normally do R&D and create CRITERIA query from SQL. but this time i really confused with below query. From where I do start I can't understand.
TABLE Mapping:

SQL Query:
SELECT templates.TEMPLATE_ID,
       templates.TEMPLATE_NAME,
       template_categories.CATEGORY_DESC,
       GROUP_CONCAT(DISTINCT template_code_values.Code_Value) as Code_Valuess
  FROM (client1408.templates templates
       LEFT JOIN
        client1408.template_code_mapping template_code_mapping
           ON (template_code_mapping.Template_ID = templates.TEMPLATE_ID))
       LEFT JOIN client1408.template_code_values template_code_values
          ON (template_code_mapping.Template_Code_Value_ID = template_code_values.ID)
       INNER JOIN client1408.template_categories template_categories
            ON (templates.CATEGORY_ID = template_categories.CATEGORY_ID)   
GROUP BY templates.TEMPLATE_ID

WHAT I have DONE:
Mapped templates and template_categories table with criteria
Criteria templateSearchCriteria = session.createCriteria(Templates.class).createAlias("Category", "category")
                                     .setProjection(Projections.projectionList()
                                     .add(Projections.property("Id"), "Id")
                                     .add(Projections.property("TemplateName"), "TemplateName")
                                     .add(Projections.property("Category"), "Category")
                                     .setResultTransformer(Transformers.aliasToBean(Templates.class));

WHAT remaining:

ADD more 2 table in query template_code_values and template_code_mapping with LEFT JOIN and GROUP BY.
GROUP_CONCAT with DISTINCT as extra column add


Joins are very simple in hibernate, use createAlias and JoinType. For Distinct and grouping we have to use projections. Check the example below:
Criteria criteria = getHibernateSession().createCriteria(A.class);
criteria.createAlias("b", "b", JoinType.INNER_JOIN);
criteria.createAlias("b.r", "b.r", JoinType.INNER_JOIN);
criteria.createAlias("b.c", "b.c", JoinType.LEFT_OUTER_JOIN);
ProjectionList projectionList = Projections.projectionList();
// THE BELOW LINE WILL MAKE SURE COULMN a IS DISTINCT
projectionList.add(Projections.distinct(Projections.property("a")), "a");
// THE BELOW LINKE WILL GROUP IT BY COLUMN c
projectionList.add(Projections.groupProperty("c"));
// ADD all the fields that u want in projection
criteria.setProjection(projectionList);
criteria.setResultTransformer(Transformers.aliasToBean(A.class));
return criteria.list();

