SQL and Pivot tables, rows to columns

See:
http://modern-sql.com/use-case/pivot
How to map table rows to columns using SQL PIVOT or CASE expressions

Postgresql:
https://www.postgresql.org/docs/current/static/tablefunc.html

https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905

Oracle:
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

SQL server:
https://msdn.microsoft.com/en-us/library/ms177410.aspx

Select in Criteria using SQL, Hibernate

Select in Criteria using SQL

In an earlier post we saw how Criteria provided us with the ability to add restrictions in the where clause using pure SQL. The next question would then be if Criteria allows SQL in select clause ?
I tested the below code:

public static void testSqlSelect() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    int id = 4;
    criteria.add(Restrictions.eq("id", id));
    criteria.setProjection(Projections
        .projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master"))
            .add(Projections
                .sqlProjection(
                    "(select count(c.id) from CHILD_ENTITY c INNER JOIN ENTITY e on c.ENTITY_ID = e.id "
                    + "where e.id = " + id + ") as childCount",
                    new String[] { "childCount" },
                    new Type[] { Hibernate.LONG }
                )
            )
        );
    List<Object[]> rows = criteria.list();
    for (Object[] row : rows) {
        System.out.println(" Entity is " + row[0] + " and " + row[1] + " no of kids : " + row[2]);
    }
    session.close();
}

In this the normal Criteria returns me the details of Entity and its Master. But I have added a separate SQL query that will return me the number of ChildEntities associated with the particular record. The output of the code is as below:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
            count(c.id) 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 4) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID like ?
Hibernate: 
    /* load com.model.Master */ select
        master0_.ID as ID2_0_,
        master0_.DATA as DATA2_0_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID=?
 Entity is entity100 and [Master] : ( id 2 , data : master No 2 )] no of kids : 1

As can be seen the SQL query was embedded within the select clause. The query being SQL, it could refer to any table or column whether known or unknown to Hibernate. The other parameters are used to update Hibernate with details of the return type from the statement. As it is an array, the inner select is capable of returning multiple columns.
An important thing to keep in mind though is that the sub-query is not allowed to return more than one row. This will result in an SQL exception if my entity row has more than one Entity_Child associations:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
c.id 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 2) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID=?

Source: http://learningviacode.blogspot.com/2013/04/select-in-criteria-using-sql.html

Native SQL inside Criteria, Hibernate

SQL inside Criteria

We earlier how a custom criterion could be created to handle special scenarios. However this may seem overkill if the condition can be easily expressed using native SQL. For Criteria allows us to add custom SQL expressions which get added in the where clause.
I took the same scenario as the custom criteria – checking the length of a string column:

public static void testViaSql() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.sqlRestriction(
            "length({alias}.name)< ?", 5, Hibernate.INTEGER)); 
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

The result is :

select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        length(this_.name)< ?

As can be seen the restriction was added to the generated SQL query – with one tiny change: The {alias} parameter was replaced with the alias used for the table Entity.
The {alias} here represents the root class – the class for which the criteria is created. Also the “name” used in the SQL string represents the column name and not the class property. (This is SQL remember.)
The Restrictions.sqlRestriction method also has two more parameters – an object that will be substituted for the question mark. The third is the Hibernate built-in type. In this case as our parameter is an integer, we pass the INTEGER type.
What if we do not have any parameters ?

public static void testViaSql_Simpler() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.sqlRestriction("{alias}.master_id = 2"));
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

As can be seen there is an overloaded version that allows for just the where condition to be specified – without any parameters.
This facility can be used to create much more complex queries too.

public static void testViaSql_Complex() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions
            .sqlRestriction("{alias}.master_id = "
                    + "(select id from entity_master where data like 'master No 1')"));
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

The SQL is:

select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.master_id = (
            select
                id 
            from
                entity_master 
            where
                data like'master No 1'
        )

In this case we used it to fire nested queries. The subquery feature is also available directly in the Criteria API and we shall see that in the next post.