hql

How do I turn this HQL into a filter query?

I have this query: sess.createQuery("from Box b join b.items bi order by bi.name").list() It works fine. However, I have a hibernate's Collection boxes and want to filter is. Naive tries: sess.createFilter(boxes, "join this.items bi order by bi.name").list() sess.createFilter(boxes, "from this join this.items bi order by bi.name")....

(Detached)Criteria equivalent for HQL's 'index' function.

I have an IDictionary on an object which I'm loading with the following mapping: public class InternalFund : IInternalFund { public virtual IDictionary<DateTime, IValuation> Valuations { get; set; } } <class name="InternalFund"> <map name="Valuations"> <key> <column name="FundID" /> </key> <i...

How to build HQL query, thats joins subtables marked LAZY, automatically?

I have some entity: public class Album extends GenericAuditedEntity { @OneToMany(fetch = FetchType.LAZY) private Set<Item> itemSet = new HashSet<Item>(); } And when i run HQL like this: em.createQuery("select a from Album a").getResults() it produses many SQL queries: One for select data from Album's table. Smth like thi...

Hibernate hql problem using case keyword

Hello! I really need help regarding this one. Here is the code snippet: hSql=" select case when min(start_day_plan) is not NULL then min(start_day_plan) else to_date((min(insertDate)) - cast('1 month' as interval),'yyyy-MM-dd' ) end from Project" getHibernateTemplate().find(hSql); But this generates the error below: java...

HQL joining a class and a joined-subclass

Hi, I have a class Top and a Bottom, Bottom is a joined-subclass to Top like: Well, I'd like to join them in hql like: select t.id Top t left outer join Bottom b on t.id = b.id. But I don't know how. Any idea ¿? thanks! ...

HQL select case

Hi I want to do like: select case when a.id is not null then new A(a.id) when b.id is not null then new A(null) but it gives the error: unepected token new on line... Any ideas? thanks! ...

How to query distinct values from map with given key in Hibernate?

I have following Hibernate 3 mapping for User class: <class name="org.test.User" table="users"> ... some other mapping... <map name="metadata" table="user_metadata"> <cache usage="transactional"/> <key column="user_id" not-null="true" foreign-key="FK_USERMETADATA_USER_ID"/> <map-key type="string" column="da...

How to get this kind of query in NHibernate : SELECT DISTINCT FileName From CustomerFile WHERE name = ' ' AND timeframe = ''

Please do not redirect me towards the other similar kinds of HQL in Stackoverflow, because they did not work for me. I really appreciate your direction on it. Thanks. Reproducing this kind of query in Nhibernate with ICriteria API : SELECT DISTINCT FileName From CustomerFile WHERE name = ' ' AND timeframe = '' ...

Data structure to hold HQL or EJB QL

We need to produce a fairly complex dynamic query builder for retrieving reports on the fly. We're scratching our heads a little on what sort of data structure would be best. It's really nothing more than holding a list of selectParts, a list of fromParts, a list of where criteria, order by, group by, that sort of thing, for persistence...

Can HQL substract or sum date fields?

A have two entities. For example timing settings and orders. @Entity public class TimingSettings{ @Basic private Long orderTimeout = 18000; //for example 18000 sec .... } @Entity public class Order{ @Basic @OneToOne private TimingSettings timingSettings; @Temporal(value = TemporalType.TIMESTAMP) @Co...

NHibernate HQL Group by Entity

Is it possible to run a query similar to this one in HQL without having to specify all of the column names. select med, MAX(med.PrescriptionLines.Prescription.PrescriptionDate) from Medication med where med.Patient.PatientId = :patientId group by med This query expands out all of the properties of the Medication object in ...

Hibernate "IN" clause as ALL instead of ANY

I'd like to start by apologizing for my unfamiliarity with Hibernate. I'm only recently getting into it and am far from an expert. I have three tables: Contract, Products, and a link table between them to define a many to many relationship. I'm trying to write an HQL query to return all contracts that contain a range of products. Un...

NHibernate HQL Join Not Returning All Required Rows

Hi, I am modifying an existing HQL query which returns individual columns rather than an object graph but now I am not getting all rows that I need. Here a few facts about the current schema: An Estimate belongs to a Contract. The OwningDepartment property of a Contract can be null. The ParentBusinessStream property of a Department c...

hibernate HQL elements error

My HibernateUserImpl mapping look like below <set name="groups" table="OS_USER_GROUP" inverse="false" cascade="none" lazy="false"> <key column="user_id"/> <many-to-many column="group_id" class="com.opensymphony.user.provider.hibernate3.ahxu.impl.HibernateGroupImpl"/> </set> I do HQL query like select distinct hibuser from Hi...

hibernate query language

please convert the sql in hql SQL Statement : s*elect username from useraccout where email = "parameter value"* More description: while running the code i am getting this error: public List dispUser(String email){ Query query = em.createQuery("SELECT u.username FROM Useraccout u WHERE u.email=:email)"); query.setParameter(email, ...

Using a join in Hibernate HQL update query

string query = "update User u set u.PointsTotal = 1 join u.Rounds r where r.RoundId = :round and (r.Row1 & :val) > 0"; NHibernateSession.CreateQuery(query) .SetByte("val", (byte)val) .SetInt32("round", roundId) .ExecuteUpdate(); Just gives me "The given key was not present in the dictionary." And yes, the relations works ...

hibernate how to retrieve a hierarchy object

Hi all, I have the following beans Task, ServerDetails and ApplicationDetails. I wish to retrieve all tasks, their server details and application details based on a specific application name. From the result i expect to be able to retrieve the data in a manner such as: task.getServers().getApplicationDetails() In actuality, I get wha...

Does NHibernate HQL support the UNION ALL keyword?

After extensive googling, I still can't find a definitive answer to this question. Some old articles/blog posts I've seen say not at all. Some say yes if the underling database supports it. Which is it? I asked on the nhusers group with no answer so far. Any help would be appreciated. ...

Hibernate detached queries as a part of the criteria query

Hi Hibernate, java experts can you please help me write detached queries as a part of the criteria query for the following SQL statement. select A.* FROM AETABLE A where not exists ( select entryid FROM AETABLE B where B.classpk = A.classpk and B.userid = A.userid and B.modifiedDate > A.modifiedDate ) and userid = 10...

IFNULL equivalent in Hibernate Query Language?

I'm trying to write an HQL query which will calculate an average rating for an item. I want the query to return 0 instead of null when there are no rating for a given item - so that I can use my query as a subquery. So is it possible? Is there an HQL equivalent of IFNULL or NVL? ...