CODEWITHSUNDEEP
hibernatenhibernate
Krishna
Krishna

Reputation: 321

select columns and write cases in hibernate

I want to write detached criteria for query.

SELECT Id, sum(1) as total 
    ,sum(CASE WHEN e.salary > 2000 THEN e.salary
         ELSE 2000 END) "total Salary" 
FROM employees e;

can someone please help?

Upvotes: 3

Views: 1759

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

We can do it this way:

First of all let's create the condition, to be computed later:

var computed = Projections.Conditional(
    Restrictions.Gt("Salary", 2000)
    , Projections.Property("Salary")
    , Projections.Constant(2000));

At this moment, we have the CASE statement wrapped inside the computed projection. So let's use it:

var session = ... // get the ISession 

// criteria querying the Employee
var criteria = session.CreateCriteria<Employee>();

// the projections we need
criteria.SetProjection(
    Projections.ProjectionList()
        .Add(Projections.GroupProperty("Id"), "Id")
        .Add(Projections.Sum(Projections.Constant(1)), "Total")
        .Add(Projections.Sum(computed), "Salary")
    );

// result transformer, converting the projections into EmployeeDTO
var list = criteria
    .SetResultTransformer(Transformers.AliasToBean<EmployeeDTO>())
    .List<EmployeeDTO>();

And this could be our EmployeeDTO, if Salary is int:

public class EmployeeDTO
{
    public virtual int ID { get; set; }
    public virtual int Total { get; set; }
    public virtual int Salary { get; set; }
}

Upvotes: 1

Related Questions