hql

NHibernate - How to write this Query: Select parents & find child for each parent that matches a condition

OK, first my simple Domain Model is 2 classes with a one-to-many relationship, a simple Parent -> child relationship. A 'Tweet' has one or more 'Votes', but each Vote belongs to just one Tweets etc. public class Tweet { public virtual long Id { get; set; } public virtual string Username { get; set; } public virtual string Me...

Hibernate - Perform HQL query on Criteria Result

Hi @ all, I am concerned with a problem regarding criteria and hql. In first step a complex criteria construction limits a big amount of datasets. In second step a hql calculation should be performed on these preselected datasets of step one. The problem is that the code of both has been developed seperately and i am wondering if it is...

How to reference a map name for a subquery in an HQL order by clause

I've got an HQL statement like this: select new map (f1 as field1, (select ...) as field2) from ... where ... order by field2; It fails saying "Unknown column 'field2'". I experienced this in general that when using the "new map" statement, I can't reference the map names in the order by field. As HQL subqueries are only allowed in t...

Using CONTAINS from HQL / Criteria API

Hi all, I'm using NHibernate 2.1.2.4000GA. I'm trying to use SQL Server's CONTAINS function from within HQL and the criteria APIs. This works fine in HQL: CONTAINS(:value) However, I need to qualify the table in question. This works fine: CONTAINS(table.Column, :value) However, I need to search across all indexed columns in my tab...

Validating HQL against the domain with Castle Active Record

Hi all, We use a lot of view model builders which pass HQL strings to the ActiveRecordMediator.Execute method to populate search objects for our views. Doing refactoring occassionally breaks these 'magic' hql strings (without us knowing) I was wondering if anyone has tried using nhibernate named queries to validate HQL in Castle Activ...

HQL statements with no FROM and WHERE clause

I am trying call a db-function from HQL. The HQL statement should just call the function and return its value, like this select someFunction(:someParameter) If i try to call select current_timestamp() it fails with NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Exception of type 'Antlr.Runtime.MismatchedTreeNodeException' was thrown...

HQL Query to check if size of collection is 0 or empty

I try to generate a HQL query that include user with a empty appoinment collections(mapped by OneToMany): SELECT u FROM User u JOIN u.appointments uas WHERE u.status = 1 AND (uas.time.end < :date OR size(uas) = 0) I tries it on several manners (NOT EXIST ELEMENT(), IS NULL) also see: http://stackoverflow.com/questions/1105011/how-to-c...

Hibernate inheritance and HQL

Hello Hibernate daemons, I have inheritance in Hibernate for where Connection is my parent entity, and MobilePhoneConnection is extended entity. I used one table per subclass strategy for inheritance mapping. This is my file: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3...

How to convert MySQL query into HQL query?

I am new to HQL. This is a mysql Query. I need to convert it into HQL query. How to do that any suggestions please? `SELECT STUDENT.ID, STUDENT.NAME, STUDENT.GRADE_ID, STUDENT.CLASS, GRADE.NAME FROM STUDENT INNER JOIN GRADE ON STUDENT.GRADE_ID = GRADE.GRADE_ID` STUDENT[ID, NAME, GRADE_ID, CLASS] GRADE[GRADE_ID, GRADE_NAME] ...

Where to put object-oriented queries in a layered architecture?

Given: You have an architecture with the layers presentation, business and data. You are applying domain-driven design. You are using an object-relational mapper that lets you create object-oriented queries (e.g., NHibernate which lets you create HQL queries). Question: Into which layer should you put the object-oriented queries? M...

Grails and HQL Recover Insert ID

Is there a way to recover the last generated auto_increment identifier generated by an insert when using Hibernate ? I know from my php background that you can get this information by way of a function call like mysql_insert_id(). I'm operating in grails and using HQL to get a performance boost on some insert operations, but I need to ...

Problem with saveOrUpdate() in hibernate

Hi, Recently i am started using hibernate. to insert or update i am using saveOrUpdate() function. If i update an entry i works fine. But, with new entry hibernate generate a Query. Buts nothing gets updated in the table. My scenario is like this, i am using Many to One & One to Many relationship between two tables[Expense, Category]...

How this SQL Query in hbm.xml file can be written in source code?

I am trying to run a SQL query in Hibernate. I am using its documentation. Because of some unknown values i am trying to do inside the Source code. See this below SQL-Query configuration. How to re-write it in source code itself!! I tried this personList = session.createSQLQuery("SELECT person.NAME, person.AGE, person.SEX, address.STR...

Convert a SQL query to HQL

Hi my question is simple but may a little bit rude. Could please one of you convert two SQL Query to hql :> Select statistic.* FROM statistic INNER JOIN (SELECT MAX(st_id) AS id " + "FROM statistic WHERE status = 'N' GROUP BY bc_id) ids ON statistic.st_id = ids.id" + "ORDER BY bussinessprocessID; Select post_id, u_post_id FROM statist...

Multiple row update in nhibenrate

Hi guys, So I was wondering whether it is best to update multiple rows in a database using an hql query or calling nhibernates save after modifying each object.. I know that the answer is the HQL query but please give me adequate reasons for the same. but there are disadvantages of using an HQL query right like my API has a hard depend...

Finding the row with matching relations using HQL

I am using Castle ActiveRecord and NHibernate. I have an Instance class which has a many-to-many relationship with a Component class. I would like to find the instance which is related to a specific set of components. Is this possible in HQL (or anything else in NHibernate)? The linq version of this function would be: public Instanc...

How to make an optimal hibernate query with three objets ?

I have a query but I dont know how to make it optimal. I have three objects. SCP Question : it has a field that points SCP (SCP_id) Answer : it has a field that points Question (question_id) How can I make a query that counts the number of answer within a given SCP ...

Problem using nested avg(..) aggregate function in hibernate hql

I am using HQL to get data through DAO class but it throws as error stated below : ERROR org.hibernate.hql.PARSER - <AST>:0:0: unexpected AST node: query Below is my Hql Query : select new com.shaikh.dto.UserResult ( user.userSurName, avg((select avg(v1.age) from com.shaikh.dto.UserResult v1 where v1.joinDate between to_date(:dayF...

How to write a HQL query for Many To Many Associations?

I have 3 tables, Role[roleId, roleName], Token[tokenID, tokenName] & ROLETOKENASSOCIATION[roleId, tokenID]. The 3rd one was created automatically by hibernate. Now if i simply write a Query to get all the objects from Role class means, it gives the all role objects along with the associated tokenID & tokenName. I just wanted the associa...

HQL: combine "insert into ... select" with fixed parameters values

I have HQL statement: insert into Item (ost, value, comments, startTime, endTime, proposedBy) select si.ost, si.value, si.comments, si.endTime, si.endTime, u from Item si, User u where si.ost = ? and u.id = ? How could it be modified to use parameters' values for ost and startTime columns while taking other columns from select? ...