Use of Scalar queries in SQL by R4R Team

In the Hibernate the most basic SQL query is to get a list of scalars (values).

It means which is shown in below example:

sess.createSQLQuery("SELECT * FROM CATS").list();

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

By the help of these we can return a List of Object arrays (Object[]) with scalar values for each column in the STUDENT table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values. To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar().

Use of addScalar() in below code:

sess.createSQLQuery("SELECT * FROM CATS")

 .addScalar("ID", Hibernate.LONG)

 .addScalar("NAME", Hibernate.STRING)

 .addScalar("BIRTHDATE", Hibernate.DATE)

We can say that this query specified:

1. the SQL query string

2. the columns and types to return

By the help of this we will return Object arrays, but now it will not use ResultSetMetadata but will instead explicitly get the ID, NAME and BIRTHDATE column as respectively a Long, String and a Short from the underlying resultset. This also means that only these three columns will be returned, even though the query is using * and could return more than the three listed columns. It is possible to leave out the type information for all or some of the scalars.

sess.createSQLQuery("SELECT * FROM STUDENT")

 .addScalar("ID", Hibernate.LONG)



It is essentially the same query as before, but now ResultSetMetaData is used to determine the type of NAME and BIRTHDATE, where as the type of ID is explicitly specified.

How the java.sql.Types returned from ResultSetMetaData is mapped to Hibernate types is controlled by the Dialect. If a specific type is not mapped, or does not result in the expected type, it is possible to customize it via calls to registerHibernateType in the Dialect.

Leave a Comment:
R4R Team
R4Rin Top Tutorials are Core Java,Hibernate ,Spring,Sturts.The content on 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!