DISTINCT « Query « JPA Q&A





1. How do you create a Distinct query in HQL    stackoverflow.com

Is there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a ...

2. Using DISTINCT in JPA    stackoverflow.com

What column does DISTINCT work with in JPA and is it possible to change it? Here's an example JPA query using DISTINCT:

select DISTINCT c from Customer c
Which doesn't make a lot of ...

3. NHibernate How to Select distinct objects based on specific property using HQL?    stackoverflow.com

How can HQL be used to select specific objects that meet a certain criteria? We've tried the following to generate a list of top ten subscribed RSS feeds (where SubscriptionCount is a ...

4. Nhibernate: distinct results in second level Collection    stackoverflow.com

I have an object model like this:

   class EntityA
   {
        ...
        IList<EntityB> BList;
 ...

5. Get distinct months from database using HQL    stackoverflow.com

I have a java.util.Date field in an @Entity and would like to get the distinct months from that column. Suppose I only had three rows, with 14/07/2010, 24/11/1975 and 03/11/1975 (European date ...

6. Is it possible to use COUNT with a DISTINCT JPA projection?    stackoverflow.com

I am using a JPA distinct projection to get some data:

select distinct o.f1, o.f2, o.f3 from SomeEntity o where ...
This works fine with setFirstResult and setMaxResults to page data. However I need ...

7. how to create a hibernate distinct query    stackoverflow.com

I have taken a look around the web and can not really find the clear answer on this. I have two tables A and B. B is a child of A. ...

8. Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns    stackoverflow.com

Using Hibernate's Criteria, I wan't to execute the equivalent of:

select distinct uspscity, state from citycomplete where USPSCITY = 'HOUSTON'
I thought doing the following would yield the results I wanted:
ProjectionList projList = ...

9. Distinct + Order By in Hibernate    stackoverflow.com

Related to my last question I have worked with that answer, but now I require to do with a bit modification using hibernate. I need to now add a string to TestData ...





10. hibernate Join with criteria with distinct won't work    stackoverflow.com

when i run i got resulted query where i expect Actual Query Actual Query(to be Produce, Desire Query) distinct [ID]

select distinct rc.* from ratecodeparam rcp , ratecodes rc where rc.travelfrom <= '2011-04-22' and ...

11. Hibernate distinct clause problem    stackoverflow.com

I searched a lot around but cannot find the answer: I have the following SQL query:

select distinct l.id_book from wa2011.tb_lending as l where l.id_user = 1
It's a very simple query but I ...

12. NHibernate HQL distinct and order by problem    stackoverflow.com

I've recently upgraded from NHibernate 1.2 to 3.1 in an old code base. I've fixed most problems but I'm stuck on this one. (I can't change from HQL to another access ...

13. How to do a distinct count in JPA critera API?    stackoverflow.com

I would like to do this but with the criteria API instead:

select count(distinct e) from Event e, IN(e.userAccessPermissions) p where p.principal = :principal and p.permission in (:permissions)
Any ideas?

14. HQL distinct set    stackoverflow.com

I have an entity that contains a set of other entities.

class Foo {
    ....
    private Set<Bar> bars = new HashSet<Bar>();
    ....
}

class ...

15. Hibernate criteria with Distinct and order by    stackoverflow.com

can anyone tell me how to use hibernate criteria with Distinct and order by. i've been looking for a solution but can't seem to find one thanks.

16. Hibernate new keyword with distinct    stackoverflow.com

I need to take hql that is currently :

select distinct a from Asset as a where ...
and change it to
select new com.org.AssetDTO(a.id, a.address, a.status) from Asset as a where ...
My problem ...





18. hibernate Distinct + order by criteria    coderanch.com

19. Problem with StatelessSession and Distinct_root    forum.hibernate.org

Hello, I want to query my results via StatelessSession and the following code does not ensure distinct root entity... Each entity are as much available as rows are available for it. (JOIN FETCH used in query) org.hibernate.Query query= session.statelessSession.createQuery( queryStr ).setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY ) ; businessObjects = query.list().iterator(); Question : How can I set an join fetch query to distinct root entity ...

22. JPA 2.0 criteria.setResultTransformer(Criteria.DISTINCT_ROOT    forum.hibernate.org

Hi, What is the alternative in JPA2.0 for the following line? criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); I have a JPA2.0 Criteria, and i need to distinct my root entities. How can I do that? Code: CriteriaBuilder builder = getEntityManager().getCriteriaBuilder(); CriteriaQuery criteria = builder.createQuery(GloCompanyAddress.class); Root ...

23. Criteria Distinct    forum.hibernate.org

24. Distinct root is not distinct    forum.hibernate.org

Hi there, I'm having issues with getting proper query results using DISTINCT_ROOT_ENTITY. My query is as following: detachedCriteria.add(Restrictions.in(idField, ids)); detachedCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List objects = getHibernateTemplate().findByCriteria(detachedCriteria); Reason for using the Restrictions.in construction is that I want to be able to limit the number of root objects. The query above is part of a larger retrieve() function. The issue here is that the objects ...

25. criteria - how to inject "distinct"into the query ?    forum.hibernate.org

Hey, I have a criteria query I want to speed up. I currently use a "criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);" to perform a distinct on my results, but in the sql query the distinct doesn't appear : Hibernate filters the results after the query to make sure there is no duplicate data... Is there a way to inject a "distinct" into the query with criteria ...

26. count( distinct ... ) in HQL    forum.hibernate.org

27. DISTINCT query not working    forum.hibernate.org

public List getProductsByManu(Integer categoryId) { em.getTransaction().begin(); List prods = em.createNamedQuery("Product.findByManu") .setParameter("categoryId", categoryId).getResultList(); em.getTransaction().commit(); return prods; }

28. why does hibernate hql distinct cause an sql distinct on lef    forum.hibernate.org

session.createQuery("select distinct o from Order o left join fetch o.lineItems").list(); It looks like you are using the SQL DISTINCT keyword here. Of course, this is not SQL, this is HQL. This distinct is just a shortcut for the result transformer, in this case. Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: ...

29. sum(distinct property) in HQL    forum.hibernate.org

30. Distinct Count across multiple columns    forum.hibernate.org

31. distinct in Criteria    forum.hibernate.org

I have a Parent object which has many children. Using HQL I can do a simple query to find parents which have children of a specified age like so: "select parent from Parent as parent join Children as child where child.age=10" I think I just learned today that if a parent has two children age 10 (twins) then I get the ...

32. select distinct throws execption    forum.hibernate.org

Hi there! I'm trying to run the following query: select distinct technician from Technician as technician join technician.workingDays workingDays where workingDays.salesorganisation = :so order by technician.lastName, technician.technicianNo If i call the query.iterate i get the following JDBC exception: net.sf.hibernate.JDBCException: Could not execute query: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. I tried to get ...

33. distinct in Hibernate ?    forum.hibernate.org

net.sf.hibernate.sql.QuerySelect qs = new net.sf.hibernate.sql.QuerySelect(new net.sf.hibernate.dialect.OracleDialect()); qs.setDistinct(true); qs.addSelectColumn("device.deviceName", ""); qs.addSelectColumn("device.deviceDesc", ""); qs.addSelectColumn("user.firstName", ""); String qu = qs.toQueryString() + " Devic as ...

34. Most efficient way to get distinct classes    forum.hibernate.org

Hi, I have a query like "from TimeSheet as timeSheet left join fetch timeSheet.timeEntry " + "where timeSheet.employee.department.id=:id " + "and timeSheet.employee.approver.approverLevel.level < :approval " + "and timeSheet.date = :date " + "order by timeSheet.employee.id asc" But this will return an entry for every time entry, even though I just want to initialize the timeEntry collection in timeSheet. Is there either ...

35. how to find count on distinct object    forum.hibernate.org

Hi, I have a named query like this. SELECT distinct new com.radian.axiom.billing.cuw.bo.dto.BillingProfileSummaryDTO( summaryDTO.billingProfileID,summaryDTO.billingProfileDisplayID, summaryDTO.customerID) FROM com.radian.axiom.billing.cuw.bo.dto.BillingProfileSummaryDTO AS summaryDTO WHERE ((:customerNameFlag = 0) OR (UPPER(summaryDTO.customerName) LIKE UPPER(:customerName))) AND ((:branchNameFlag = 0) OR (UPPER(summaryDTO.coveredBranchName) LIKE UPPER(:branchName))) here i need to find the count of these distinct new records. Let me know the solution if any body knows. I am using oracle 9i and ...

36. HQL Distinct behaviour    forum.hibernate.org

Hibernate descriptor: Code: ...

37. Using distinct and order by    forum.hibernate.org

Newbie Joined: Thu Dec 11, 2003 7:20 pm Posts: 4 I'm using Hibernate 2.1.2 with Oracle 8.1.7. I'm having a issue with using the distinct keyword along with order by. I want to select only distinct Event objects because they can have multiple types and would be listed twice in the query. The query works fine as long as I do ...

38. Distinct in criteria queries    forum.hibernate.org

39. How to implement Orderby and distinct keywords in HQL    forum.hibernate.org

Dear All, Iam using hibernate2.0. My problem is like, when Iam trying to use 'Distinct and Orderby' key words in my query using Hibernate2.0 giving exception. my query as follows: select distinct new QuirkVO(qvo.quirkID,qvo.title,qvo.description) from com.phoenixcolor.pcs.vo.quirk.QuirkVO as qvo , com.phoenixcolor.pcs.vo.quirk.AccountQuirkVO as aqvo, com.phoenixcolor.pcs.vo.quirk.QuirkAccountListVO qalvo where qvo.quirkID = aqvo.quirkID and aqvo.accountID = qalvo.accountID and lower(qalvo.account) like '%a%' order by lower(qvo.title) The Exception ...

40. Distinct problem    forum.hibernate.org

Hi, I have tried to do a distinct query in Hibernate but it still keeps giving me duplicates. My query is SELECT distinct ROLENAME, ROLEID FROM A_TABLE All I want is the ROLENAME to be distinct but it keeps giving me duplicate role names. This is supposed to be a very simple query. I even tried a native SQL query with ...

41. Can I write count(distinct po.compositeId) in hql?    forum.hibernate.org

net.sf.hibernate.QueryException: path expression ends in a composite value: collection1_.id [ select count(distinct collectionPO.id) from gov.ntcri.domain.po.collection.CollectionCreatorPO as creatorPO join creatorPO.collectionPO as collectionPO where creatorPO.creatorName like '%%'and collectionPO.isDeleted=0] at net.sf.hibernate.hql.PathExpressionParser.getWhereColumn(PathExpressionParser.java:375) at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:160) at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87) at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114) at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138) at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293) at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39) at com.dsii.common.hibernate.HibernateUtils.getCount(HibernateUtils.java:297) at gov.ntcri.domain.dao.collection.CollectionDAO.searchByCollectionCreatorName(CollectionDAO.java:301) at gov.ntcri.domain.dao.collection.CollectionDAO.main(CollectionDAO.java:311)

