API « Query « JPA Q&A

1. Hibernate Criteria API - HAVING clause work arounds    stackoverflow.com

I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum ...

2. Hibernate Criteria Query API    stackoverflow.com

I assume that the following is a no no(?)

> public Criteria createCritera(Class<?> persistentClass) { 
>     ...//creation of session object etc.
>     session.beginTransaction();
>   ...

3. Hibernate: ORDER BY using Criteria API    stackoverflow.com

When I write a HQL query

Query q = session.createQuery("SELECT cat from Cat as cat ORDER BY cat.mother.kind.value");
return q.list();
Everything is fine. However, when I write a Criteria
Criteria c = session.createCriteria(Cat.class);
return c.list();
I get ...

4. Can I write this query using the criteria API or am I stuck with HQL?    stackoverflow.com

I have the following query which I would like to write using the criteria api of NH.

select status, count(1) from (select distinct Status, post_id from post_statistics) tbl group by status
each post_id ...

5. Hibernate Criteria API and Scalar Subqueries    stackoverflow.com

I want to translate a HQL query to Criteria API but I don't know if it's possible to write the same thing with Criteria. The HQL looks like this:

select distinct new DataTransferObject(property1, ...

6. Select columns from join table only without requiring a join    stackoverflow.com

Given these tables:

create table Orders (
   primary key (Id)

create table Items (
   primary ...

7. Creating queries using Criteria API (JPA 2.0)    stackoverflow.com

I'm trying to create a query with the Criteria API from JPA 2.0, but I can't make it work. The problem is with the "between" conditional method. I read some ...

8. Hibernate Criteria API equivalent to HQL select clause?    stackoverflow.com

I'd like to have a combined query for two persistent classes. In HQL this could be achieved by the select clause,

select new Family(mother, mate, offspr)
    from DomesticCat as mother

9. Hibernate ResultTransformer with JPA API    stackoverflow.com

Has anyone figured out a smart way to do query result transformation through a similar mechanism like specifying a ResultTransformer in Hibernate? All I can think of is transforming each ...

10. Can the same CriteriaBuilder (JPA 2) instance be used to create multiple queries?    stackoverflow.com

This seems like a pretty simple question, but I have not managed to find a definitive answer yet. I have a DAO class, which is naturally querying the database by using ...

11. NHibernate Query    stackoverflow.com

Is it possible to get NHibernate to generate a query similar to the following with HQL or Criteria API?

from (

12. NHibernate: help translating an hql query to use criteria api instead    stackoverflow.com

I have the following hql query which I'd like to switch over to the criteria API

select a.Id as Id, a.Name as Name, a.ActiveStatus as ActiveStatus, 
dbo.GetActivityStartDate(a.Id) as StartDate, 
dbo.GetActivityEndDate(a.Id) as EndDate, ...

13. JPA Criteria API - How to add JOIN clause (as general sentence as possible)    stackoverflow.com

I am trying to construct queries dynamically, and my next target is add JOIN clauses (I don't know how can I use the API). By now, for example, this code work for ...

14. Selecting specific columns in jpa 2 Criteria API?    stackoverflow.com

Is there a way to select specific column using the JPA 2 Criteria API? The following is the target SQL Statement:

    SELECT column1, column2 FROM MyTableThatHasMultipleColumns
With Hibernate's Criteria API ...

15. Query many-to-many without selecting all objects using Criteria API    stackoverflow.com

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 ...

16. JPA/Criteria API - Like & equal problem    stackoverflow.com

I'm trying to use Criteria API in my new project:

public List<Employee> findEmps(String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);

17. JPA criteria query, order on class    stackoverflow.com

Is there a way with JPA criteria queries to order on class? Imagine the following domain objects:

abstract class Hobby { ... }
class Coding extends Hobby { ... }
class Gaming extends Hobby ...

18. Paginating a JPA 2 criteria query    stackoverflow.com

Is it possible to paginate a JPA 2 criteria query, as you can with in Hibernate with setFirstResult and setMaxResults? If not, are there any workarounds?

19. How can I express this query using Hibernate's HQL and Criteria api?    stackoverflow.com



Here are the classes


Integer id;
String name;


Integer id;
Integer countryId;
String name;


Integer id;
Integer stateId;
String name;
I'm trying to get all the ...

20. hibernate how to show the criteria queries    stackoverflow.com

I think this is a simple question although I do not know how to solve it. In a spring/Hibernate application I need to show the query that a criteria execute. I know that ...

21. Hibernate criteria API, working with multiple tables in one query    stackoverflow.com

At work we have a project to build a dynamic reporting tool against two specific tables in the database. We want to normalize the data in the two tables so via ...

22. JPA 2 No explicit selection and an implicit one cold not be determined    stackoverflow.com

I am trying to fetch all users for a folder where the user was created after a certain date. the relationship between the user and the folder lives in a seperate ...

23. Select ... in equivalent in JPA2 criteria    stackoverflow.com

Is there any way to perform a query like the following using JPA2 criteria APIs?

select a from b where a in (1, 2, 3, 4)
There's a way to do that using ...

24. Joining with specific ON-clause with JPA 2 Criteria API    stackoverflow.com

Is it somehow possible to do one of the following queries with JPA 2 Criteria API? Basicly, there are a number of Templates which are used by Profiles. Each User in ...

25. Hibernate Criteria Api Subqueries    stackoverflow.com

I am currently working on a project to transfer some legacy jdbc select statements over to using Hibernate and it's criteria api. The Two relevant table columns and the SQL query looks ...

26. Do you like the Criteria api of JPA 2.0? Do you use it with framework?    stackoverflow.com

I'm used to work with Criteria API in Hibernate, and just watched how the Criteria in JPA 2.0 work. What i like most in the Criteria of Hibernate is the ease we ...

27. Select MaxElements with Nhibernate using QueryOver    stackoverflow.com

Is there a way to select the Elements of a Composite-Element Nhibernate association withtout using HQL? Using HQL there is the MaxElements() function that will get the Elements, is there anything like ...

28. JPA 2.0 Criteria API query by subquery    stackoverflow.com

The table of data is like

   pid (pk)
I would like to do a query to extract the latest version of ...

29. create typesafe query for one to many reltionship using JPA 2.0 typesafe query api and hibernate    stackoverflow.com

given the the following entities:

Class A
  long id;
  B b;

Class B
I would like to query for all B objects for which A.id= 1 it looks like ...

30. JPA Named Queries vs Criteria API?    stackoverflow.com

Is there a heuristic/best practice/ruleset for a decision between the Criteria API and NamedQuery? My thoughts so far :
Named queries are generally more readable. Criteria queries are more flexible.
Both are ...

31. hibernate subqueries using criteria api    stackoverflow.com

Can you help me translating this query:

--    **tableA**
--    tableA_id
--    names
--    tableA (1:n) tableB
--    **tableB**
--   ...

32. How to calculate age with JPA 2 criteria queries?    stackoverflow.com

I'm quite amazed that I could not find anything on this topic:

How do I calculate the age of a person at a given date, based on a date ...

33. JPA Critiera query with many joins using three OR predicates    stackoverflow.com

I'm writing a query using JPA Criteria API where I want to get all travels matching any of these predicates:

  • Get all travels (MasterTravels) that I own (owner = user)
  • Get all travels ...

34. How migrate jqpl query to Criteria API    stackoverflow.com

Please have this query in jpql i want migrate to criteria. How do I you that? The Query:

SELECT c FROM Cartera c,Cliente cli WHERE c.aseId = :aseId and cli.aseId=c.aseId

35. How to: Hibernate 3.xx Criteria Query    stackoverflow.com

Here is the scenario I have: 5 tables. Author, Publisher, Region, Medium & Title Title has foreign keys to all the other tables. I have a list of Title objects and want to create ...

36. Criteria API - Foreign Query    forum.hibernate.org

37. How to write subquery in IN clause through criterian api    forum.hibernate.org

HQL Query: from Customer as customer where customer.username like '%"+customer+"%' and " + " customer.statusMap="+StatusMapInterface.ACTIVE+" and " + " customer.customerId in (select distinct cRole.customer from CustomerRole as cRole where cRole.role!="+RoleMap.SITEADMIN+")" + " order by registeredDate asc I need Criterian query. Please help me.. In the above Cusomer is the Parent class. Cusomer_Rile is the child class. customer_id in Customer class is ...

38. Subqueries in the select clause using Criteria API    forum.hibernate.org

Hi, I want to translate a HQL query to Criteria API but I don't know if it's possible to write the same thing with Criteria. The HQL looks like this: select distinct new DataTransferObject(property1, property2, ..., (select NVL(property3, null) from Table1 where property3 in elements(...) and ... ), property4, ..., (select .....), ...) from Table2 as table2 left join table2.property5 as ...

39. multi join query using hibernate criteria api    forum.hibernate.org

I have a situation here where I need to create a multi join query using hibernate criteria api... the sql format of the query is: SELECT p.name, pp.name FROM team t,player p,position pp where p.teamId = t.teamId and t.leagueId = 1 and p.positionId = pp.positionId in the above query t.leagueId is a variable which I will be passing while calling this ...

40. simple subquery in criteria api nullpointer    forum.hibernate.org

I am getting this nullpointer When I add a simple subquery. Am I doing something wrong or is this a bug? Code: java.lang.NullPointerException at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:341) at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:136) at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:71) at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:357) at org.hibernate.loader.criteria.CriteriaJoinWalker.(CriteriaJoinWalker.java:102) at org.hibernate.loader.criteria.CriteriaJoinWalker.(CriteriaJoinWalker.java:82) at org.hibernate.loader.criteria.CriteriaLoader.(CriteriaLoader.java:91) ...

42. Performing a select from a subquery using Criteria API    forum.hibernate.org

Hello, I know this topic has been already discussed couple of time, but I could not find a definite answer even after spending an hour reviewing existing posts. Is there any way to write a Criteria that selects from another DetachedCriteria? So the result SQL would be something like Select * FROM (Select ... I have already spent couple of hours ...

43. Query with Criteria API    forum.hibernate.org

Hi, I am working with the Criteria API. The most queries have not been any problem, but for one query, I do not find a solution. There are two tables: Sight and Attendance. Between the two tables, there is a n:m relation. Now I want to get all sights that offer certain attendances. If there is an other attendance to look ...

44. Subtype query using Criteria API    forum.hibernate.org

In topic 925219 I asked about a query on subtype attributes using HQL and got a great answer. This is with a base class of Event with subclasses AlarmEvent and ManageEvent. In HQL the query is something like: select event from Event event, AlarmEvent alarm, ManageEvent manage where (event = alarm and alarm.acknowledged = false) or (event = manage and manage.confirmed ...

45. Criteria API and count(*)    forum.hibernate.org

46. select close with criteria api    forum.hibernate.org

47. Can I do this HQL query with the Criteria API?    forum.hibernate.org

Hi, I apologize for the repost, but I thought I'd try to simplify my question. I'm trying to use the criteria API to perform the quivalent of the following HQL query: Code: Query query = session.createQuery("select l from Listing l join l.areas area where area in (:areas)"); query.setParameterList("areas", areas, Hibernate.entity(Area.class)); return query.list(); There is a unidirectional many-to-many relationship between Listing and ...

48. Give your thoughts on rowCount()/count() in Criteria API    forum.hibernate.org

Hi everyone! I would like to set up a vote on the implementation of count()/rowCount() on the Criteria API. Lots of people in Hibernate community got frustrated with implementation (?) of that functionality in Hibernate 3.x and lack of it in "official" release of Hibernate 2.x. Patch for H2.x was very helpful, but it was not exactly what it needs to ...

49. Criteria API have a "having clause"    forum.hibernate.org

crit.setProjection( Projections.projectionList() .add( Projections.groupProperty("id")) .add( Projections.groupProperty("uri")) .add( Projections.groupProperty("node")) ...

50. Order By using Criteria API    forum.hibernate.org

its a bit more complicated than that. its my fault for showing to simplified code. What I want to do is order by the results by the product of values from 2 or more tables other than the start table. The obvious thing is to have a derived field but Im really put off by how often the SQL within the ...

51. Using Criteria API to replicate a subquery    forum.hibernate.org

Hello, I am using Hibernate 3.0 with an Oracle 10i database. For performance reasons I have to move what is currenlty being done via an HQL query to use the Criteria interface. This is because I've found that the createQuery interface ignores the max_fetch_depth parameter, and therefore after I retreive my list of objects, as I am iterating through them and ...

52. equivalent for select in Criteria API    forum.hibernate.org

Hi, for some reasons my settings in my hibernate-mappings is default-lazy="false" I prefer to use the criteria API. Therefore when I load objects Hibernate reads all associations automatically. But when I need a list I would prefer to have control what is read from the database. So my question is: Is an equivalent to such a construct like "select new Xyz(id,descr,....) ...

53. Criteria API: Add a count subquery    forum.hibernate.org

Hi all i have the following SQL Query: SELECT DISTINCT TKAG.ILAUFNUMMER, TKAG.SKURSNR, TKAG.SREGION, TKAG.DKURSDATUM, (SELECT count(*) FROM TKURSAGTEIL TKTE WHERE TKTE.FK_TKAG_LAUFNR = TKAG.ILAUFNUMMER) AS TEILE FROM TKURSANGEBOT TKAG WHERE ........ I was able to add Restrictions in the WHERE clause. This works fine. But now i would like to add the count subselect. How do i have to implement this with ...

54. Having clause with Criteria API possible?    forum.hibernate.org

Hi all, In our client-server application the user can build up a report by selecting attributes (normal properties i.e id, name,....) and metrics (count, sum,...), by giving the conditions. Of course the conditions for the metrics would become HAVING in the SQL, HQL. As in this use case we build up the queries on the fly, it would be fine to ...

55. Criteria Query Api Problem!!!!!    forum.hibernate.org

hi to every one! i'm using HQL right now for my queries but I have tried to use Criteria Query Api. I just like to learn a bit from it... But here is the problem. I have a "hibernate.cfg.xml" file that has these proporties: hibernate.connection.driver_class hibernate.connection.url hibernate.connection.username hibernate.connection.password hibernate.connection.pool_size show_sql dialect after these proporties, I have a mapping resource as:

56. Criteria API: Selecting columns of multiple tables    forum.hibernate.org

Hi, I have two tables (A and B). Table A has a many-to-one mapping to table B. I would actually like to select only certain columns from table A and table B and store them in an object. First off, is it even possible using the Criteria API? Else I guess I need to use HQL? Either way how would I ...

57. Criteria API interlinked subqueries    forum.hibernate.org

Hello all, I have a problem with interlinked Subqueries.propertyIn. I am using Spring framework, hibernate 3, and MySql 5.0. I have 4 tables : candidate : id_candidate, lastName, firstName, ... Example : 1, Smith, John candidateLanguage : id_language, id_level, id_candidate Example : English, Native, 1 Language, id_language, Example : English Level, id_level, Example : Native I would like to retrieve every ...

58. Many-to-many querying using Criteria API    forum.hibernate.org

session.createCriteria(Document.class) .createAlias("authors", "a") .createAlias("editors", "e") .createAlias("publishers", "p") .add(Restrictions.disjunction() .add(Restrictions.eq("a.id" idIAmLookingFor)) .add(Restrictions.eq("e.id" idIAmLookingFor)) ...

59. how to optimize select of api criteria    forum.hibernate.org

I would like to optimize the generated select of api criteria With 2 tables Criteria crit = this.getSession().createCriteria(T1.class); crit.createCriteria(T2.class); crit.add(Restrictions.eq(T2NAME,"TOTO")); Api generate sql like: SELECT T1.*,T2.* FROM T1 INNER JOIN T2 ON T1.C1=T2.C2 where T2.T2NAME = 'TOTO'; But how to specify to criteria to recover only one or more property into T1 objet SELECT T1.C1, T1.C4 FROM T1 INNER JOIN T2 ...

60. Inner select using Criteria API    forum.hibernate.org


61. Problems with Criteria API (subquery)    forum.hibernate.org

Hi, I cannot find out my problem: I have quite complicated query (lot of outer joins) and I want to filter the result set with a subquery. The problem is that the code does not work (returns zero count) and also I do not see any exceptions. so first the code: Code: //create main criteria object on a class with alias ...

62. Query Parser API    forum.hibernate.org

Hi, We have a legacy application which works with Postgres database. It has sql queries which use postgres specific functions like date_part and date_trunc in the query select clause, where clause etc Now the target is to port this application to Oracle and potentially SQL server. This application uses JDBC and does not use Hibernate. Now what we plan to do ...

63. Exclude a Column from the select claues using Criteria API    forum.hibernate.org

Hi All, Hibernate version: 3.2.1 I am trying to fetch records using Hibernate Criteria API. For example: Criteria criteria = getSession().createCriteria(Definition.class) Where Definition pojo object has multiple associations and the sql that is generated has more than 200 columns. From a performance point of view I need to exclude a single disclosure column of type blob from the select statement. Please ...