sql « Query « JPA Q&A

1. Getting a query intersection in JPA?    stackoverflow.com

I have: "image" 1:Many "imageToTag" Many:1 "tag" I want to issue a query that will return all images that have at least tags [a, b, c]. It's not clear to me how ...

2. How to implement this Query in Hibernate?    stackoverflow.com

I'm converting a legacy iBatis implementation to Hibernate, and for backwards compatibility purposes need to present counts of an object's collections rather than the collections themselves. The original query was:

select ...

3. hibernate sql query    stackoverflow.com

i have a strange problem with a hibernate sql query: The db relations are like follows:

registration has one invoicerecipient
registration has many attendees
i have the persid of an invoicerecipient, so I should get ...

4. Mix HQL and SQL in the same query    stackoverflow.com

I'm trying to mix HQL and SQl in the same query. Like using "from ObjectA obj, TABLE_B tbl where obj.someProp = tbl.COLUMN" because my client will need to modify the query, and learning ...

5. Hibernate and dry-running HQL queries statically    stackoverflow.com

I'd like to "dry-run" Hibernate HQL queries. That is I'd like to know what actual SQL queries Hibernate will execute from given HQL query without actually executing the HQL query against ...

6. Query by age in hql    stackoverflow.com

I have a class User with one field called birthDate which is a java.sql.Date. How do I do a hql query that will retrieve all Users that are between min and max ...

7. hql equivalent query to this sql query    stackoverflow.com

Select top 1 from <tablename>
what is hql query for the above ?

8. Coalesce equivalent in Hibernate Criteria query?    stackoverflow.com

I want to write the following query as a Hibernate Criteria query:

      to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price)
   from order_line ol

9. SQL query to JPA    stackoverflow.com

just started using JPA today, so i'm preety new to it.
How would one say
"SELECT login.ID, userID, logInTime, logOutTime, user.ID AS 'uID', surname FROM login, user WHERE login.userID = user.ID" in ...

10. Hibernate custom sql query for one-to-one?    stackoverflow.com

I'm trying to map a one-to-one association. My DB is like this:

CONTACT                     ...

11. How to view the SQL queries issued by JPA?    stackoverflow.com

When my code issues a call like this:

entityManager.find(Customer.class, customerID);
How can I see the SQL query for this call? Assuming I don't have access to database server to profile/monitor the calls, ...

12. Hibernate query causing lots extra unnecassery queries    stackoverflow.com

I am developing a auction site. The problem lies in 3 entities i use:

  • Product (has zero or many ProductBid)
  • ProductBid (has zero or one ProductBidRejection)
  • ProductBidRejection
I use a hibernate query to get the ...

13. constructing a Hibernate HQL query    stackoverflow.com

How can this SQL query be written in HQL?

