hql

Hibernate criteria: Joining table without a mapped association

I'd like to use Hibernate's criteria api to formulate a particular query that joins two entities. Let's say I have two entities, Pet and Owner with a owner having many pets, but crucially that association is not mapped in the Java annotations or xml. With hql, I could select owners that have a pet called 'fido' by specifying the join in...

NHibernate: Return A Constant In HQL

I need to return a constant from an HQL query in NHIbernate SELECT new NDI.SomeQueryItem(user, account, " + someNumber + ") FROM NDI.SomeObject object I am trying for something like above. I've tried this: SELECT new NDI.SomeQueryItem(user, account, :someNumber) FROM NDI.SomeObject object And then later: .SetParameter("someNumb...

Fetch Tags and Tag count using HQL on SQL Server 2008

I'm implementing tagging on a particular entity, using NHibernate on SQL Server 2008. The structure I have now is, simplifying, like this: public class Entity { public Guid Id { get; set; } } public class Tag { public Guid Id { get; set; } public string Name { get; set; } } public class TagAssoc { public Tag LinkedTag ...

Hibernate: update to sum from other table

I'm trying to replicate this query from MySQL to HQL: *UPDATE users u, mines m SET u.mana = u.mana + COALESCE((SELECT SUM(m.mana_rate) FROM mines m WHERE m.user_id = u.id), 0)* Simply doing this in HQL: update User u set u.mana = u.mana + (select coalesce(sum(m.manaRate), 0) from Mine m where m.userId = u.id) Gives following error: Exc...

HQL: How to sort list of objects on property of mapped composite element

Hi everyone, I have an object with a map of components: <class name="Article" table="articles"> ... <map name="i18nData" table="articles_i18n"> <key column="id" not-null="true"/> <map-key column="language" type="string"/> <composite-element class="Article$ArticleI18nData"> <property name="displayName" type...

using hql in NHibernate

I have two tables: -- Table: medibv.btdbn -- DROP TABLE medibv.btdbn; CREATE TABLE medibv.btdbn ( mabn varchar(8) NOT NULL, hoten text, ngaysinh timestamp, namsinh varchar(4), phai numeric(1) DEFAULT 0, mann varchar(2), madantoc varchar(2), sonha varchar(15), thon text, cholam text, matt varchar(3), maqu varcha...

NHibernate: HQL and UserTypes as query paramaters

I'm using a database that has a weird date format. I wrote a UserType to transfer standard .NET DateTime to/from the weird format and it works fine. I've normally used ICriteria queries but decided to try IQuery using HQL on this project. I ran into a problem that query doesn't translate parameters to the appropriate UserType. eg: I...

NHibernate: a reverse version of in()?

Hi all, I am trying to perform what I can only describe as the reverse version of IN() using the Nhibernate Criteria.. Rather than saying the value stored in the property is in the list/collection X I want to say, this value X (an ID) is in the list which is a property of the object. Any help appreciated, I can try to explain better...

Select constant in hibernate?

In SQL I can perform the following select 10000 as num Can I do something similar in hql. I know it sounds like a strange request. I do not want to use the Criteria api, and I don't have access to the query, I can only pass an hql string to a method that will then perform the query for me. ...

Left Join in NHibernate HQL

Is it possible to perform a left join between two tables that are not related each other through a parent-child or many-to-many relationship ?. All the samples I found around only show those scenarios. I have the following tables, Sync -> Id (string) -> EntityId (string) -> OtherInfo Customer -> Id -> OtherInfo Project ...

Grails query association problem

Hi, I'm having trouble writing a query for the following domain classes: class Person { static hasMany = [memberships: Membership] } class Membership { static belongsTo = [person: Person, group: Group] Date joinDate = new Date(); Group group; Person person; } class Group { static hasMany = [memberships: Member...

iReport and Hibernate.. trying to understand please and thank you!

Okay, so I've got iReport up and running, and I can now run HQL queries in it, and it will fetch back objects, and put them in the Fields section of the Report inspector. Do I really need to flatten out all my queries so that I only return one "level" of actual data values, and not my entity objects? I'd rather like to just return the ...

Aggregate query with Castle ActiveRecord

I'm trying to perform a simple aggregate query that returns the aggregate's result plus an extra column. This post -> http://stackoverflow.com/questions/308203/custom-query-with-castle-activerecord had a good example about how to achieve this, but I can't seem to get it to work. It seems that ActiveRecordMediator.ExecuteQuery returns an ...

Get "surrounding" rows in NHibernate query

I am looking for a way to retrieve the "surrounding" rows in a NHibernate query given a primary key and a sort order? E.g. I have a table with log entries and I want to display the entry with primary key 4242 and the previous 5 entries as well as the following 5 entries ordered by date (there is no direct relation between date and prima...

hibernate annotations, hql query by interfaces?

The hibernate (I'm using version 3.4) documentation for hql says that it supports interfaces, and I'm having trouble getting it to work. I have some persistent classes (not inherited from eachother, but sharing many functions) that all share an interfaces (CategorizableEntity). I can use it with instanceof in my java code, but I cannot...

hibernate: retrieving orphan children

I have a unidirectional one to many relationship, and would like to select all children who have no parent. How can this be done in HQL? 10x ==== i.e. Parent: id, name, ..., Collection<Child> Child: id, name ...

set lazy as true during HQL execution time

In our application, we have various objects set to lazy false based on the application needs. However, in one of the use case we want to ignore all the lazy settings within the HBM files, and get ONLY the target object. So the question is: is there a way to specify in the HQL to fetch ONLY the target object irrespective of the HBM sett...

HQL: order all items by a specific item in its map

Hi, I am quite new to Hibernate and currently struggling a bit with HQL. I have the following mapping and would like to get all "Industry" entities ordered by the "translation" for a given "culture_id" Code: <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <class name="Domain.Industr...

How to join on none PK/FK columns using HQL?

Using HQL, how do you join on columns (or object properties) that are non PK/FK? I'm reading the docs, and it seems it implicitly is going to join on the PK columns right? https://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html ...

Hibernate query for entity based on whether related entity property is null?

I have the following mapping: <class name="Customer"> <!-- actually one-to-one for all intents and purposes--> <many-to-one name="specialProperty" class="SpecialProperty" cascade="all" not-found="ignore" insert="false" update="false" column="id" unique="true"/> </class <class name="SpecialProperty" lazy="false"> <id name="id" ...