42. Criteria + order distinct    forum.hibernate.org

Hi all, I only want to know something about distinct with criteria. I saw that an implementation of distinct might be developped for Criteria and I want to know if it's released in the last version of Hibernate? Sorry if this question is already answered but I only found old post about this subject. Thanks in advance. kind regards.

43. When distinct will be available in Criteria?    forum.hibernate.org

44. distinct selection problem!    forum.hibernate.org

45. Why DISTINCT doesnt't work    forum.hibernate.org

Full stack trace of any exception that occurs: net.sf.hibernate.QueryException: could not resolve property: currentstatus of: cas.system.app.model.Service [select distinct s.currentstatus from cas.system.app.model.Service s where service_id > 1] at net.sf.hibernate.persister.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38) at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:249) at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:123) at net.sf.hibernate.hql.SelectPathExpressionParser.end(SelectPathExpressionParser.java:12) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:170) at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87) at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114) at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143) at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149) at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138) at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293) at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)

46. Criteria distinct    forum.hibernate.org

47. Select a distinct class in a criteria query?    forum.hibernate.org

48. Select distinct    forum.hibernate.org

49. count distinct a, b?    forum.hibernate.org

50. Problem with Select Distinct query    forum.hibernate.org

Is it possible to do a Select Distinct on a computed column using HQL ? I have the following perfectly valid SQL statement : SELECT DISTINCT zp.zipCode / 100 from ZipCodes zp Where zp.referenceId = 200; I try the same thing in as HQL : SELECT DISTINCT zp.zipCode / 100 from ZipCodes as zp Where zp.referenceId = 200; and I get ...

