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...
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 ...
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....
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...
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 ...
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...
Is there any way to do the following in HQL: SELECT case when flag = true then SUM(col1) else SUM(col2) FROM myTable ...
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. ...
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 ?...
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...
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...
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...
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...
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...
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; ...
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...
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...
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...
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 ...
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. ...