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
– Mr. Mak, Commented Nov 21, 2016 at 7:30
what is the problem you are facing .. please explain that ?? — Zulfi
– Zulfi, Commented Nov 21, 2016 at 8: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
– JB Nizet, Commented Nov 21, 2016 at 9:22
@Zulfi, In the above code there is a Projections with property name stage.name. I do not get the rows whenstage is null. in database. — Mr. Mak
– Mr. Mak, Commented Nov 22, 2016 at 8:47

Koti Eswar · Accepted Answer · 2017-02-06 05:39:01Z

7

Set the CriteriaSpecification.LEFT_JOIN to alias function

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

edited Feb 6, 2017 at 5:39

answered Jan 24, 2017 at 17:23

Koti Eswar

861 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr. Mak Over a year ago

Dear bro, is there any way to use CriteriaSpecification.LEFT_JOIN with DetachedCriteria ?

IgniteCoders · Accepted Answer · 2020-02-14 16:12:15Z

@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

Collectives™ on Stack Overflow

Hibernate Criteria, createAlias() If Alias is Null

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related