Hibernate Criteria, createAlias() If Alias is Null

Question

In the code below.. There are two Alias as Entity Object Reference. Sometimes "caseStage" as stage can be null in Database. When "caseStage" is null I want stage.name value as an empty String or something customized like "---" etc.

session.createCriteria(CaseMasterPO.class)
       .createAlias("branch", "br")     // BranchPO.class
       .createAlias("caseStage", "stage") // CaseStagePO.class
       .setProjection(Projections.projectionList()
          .add(Projections.property("caseCode"))
          .add(Projections.property("br.zoneCode"))
          .add(Projections.property("stage.name")) // Problem, when stage == null
       )
       .add(Restrictions.eq("caseCode", caseCode)).uniqueResult();

I usually use coalesce(stage.name, '---') in HQL, so is there any mechanism for Criteria Query ? — Mr. Mak, Nov 21 '16 at 07:30
what is the problem you are facing .. please explain that ?? — Zulfi, Nov 21 '16 at 08:13
How you display your objects on the screen shouldn't be decided and specified in a database query. Modify your presentation-layer code to display whatever you want in case the value is null. — JB Nizet, Nov 21 '16 at 09:22
@Zulfi, In the above code there is a `Projections` with property name `stage.name`. I do not get the rows when`stage` is `null`. in database. — Mr. Mak, Nov 22 '16 at 08:47

Koti Eswar · Accepted Answer · 2017-02-06T05:39:01.957

7

Set the CriteriaSpecification.LEFT_JOIN to alias function

.createAlias("branch", "br" , CriteriaSpecification.LEFT_JOIN) .createAlias("caseStage", "stage", CriteriaSpecification.LEFT_JOIN)

edited Feb 06 '17 at 05:39

answered Jan 24 '17 at 17:23

Koti Eswar

86
1
5

Dear bro, is there any way to use `CriteriaSpecification.LEFT_JOIN` with `DetachedCriteria` ? – Mr. Mak Jan 23 '18 at 17:47

score 2 · Answer 2 · answered Feb 14 '20 at 16:12

2

@Koti Eswar answer is right, but it's deprecated now.

It's should be done like this:

        join('branch', JoinType.LEFT)
        createAlias('branch', 'br')

Tested in Grails 3.3.5

answered Feb 14 '20 at 16:12

IgniteCoders

4,834
3
44
62

Currently I am using `JoinType.LEFT_OUTER_JOIN` – Mr. Mak Feb 17 '20 at 07:56

Hibernate Criteria, createAlias() If Alias is Null

2 Answers2