Is it mandatory to use addScalar () in createSqlQuery () in sleep mode? Why do we need to specify the data type for sleep mode when 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 found out that addScalar () is used to indicate the hibernate DataType of the selected item, bandId in this case. But my question is: why do we need to specify the type for sleep mode? What is he doing inside? Secondly, is it an exception if we do not add Scalar ()? Finally, is there an alternative way in which this can be achieved?

Answer 1

This is not necessary, but will certainly help to use

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

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

They will return an array of List Objects (Object []) with scalar values ​​for each column in the CATS table. Hibernate will use ResultSetMetadata to infer the actual order and return types of scalar values.

To avoid the overhead of using ResultSetMetadata or just to be more explicit in what is returned, addScalar () can be used:

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

The specified request:

SQL query string columns and types to return

This will return arrays of objects, but now it will not use ResultSetMetadata, but instead will explicitly get the column ID, NAME and BIRTHDATE, respectively, Long, String and Short from the base result set.

It also means that only these three columns will be returned, although the query uses * and can return more than three listed columns.

You can leave type information for all or some 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 types NAME and BIRTHDATE, where when the identifier type is explicitly specified.

How java.sql.Types is returned from ResultSetMetaData mapped to Hibernate types is controlled by the dialect. If a particular type is not displayed or does not result in the expected type, it can be configured using calls to registerHibernateType in the dialect.

Answer 2

A simple example of what addScalar uses 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 a blob data type in your database, in this case you can easily convert your result to byte [], but if you run the query without the addScalar function, you will get the result as blob, and you cannot cast blob to byte [] directly, you need to write code to convert:

 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 task it is much easier to use addScalar.

Answer 3

As far as I am now, this is not necessary. I have never written a query using .addScalar .

You can simply replace this 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 objects are configured, it should work.

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

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

Edit: I am familiar with Java, and I assume that I assumed that you are using Java for your message. If you use C #, this may be different.