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();

Leave a Comment:
Search
Categories
R4R Team
R4Rin Top Tutorials are Core Java,Hibernate ,Spring,Sturts.The content on R4R.in website is done by expert team not only with the help of books but along with the strong professional knowledge in all context like coding,designing, marketing,etc!