hql

JPA/Hibernate query optimization with null values

I'm using Hibernate's implementation of JPA and am seeing poor performance as multiple SQL queries are issued for each entity that is fetched. If I use a joined JPA query it generates just one SQL query but doesn't find rows will null relationship. Example, consider this simple schema. A person lives at an address and is employed by a c...

What adavantages does HQL have over SQL for Group By queries

I am concentrating this question on 'reporting-type' queries (count, avg etc. i.e. ones that don't return the domain model itself) and I was just wondering if there is any inherent performance benefit in using HQL, as it may be able to leverage the second level cache. Or perhaps even better - cache the entire query. The obvious implied ...

How to do custom-query based collection mappings in Hibernate?

I have an object that is mapped to have a set of objects, very simple. However, what I really want to do is put some criteria on that mapping. Here's the current mapping: <set name="ops" inverse="true" cascade="all, delete-orphan"> <key column="cityblock_id" on-delete="cascade"/> <one-to-many class="com.tamedtornado....

Self-referencing updates in HQL

I have the following query in HQL: update ProjectFile pf1 set pf1.validUntil.id =123 where pf1 = ( select pf from ProjectVersion pv, ProjectFile as pf where pf.validFrom.sequence <= pv.sequence and pf.validUntil.sequence >= pv.sequence and pf.state <> 12 and pf.projectVersion.project.id = 1 and pv.project.id = 1 and pv.id = 12 and pf...

Getting DATEPART in HQL or Criteria ?

How can i get DATEPART of a time using HQL or Criteria ? I have googled it up and get some tips, but wasn't enough. If there is someone who has experienced it before, please let us know. Thanks in advance ...

Unicode String in Hibernate Queries

In SQL one can write a query that searches for a name of a person like this: SELECT * FROM Person P WHERE P.Name LIKE N'%ike%' This query would run with unicode characters (assuming that the Name column and database were setup to handle unicode support). I have a similar query in HQL that is run by Hibernate (NHibernate). The genera...

Using a CASE statement in HQL select

Is there any way to do the following in HQL: SELECT case when flag = true then SUM(col1) else SUM(col2) FROM myTable ...

How to count in Grails/Hibernate: Message.countBy

How do I count the number of messages where my body length is between 0 and 25 characters long? Message.countBy('from Message m where m.body.length <= 25') Unfortunately for me, countBy does not take a string parameter. ...

How to construct this NHibernate query

I have a one-to-many relationship. I would like to construct this query: Give me all the parents that have only one child and for this child child.Type=X Since I 'm learning, please show me the query with the Criteria API and with HQL. Thanks. And btw, is there any automatic way to know what HQL is identical to a criteria expression ?...

HQL Query using group by

I have the following class structure FlowerDAO with the fields (mapped using Hibernate): id unitPrice name color How can i create an hql query to obtain the flower(s) for each color that has(ve) the minimum unit price on that color? I have tried this, but it doesn't work from FlowerDAO as f where f.unitPrice<= (select min(f2.unitPr...

HQL - row identifier for pagination

Does anyone know if HQL has a keyword to identify rows such as ROWID or ROWNUM? I would like to implement pagination with HQL but I am not able to use .setMaxResult() or .setFirstResult() because I don't work with the session object directly and therefore don't use the Query object but simply create my query as a string and use the .fin...

HQL string matching on complete words?

How do I match complete words in a hibernate HQL query? For example, imagine in our database we have an entry "Sam Adams". Now, given this HQL fragment: book.character like 'Sam' I do not match on "Sam Adams". However, if I change my query to: book.character = 'Sam%' then I also match on "Samantha". But, I don't want that to mat...

How do I use the current date in an HQL query with an Oracle database?

I'm trying to write this query using Hibernate 3 and Oracle 10. from Alert alert where alert.expiration > current_date() order by alert.priority, alert.updated, alert.name It's creating SQL like this - Hibernate: select alert0_.ANNOUNCEMENTS_ID as ANNOUNCE1_1_, alert0_.ANNOUNCEMENT S_NAME as ANNOUNCE2_1_, alert0_.ANNOUNCEMENTS_PRIO...

Can you order hql results based on matching restrictions?

I have an object Dog which holds a set of DogMetadata. Each DogMetadata has two values: a String ("desc") and an int ("rank"). Descriptions of dogs have different rankings such as: "small" is 5, "furry" is 2, "friendly" is 9, "dalmation" is 11, "mutt" is 22. I need to search for dogs based on any of their desc values (for example, fin...

Hibernate HQL with list getters

I have a Hibernate entity, with a getter that is mapped as a @OneToMany: @Entity class Parent extends BaseParent { @OneToMany(cascade = {CascadeType.ALL}, mappedBy = "parent") public List<Child> getChildren() { return super.children; } public void setChildren(List<Child> list) { super.children = list; ...

HQL "Contains" statement howto?

Hi, I have an entity that has a string property called Tags. I would like to query this entity based upon if a certain string is located in Tags property. So for example, I would have a function IList GetEntityByTag(string tag), this would return all Entity's that have the value of tag in their 'Tags' property. I tried going through t...

HQL to return the index of an object in an ordered query result?

Can I use HQL to get the index of an ordered query result? The only way I know to do this is to get back all of the results from the database, then iterate through all of the results. For example, given: <hibernate-mapping> <class name="Dog" table="DOG"> <id name="id" type="int" column="DOG_id"> <meta attribute="scope-set">protec...

how to return rownum column value with HQL? (using oracle db)

I have a complicated HQL query. I would like to access the Oracle specific rownum column value as part of my returned results. How do I write my query (and/or change my hbm.xml) to support this? What I have tried so far does not work: modifying my hbm.xml <property name="rownum" type="int" update="false" insert="false" generated="ne...

SQL string from an HQL query?

HQL queries are converted to SQL. I would like my program to have the complete SQL string before it is sent to my database. Can I do this? Notes: I can see the SQL sent to the database by setting <property name="hibernate.show_sql">true</property> in my cfg.xml ...

JPA and Hibernate Fetch ignoring Associations?

I have JPA entity (Object A) with a One-Many owning relationship (Object B) in an ArrayList. I want to be able to query (either Hibernate or JPA) for Object A without having any of the instances of association Object B returned (no proxies or otherwise) in the One-Many ArrayList. Ideally the returned ArrayList would be null or empty. ...