createSQLQuery vs HQL

RubelKanubel · **Joined:** Tue Jun 22, 2004 2:23 am **Posts:** 3

Hi,

In my project we're in the process of structuring database access logic. We've decided to try and incorporate SQL (as opposed to HQL) as our primary query language (using createSQLQuery), and thus would like to be as consistent with this as possible. The main arguments for doing this is:

By using createSQLQuery it is required to fetch *all* properties for the related persister classes. I.e:

Code:

   String sql =
      "SELECT {PERSON.*}" +
      " FROM PERSON P" +
      " WHERE P.SSID = :ssId"
   String aliases[] = new String[] {"P"};
   Class classes[] = new Class[] {Person.class};

   List qRes = session.createSQLQuery(sql, aliases, classes)
                      .setString("ssId", ssId)
                      .list();

In the cases where I'm only intersted in a single field from the result, fetching all columns intuitively seems to be inefficient. I might very well be wrong on this, though. My big concern is that the overhead related to this is heavy, which suggests using a mix of SQL and HQL. Any views on this would be greatly appreciated.

Regards,
Rubel

anthony · **Posted:** Tue Jun 22, 2004 3:04 am

learning hql is not so complex and is more "readable" since it's really oo.
It's also more logic to use.
SQLQuery should be used only for reuse of SQL in JDBC code for example.
You're going into very large query if you continue like this

RubelKanubel · **Joined:** Tue Jun 22, 2004 2:23 am **Posts:** 3

It's not a matter of learning HQL, but rather more of a code consistency question.

I understand that this will result in more code. My main consern is that it might be more inefficient than using HQL to query specific columns.

anthony · **Posted:** Tue Jun 22, 2004 4:39 am

i don't think so, sqlQuery and HQL are parsed to give a SQL ... i don't think there is significant overhead at this level

RubelKanubel · **Joined:** Tue Jun 22, 2004 2:23 am **Posts:** 3

The problem could in essence be rephrased to a more general database question:

What is more efficient:

Code:

SELECT * FROM person

or

Code:

SELECT name FROM person

(Also, how does the resulting SQL look like after being converted from HQL, i.e for a HQL statement like "SELECT p.name FROM Person p"

I guess I could have a look in hibernate log files to find out...)

anthony · **Posted:** Tue Jun 22, 2004 5:04 am

SELECT p.name FROM Person p

will only retrieve the column you need.

and of course select * from is slower than select oneColumn from... but is it really significant? it depends on your network and columns type...