How to select from HQL

Question

I'm new to HQL and have a SQL expression I need converted but am unable to select the SQL statement is:

select SenseDate as Time,SenseValue as Value
from UserData
where NetworkID = '23'
and IODeviceID = '129'
and SenseDate >=  DateAdd("d",-1, GETDATE())
and SenseDate<=GETDATE()

I can run this part in HQL without problems:

from UserData 
where NetworkID = '23'
and IODeviceID = '129'
and SenseDate >=  DateAdd(d,-1, GETDATE())
and SenseDate<=GETDATE()

However I only want the SenseDate and SenseValue values returned, could someone show me how to select as when I try to add select SenseDate, SenseValue etc. I keep getting errors in Netbeans

Solution 1

You can select fields/columns using HQL. Looks like the following:

select
    SenseDate,
    SenseValue
from
    UserData
where
    NetworkID = '23'
and
    IODeviceID = '129'
and
    SenseDate >= DateAdd(d, -1, GETDATE())
and
    SenseDate <= GETDATE()

When you execute this you will receive a list of arrays of objects:

final List<Object[]> values = query.list();

Each element in the list represents a found row. The array itself contains the two selected fields in the same order you declared them in the statement.

Solution 2

I think HQL has the "new" keyword now.

select new Family(mother, mate, offspr) from Eg.DomesticCat as mother join mother.Mate as mate left join mother.Kittens as offspr

Similar to projections/resulttransformers in the Criteria world.

Solution 3

You'll have to use something like projections.

This means, that you'll have to create a class that will hold the results that you're trying to retrieve.

In your case, this could look something like this:

public class SenseUserData
{
    public DateTime SenseDate
    {
        get;
        private set;
    }

    public Decimal SenseValue
    {
        get;
        private set;
    }

    public SenseUserData( DateTime date, Decimal value )
    {
       this.SenseDate = date;
       this.SenseValue = value; 
    }
}

Then, you'll have to let NHibernate know about the existence of this class. You can do that, by 'importing' it. So, this means you do not have to create a mapping file for it, you just have to do this in a hbm file:

<import class="myclassname" />

And then, you can just do this in HQL:

select new SenseUserData (SenseDate, SenseValue) from UserData

Solution 4

It looks like you need to do what hibernate calls projections. Here is some info on how to do projections in HQL:

http://www.devarticles.com/c/a/Java/Hibernate-HQL-in-Depth/1/

View more solutions