51. SQLSERVER: ORDER BY items must appear inlist w/ DISTINCT    forum.hibernate.org

hi, hibernate 3.0 sql server 2k windows 2k3s, jdk 1.4.2_05 i'm using criteria to build my queries and it was working great until i did an "in" Restriction on a mapped Set and started getting dupes. From this, I decided to add a distinct Projection. When I did that, I got this query, which looks correct: select distinct this_.content_id as y0_ ...

52. distinct and order by    forum.hibernate.org

Hi, I am using Hibernate 3. I have a complex query that looks like that: select setting from Device device inner join device.supportedSettings setting inner join setting.settingType type where device.manufacturer = 2 and type = 1 order by setting.name" The problem is that I am getting duplicate results. I put the distinct (select distinct setting from). However I get an exception ...

53. distinct and order by Problem    forum.hibernate.org

Hi All, I am facing problem when try to execute the following query and I am using Jboss 4.0. Here is the query.. Code: public static List getProtocolConditions(long protocolID) { String hql = "select distinct ps.conditionType from ConditionsProtocolCondition " + " as ps where ps.protocol=:protocolID order by ps.conditionType"; ...

54. Distinct and Order by Problem    forum.hibernate.org

HI ALL, I am getting the following error when I am trying to execute the distinct and order by query. I am getting "not a SELECTed expression" error, since I am selecting the column which iam using in distint clause, why any suggestions would appreciated?? Hibernate version: Hibernate 3.0 Full stack trace of any exception that occurs: 14:44:27,934 WARN [JDBCExceptionReporter] SQL ...

55. Distinct On in Hibernate    forum.hibernate.org

56. HQL distinct on a collection property    forum.hibernate.org

Hibernate version: 3 Name and version of the database you are using: Oracle 10g I have a class Cat with a collection of Kitten objects. I'm trying to perform a select on Cat with a distinct on Cat.kittens.id. The idea is that my HQL will return only the first Cat object that contains a specific kitten. For instance: Table: CAT id ...

57. distinct by property    forum.hibernate.org

Hello, I have object with some properties: name, id, category, etc... I want to get all objects but I don't want to get two or more objects with same name property. So, first thing that crossed my mind is distinct, but when using distinct I don't really get my objects, but Strings. my hql looks like this: Criteria cr = session.createCriteria("hr.klopa.domain.FoodItem").setProjection(Projections.distinct(Projections.property("name"))); ...

58. Problem when using count with distinct    forum.hibernate.org

If your database or JDBC driver supports this, you could use ScrollableResults. This does not actually iterate over the whole result set when you request the last page and get the row number. I think this will allow you to run the distinct query and get the size of the result set without costing too much time and without mapping the ...

59. Hibernate TX v. DB TX: same or distinct?    forum.hibernate.org

Well, let's focus on non-JTA env, i.e. using JDBCTransactionFactory. Still, the question remains: what is that JDBCTransaction's mapping to underlying DB transaction (as viewed by MySQL server). Here's the reason for my question: -MySQL 5.0 server defaults to autocommit after every SQL statement, and apparently, disabling that complicates (or makes impossible) DB clustering. -Hibernate, on the other hand, defaults to false ...

60. select count(distinct col1, col2) from ...    forum.hibernate.org

Hibernate version: 3.1.2 I'm trying to find a way to generate the following query using Criteria or HQL: SELECT count(distinct col1, col2) FROM ... I get the parsing exception below when I try this in HQL. It appears that only one column is allowed in the distinct. So you are not allowed to specify multiple columns or concatenations of columns. Can ...

61. About Distinct Query??    forum.hibernate.org

Hi all, I'm newbie on Hibenate and my problem is; I have 2 main tables(TerminalTable,AirlineTable) and a sub table(TerminalAirlineBaseTable). I want to use this sub table distincted with the select query as to airline. Which criteria code should I write for resultList? TerminalAirlineBaseTable terminalID AirlineID ---------- --------- 1 2 1 2 1 2 2 4 2 4 2 4 . . ...

62. "select distinct" objects with joined tables    forum.hibernate.org

hi, if I have two tables, Structures and Employes ... ...

63. How do I count distinct columns in Hibernate 3.2?    forum.hibernate.org

Hi there, how do I do the following SQL in Hibernate 3.2 using Criteria and ProjectionList? I keep getting the wrong rowCount, I'm expecting 5 and I get 21. Here is the SQL I want to emulate in Hibernate: SELECT count(distinct order_id, tower_no, pallet_layer_no) from stackedproduct; My Hibernate code looks like this: stackedProductsCriteria.setProjection(Projections.projectionList() .add(Projections.rowCount(), "noOfPallets") .add(Projections.countDistinct("sortedOrder")) .add(Projections.countDistinct("towerNo")) .add(Projections.countDistinct("palletLayerNo"))); List list ...

64. Distinct and Projections    forum.hibernate.org

My apologies. Yes. That is the class I'm attempting to use. Version: 3.0.1 I don't have the API for this version and was trying to use the 3.2.1 version API to solve the issue. Evidently, the Projections.distinct method is newer than 3.0.1. Business is hesitant to upgrade to 3.2 until we can determine the impact on other projects so I'm needing ...

66. Getting Distinct Count of a Projection    forum.hibernate.org

Thanks for pointer, but CountProjection accepts a property, not a Projection. We're looking to get the count of a Projection. I'm worried this isn't possible because it's a "Bad Idea", but I can't imagine why. So I need help making the choice one way or the other - if it can be done, how? If not, why not? Thanks for your ...

67. cannot get distinct results for query    forum.hibernate.org

... ...

68. count (distinct (coalesce (A.x, A.y)))    forum.hibernate.org

70. HQL: how to write "select distinct" query    forum.hibernate.org

Hi, I want to write a "select distinct" query in HQL. my query has a lot of joins, so i have a lot of duplicates in the results - and this cause a performace issue. The idea of the query is as follows (see the query below): I have two entities "A" and "B". "A" has a reference to "B" (Many-to-Many). ...

71. Hql distinct?    forum.hibernate.org

Hi! I have Parent object, which has a collection of Child objects. Child has an attribute attr1. I need those Parent objects, which has a Child that has an attr specified by a parameter. My try: String query = "from Parent r where r in (select a.parent from Child a where a.attr1=:attr1)"; The problem is that this query can return the ...

72. count(distinct nvl(a,b)) problem on HQL    forum.hibernate.org

73. Derby: distinct results in error    forum.hibernate.org

Hibernate: select distinct user0_.ID as ID10_, user0_.EMAIL as EMAIL10_, user0_.FIRSTNAME as FIRSTNAME10_, user0_.LASTNAME as LASTNAME10_, user0_.ROLE as ROLE10_, user0_.USERNAME as USERNAME10_ from XY.XUSER user0_ where user0_.ROLE=? order by user0_.LASTNAME 2008-04-07 15:56:25,937 WARN [http-8080-2] org.hibernate.util.JDBCExceptionReporter: SQL Error: -1, SQLState: 42879 2008-04-07 15:56:25,937 ERROR [http-8080-2] org.hibernate.util.JDBCExceptionReporter: The ORDER BY clause may not contain column 'LASTNAME', since the query specifies DISTINCT and ...

74. how to make a distinct    forum.hibernate.org

Hi everybody. I'm a new user of hibernate and jsp and i have this problem: I Have this method in my DAOBean class: public Set listScheda() throws Exception { try { Set ret = new HashSet(); ret.addAll(manager.createQuery("from Scheda scheda").getResultList()); return ret; } catch (Exception e) { log.error("listScheda() - Failed to find scheda: "+e); throw e; } } I want a query ...

75. sum( distinct ...) in hql    forum.hibernate.org

76. Having count(distinct a,b,c,...    forum.hibernate.org

Hello. I want to realize a statement in HQL or Criteria which works in SQL. The statement returns all mediator(id)s which have more than 250 orders. A order is a combination of the order date (co.created) and the id of a indent or a contract. I don't know the exactly SQL code now but this pseudo code should explain what i ...

77. distinct selection    forum.hibernate.org

Hi, I have two tables and I'd like to query the last 20 comments, this works: session.createQuery("from Comments order by cm_date desc").setMaxResults(20).list() I'd like to make sure that there is no Comments from the same User in the last 20, any idea? Thanks, Angelo public class Comments { private Long id; private User user; private Timestamp cm_date; } public class User ...

78. select distinct on multiple properties not working in HQL    forum.hibernate.org

Ok - I gave up on the HQL since the distinct element in it is only partially functional. I can get it to kind of work using a raw SQL query that looks like this: Code: session.createSQLQuery( " select count(distinct Month(i.REPORTED_DATE), i.PERSON) as count, " + " Month(i.REPORTED_DATE) as theMonth, " + ...

79. [HQL]count and distinct on multiple columns    forum.hibernate.org

Hi, on SQL i can do sth like SELECT COUNT (*) FROM (SELECT DISTINCT column1, column2 FROM table1) to get the number of results for a distinct query. On HQL it is not allowed to use subqueries in the from-clause. I there any way i can "decorate" my query with a count or use distinct and count at the same time ...

80. Hibernate query using DISTINCT    forum.hibernate.org

Is it possible to find a unique records based on a single filed. eg: select distinct on (md.STRING01) md.STRING01, md.DATE01 , md.LONG01, md.LONG02, md.LONG03,md.LONG04 from app.MENU md Can you pls suggest an equivalent hibernate query? When tried distinct it checks distinct on all fileds. I want to check only one filed and return the latest one when duplictes occurs.