hql « SQL « JPA Q&A

1. Hibernate SQL to HQL    stackoverflow.com

Im running into some problems when trying to convert a SQL query into HQL (or Criteria/Restriction). I have the following SQL query:

Select count(n), CreatedDate from (
  Select count(serverId) as n, ...

2. hibernate many-to-many    stackoverflow.com

i have three tables and 2 JPA model classes:

id [PK]    - Integer
code       - String
unitGroups - List<UnitGroup>

id [PK]    - Integer
ugKey ...

3. launch script hql or sql on hibernate start    stackoverflow.com

I remember a function that permits to execute hql or sql script during hibernate startup. Someone remember command syntax? The sql script should be locate in meta-inf with a specific name... ...

4. HQL vs. SQL / Hibernate netbeans HQL editor    stackoverflow.com

HI there I am teaching my self hibernate, and am quite confused of why i can not just write simple SQL quiries. I find it rather more confusing to use than plain SQl ...

5. SQL to HQL with subquerys    stackoverflow.com

I've read about the lack of support for subquerys with HQL, but anyway, anyone could tell me if this SQL query could be implemented with HQL? I've already made some testing but ...

6. Using IF construction in Hibernate    stackoverflow.com

I'm using the construction "if" in HSQL:

String q = "SELECT id, name, " +
                "IF (ABS(name) ...

7. netbeans hql editor wrong translation to sql    stackoverflow.com

i'm trying to access a mysql database from a java webservice i'm using the netbeans IDE and following this tutorial: http://netbeans.org/kb/docs/web/hibernate-webapp.html now when i try to test the hql query: from calls i ...

8. SQL Replace in HQL    stackoverflow.com

Any idea why this snippet of HQL would be failing.. REPLACE function requires three arguments is the error. Is there any other way to perform a replace in HQL>? "WHERE :url LIKE ...

9. How can someone give the HQL / SQL a better OO approach?    stackoverflow.com

I work with Hibernate and particulary like the Criteria api for the way to compose with request predicates, but in some cases it's not possible to use it so i have ...

10. Why this HQL is not valid?    stackoverflow.com

The error is: Error: org.hibernate.QueryException: can only generate ids as part of bulk insert with either sequence or post-insert style generators HQL:

insert into CategoryProduct (category, product) 
select c, p from Category c, Product ...

11. specify Index hint in HQL    stackoverflow.com

I need to specify a Index hint for my HQL

method is of no use as it is appending the hint before the select clause, though ...

12. HQL: using elements() on objects    stackoverflow.com

say I have a database with two tables: classes and students. Each table contains metadata about the relevant topic. For example, classes contains fields 'classid', 'name' and 'room_number' and ...

13. Many to Many and HQL    stackoverflow.com

I got class:

public class Restaurant {
    private int id;
    private String name;
    private String ...

14. sql into hql - hibernate    stackoverflow.com

I have these twos sql scripts, and i'm getting some problems to perform the translation in hql, to be used in a Query from JPA:

SELECT f.idprovider, pe.name from provider f
inner join ...

15. Hibernate throws "QueryException" while compiling, although SQL is valid    stackoverflow.com

I'm facing a kind of strange problem and don't know in which direction to go to solve it ;-) I'm currently developing a web application for storing employees using Hibernate. Inserting, deleting, ...

16. Any innate limitations to HQL vs. SQL?    coderanch.com

Hi everybody. I'm just starting to take a look at Hibernate and it's query language, and wondered if there are any innate limitations to the object-oriented queries versus standard SQL. I've seen some simpler ORMs lacking various capabilities such as left joins (I think it was a python one), but I haven't come across anything that HQL can't do. Performance isn't ...

17. difference between hql and sql    coderanch.com

18. From SQL to HQL    coderanch.com

19. SQL to HQL    coderanch.com

Hi, i have a problem transforming the following SQL-Statement into HQL: SELECT * FROM table_1 AS A INNER JOIN (SELECT B.fkC, max(B.date) maxDate FROM table_1 AS B GROUP BY B.fkC) AS tmp ON A.fkAlarmPointId = 100 AND A.dateCreated = tmp.maxDate I know how a "normal" INNER JOIN works, but not with a subquery. Thank you, Ray

20. HQL to SQL with chinese. Hibernate 3.3.2 GA    forum.hibernate.org

Hello. We are using Hibernate to get objects from Oracle SQL DB. We are using UTF-8 Database encoding with JAVA. We have HQL query like this: DEBUG {934335468@} org.hibernate.hql.ast.QueryTranslatorImpl - HQL: select changeRequest from ChangeRequestBrief as changeRequest where upper(changeRequest.evidenceNumber) like '%'ESCAPE'\' But this query get's translated to: DEBUG {934335468@} org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select changerequ0_.CHNG_REQ_ID as CHNG1_, changerequ0_.EVIDENCE_NR as EVIDENCE2_42_, changerequ0_.RECEIVED_DATE as ...

21. Hibernate newbe needs some help - SQL to HQL    forum.hibernate.org

22. SQL to HQL with subquerys    forum.hibernate.org

I've read about the lack of support for subquerys with HQL, but anyway, anyone could tell me if this SQL query could be implemented with HQL? I've already made some testing but with no success...don't know if I'm in the right path... SQL (working) Code: SELECT foo.id as fooId, foo.name AS fooName, ...

23. HQL to SQl conversion    forum.hibernate.org

24. SQL to HQL help please!!    forum.hibernate.org

Hi I have a SQL query I am trying to translate to HQL, and having difficulty. It's not too complex, but has a couple of outer joins to the same table, with correlated subqueries: Code: select document.Id, document.Name, document.FileName, md_author.Value as Author, md_publishDate.Value as PublishDate from ...

25. same HQL gives different SQL + results in Hibernate 3.3/3.6    forum.hibernate.org

Hi, We have just upgraded from Hibernate 3.3 to Hibernate 3.6 (actually as part of upgrading JBoss AS but I don't think this matters in this case), and at least one of our HQL queries are now generating different SQL - which is unfortunately returning different results. I've not had much luck trying to figure out what the problem is, or ...

26. SQL to HQL help    forum.hibernate.org

Hi, I have a fairly complex join query that I wrote in native SQL (using mysql) that I am trying to rewrite in standard HQL to be db agnostic but am having a lot of difficulties. For starters, HQL does not seem to support limit statements other than in query.criteria settings, but I have to join against a sub-select with a ...

27. HQL and SQL results different?    forum.hibernate.org

Hi, I have the following query: Department and Student have bidirectional many-to-many association. Query query = session.createQuery("SELECT d.id, d.name, s.name, elements(d.students) "+ " FROM Department as d, Student as s "+ " WHERE d.id = ? and s.id = ?"); query.setString(1, "01"); query.setString(2, "00001"); List summaryList = query.list(); I got "java.lang.NullPointerException" for the above query. But when I use the Hibernate ...

28. SQL to HQL    forum.hibernate.org

Hi! I do have some problems understanding HQL... This is the SQL statement: SELECT urls.url, urls.id, html.html FROM html INNER JOIN urls ON urls.id = html.urlid WHERE url LIKE 'http://www.domain1.com/%' OR url LIKE 'http://www.domain2.de/%' OR url LIKE ...... And this is my idea of a corresponding HQL statement (there no ON for JOINs?): SELECT urls.url, urls.id, html.html FROM f4t.hibernate.Html INNER JOIN ...

29. SQL != HQL?    forum.hibernate.org

SELECT urls.url, urls.id FROM f4t.hibernate.Url as urls WHERE urls.id = html.urlid AND ( urls.url LIKE 'http://foo/%'" OR urls.url LIKE 'http://bar/%'" ) return 3768 rows while SELECT urls.url, urls.id FROM urls WHERE urls.id = html.urlid AND ( urls.url LIKE 'http://foo/%'" OR urls.url LIKE 'http://bar/%'" ) does return only 3656 rows. Why is that? And which result is correct actually?

30. HQL versus SQL    forum.hibernate.org

31. SQL to HQL translation help    forum.hibernate.org

32. Can't see any difference between HQL and SQL, help?    forum.hibernate.org

I couldn't apply the advantge of HQL well, since I can't tell the main difference between HQL and SQL. In Hibernate, I also can use SQL to query, right? Like this, Query query = session.createQuery("select from User as user"); Query query = session.createQuery("from User as user"+ " where user.uname like '%A' " ); But what's the good thing of HQL, any ...

33. How to write HQL with these SQL    forum.hibernate.org

String query = "select {profile_.*}, count(message.userID) as i from Forum forum_ inner join ForumMessage message on message.forumID=forum_.forumID " + " inner join profile {profile_} on message.userID={profile_}.id" + " group by message.userID order by i desc "; I use the statement : Query hibernateQuery = session.createSQLQuery(query, "profile_", Profile.class); to execute the query, cause it seems I can not give an alias to ...

34. Help Translating this SQL to HQL    forum.hibernate.org

35. Translating HQL to SQL    forum.hibernate.org

I need to have the string of SQL that Hibernate is going to execute for a SELECT, starting from a HQL string. I mean a method like session.find(HQLQuery) which returns the SQL query. This because I need to perform a INSERT INTO table_name SELECT ... I I whould like to use Hibernate for the last part of the statement Any help ...

36. SQL to HQL    forum.hibernate.org

can someone help to convert this query to Hql select involog.id,exelog.id,exelog..state from org.jbpm.model.log.impl.InvocationLogImpl involog, org.jbpm.model.log.impl.ExecutionLogImpl exelog where exelog.invocationLog=involog.id and involog.serviceMethod='endOfState' and involog.actorId="testuser" mappings are as below : these files are from jbpm : jbpm_exelog :

38. Translate SQL to HQL    forum.hibernate.org

Hi, I have printed and read the hibernate doc but it is very poor in explanations about HQL (chapter 15). So I managed to do simple queries but now I have one more complex that i'm not able to translate into HQL. Here is the SQL query that I want into HQL : Code: select person, empServ, serv, emp from Person ...

39. Is the following SQL possible in HQL?    forum.hibernate.org

SELECT TL.subDocIndexId, T1.data AS modelic, T11.data AS sdc, T2.data AS chapnum, T3.data AS section, T4.data AS subsect, T5.data AS subject, T6.data AS discode, Tx7.data AS discodev, T8.data AS incode, T9.data AS incodev, T10.data AS itemloc FROM TagList TL, TagList TL2, Text T1, Text T2, Text T3, Text T4, Text T5, Text T6, Text Tx7, Text T8, Text T9, Text T10 , ...

40. SQL TO HQL Translation    forum.hibernate.org

41. Get SQL translated from HQL    forum.hibernate.org

Hi, I know and use this. I wanted to save the generated SQL into database during session, without executing it. I develop application that runs upon AS/400 DB2 (and many others). And there is batch processing of queries during night - the queries are very complex upon large bank data. But by another client I want to execute it satement directly ...

42. hql vs. sql    forum.hibernate.org

I'm curious about the advantages/disavantages of using sql vs. hql with hibernate 3.1. With SQL, we save the learning curve, but what are losing out on that hql does? With hql, I know I can concentrate on my object, but am I losing anything with performance? I'm not looking to be talked into either one, just looking to make an objective ...

43. ORM using HQL (or SQL)    forum.hibernate.org

Hi imchi, Sorry for ambiguity. I have no problem to do query with HQL, in fact, I can even write Java code in the class to do the same thing, but I want to have cleaner (clear) code, so I want to have mapping in hbm file, if Hibernate is capable to do this. Thanks again with your help. Eric

44. Classpath weirdness with Hibernate and HQL/SQL in JUnit    forum.hibernate.org

I am well aware that HibernateUtility is my class, thank you very much for pointing out the obvious. However, I think there is nothing wrong with HibernateUtility, as is proven (I think) by the fact that when the query is replaced by criteria logic, while still using HibernateUtility (again, it's virtually a one-for-one knockoff of Hibernate's CaveatEmptor HibernateUtil sample), the problem ...

46. SQL to HQL    forum.hibernate.org


47. need a help in sql( to hql)..    forum.hibernate.org


48. Inappropriate SQl genrated from HQL    forum.hibernate.org

Need help with Hibernate? Read this first: http://www.hibernate.org/ForumMailingli ... AskForHelp Hibernate version:3.0 I have the following problem: have three classes with many-to-many association with each other.: [b]Mapping documents: MessengerRNA.hbm Protein.hbm

49. How to rewrite HQL to match more efficient SQL?    forum.hibernate.org


50. SQL to HQL    forum.hibernate.org

Hi people, I've started using Hibernate 3 days ago :) what I want is to get HQL of this MySQL code: select result.id as candidates from (select count(0) mycount, GraduateQualification.Grad_id as id from GraduateQualification where GraduateQualification.Qual_id in (1,2) group by GraduateQualification.Grad_id) as result where result.mycount = 2 GraduateQualification is a mapping class from Hibernate help me, plz Regards, Bassam

51. Howto use case when then else end? Translate SQL to HQL    forum.hibernate.org

Hi, I need some help how to translate this sql to hql. "select s.ID, case when (s.UPDATED > s.CREATED) then s.UPDATED else s.CREATED end LASTUPDATED from STUFF s order by LASTUPDATED" According to the manual the "case" construction are supported, but I cant find any example or more info about it. Can anyone help me? :) Best regards! /Fredde

52. SQL inside a HQL    forum.hibernate.org

53. HQL TO SQL    forum.hibernate.org

54. Mixing HQL with SQL    forum.hibernate.org

55. reverse engineering SQL into HQL/JPAQL    forum.hibernate.org

56. FROM SQL TO HQL    forum.hibernate.org

hI every body i have a "little " problem with a hql code . i want to "traslate" a sql code to hql Code: SELECT ID ID_JOB, SEQ ID, (SELECT DISTINCT VALUE JOB_GROUP FROM DC_LOGGING WHERE ID = AA.ID AND SEQ = AA.SEQ AND POS = AA.POS AND NAME = 'JOB_GROUP') JOB_GROUP, ...

57. Need help expressing this SQL in HQL    forum.hibernate.org

58. SQL to HQL Converter Utility    forum.hibernate.org

59. From-element disappears in HQL-SQL conversion    forum.hibernate.org

View unanswered posts | View active topics Board index Hibernate & Java Persistence Hibernate Users All times are UTC - 5 hours [ DST ] From-element disappears in HQL-SQL conversion Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message bennysce Post subject: From-element disappears ...

60. HQL Vs SQL in Hibernate    forum.hibernate.org

I don't see any reason why you would want to make SQL standard practice. If you use HQL, you can still fall back to SQL in those rare cases where you can't solve your problem with HQL. Apart from that I simply prefer HQL over SQL cause of it's object-oriented syntax. And you don't need to specify how you want to ...

61. Help with SQL to HQL    forum.hibernate.org

Hi, I'm just learning the intricacies of HQL, and I've stumbled upon something I need to do that I can't figure out how to implement in HQL. I need to order a list of object by a sub - sub - property - meaning that it is a property two layers down in a set of associations. The SQL looks like ...

62. How to write the sql or hql for...?    forum.hibernate.org

Is this a query that is going to be used a lot? The SQL queries that I have seen online for this sort of thing tend to involve casting and calling functions on your data - something that is likely to lead to poor performance in your application if the query is used extensively. -- Stephen Souness

63. SQL to HQL conversion    forum.hibernate.org

64. SQL to HQL conversion    forum.hibernate.org

Hi, I am struggling to convert the following SQL to HQL. Please help me. select lps.plp_log_pos_type logical_position ,si.iv_manuf_model_version model_version ,si.iv_sysid iv_sysid ,lps.plp_card_mandatory mandatory ,lps.display_order display_order ,si.sysid record_id ,si.iv_sysid shelf_iv_sysid ,lps.plp_log_pos_type shelf_position ,ss.scm_sysid scm_sysid from logical_positions lps ,shelf_instances si ,scheme_shelves ss where lps.sne_id = and lps.shelf_log_pos = 'Y' and si.lps_sne_id(+) = lps.sne_id and si.lps_plp_log_pos_type(+) = lps.plp_log_pos_type and ss.shelf_lps_sne_id(+) = si.lps_sne_id and ...

65. HQL to SQL Transformation    forum.hibernate.org

Hi , I want to know the implementation of "HQL to SQL transformation" in Hibernate. Especially, the parsing of HQL and Converting it into AST so that i can design the AST for any of the HQL Query manually . I tried to go through the API Docs but due to lack of proper documentation i could not get anything. I ...

66. Help in using SQL's "Exists" operator in HQL    forum.hibernate.org

Hi, Iam trying to write following SQL query with exists operator in HQL. Select * from partnumber pn where exists (Select 1 from analystProduct ap where ap.partnumId = pn.objid and ap.sourceTable = "User" and ap.user_id = 1234); In HQL i am using the following : "from partNumber where exists elements (partnum.analystProduct)" The above HQL code generated the SQL query correctly as ...

67. how to translate my below SQL to HIBERNATE HQL?    forum.hibernate.org