select * 
from ( select a.*, rownum rnum
       from ( select * 

14. HQl equivalent of sql query    stackoverflow.com

String SQL_QUERY = "SELECT count(*) FROM (SELECT * FROM Url as U where U.pageType=" + 1 + " group  by U.pageId having count(U.pageId) = 1)";
query = session.createQuery(SQL_QUERY);
I am getting an ...

15. Custom Select for hibernate/JPA    stackoverflow.com

I'm using JPA over Hibernate, with an annotation configuration. I'm looking for doing a custom "select", that would allow me to know the size of a "lazy collection", without getting the collection. I ...

16. SQL Query to HQL Query Conversion    stackoverflow.com

I am new to hibernate and I wnt to know that while converting SQl to its equivalent HQL is it neccessary that the columns used in SQL query must be mapped ...

17. how to execute sql query in hibernate    stackoverflow.com

i have to select the record from table users_roles and i am using hibernate. but i don't want to to make the separate class in java for that , so i am thinking ...

18. ScrollMode.FORWARD_ONLY    stackoverflow.com

Even I've searched on google I want to be very sure about a thing. Does ScrollMode.FORWARD_ONLY means that I will receive the results in the order they are in DB? I have something ...

19. HQL Many to Many how to query    stackoverflow.com

i have two entities : Customer and User. They have a many to many relationship. Assume I have the following data in database.

id  | name 
1   | customer

id ...

20. How to translate sql to hibernate criteria query    stackoverflow.com

How do i translate this pseudo-query into a hibernate criteria query? Given itemID=123, item table has a foreign key reference to category table (catgeoryId), i want to return category coresp to the ...

21. hibernate sql query    stackoverflow.com

I'm trying to implement my very first hibernate sql query

public List<Subjectgrouplist> getSubjectgroups() {
    return hibernateTemplate.find("from subjectgrouplist where id > 0");
I'm trying to return all of the table. I ...

22. SQL to Hibernate query conversion    stackoverflow.com

I came across a problem and can not move forward because I cannot get converted from SQL to HSQL. This is de SQL:

SELECT * FROM live_information i, live_matches_away ma, live_matches_home mh, ...

23. How to write the following SQL query using JPA criteria Query?    stackoverflow.com

    Person p,
    PersonIdentifier pi,
    IdentifierDomain idomain,
    PersonName pName,
    Encounter encounter,

24. Is this JPA query vulnerable to SQL injection?    stackoverflow.com

I have a library that executes a query based on a JPA WHERE statement provided by a caller. I am executing the following JPA query:

public void executeQuery(String jpaWhereStatement) {
String queryString = ...

25. One-to-One or Many-to-One with CF9 ORM in 2 queries    stackoverflow.com

I have two objects

<cfcomponent persistent="true">
    <cfproperty name="id" column="homeFeatureid">
    <cfproperty name="tabTitle">
    <cfproperty name="title">
    <cfproperty name="description">
    <cfproperty name="sort">

26. What is the Hibernate equivalent of "select 1 from DUAL"?    stackoverflow.com

What is the minimal Hibernate query? Is there an equivalent of "select 1 from DUAL" for Hibernate? I am writing a healthcheck function and would like to execute a minimal query, but ...

27. conversion sql query to jpa    stackoverflow.com

I have a query

SELECT d.name, count(e.id) FROM department d LEFT OUTER JOIN employee e on e.department_id = d.id and e.salary > 5000
and how i can convert this to jpa right now i ...

28.  HQL equivalent to this Sql Query    stackoverflow.com

select part.PaId,part.PaName,part.PaPartNumber,part.PaGroup from Part as part,(select 
PaId,PaGroup from Part where PaPartNumber='195-15-12750') as pa where pa.PaId=part.PaID  
or pa.PaGroup=part.PaGroup
what is hql query for the above ? replace all my names with classes and ...

29. Hibernate direct SQL query feature    coderanch.com

30. Hql And sql Queries    coderanch.com

I have a Trip and Leg tables. They are related as : One Trip can have 1 or more Legs. And Leg has fields: DepartTime, ArrivalTime, From, To. I want to Fetch All Trips, and with in each Trip I require only the First Leg (Leg with LeastDeparttime). How could I write a HQL query for this ? My Trip Class ...

31. Executing HQL queries along with SQL Queries    coderanch.com

Friends, I have a requirement in my project like: I have a Stored Procedue which will return a set of tables and considering the result set as a table , I need to execute HQL query on it. How can this be done. Can be better understood with the query which works fine in the SQL IDEs. select * from ( ...

32. JPA: incomplete SQL query    coderanch.com

&lt;openjpa-1.2.1-SNAPSHOT-r422266:707222 fatal store error&gt; org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back. See the nested exceptions for details on the errors that occurred. at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523) ... ... ... 38 more Caused by: &lt;openjpa-1.2.1-SNAPSHOT-r422266:707222 nonfatal user error&gt; org.apache.openjpa.persistence.ArgumentException: Field "entity.SubJob.pk" of "entity.SubJob@577a577a" can not be set to "entity.SubJobPK@84933dcf" value. at org.apache.openjpa.jdbc.meta.strats.HandlerFieldStrategy.insert(HandlerFieldStrategy.java:131) ... ... ...

34. combine hql and sql at the same query?    coderanch.com

hi.. we have a list of object graphs we need to extract, problems we only want to extract it if the of of the properties of the root object matches a record in a table which is not mapped (actually it's jbpm, but it's in a differetn sessionfactory, and we'd like to keep it that way) so is there a way ...

35. HQL for my SQL query    coderanch.com

SQL query : Select * from Lidl_Round lidlRound where 1=1 and lidlRound.lrd_id in (select lrd_id from Lidl_Round_Distr where dbr_id in (select dbr_id from distributor where first_name like '%LOUISE%')) HQL query:Select lidlRound from LidlRound lidlRound , LidlRoundDistr lidlRoundDistr where 1=1 and lidlRound =all elements (select lidlRoundDistr.lidlRound from lidlRoundDistr where lidlRoundDistr.distributor.firstName like '%LOUISE%') Can anyone check whats wrong in the above HQL query? ...

37. Criteria query constants, HQL equivalent sql query?    forum.hibernate.org

How do we write the equivalent of the following HQL as a criteria query? HQL: "from Entity where prop1 = '1' and prop2 = 2" CRITERIA: session.createCriteria(Entity.class) .add(Restrictions.eq("prop1",'1')) .add(Restrictions.eq("prop2",2)) These two are not the same, because the criteria query executes it as a prepared statement. How do we force the HQL or criteria to not execute it as a prepared statement ...

38. sql query in criteria    forum.hibernate.org

hi All, I am using following code for getting distinct question code. try { Session session = HibernateUtil.currentSession(); Query query = session.createSQLQuery("SELECT distinct level1_code,level1_questions from TBLTEP_CLIENT order by level1_code"); list = query.list(); System.out.println("========= list Size = " + list.size()); Iterator iter = list.iterator(); if (!iter.hasNext()) { System.out.println("No question to display."); } while (iter.hasNext()) { TbltepClient tblTepClientObj = (TbltepClient) iter.next(); String questionCode ...

39. Problematic SQL query, can it be optimized manually?    forum.hibernate.org

Hi, I have a "complex/big" SQL query that is rather slow and probably unoptimized. It takes a lot of time for the database to process and return the answer. The tables included in the search are also rather big (> 1 000 000 rows). For testing I've tried to split up the giant SQL question and run the queries separately, which ...

40. translate a simple SQL query to HQL    forum.hibernate.org

SELECT C.* FROM Chambre C ,Hotel H WHERE NOT EXISTS ( SELECT R.NumChambre FROM Reservation R WHERE R.DebutReservation BETWEEN '2011-01-01' AND '2011-01-07' ...

41. HQL equivalent to this Sql Query    forum.hibernate.org

SEVERE: line 1:73: unexpected token: ( Oct 26, 2011 7:23:01 PM org.hibernate.hql.ast.ErrorCounter reportError SEVERE: line 1:98: unexpected token: From unexpected token: ( near line 1, column 73 [Select part.ID,part.Name,part.PartNumber From Depot.Entity.Part as part,(Select par.ID,par.Group From Depot.Entity.Part as par Where par.PartNumber Like ...

42. Why Hibernate query takes much longer than normal sql    forum.hibernate.org

Hi, I have some problem. I use ordinary queries hiberanta on tables, that are not entity. And when I creat SQL cuery using hibernate, this query take long long time more than the same query fired in sql editor ?? String queryStr = "select " + " COUNT(*) AS liczba_dekl, " + " coalesce( sum(coalesce(kwota_deklaracja_1, kwota_deklaracja_2, '0')), 0) AS suma_dekl " ...

43. Get the SQL query    forum.hibernate.org

44. Grouped SQL Query    forum.hibernate.org

Hi, i have a problem creating a SQL Query containing COUNT(*) values: Code: _query = "select {c.*}, " + "count(distinct task.task_id) as {tcount}, " + "count(distinct ih.issue_id) as {ihcount}, " + "count(distinct im.issue_id) as {imcount} " + "from coordinator {c} " + "left ...

45. different sql for same query    forum.hibernate.org

14:10:03,954 DEBUG QueryTranslator:199 - HQL: from com.rcp.client.internal.core.catalogue.Transport as t where t.class = com.rcp.client.internal.core.catalogue.Transport 14:10:03,964 DEBUG QueryTranslator:200 - SQL: select transport0_.id as id, case when transport0__3_.id is not null then 1 when transport0_.id is not null then 0 end as clazz_, transport0_.arrivalTime as arrivalT2_9_, transport0_.departureTime as departur3_9_, transport0_.destination as destinat4_9_, transport0_.origin as origin9_, transport0__1_.company as company6_, transport0__1_.inventory as inventory6_, transport0__1_.unitCost as unitCost6_, ...

46. Name SQL Query    forum.hibernate.org

Hello, When trying to perform the following query Code: SELECT {destination}.DABBR AS {destination.abbreviation}, {destination}.DNAME AS {destination.name} FROM DESTINATION {destination}, PORTS_FOR_DESTINATION {ports_destinations} ...

47. Multiple sql query for simple hql query.    forum.hibernate.org

Hi to all, I use the following hql query: -------------------------- from ValoreDato valoreDato -------------------------- and, on my surprise, I've noticed that Hibernate use an N + 1 sql query while retriving persisted entity: In the first, it load only the IDs of entitys (idValoreDato), and then, for every id, it make a single sql query (so, if i have 1000 ValoreDato ...

48. Multiple SQL queries performed    forum.hibernate.org

Hibernate2 I have only just started using hibernate and am experiencing very large number of hits on the database when I am trying to query just one table. For each Service issue it finds within the query it runs two sub queries to get the region codes and the products which are both one to many. I do not want to ...

49. Migrating queries from SQL to HQL    forum.hibernate.org

50. How to select a cat's name using a SQL query ?    forum.hibernate.org

Can please somebody give me hand and tell me how just to get the name of cat using Standard SQL queries in Hibernate ? My attempts always just return a SQLException // this works String sql = "select {cat.*} from cat {cat}"; Query sqlQuery = s.createSQLQuery(sql, "cat", Cat.class); // this doesn't String sql = "select cat.name as {cat.name} from cat {cat}"; ...

51. Hibernate 3 sql-query not working    forum.hibernate.org

Newbie Joined: Mon Nov 22, 2004 11:41 am Posts: 8 I have had a problem using order by on one of my mappings. So as an attempted solution I have tried upgrading the application to Hib3, and tried to use the sql-query, so that I could use my own sql statement to load the object. For some reason it is not ...

52. Dyanmic Instantiation w/ SQL queries    forum.hibernate.org

53. SQL Query    forum.hibernate.org

I have defined an sql query and defined the hibernate return classes using the mapping tag. However, I wonder if it is possible to get some other value from the query that is not actually a class? I.e. some other string or integer column. select {alias1.*}, {alias2.*}, other.some_value from table1 alias1, table2 alias2, ...

54. Unexpected '?' in the SQL query    forum.hibernate.org

I have problems getting the example program 'Auction' to run successfully using the 'ant eg' command. It succeeds in creating and populating the tables in the MySQL database. But it fails to query the table. Here is the java code from the example: Code: List auctions = s.createQuery( "from AuctionItem item " ...

55. Have to SQL select twice    forum.hibernate.org

Hi, I am describing you the following scenario: 1) Use a php page to enter a new user inside my table. 2) Use my JBOSS application to inquiry my user through the following line of code: private OutMessage login(SuDeTPSession s, UserLoginMsg msg) throws BaseException { User user; String username = (String) msg.getField(UserLoginMsg.USERNAME); String pwd = (String) msg.getField(UserLoginMsg.PWD); try { List users ...

56. CLI0637E QUERY cannot be found & use_sql_comments    forum.hibernate.org

The generated SQL (show_sql=true): [5/10/05 10:09:34:628 EDT] 7fc259d5 SystemOut O Hibernate: /*criteria query*/select this_.CONFIG_ID as CONFIG1_0_, this_.CONFIG_KEY as CONFIG2_7_0_, this_.CONFIG_VALUE as CONFIG3_7_0_ from README.CONFIG this_ where this_.CONFIG_KEY=? [5/10/05 10:09:34:688 EDT] 7fc259d5 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -99999, SQLState: S1000 [5/10/05 10:09:34:698 EDT] 7fc259d5 JDBCException E org.hibernate.util.JDBCExceptionReporter [IBM][JDBC Driver] CLI0637E QUERY cannot be found.

57. Showing SQL Queries On Web Page...    forum.hibernate.org

Since you probably have session per transaction/request. Configure your logger adapter in such way that you know when sql queries are logged in Hibernate. In your apender class: private static ThreadLocal resources = new ThreadLocal(); public static List getQueries() { return (List)resources.get(); } public static void clearResources() { resources.set(null); } private void addQuery(String query) { List queries = (List) resources.get(); if ...

58. Hibernate remove empty spaces to single space in sql-query    forum.hibernate.org

I have defined this query string in filename.hbm.xml When the query print in the console, I have ...

59. sql query - param pb    forum.hibernate.org

Hi, I am new to hibernate. Please help me in resolving the issue I am facing. I have a query which takes values through params. The problem is I am not able to execute the following query as I get Illegalargument exception. The param value is not set inside % %. Is there any thing wrong with using params inside %%? ...

60. how change this sql query to HQL?    forum.hibernate.org

My SQL Query is: select Q1.paAmount, Q2.pAmount From (select sum(pa.amount) paAmount,pa.BookingID paBookingid from package_acct pa, Groups g , Person_Group pg where .... group by pa.BookingID )Q1 Inner Join (SELECT sum(p.amount) pAmount, p.BookingID pbookingid FROM `payments` p where p.ispaid=1 group by p.BookingID )Q2 on Q1.paBookingid=Q2.pBookingID I mean kind of queries that selects from innerjoin of other 2 selects?

61. SQL query produced from HQL    forum.hibernate.org

62. Strange SQL executed from HQL query    forum.hibernate.org

My correct HQL generates correct SQL but wrong SQL gets executed. I have this HQL (doesn't look nice, I know): Code: select count(*) from Pripomienka as p where 1=1 AND p.autor LIKE :externyAutor_ and exists ( from Komentar k ...

63. SQL query    forum.hibernate.org

the following sql is sent to the db using hibernate From com.workpool.directory.Entry entry where ( entry.name like "%Nigeria' s%") OR (entry.refNo like "%Nigeria's%") and (entry.personal="No" or entry.author.id=3) order by entry.name If you do the query in sql it works and displays a result but in hibernate it throws an exception beacause of the apopstrophe. Is there any why to search with ...

64. HQL select is translated wrong in SQL    forum.hibernate.org

public Integer getClientBuyerUsersCount() { ... String query = "select count(u) from UserVO u where ${quickSearchCondition} and u.client.id = :clientId and u.buyer = :buyer and u.status.id is null"; Query q = createQuery(query,listForm); q.setLong("clientId", clientId); q.setParameter("buyer", Boolean.TRUE, Hibernate.YES_NO); ...

65. SQL Query failing with SQLGrammarException    forum.hibernate.org

Newbie Joined: Tue Oct 04, 2005 5:31 pm Posts: 2 I'm in the unfortunate position of building a Hibernate layer on top of a very messy existing database that can't be changed. The code below is from a JUnit TestCase designed to benchmark some fetches so I can test various strategies for loading data. I've settled on a SQL Query to ...

66. support for many-to-many on sql-query    forum.hibernate.org

67. SQL to Criteria Query    forum.hibernate.org


68. sql query to HQL    forum.hibernate.org

i think the "insert into..select" paradimgm goes against the concept of ORM tools where you manage entities and not jsut do insert updates. Also you have to keep in mind that for Hibernate to be efficient it needs to perisit idependently. For e.g. first and second level caching will be possible only if Hibernate is called for persisting the entities. In ...

69. Need reference for framing SQL Queries    forum.hibernate.org

71. sql query to hql    forum.hibernate.org

72. Autoflushing on sql queries    forum.hibernate.org

I have a problem with autoflushing on native SQL queries. The scenario is: 1. I use a complex sql with a lot of joined tables 2. I change some of the associated objects from the result (but not the returned types. 3. I do the same sql as in step 1 The problem is that the autoflush before the sql in ...

73. How to show full SQL query ?    forum.hibernate.org

74. Incorrect SQL from criteria query isNull on one-to-one assoc    forum.hibernate.org

select this_.id as id1_1_, this_.name as name1_1_, this_.entityA_id as entityA3_1_1_, entitya2_.id as id0_0_, entitya2_.name as name0_0_ from EntityB this_ left outer join EntityA entitya2_ on this_.entityA_id=entitya2_.id where this_.entityA_id is null

75. Queries - HQL, SQL, Hibernate    forum.hibernate.org

76. HQL to SQL query mismatch?!    forum.hibernate.org

Only the important part: select distinct r, s.weight, index(r) from ProductDiscountRuleSet s inner join s.rules r where ( (s.from = null and s.until = null) or :now between s.from and s.until ) and ( :customer in elements(r.customers) or :priceFamily in ...

77. simple sql query through Hibernate    forum.hibernate.org

78. simple sql query through Hibernate    forum.hibernate.org

79. Report query: rewrite SQL to HQL or Criteria    forum.hibernate.org

... ...

80. How to rewrite my Hibernate query for the Plain Sql below.    forum.hibernate.org

String queryString = "from Plist play where play.owner = :key " + " and play.type = :str " + "and gprid =Group.groupId" + " and play.bid = :brn order by owner,groupId; queryObject = getSession().createQuery(queryString); queryObject.setLong("brn", bran); queryObject.setParameterList("gprid", (Collection) groupIds); queryObject.setString("key", user); queryObject.setString("str", type);

81. Transfer SQL query to Hibernate Criteria    forum.hibernate.org

82. SQL to Criteria Query    forum.hibernate.org

83. Translate this SQL query to HQL    forum.hibernate.org

That is not useful. I cannot change the original query as I will get the query only at runtime. I need to find out how many records are returned by the original query. What I have to do is wrapping of any query inside the count(*) query. So that I get the no of records returned by the original query.

84. Need Equivalent Hibernate Query for the SQL Query.    forum.hibernate.org

I need a equivalent hibernate criteria for this sql query. Thanks in advance. Code: select p.personId_pk as 'Patient ID',concat(p.lastname,' ',p.middlename,' ',p.firstname) as 'Patient Name', h.name as 'Health Care Facility Name',concat(pr.lastname,' ',pr.middlename,' ',pr.firstname) as 'Practitioner Name', sa.occuranceDateTime as 'Date & Time',nc.shortDescription as 'System Notifications' from person p, person ...

85. Translate simple SQL query to HQL    forum.hibernate.org

87. Get SQL query in Hibernate    forum.hibernate.org

88. [Criteria query] how to adapt the following SQL query?    forum.hibernate.org

Using the standard Order/Bids relationship (an Order has 0-* Bids) as an example: obtain a list of Orders sorted by most to least Bids. How can this be done in Criteria? Code: select orders.* from ( select ...

89. SQL to HQL query    forum.hibernate.org

90. New to Hibernate (SQL queries using Hibernate)    forum.hibernate.org

Hi, I am very very new to Hibernate. I needed to execute the following sql queries using Hibernate Criteria. SELECT s.id, s1.name FROM table s, table s1, table s2 WHERE s1.id = '1' AND s2.name = 'VEST' Could someone please give me some tips on this? Thank you so much in advance!!

91. how to get all sql queries fired in Hibernate application    forum.hibernate.org

1. false this is a setting in a xml mapping configuration. 2. Log and see the queries arriving in your dbms. 3. Get the source code of hibernate and step into execution via a debugger. 4. Turn on tracing logging of hibernate. HTH Thomas PS: 3. is highly recommend for all Hibernate users. It is worth it.

92. How to use sql-select for this query?    forum.hibernate.org

I have a tree-explorer java widget tied to hibernate. To correctly display whether a node has children (so as to display the '+' or not), I would like to use hibernate to find if the node has children, but not return all the children. Can i use for this? If I use select count(*), is there a way to map ...