Currently, I have an HQL query that returns all Members who possess ANY Award from a set of specified Awards: from Member m left join m.awards as a where a.name in ("Trophy","Ribbon"); What I now need is HQL that will return all Members who possess ALL Awards specified in the set of Awards. So, assuming this data: Joe has Trophy, M...
I'm still a little new to hql, and I had a question about aggregation functions and efficiency for a query I'm writing. Let's say I have this class mapped in hibernate (getters/setters/constructors/etc. omitted for brevity): public class Foo { private int i; private String s; private float f; } I want to do a hql query ...
Any one who tell me the query using criteria/hql/sql. Requirement is that user enter email or username the query return the password of the user from table user. ...
Hi all, I'm developing a web application using asp.net Mvc 2 and NHibernate, and I'm paging data (products in a category) in my page, but this data are random, so, I'm using a HQL statement link this: string hql = "from Product p where p.Category.Id=:IdCategory order by rand()"; It's working fine, but when I page, sometimes the same p...
What is the translation of the following HQL query to EclipseLink compliant JPQL : select p from NameList list, Person p where p.name in elements(list.names) (this is just a variation of a HQL example query taken from here) In EclipseLink the IN function doesn't seem to take property paths : Internal Exception: NoViableAltException(...
I'm trying to restrict my NHibernate query with some basic date/time manipulation. More specifically, I want to execute the following statement (pseudo-SQL): select * from article where created_on + lifespan >= sysdate with: created_on is mapped to a property of type DateTime. lifespan is mapped to a property of type TimeSpan. sysda...
Is it possible to do indexed element access in JPQL like in HQL : select o from Order o where o.items[0].id = 1234 I couldn't find something related in the JPA 2 specs, I am targeting EclipseLink JPA here, so if you come up with an EclipseLink solution, that's ok as well, although a JPQL standard solution is preferred. ...
Is it possible to use casts in JPQL? In HQL it seems to be supported cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database source : Hibernate reference My target JPA provider is EclipseLink, so any EclipseL...
I am having trouble optimizing Hibernate queries to avoid performing joins or secondary selects. When a Hibernate query is performed (criteria or hql), such as the following: return getSession().createQuery(("from GiftCard as card where card.recipientNotificationRequested=1").list(); ... and the where clause examines properties that ...
Hello, simple question regarding HQL(Hibernate query language) so i have user class , that can hold a list of Projects, how do i take this out of the database depending on a username, this is how i take out my user String username = "stephen"; YFUser user = (YFUser) session.createQuery( "select u FROM YFUser u where u.usernam...
Hi all I have the following issue. I have a table of reserves in my MySQL DB, the date columns is defined DATETIME. I need to make a query using hibernate to find all reserves in one day no matter the hour, just that its the same year month and date, and I'm doing this public List<Reserve> bringAllResByDate(Date date){ em = emf.createE...
I am trying to convert this native sql into an HQL query. Basically it returns all the contacts that are duplicates (by company, firstname, and lastname). The query below works great, but performing a joined subselect seems impossible in HQL is it not?! Select c.* from contact c join ( Select c2.* from contact c2 group by c2.com...
Is it possible to get NHibernate to generate a query similar to the following with HQL or Criteria API? select * from ( select row_number() over ( partition by Column1 order by Column2 ) as RowNumber, T.* from MyTable T ) where RowNumber = 1 I can get it to execute the inner select using the formu...
I am trying to create an HQL query that will filter a tree based on a user. On the tree root i have AllowUsers and AllowRoles and on each node I have DenyUsers and DenyNodes. I can filter to on the user on the root of the tree using select e from oStructureMenu e join fetch e.Nodes n where e.Id = :id and :user in (select u from...
I have a Vote domain class from my grails application containing properties like article_id and note I want to HQL query the Vote domain class in order to retrieve the 5 best rated articles having at least 10 votes. I tried : SELECT v.article_id, avg(v.note), count(*) FROM vote v where count(*) >= 10 group by v.article_id order by av...
For a grails application, I need to find a list of objects whose "attr" is one in a dynamic list of strings. The actual HQL query is more complex, but the bit I need help with is this: def result = MyObject.executeQuery("select o from MyObject as o where o.attr in :list", [list: aListOfStrings]) This is obviously not the right syn...
Hi guys, I have a forum-like system where a user has an access group and a post can be accessed by an access group. A post is of a category type, and I'd like to make a list of the category names with the number of posts the user has written in this category in a paranthesis. A user may not have access to all his posts, because one acce...
I have two tables: Job job_id, <other data> Job_Link job_link_id, job_id, start_timestamp, end_timestamp, <other data> There may be multiple records in Job_Link specifying the same job_id with the start_timestamp and end_timestamps to indicate when those records are considered "current", it is guaranteed that start_timestamp and end_...
I'm stuck with a query I need to write. Given the following model: public class A : Entity<Guid> { public virtual IDictionary<B, C> C { get; set; } } public class B : Entity<Guid> { } public class C : Entity<Guid> { public virtual int Data1 { get; set; } public virtual ICollection<D> D { get; set; } } public class D : En...
I have a mapping like this: @ManyToMany(cascade = CascadeType.PERSIST) @JoinTable( name="product_product_catalog", joinColumns={@JoinColumn(name="product_catalog", referencedColumnName="product_catalog")}, inverseJoinColumns={@JoinColumn(name="product", referencedColumnName="product")}) public...