hql

Querying collections of value type in the Criteria API in Hibernate

Hi, In my database, I have an entity table (let's call it Entity). Each entity can have a number of entity types, and the set of entity types is static. Therefore, there is a connecting table that contains rows of the entity id and the name of the entity type. In my code, EntityType is an enum, and Entity is a Hibernate-mapped class. in ...

Looking for an HQL builder (Hibernate Query Language)

I'm looking for a builder for HQL in Java. I want to get rid of things like: StringBuilder builder = new StringBuilder() .append("select stock from ") .append( Stock.class.getName() ) .append( " as stock where stock.id = ") .append( id ); I'd rather have something like: HqlBuilder builder = new HqlBuilder() .selec...

NHibernate, Sum Query

If i have a simple named query defined, the preforms a count function, on one column: <query name="Activity.GetAllMiles"> <![CDATA[ select sum(Distance) from Activity ]]> </query> How do I get the result of a sum or any query that dont return of one the mapped entities, with NHibernate using Either IQuery or ICriter...

HQL querying columns in a set

Is it possible to reach the individual columns of table2 using HQL with a configuration like this? <hibernate-mapping> <class table="table1"> <set name="table2" table="table2" lazy="true" cascade="all"> <key column="result_id"/> <many-to-many column="group_id"/> </set> </class> </hibernate-mapping> ...

C# + Castle ActiveRecord: HasAndBelongsToMany and collections

Let's say I have many-to-many relationship (using the ActiveRecord attribute HasAndBelongsToMany) between Posts and Tags (domain object names changed to protect the innocent), and I wanted a method like FindAllPostByTags(IList<Tag> tags) that returns all Posts that have all (not just some of) the Tags in the parameter. Any way I could ac...

Is there a more efficient way of making pagination in Hibernate than executing select and count queries?

Usually pagination queries look like this. Is there a better way instead of making two almost equal methods, one of which executing "select *..." and the other one "count *..."? public List<Cat> findCats(String name, int offset, int limit) { Query q = session.createQuery("from Cat where name=:name"); q.setString("name", name);...

Hibernate: Criteria vs. HQL

What are the pros and cons of using Criteria or HQL? The Criteria API is a nice object-oriented way to express queries in Hibernate, but sometimes Criteria Queries are more difficult to understand/build than HQL. When do you use Criteria and when HQL? What do you prefer in which use cases? Or is it just a matter of taste? ...

HQLfind instances of a class that have all of a given list of keys in its composite-map-key

<classname="Source" table="source"> <component name="map" access="field"> <map name="parameters" access="field" table="parameters" cascade="all-delete-orphan" lazy="true" sort="natural"> <key column="owning_source" not-null="true" foreign-key="source_params"/> <composite-map-key ...

Hibernate dynamic query performance issue solutions using result transformer!

This is a important addition especially to solve performance issues while being able to write efficient dynamic HQL queries. But, how do we modify the HQL transformer in case of loading a specific parent or another mapped entity property. session.createQuery( "select st.stNumber as stNumber, st.stDate as stDate " + " from SomeTable st "...

How do you do a union of two tables in NHibernate?

I need to do a union of two tables using NHibernate and HQL. I have found very little help online, and I want to know if it is possible and if so how? ...

How do you create a Distinct query in HQL

Is there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a valid keywork for HQL, but I am looking for the HQL equivalent of the SQL keyword "distinct". ...

Converting SQL to HQL

I'm trying to convert the below SQL query to HQL and am having a few issues. A straight line by line conversion doesn't work, I am wondering if I should be using an Inner Join in the HQL? SELECT (UNIX_TIMESTAMP(cosc1.change_date) - UNIX_TIMESTAMP(cosc2.change_date)) FROM customer_order_state_change cosc1 LEFT ...

Doesn't NHibernate HQL support "with" keyword?

I'm trying to build a HQL that can left join values from a collection, in order to give me the chance of checking "is null" on it. Taken from the example from hibernate manual: from Cat as cat left join cat.kittens as kitten with kitten.bodyWeight > 10.0 doesn't seem to work in NHibernate, since it doesn't recognize the...

having trouble converting a query to hql

Hi I'm pretty new to HQL (well, nhibernate in general) and am currently building a simple app to learn more about it. I've run into problems trying to express the following sql as hql though, and would be very grateful for any ideas. Here's the query: select * from parent p where p.id in (select p.parentid from child c where c.create...

How can I express this in HQL

I've been stuck with this query for some time now. The SQL returns the result that I want, but I cannot work out how to express the query in HQL. Here's the SQL: select thread.ThreadId, thread.Title, thread.CreatedOn, thread.ViewCount, thread.CreatedBy, thread.ForumId from Threads thread where (...

Hibernate: Parse/Translate HQL FROM part to get pairs class alias, class name

Can anyone point me out, how can I parse/evaluate HQL and get map where key is table alias and value - full qualified class name. E.g. for HQL SELECT a.id from Foo a INNER JOIN a.test b I wish to have pairs: a, package1.Foo b. package2.TestClassName It's relatively easy to do for result set HQLQueryPlan hqlPlan = ((SessionFac...

Using Min, Max and Count on HQL

Hello there. Does hibernate HQL queries support using select min, max, count and other sql functions? like select min(p.age) from person p Thanks ...

Get Row Count InvalidCast Exception from ScalarQuery

ScalarQuery<int> query = new ScalarQuery<int>(typeof(Role), "select count(role.RoleId) from Role as role"); return query.Execute(); It fails with the invalidcast exception but succeeds when count is replaced with max. ...

Use type of object in HQL where clause

In my domain model I have an abstract class CommunicationChannelSpecification, which has child classes like FTPChannelSpecification, EMailChannelSpecification and WebserviceChannelSpecification. Now I want to create an HQL query which contains a where clause that narrows down the result to certain types of channel specifications. E.g. (i...

How to a write a Criteria query with multiple joins involved

Hi, I'm trying to code the following HQL query using the Criteria API: var userList = _session .CreateQuery("select u from User u where u.Role.ID=3 and u.Customer.ID=:cID") .SetInt32("cID", 1) .List<User>(); (3 NHibernate objects : User(ID, Name, Role, Customer), Role(ID, Name) and Custo...