hql

Hibernate Parent/Child SELECT N+1 issue

Hi Folks (and Pascal), I jave the following mapped superclass that provides a basic implementation for a parent/child self relationship to create a parent/child list for unlimited nesting of items (i.e. Categories) @MappedSuperclass public abstract class ParentChildPathEntity<N extends ParentChild> implements MaterializedPath<N> { ...

Abnormal behaviour by HQL while using JPA (*** More of an information to readers than a question *** )

Hello, I was trying to fire hql for some reporting purpose in my JPA based application. The following query that I tried kept conking out on startup (Since I had given it as a NamedQuery, it was checked for syntax at startup). Incorrect Query: SELECT t FROM Table_1 tb1 INNER JOIN Table_2 tb2 where tb1.name = 'someName'; After lot o...

Hibernate HQL strange behavior with IS NULL

I have a problem with a HQL query. I want to get all PID that has an administrative sex set to 'M' or no administrative sex (in Java the value is set to null). PID.class @Entity @Table(name = "PatientIdentification") public class PID { @OneToOne(cascade = CascadeType.ALL) @JoinColumn(name = "administrativeSex", referencedC...

HQL version of a SQL join with group by

I have two tables, Band and Votes. Band has a name and an id, and Votes has a total_votes column and a foreign key called band_id pointing to band.id. I have lots of votes, saved at various dates. What I want to do is find the maximum value of the total_votes column for each band. The following SQL query works: select b.name,max(v.t...

HQL Query with multiple Criteria

I am trying to write a HQL Query which selectes rows from a table based on multiple criteria. firstName,lastName the catch is that the query should be flexible to ignore any empty or null values so select t from table t where (:firstname = '' or t.firstName = :firstName) AND (:lastName = '' OR t.lastName = :lastName) I would have ...

Query scalar collections in HQL

I have the following class: class User { String username; @CollectionOfElements private Set<String> roles = new HashSet<String>(); [many more things here] } And I want to write a HQL query that retrieves the username and the roles for all the users. Query query = session.createQuery("select distinct u.username, u.roles from User u");...

How to get HSQLDB to emit error messages during initialization of a datasource from a .script file?

I am working on a Java application that will use some Hibernate (annotated by JPA) classes, backed by a HSQLDB datasource (DBCP BasicDataSource). I am trying to manually tweak the HSQLDB ".script" file (which I can't for the life of me find the authoritative name for by web searching/reading the docs; it's only mentioned in passing) to ...

HQL: Hibernate query with ManyToMany

I have a question with HQL query and hibernate. I have a user class and a role class. A user can have many roles. So I have a ManyToMany relatation like this: In user class: @ManyToMany(fetch = FetchType.LAZY) @oinTable(name = "PORTAIL_USERROLE", joinColumns = { @JoinColumn(name = "USERID", nullable = false, updatable = false) }, inve...

Hibernate:: query.list always contains the records of the first entry of the list

In the Database: TIMESTAMP DEVICE_ID SRC_ADDR PKTS_FWDED ------------------------------------------- 2010-08-10 11:45:07.547 4 2887253771 3 2010-08-10 11:45:09.547 4 2887253771 18 output of Hibernate: TIMESTAMP = 2010-08-10 11:45:07.547 DEVICE_ID: 4 SRC_ADDR: 2887253771 PKTS_FWDED 3 TIMESTAMP = 2010-08-10 11:45:07.547 DEVICE_ID...

Secondarytables or OnetoOne associations ?

Considering the following "model": USER Long: PK String: firstName String: lastName USER_EXT Long: PK String: moreInfo Date: lastModified I'm trying to find/create the correct Hibernate mapping (using Annotations) such that, with an HQL query as simple as "from User", it would generate the following SQL: sel...

HQL: Fetch Join Collections from Eager Table

I have four tables: RootNode // Will return multiple root nodes SubNode // Will return one sub node per root node SubNodeChildren1 // Will return multiple for each sub node SubNodeChildren2 // Will return multiple for each sub node and a similar entity structure: RootNode -> SubNode -> SubNodeChildren1 -> SubNode...

Hibernate recursive query

My desired query is to get a list of Course objects that belong to a Category. My objects are as follows: public class Course{ String name; List<Category> categories; } public class Category{ String name; Category parent; } Since the categories reference each other, they can have an infinite depth: A A.A ...

What's Wrong with this HQL Query?

With this HQL query: SELECT DISTINCT fs FROM FileStatus fs WHERE UPPER(STR(fs.filePath)) LIKE :FILE_PATH I get: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637) ...

org.hibernate.hql.ast.QuerySyntaxException

i get this exception when i was running an application please give me a solution in my mapping file like as follows: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt; <hibernate-mapping> <class name="mobilserv....

Datetime string in HQL

hi, I am using Castle ActiveRecord for my application. My problem is how to use a datetime string in HQL like this (without using parameters): "from Contact c where c.DayOfBirth = '5/3/1988'" ...

Help with HQL query (group by, order by)

I have problem writing HQL to display distinct applicationId with the latest application (newest createDate) for the following data in the table. +---------------+-------------+----------+---------------------+ | applicationId | firstName | lastName | createDate | +---------------+-------------+----------+--------------------...

Query many-to-many without selecting all objects using Criteria API

I have a many-to-many relationship between Project and Site. I am trying to retrieve a list of Sites for a project using the Criteria API. I've got this working but the query also selects all of the columns for the associated Projects, which I don't want. I wrote what I thought was an equivalent query using HQL and it only selects the Si...

avg aggregate function in HQL causing trouble in NHibernate, what is wrong with my query?

Hi, Having trouble with an HQL query. If I remove the avg(..) from it it will return all of the empty weights, so the trouble is with the avg function. It seems clear in the Nhibernate docs that I can perform an aggregate function on the select item like this. Is my problem something to do with query.ToList not liking to return a list of...

Hibernate get single row from the table with maximum field value

Hi Guys, In Hibernate, how can I get single row from the table with maximum field value? Thanks ...

Hibernate: cant get the associated List retrieved

Hi An entity X has a list of entity Y and the entity Y has an instance of entity Z. The relation between X to Y is OneToMany and the relation between Y to Z is ManyToOne. I want to retrieve X and have all the associated entities retrieved with them as well. What HQL query do I write so that I get the whole chain retrieved all at once...