Is it mandatory to use addScalar() in createSqlQuery() in hibernate? Why do we need to specify the Data type to hibernate while executing SQL queries?

Question

String sql = "select Band.band_id bandId from guest_band Band";
   sessionFactory.getCurrentSession().createSQLQuery(sql)
    .addScalar("bandId", Hibernate.LONG)
    .list();

I got to know that addScalar() is used to state hibernate the DataType of the selected item, bandId in this case. But my question is, why do we need to specify the type to hibernate? What does it internally perform? Secondly is it an exception if we don't addScalar()? Lastly, is there any alternate way how this can be achieved?

score 3 · Answer 1 · answered Dec 20 '16 at 10:12

It's not mandatory but would certainly help to use

From https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

The most basic SQL query is to get a list of scalars (values).

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

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS 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():

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME", Hibernate.STRING)
 .addScalar("BIRTHDATE", Hibernate.DATE)

This query specified:

the SQL query string the columns and types to return

This 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 CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME")
 .addScalar("BIRTHDATE")

This 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.

Fadamaka · Answer 2 · 2015-08-14T13:14:07.970

An easy example what addScalar is used for:

public byte[] getFile(Integer id){
Query q = session
    .createSQLQuery("select some_file from tbl_name where id=:id")
            .addScalar("some_file", StandardBasicTypes.BINARY);
    q.setInteger("id", id);
    return (byte[]) q.uniqueResult();
}

For example you have blob data type in your database, in this case you can easily cast your result into byte[] but if you run the query without the addScalar function you will get your result as a blob and you can't cast blob to byte[] directly, you need to write a code for conversion:

try{
    Blob blob =(Blob)q.uniqueResult();
    int blobLength = (int) blob.length();  
    byte[] blobAsBytes = blob.getBytes(1, blobLength);
    return blobAsBytes;
} catch (Exception e) {
    return null;
}

In this problem, it's much easier to use addScalar.

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

As far as I now, it is not necessary. I've never, ever, written a query with .addScalar.

You could simply replace that with something like:

Query q = sessionFactory.getCurrentSession().createQuery(
                "select b.band_id " +
                "from guest_band as b "
                );

List idList = q.list();

Although this may depend on how your entities are set up, it should work.

Perhaps .createSQLQuery and .createQuery are different in this manner.

Refer to this post on what the .addScalar() actually does: What does addScalar do?

Edit: I am familiar with Java, and I guess I was assuming you were using Java for your post. If using C# this may be different.

edited May 23 '17 at 10:31

Community

1
1

answered Aug 13 '15 at 19:27

ballBreaker

722
1
10
29

Thanks for your reply. I however have gone through that link before. But my question is, why do we need to specify the type to hibernate? What does it internally perform? Is it an exception if we don't addScalar()? Is there any alternate way how this can be achieved? When you converted createdSqlQuery() to createQuery(), it's not the right conversion, as in createQuery() we use Java Class Name as it executes hql, but guest_band is DB table name as sql is involved. So we can't convert from createSqlQuery to createQuery.And we don't use addScalar() with createQuery() as far as I know. – Deca Aug 13 '15 at 19:45
Ahhh I see now, I wasn't sure what your entity names were, but that makes sense. May I then ask why you do not use the entities and instead use the database table names? As for your other questions about the internal workings I am unsure, sorry. – ballBreaker Aug 13 '15 at 19:58
My question was in general. This code snippet, I just gave it as an example. Regarding internal working, I wanted to know when hibernate takes the confirmation by addScalar() about the data type, what does it perform after that. Why is it required to state hibernate about that? – Deca Aug 13 '15 at 20:04

Is it mandatory to use addScalar() in createSqlQuery() in hibernate? Why do we need to specify the Data type to hibernate while executing SQL queries?

3 Answers3