Projections, aggregation and grouping by R4R Team

The class org.hibernate.criterion.Projections is a factory for Projection instances. Now we can apply a projection to a query by calling setProjection().

List results = session.createCriteria(Student.class)

    .setProjection( Projections.rowCount() )

    .add( Restrictions.eq("color", Color.BLACK) )

    .list();

List results = session.createCriteria(Student.class)

    .setProjection( Projections.projectionList()

        .add( Projections.rowCount() )

        .add( Projections.avg("weight") )

        .add( Projections.max("weight") )

        .add( Projections.groupProperty("color") )

    )

    .list();

There is no explicit "group by" necessary in a criteria query. Certain projection types are defined to be grouping projections, which also appear in the SQL group by clause. An alias can be assigned to a projection so that the projected value can be referred to in restrictions or orderings. 

Here are two different ways to do this:

List results = session.createCriteria(Student.class)

    .setProjection( Projections.alias( Projections.groupProperty("color"), "colr" ) )

    .addOrder( Order.asc("colr") )

    .list();

List results = session.createCriteria(Student.class)

    .setProjection( Projections.groupProperty("color").as("colr") )

    .addOrder( Order.asc("colr") )

    .list();

The alias() and as() methods simply wrap a projection instance in another, aliased, instance of Projection. As a shortcut, you can assign an alias when you add the projection to a projection list:

List results = session.createCriteria(Student.class)

    .setProjection( Projections.projectionList()

        .add( Projections.rowCount(), "studentCountByColor" )

        .add( Projections.avg("weight"), "avgWeight" )

        .add( Projections.max("weight"), "maxWeight" )

        .add( Projections.groupProperty("color"), "color" )

    )

    .addOrder( Order.desc("studentCountByColor") )

    .addOrder( Order.desc("avgWeight") )

    .list();

List results = session.createCriteria(Domestic.class, "student")

    .createAlias("poraskar", "pkr")

    .setProjection( Projections.projectionList()

        .add( Projections.property("student.name"), "studentName" )

        .add( Projections.property("prk.name"), "pkrName" )

    )

    .addOrder( Order.asc("studentName") )

    .addOrder( Order.asc("pkrName") )

    .list();

Now we also use Property.forName() to express projections:

List results = session.createCriteria(Student.class)

    .setProjection( Property.forName("name") )

    .add( Property.forName("color").eq(Color.BLACK) )

    .list();

List results = session.createCriteria(Student.class)

    .setProjection( Projections.projectionList()

        .add( Projections.rowCount().as("studentCountByColor") )

        .add( Property.forName("weight").avg().as("avgWeight") )

        .add( Property.forName("weight").max().as("maxWeight") )

        .add( Property.forName("color").group().as("color" )

    )

    .addOrder( Order.desc("studentCountByColor") )

    .addOrder( Order.desc("avgWeight") )

    .list();