How to select from HQL

Question

Hi im a newbe 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()

and 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

many regards

Patrick

Answer 1

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.

Answer 2

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/

Answer 3

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.

Answer 4

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

Answer 5

Hi

I have one cuestion

it ´s can work this hql part????

sElEt employed.age as birthy ...