Hi, Im facing simple problem with searching entities by some (sub)string, which they might contain. E.g. I have users user1, usr2, useeeer3, user4 and I will enter to search window "use" and I expect to return user1, useeer3, user4. Im sure you know what I mean now. Is there any construction in JPA (JQPL)? It would be nice to search u...
Hello, I want to write query something like :- Select u from bookmaster u order by Year(b.createDate),Month(b.createDate),day(b.createDate) How do i do this in JPQL? Are there any date parsing functions available? ...
I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "IN" clause. JPQL allows for IN, but it seems to require me to specify every single parameter to IN directly (as in, "in (:in1, :in2, :in3)"). Is th...
I have a JPA 2 Entity named Surgery. It has a member named transfusionUnits that is an Integer. There are two entries in the database. Executing this JPQL statement: Select s.transfusionUnits from Surgery s produces the expected result: 2 3 The following statement produces the expected answer of 5: Select sum(s.transfusionUnits...
Hi can anyone tell me if its possible to write a cross tab query in JPQL? (I'm using eclipse link JPA2) An example of a cross tab query in SQL can found here http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html SELECT dept, COUNT(CASE WHEN gender = 'm' THEN id ELSE NULL END) AS m, COUNT(CASE WHEN gender = 'f' THEN id ...
Say I have 5 tables, tblBlogs tblBlogPosts tblBlogPostComment tblUser tblBlogMember BlogId BlogPostsId BlogPostCommentId UserId BlogMemberId BlogTitle BlogId CommentText FirstName UserId PostTitle BlogPostsId BlogId ...
Hi, If I have a complex where clause which varies only slightly between many queries, in SQL I would create a view based on most of the where clause, then query the view multiple times. Both for performance and maintainability. I don't see an equivalent of that in jpql. Am I or the jpql spec. missing something? Cheers, Phil ...
SELECT x FROM SomeClass WHERE x.dateAtt BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 MONTH) In the above JPQL statement, SomeClass has a memebr dateAttr, which is a java.util.Date and has a @Temporal(javax.persistence.TemporalType.DATE) annotation. I need a way to do the (CURRENT_DATE + 1 MONTH) bit - it is obviously wrong in its curren...
I have an entity (let's say Person) with a set of arbitrary attributes with a known subset of values. I need to search for all of these entities that match all my filter conditions. That is, given a set of Attributes A, I need to find all people that have a set of Attributes that are a superset of A. For example, my table structures loo...
select x from X x where x.a.id = :a_id --> Always 0 objects selected Why does the above JPQL statement not work, but the one below work? select a from A a where a.id = :a_id --> a_obj select x from X x where x.a = :a_obj --> Always correct number of objects selected Neither query throws an exception during execution, but a different n...
Just a quick question: There's the entity (for example User) who is connected with the ManyToMany relationship to the same entity (for example this relation describes "friendship" and it is symmetric). What is the fastest way in terms of execution time to check if User A is a "friend" of user B? The "dumb" way would be to fetch whole L...
Is it possible to perform date arithmetic using JPA/Hibernate? For example, I have an entity with a java.util.Date field indicating when the row was created. Is it possible to perform a query using JPQL and include date arithmetic on that field? For example, can I perform a COUNT(*) of rows and then GROUP BY the month in that field? ...
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(...
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've been looking in the web for examples on the aggregates like count but it seems all of them are using the aggregate alone. SELECT field, count(*) FROM table GROUP BY field Should have something like: field.value1, x1 field.value2, x2 .... I'm looking for a pure JPA answer for this one. If not I guess I can then do further queri...
Hello! I'm trying to do something which is easy as pie in PHP & Co: SELECT COUNT(x) as numItems, AVG(y) as average, ... FROM Z In PHP I would get a simple array like [{ numItems: 0, average: 0 }] which I could use like this: echo "Number of Items: " . $result[0]['numItems']; Usually in JPQL you only query single objects or single co...
I have two tables tab1 { col1 (PK), col2, col3 } tab2 { col1, col2(PK), col3 } I am using Hibernate annotation for joining using "OneToOne" I have the below Hibernate class for tab1 class tab1 { @OneToOne @JoinColumn(name = "col2", referencedColumnName = "col1") private tab2 t2; } i was expecting to run the below sql se...
What methods are there to get JPQL to match similar strings? By similar I mean: Contains: search string is found within the string of the matches entity Case-insensitive Small mispellings: e.g. "arow" matches "arrow" I suspect the first two will be easy, however, I would appreciate help with the last one Thank you ...
I noticed that the children of PersistentUser are not deleted when using the JPQL query below. However, the children are deleted if I perform an entityManager.remove(object). Is this expected? Why doesn't the JPQL query below also perform a cascaded delete? @OneToMany(mappedBy = "persistentUser", cascade = CascadeType.ALL) private Colle...