hql

ClassCastException when usign HQL

Hi, See the following mapping public class SomeClass { private Integer someField; } When i call the following query select someField, count(*) from SomeClass inner join OtherClass... group by ... And i proccess the query as follows Map<Integer, Integer> result = new HashMap<Integer, Integer>(); List<Object> objectList = qu...

get first item of a non indexed collection in hql

Hi there, Is there a way to get the first item of a non indexed collection in HQL, nested in a select statement? f.e. FROM ProjectPhase p WHERE p.Statusses[0].FullFilled = 'True' When p.Statusses is an IList, this works because the list is indexed but if p.Statusses is a bag, this doesn't work (because the items in a bag aren't indexed)...

HQL nullable property should not be ignored, help?

Hey All, I have: Class Foo { String name; String value; Foo parent; //Foo.parent is OneToOne and nullable } I have the following HQL: FROM Foo f WHERE (lower(f.name) like concat( lower('string') , '%' )) or (lower(f.value) like concat( lower('string') , '%' )) or (lower(f.parent.name) like concat( lower('string') , '%' )) The que...

Property Mapping based on dynamic criteria at runtime

Hi All, I have been sifting through pages on Google looking for the answer to no avail, however I think I am just phrasing the question incorrectly. The scenario is as follows: I have an entity which users are able to either vote for or against. For arguments sake lets call the entity a business. I would like to have a property on my ...

HQL Query with where clause

Hi guys; I am trying to query hibernate for given scenario: My Data Model Class Communication: variables : subject, sentTo, creator. Now lets say there are some notes exchanged between usera and userb. I want to find all those notes by a hibernate query. I have written below my query string. select note from Communication n where n...

HIBERNATE: Load multiple collections in single select.

Hello, I have the following class-mapping that contains multiple collections mapped as : Code: <class entity-name="tab_a" table="tab_a" lazy="true" schema="dbo" dynamic-update="false" dynamic-insert="false" select-before-update="false"> <id name="tabid" type="string" column="`TABID`"> <generator class="nativ...

Query by age in hql

I have a class User with one field called birthDate which is a java.sql.Date. How do I do a hql query that will retrieve all Users that are between min and max years old? (My real scenario is slightly more complex than that but that's where I am stuck right now). UPDATE It must be an hql expression so I can put the age expression in a...

Ordering results by computed value in Hibernate

Hello, I have a table Player with columns id, name, wins, games_played. I mapped it to a class Player. I want to do the following query in Hibernate (preferably with Criteria, if not possible with Criteria HQL will also help) select * from Player order by (wins / games_played) I expect to get List<Player> sorted by their win rat...

T-SQL to HQL (NHibernate)

I have the following T-SQL: DELETE FROM Table WHERE UserId=@UserId AND TableId NOT IN (SELECT TOP 10 TableId FROM Table WHERE UserId=@UserId ORDER BY DateColumn) What is the NHiberante equivalent? Cheers. ...

Linq to NHibernate returns different results than HQL?

I have this basic entity setup: public class Instrument { public virtual int Id { get; set; } public virtual Guid? InstrumentGuid { get; set; } public virtual string FIPSCode { get; set; } public virtual IList Names {get; set;} } public class Name { public virtual int Id {get; set;} public virtual string Name {g...

Join tables with nested select count and group by in Nhibernate HQL

I have post, vote and comment table. Each post can have N votes and N comments. I have been trying to find a way to do this query using Nhibernate HQL with no success. SELECT P.Id, P.Title, P.TextDescription, ISNULL(V.TotalVotes,0), ISNULL(C.TotalComments, 0) FROM Post P LEFT JOIN (SELECT PostId, count(PostId) as TotalVotes...

HQL Min Max, how to

Hi, I have two object : "Mother" and "Child". Mother have many Children, How can i get from the DB a Mother with only 2 Children (or less), the younger and the older. Thanks edit: The mother i want to get have a lot of children but i only want the younger and the older. Something like that : from Mother m left join m.Child c where (m...

Parameterizing a HQL IN clause using HqlBasedQuery?

How do you pass a list of things for the 'in' clause in Nhibernate HQL? e.g. // data input from the user interface, not known at compile time object[] productIds = {1, 17, 36, ... }; string hqlQuery = @" from Product as prod where prod.Id in ( ? )"; HqlBasedQuery query = new HqlBasedQuery(typeof(Product...

hql equivalent query to this sql query

Select top 1 from <tablename> what is hql query for the above ? ...

Order by nullable property simultaneously with ordering by not nullable property in HQL

Hi, I have a table called Users in my database. Let's assume that User has only 3 properties int ID; string? Name; string Login; If user doesn't specify his name then Login is displayed. Otherwise Name is displayed. I wan't to get list of all users sorted by what is displayed. So if user specified Name, it is taken into consideration ...

Nhibernate HQL where IN query

Im trying to return a SimpleQuery list that queries a single table and uses IN. I can get this to work using return new List<Jobs>( ActiveRecordMediator<Jobs>.FindAll(Expression.In("ServiceId", ids)) ); However this is really really really slow. So id like to do something like this SimpleQuery<Job> query = new SimpleQuery<Jo...

Collection.contains(Enum.Value) in HQL?

I'm a little confused about how to do something in HQL. So let's say I have a class Foo that I'm persisting in hibernate. It contains a set of enum values, like so: public class Foo { @CollectionOfElements private Set<Bar> barSet = new HashSet<Bar>(); //getters and setters here ... } and public enum Bar { A, B }...

How do I query through a many-to-many relationship using NHibernate Criteria and Lambda Extensions?

In my database I have a Person table and an Event table (parties, meetings, &c.). This many-to-many relationship is represented through an Invitation table. Each Person can have many Invitations. Each Event can also have many Invitations. If I want a list of Events to which a Person is invited, I can use this HQL query: IQuery query = ...

How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way?

How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? As I see it returns a list of Objects. yes, it is tricky and only you who write the query know what should the query return (what objects). But are there ways to simplify things, so that it returned specific objects with no need in casting Object to a...

Subquery using derived table in Hibernate HQL

I have a Hibernate HQL question. I'd like to write a subquery as a derived table (for performance reasons). Is it possible to do that in HQL? Example: FROM Customer WHERE country.id in (SELECT id FROM (SELECT id FROM Country where type='GREEN') derivedTable) (btw, this is just a sample query so don't give advices on rewriting it, is j...